Yesterday we had a CPU performance problem on one of our SQL servers. We use a tool called DPA that clearly showed one query using a ton of CPU (more than any other query by a long shot). However, when I opened Query Store and looked at "Top Resource Consuming Queries by CPU", the query did not show up at all in the same time period. According to DPA, the query started using a new plan which caused the CPU spike. I wanted to go into Query Store and force the old plan but was not able to do so.
I did find this article that talks about this sort of thing happening but I'm not sure what to do with it...
Anyone experience this before and know how to solve it?
The article suggests building a function to find the handle for the query and then use that handle to find it in the query store. Is there a problem with doing something like that?
In the interface, it is possible to force a query plan. I just don't know how to do that through sql scripts. So, even if I find the query in the query store tables, I wouldn't know how to force the old plan. I did rerun statistics on the table in question and it has started using the old plan again. So for now, I'm fine. I'm just worried it will decide to use the bad plan again so I'd like to force the old one.
Ah... got it. Unfortunately, I'll not be of any help there. When I find such queries, I use one of several available methods to prevent the plan from going bad due to parameter sniffing, etc. I also keep column and index stats up to snuff. What that all means is that when there's actually a good plan change, it can change.
Update all stats on all tables in all databases every two weeks. Also have autoupdate stats on. You'd think that would be good enough.
If you have tables that have an ever increasing key, it frequently won't be, even with auto-stats turned on, especially for larger tables. You also have to remember that a particular code run taking a bunch of extra time to execute might just be because the optimizer has determined that the stats need to be updated and, by default, will do so asynchronously prior to the actual execution of your query.