Whenever I see some query regression in Query Store due to SQL going nuts and using an execution plan which is obviously not the best available, I wonder why it is doing that. But since I don’t have access to its source code (and even if I had, I’d probably wouldn’t fully understand it), the only thing I could do was either force the better plan and hope SQL will use it (which doesn’t always do: there are certain limitations to that), or update the statistics on all the tables involved in the query and hope for the best.
This was a very manual operation:
- Open the query text.
- Identify all the tables involved in the query, which may include accessing the definition of the views it accesses, and that’s if the query text is not truncated and some of them are lost due to that.
- Find all the statistics on the identified tables that have a certain small percentage of sample rate or had a high percentage of rows updated since the statistics were last updated, rendering them obsolete.
- Write a script to update them based using a sample rate appropriate based on the row count of the table.
But since the execution plans detail all statistics used by SQL Engine to generate the plan and they are kept in Query Store, I decided to extract those details from them.
The new tool added to the QDSToolBox, StatisticsUsed, does just that: given a query ID (or list of them), or even a whole object, generates a table with the details of the statistics used. Based on factors like when were the statistics last updated, the row count on the table or the percentage of rows changed since then, it generates the appropriate UPDATE STATS command. This is how the final result would look like:
