Mantaining Query Store at the instance level in SQL Server 2016

Hi,

Query Store is a great new feature that comes with SQL Server 2016 and I suspect will have a large acceptance among the DBAs.

As DBAs we need to manage not just one but many/all databases in one (or many) instance(s).

Continue reading “Mantaining Query Store at the instance level in SQL Server 2016”

Advertisements

Query Store New in SQL Server 2016 – WITH RECOMPILE?

Hi,

This is part VIII of several posts about a new feature in SQL Server 2016 called: Query Store!
You can find Part VII here where I showed that using Query Store it’s possible to get the execution plans even for encrypted stored procedures.

If you create a Stored Procedure with the RECOMPILE option, will you have access to its runtime statistics and execution plan?
In today’s post I’ll answer this question comparing DMOs and Query Store 🙂
Continue reading “Query Store New in SQL Server 2016 – WITH RECOMPILE?”

Query Store New in SQL Server 2016 – Encrypted SPs…

Hi,

This is part VII of several posts about a new feature in SQL Server 2016 called: Query Store!
You can find Part VI here where I spoke about execution context settings, their impact in procedure cache and performance and finally how to translate the numeric values of set_options into a nice readable string.

Now for today….what did I have for today?…. Oh yes, I remember!
When a stored procedure is encrypted we cannot get its Execution Plan, right? Well that was true, before Query Store appeared…  but not anymore!

Continue reading “Query Store New in SQL Server 2016 – Encrypted SPs…”

Query Store New in SQL Server 2016 – Part VI

Hi,

This is part VI of several posts about a new feature in SQL Server 2016 called: Query Store!
You can find Part V here where I talked about tracking database names for Ad-hoc queries execution in Query Store vs DMOs.

In todays post, imagine you’re finding two or more execution plans for the same stored procedure simultaneously in the procedure cache! Perhaps they’re being executed with a different set of SET options such as: ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT, etc.

How to troubleshoot this situations?

Continue reading “Query Store New in SQL Server 2016 – Part VI”

Query Store New in SQL Server 2016 – Part V

Hi,

This is part V of several posts about a new feature in SQL Server 2016 called: Query Store!
You can find part IV here in which I showed that Query Store saves the Estimated execution plan (not the Actual one).

Today I’ll show that using Query Store makes it a bit easier for us to know to which database does an executed Ad-hoc query belong to.

Continue reading “Query Store New in SQL Server 2016 – Part V”

Query Store New in SQL Server 2016 – Part IV

Hi,

This is part IV of several posts about a new feature in SQL Server 2016 called: Query Store!

You can find the third one here in which I showed what happens to the plans and its statistics in Q.S.  if the procedure cache is cleared.

Now, for todays post:

Question: Does Query Store save the Actual Execution Plan or the Estimated one?

Answer: The Estimated Execution Plan!

Without further delay, let’s jump into the demo:

Continue reading “Query Store New in SQL Server 2016 – Part IV”

A little bug in Query Store?…

Hello,

This post is not Part IV because I’m actually going to speak about what seems to be a little bug that I came across in the very beginning of this trip around query store and that, all things considered, I think it’s worthwhile mentioning. So here I go…

Continue reading “A little bug in Query Store?…”