I spend most of my day tuning SQL Server to make it go faster. I’m usually called in after the fact, when the app has become intolerably slow.
One of the first things I ask is, “What’s changed?”
Nobody ever knows with any sense of accuracy.
I understand – until now, SQL Server hasn’t shipped with any kind of change detection or tracking for common execution plan problems.
How We Manage Queries and Plans in SQL Server 2014
A good performance tuner uses tools like sp_BlitzCache®, Opserver, and Plan Explorer to identify their top resource-using queries and examine their execution plans. They’re intimately familiar with those plans and how they look today, and the tuner makes ongoing efforts to improve the shape of those plans.
Those tools look at execution plans exposed in dynamic management views and functions, the internal instrumentation tables of SQL Server. Unfortunately, those views clear out whenever SQL Server is restarted. Or the plan cache is cleared. Or statistics get updated. Or you come under memory pressure. Or …you get the picture.
If a query suddenly gets a bad plan and rockets to the top of the resource utilization charts, the tuner examines the root cause of the variation, but she’s often unable to see the prior version of the plan. Sometimes a growing amount of data in one of those tables will influence the optimizer into picking a different execution plan, or maybe someone made an ill-advised sp_configure change. Ideally, we work on the query, statistics, indexes, and sp_configure settings to get the plan back where it needs to be.
The hard part here is that we often have no idea what the plan looked like before. Sure, if we’ve got the budget to get fancy, we install performance monitoring software that tracks the execution plans of all our queries over time.
Even when we know what the plan looked like before, it’s not always easy to get SQL Server to change the execution plan. We end up using tricks like plan guides and hints to get the plan we want. I used to see plan guides as a tool of the devil, but I’ve lived long enough to see myself become the villain.
The SQL Server Query Store: Putting Your Plans on Layaway
Enter a recently declassified session at the PASS Summit. On Wednesday, November 5, Conor Cunningham will unveil the Query Store:
Have you ever come in to work only to have the boss come tell you that your main site is down and the database is “broken”? Fixing query performance problems can be rough, especially in high-pressure situations. Microsoft has developed a feature to help customers gain significantly easier insight into production systems and to be able to quickly fix cases where a new plan choice from the query optimizer has undesired performance consequences. This talk introduces the Query Store, explains the architecture, and shows how it can be used to solve real-world performance problems. It will now be possible to ask questions like “show me what query plans have changed since yesterday” and to quickly ask the optimizer to “go back” to the query plan that was working fine for you previously.
This is where things get a little tricky for me as an MVP. If I have any advance knowledge of something, I can’t confirm or deny it, and I certainly can’t blog about it. Buuuut…I can read the abstract, put on my thinking cap, and talk about it in terms of what’s already public.
Reading that abstract, you can infer that:
- SQL Server’s new Query Store will cache queries and execution plans even after they’ve changed (like due to a statistics change on a table)
- These changes may even persist beyond a SQL Server restart (meaning they’ll have to be written to disk somewhere)
- The current and prior plans will be exposed in a DMV for you to query (meaning you might be able to roll your own alerts when a plan changes so you can check out whether it’s better or worse)
So the questions you might ask would be:
- Will this functionality work with read-only databases? Think AlwaysOn Availability Group readable secondaries, or servers used as log shipping reporting boxes.
- Will it work with plans that are not normally cached? Think trivial plans or Optimize for Ad Hoc Workloads.
- What happens if you guide a query into an execution plan, and changes to the database mean the plan is no longer valid? Think dropping indexes.
- Will you be able to see what the query would look like without the frozen plan? Think about adding new indexes or updating stats on a table, and wanting to see whether the new plan would be better or worse without endangering running queries.
- If the Query Store data is written into the database itself, will the execution plans flow through to other servers? Think AlwaysOn AG secondaries and development servers that are restored from production. This is especially tricky if the feature is considered an Enterprise Edition feature, and thereby restricts the ability to restore the database onto a Standard Edition box like compression & index partitioning.
And of course, will it be Enterprise-only or included in Standard Edition, and what will the release date be? Those last ones are outside of Conor’s control, obviously, but you should still ask them. And then tell me what the answer is when you find out, because I don’t know either.
This Is Gonna Be Big.
I love features like this because everybody wins. It doesn’t require changes to existing applications, it doesn’t require attention out of the box, and it just gives more tools to performance tuners like me.
I don’t usually recommend that PASS attendees sit in forward-looking sessions that cover features that may not be in your hands for quite a while. In this case, I’m making an exception: attend Conor’s session. He’s one hell of a smart guy, and he has incredibly elegant ways of answering questions with honesty and insight. I’m publishing this blog post a little early because I want you to start thinking about the feature and how you’d use it in your line of work. That’ll prep you to ask Conor better questions, and you’ll get the most out of this great opportunity.
Enjoy it, jerks, because I’m giving a lightning talk at the same time. I swear, if you do performance tuning and I see you in my talk instead of Conor’s, I’m gonna make you take over my talk so I can go watch Conor.
Update 10:30AM Pacific: after a discussion on Twitter, Argenis Fernandez and I put some money down. If Query Store is fully functional in Standard Edition, I’ll donate $250 to Doctors Without Borders 2.0. If it’s only fully functional in Enterprise Edition, Argenis will donate $250. Everybody wins! Well, except Express Edition users.
30 Comments. Leave new
It will be interesting to see if Microsoft use this to implement something similar to the adaptive cursors and plan base lining available in Oracle from 11g, what is referred to in the SQL Server world as parameter sniffing is referred to as bind sniffing in the Oracle world, plan baselining which is a solution to this in Oracle relies on query plans being stored along with the related bind values.
I was about to say the almost the same thing. It sounds like a first step toward genetic or adaptive optimization. The MVPs probably know more about this than they can say. : )
SQL Server already uses something similar to PostgreSQL’s GQO to eliminate non-viable plans as well as focus on only a subset of plans. The MVPs know as much as the rest of the public (or at least as much as the rest of the public will know as of Wednesday, November 5, 2014).
Thanks, I didn’t know that SQL Server already did that. GQO in SQL Server isn’t something I’ve seen explicitly addressed before. It would still be interesting to hear if the query store is a preliminary step toward adaptive optimization like what Oracle is doing now. I expect Conor probably foresaw this question and addressed it in some manner in his talk, which as a non-PASS attendee I’ll hopefully get to watch someday!
Hi
Have been trying to read up on the topic but it still looks hazy. Is Query Data Store in SQL-2014 same as Query Store in SQL-2016.
I hope the good folks here could shed some light or provide some pointers on the subject.
Many Thanks
Query Data Store
but the topic still seem hazy. I
have noted that SQL 2014
There is no Query Store in SQL Server 2014.
Thanks Brent. In our SQL 2014 server we are noticing a “QDS_SHUTDOWN_QUEUE” wait type. Further based on the below links there appears to be some vague kind of query store happiening in sql 2014.
http://www.sqlskills.com/blogs/bobb/more-xevents-in-sql-server-2014-ctp1-dont-wait-any-longer/
http://www.insidesql.org/blogs/andreaswolter/2014/04/new-extended-events-for-tracing-in-sql-server-2014
Cheers,
Shafeeq.
If it’s available in Web Edition, I’ll wear these on my next beach trip:
http://38.media.tumblr.com/7eab607cdb06fcbfd9b198fcc6a4ec40/tumblr_n1b41hwY3m1toamj8o1_250.gif
HAHAHA, that’s terrifying.
I wish I could be smart with SQL – I dream about it, but then, I am not good in solving performance problems. Looking forward for this feature – mainly to find out, if it can tell me, “currently it takes 10 min to finish” – change this and it will run in 2 min “before running it”.
“I swear, if you do performance tuning and I see you in my talk instead of Conor’s, I’m gonna make you take over my talk so I can go watch Conor.” – hahaha..
Brent, I saw Conor’s talk, any updates on your blog post here? I am very much looking forward to that feature but somehow in my mind I imagine it comes with SQL Server 2016 or later. Conor did say he was pushing to have it in as many editions as possible. You may be out $250 to a good cause when we find out. hahaha
Vijay – no, nothing I can say publicly. Conor’s great, but this wouldn’t be the first time the product team has built something amazing, and the marketing team crippled it. (cough)AlwaysOnAGs(/cough)
How to get customer wise margin using fifo order by fiscaldayid,inventorylogid where customer is ledgerid. Can Any one help me????
Table inward is purchase entry and outward is sales entry….!!
Plz plz help me….!!!!!
Thanks
DECLARE @Inward TABLE(InventoryLogID INT ,FiscalDayID INT,ProductID INT ,Quantity DECIMAL(18,3),Price DECIMAL(18,3))
DECLARE @OutWard TABLE(InventoryLogID INT ,FiscalDayID INT , LedgerID INT ,ProductID INT , Quantity DECIMAL(18,3), Price DECIMAL(18,3))
INSERT INTO @Inward VALUES
(386 ,5381 ,105 ,1100.000 ,32.670),
(385 ,5382 ,105 ,2200.000 ,25.330),
(384 ,5386 ,105 ,200.000 ,60.000)
INSERT INTO @OutWard VALUES
(388 ,5386 ,55 ,105 ,155.000 ,220.00),
(390 ,5386 ,56 ,105 ,15.000 ,15.000),
(398 ,5387 ,NULL ,105 ,1500.000 ,22.000),
(399 ,5387 ,57 ,105 ,120.000 ,18.000),
(441 ,5390 ,55 ,105 ,155.000 ,22.000),
(446 ,5390 ,57 ,105 ,25.000 ,55.000)
Rajendra – we don’t do personalized query help here. You may want to post this on a questions and answers site like http://StackOverflow.com. Good luck on your journey!
SQL Server Query Store sounds great but you don’t have to wait for it.
You can build up a structure based on sys.dm_exec_procedure_stats DMV.
Using this DMV you can store all the SPs execution plans and IO/CPU statistics in a table. Have it scheduled as a job to run every 2-3 mins (5 mins or whatever you want ) and insert if there is newly created exec plans with the new IO/CPU statistics. And then check what SPs execution plan changed.
You can send yourself an email for the SPs which execution plan changed.
Or go a little further and calculate the IO and CPU usage difference in percentage and then send an email.
If you store the execution plan as XML, later on you can see the new and the previous execution plan and compare what changed.
But don’t forget to truncate the table every time SQL server gets restarted(
I choose to move the rows to a history table).
Also you should exclude often compiled SPs (the ones that get compiled every time they get executed).
Sevil – sounds awesome! I look forward to seeing your demos.
I was inspired to try to come up with something. So far my “solution” (ha!) doesn’t interrogate the xml, rather it just lists the recent query plan history. But I thought it would be a good way of highlighting if anything changed in cases of sudden performance problems.
I’d really appreciate if you could have a look and let me know what you think
http://myhumblesqltips.blogspot.co.uk/2015/04/tracking-query-plan-changes.html
All the beat,
Tom
I’ve been doing something similar to this. In fact we use it for monitoring too.
We had a couple of mystery incidents where the entire plan cache was dropped and the next call to a very important proc resulted in a completely out-there query plan. Had to be resolved very quickly and I’ve still not nailed the root cause (I know, I know…I’m busy) but now we at least archive the plans and have monitoring set up on the cached plan creation time/average execution time so that we get a heads-up before the client does.
In fact since recently reading somewhere (probably here) that the parameters used to evaluate the cached plan are also available, I think I’m gonna go add that in so that I can drill into it if it happens again.
You better pencil in that cheque: Conor Cunningham spoke about the Query Store feature at SQL Bits over the weekend. He revealed that it will be available in a future version ( though didn’t specify whether it would be the next version (wink wink)) of both Standard and Enterprise.
In fact when the video is up of this session on the SQL Bits website it’s definitely worth a watch as it answered most of the questions pondered in this post.
Richard – we all agree it’s coming, but the bet was about Standard Edition. Thanks for stopping by!
Yesterday, your bet inflated to $500/per. Put it in writing. 😛
Hahaha, no, the written one is what we’ll stick with.
Brent, it seems you will have to write that check, although you’ll probably do it with a smile knowing the feature will be available in Standard! Nice to hear some good news from MS, specially when my hope of getting at least 256GB RAM for Standard evaporated.
German – make sure to stay current on our blogs. We’ve already written that check. 😀
“Everybody wins! Well, except Express Edition users.”
Looks like Express Edition users are also winners.
see https://msdn.microsoft.com/en-us/library/cc645993.aspx
I don’t know that I’d call them winners.
Great article, I’m really interested to see how this works for me in a real crisis (not looking forward to that crisis of course ;)) I’m also interested in what 3rd party tools might do with this.
Can I force one query plan to different query Id using query store
FYI, the link to “common execution plan problems” is currently broken.
Doh! Thanks for the heads up, fixed.