What is Query Store?
Query Store is a tool to troubleshoot query performance by capturing a range of information about query usage, CPU, memory consumption, I/O and execution time, and retaining every Execution Plan for analysis.
So, if query store stores my query information, what kind of scenarios I could use this information?
- To fix queries with plan choice regressions is one example where to use it. Query Optimizer may decide to choose a different plan and without Query Store would be difficult to identify and fix it.
- Tune top resource consuming queries is much more easier with Query Store because this tool will show the plan is using in a query that has high reads or duration for example.
- To compare workload performance before and after the application change or adding new hardware, create indexes or the next scenario SQL Server updates.
- To keep performance stability during the upgrade to newer SQL Server
- To improve ad hoc workloads. Run the Transact-SQL script bellow to get total number of query texts, queries, and plans in the system and determine how different they are by comparing their query_hash and plan_hash
/*Do cardinality analysis when suspect on ad hoc workloads*/ SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text; SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query; SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM sys.query_store_query; SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan; SELECT COUNT(DISTINCT query_plan_hash) AS CountDifferentPlanRows FROM sys.query_store_plan;