Now I want to try that functionality alive and make sure all my expectations are true.
At first will enable Query Store functionality for a database in three easy steps:
Step 1. In SSMS 2016 CTP2 create a test database and go to it's properties:
Step 2. In Properties select the last in the list "Query Store" Option:
Step 3. In "General" section change "Enable" to "True". That will fill all default values.
Same result as Steps 1-2-3 you can get just by running following script:
Now will review the Case:
Problem Description:Company "ABC" has a database where they place thousands of sales' orders on daily basis.
At the same time couple of hundreds clients every few seconds sending heavy queries requests to the SQL Server to monitor these sales orders.
Problem Solution:Because over the time data are changing and after first time server was down, somebody very smart said: "-We have to update statistics!" and somebody added: "-And we will schedule that statistic update to prevent any problems in the future!"
Still have a problem:Even though statistics have been updated on the regular basis, performance problem occurred even more often now! Why?
Problem Simulation:I will try to simulate that particular problem using very small AdventureWorks database. To get some visible results I will use one month as a period.
In preparation I will run following script to Enable Query Store, Clear it (just in case), then re-create one additional index, clear query cache (NEVER do it in PRODUCTION) with "UPDATE STATISTICS" to simulate nightly maintenance job:
Mr. Martin is coming to work pretty early and executes his query first:
You might have noticed that I added "SET STATISTICS IO ON" command to capture IO Stats:
Maintenance job updated statistics and everything works perfectly. Everybody are happy.
Now let's add some data to a period:
Now will re-run Mr. Martin's script. We shouldn't be surprised to see new big numbers:
Now imagine busy server with hundreds of clients....
That means DBA team should use Admin connection to bring server alive.
Guess what they do to fix the problem....
Let's simulate their activity too and after that run Mr. Martin's query again.
What do we see: Number of logical reads dropped to a level SQL Server can handle hundreds of requests.
Did "UPDATE STATISTICS" helped? Yep! ....
Everything looks bright, until the next period when maintenance job updated statistics...
Let's describe the real problem: updating statistics sometimes helps, but sometimes harms.
In order to fix this issue once and for all will use "Query Store".
Open AdventureWorks database where you can notice some new items in the list:
Will briefly review these new reports:
And here is our "winner" in the "Top Resource consuming queries":
Why is that?
The plan #17 was created by SQL Server when there was no Sales orders in July of 2014 and SQL Server decided that it will be less expensive to build a plan using indexing by Order Date and associated Key Lookup. However, when number of records for the period started growing, that plan became more and more expensive.
Updating statistics invalidated that plan and when we run the query again SQL Server created plan #20, which looks for person's Last Name first and then does a full scan over Sales table. For that particular query plan #20 is more appropriate in a long run.
What would you do in these kind of situations in the previous versions of SQL Server:
- You might specify usage of Last Name Index in a hint or use an option to optimize for unknown.
The problem with these old solution is that they are applicable only when you have full control over the SQL code, in case of third party application you have nothing else other than just update statistics when problem occurs.
In SQL Server 2016 we have an option to force particular execution plan to be executed for a query.
Let's press the button "Force Plan" for plan #20.
Now will prove that it is working:
At first Enable actual execution plan (Ctrl+M), Update statistics again and execute our query for the next period - August of 2014:
Here is our plan for the query, which is supposed to be plan #20 forced by "Query Store" manager to be executed. However it isn't. It is very close plan, but it does not have Filtering operation for Sales Data.
That is strange. Let's go to the fourth report provided by query store: "Tracked Queries"
MSDN says: "When a plan is forced for a particular query, every time SQL Server encounters the query, it tries to force the plan in the optimizer. If plan forcing fails, a XEvent is fired and the optimizer is instructed to optimize in the normal way."
I've tried to look at Extended Events and there was nothing. Absolutely no reason for plan #20 to fail.
I do not know what it can be. It can be CTP 2 bug or maybe a feature.
I've clicked on "Compare Plans" button and got all three plans at glance:
So far what we've learned about "Query Store":
1. "Query Store" manager collects all query execution plans along with their statistics and visually present it with pretty friendly interface. I may predict that it will shake position of major monitoring tools unless they figure out better strategies.
2. We can force specific plan to be executed for a query.
3. (Undocumented) It looks like when we force a plan SQL Server tries to improve it if possible.
Other cull "Query Store" features will be in my further posts.