By: Marios Philippopoulos | Comments (1) | Related: > Query Store
Problem
A query execution plan in SQL Server represents a selection by the query optimizer, among a number of other plans generated and deemed to be the most cost-effective way of running a query. However, plan choice is influenced by a number of factors, such as outdated data-distribution statistics and schema/index changes. As a result, query execution can switch into less optimal plan choices over time and suffer sudden degradation of performance.
The historic record of these plan changes is not maintained in the plan cache; instead, only the latest plan executed per query is kept there. In addition, even that limited information is vulnerable to eviction from the plan cache, due to memory pressure, and is deleted every time the database engine is restarted. As a result, regression analysis of query performance through comparison of different plans over time is not possible through the plan cache. This makes query-performance troubleshooting a difficult and time-consuming exercise.
Solution
The Query Store, a new feature introduced in SQL Server 2016 CTP2, has been designed with the above difficulties in mind. It is a store of query plans and query-statistical information aggregated over time windows and persisted within each monitored database in question. Unlike the plan cache, it can retain multiple plans per query. It keeps a history of query-plan changes, along with the associated performance statistics per plan (duration, logical reads, CPU time, etc.). This information then makes it possible for someone to pick an optimal plan and "force" it, so that subsequent executions of that query are guaranteed to use that specific plan only.
In this tip I will show how to use the query-store feature in SQL Server 2016 to analyze query-plan changes and implement plan forcing, all in the context of a parameter-sniffing scenario. Parameter sniffing affects parameterized queries and is the process by which the query optimizer "sniffs out" the parameters passed and picks the best execution plan based on these parameters. That plan is then stored in the plan cache and re-used in subsequent executions, irrespective of the values of the parameters passed in each execution. Although that is usually not a problem, occasionally it can lead to performance degradation if the cardinality estimates of the set of parameters that first produced the cached plan differ widely from those of other sets of parameter values. Given an atypical set of initial parameters, a parameterized query can then end up being "locked" into a query plan that is less than optimal for the rest of the workload. The access to plan history that the query store provides will allow me to pick the "better" plan among two plans available and force it for subsequent executions. The solution presented here applies to SQL Server 2016 CTP2 and later.
Implementation
For this example I am querying the [Sales].[SalesOrderDetail] table in the AdventureWorks2014 database, upgraded to SQL Server 2016. The following two queries return very different results in terms of numbers of records and execution plans:
----------- --Script 1: ----------- USE [AdventureWorks2014]; GO SELECT * FROM Sales.[SalesOrderDetail] WHERE [ProductID] = 897; -- returns 2 records GO SELECT * FROM Sales.[SalesOrderDetail] WHERE [ProductID] = 870; -- returns 4688 records GO
Here are the "actual" plans returned:
The query optimizer has picked an index seek in the first query, as this is generally more efficient than an index scan when only a few records are involved. However, an index scan is chosen in the second query, since this is deemed more efficient given the much larger result set returned in that case.
Now let's see what happens when the two queries are parameterized:
----------- --Script 2: ----------- DBCC FREEPROCCACHE; GO USE [AdventureWorks2014]; GO EXEC sp_executesql N'SELECT * FROM Sales.[SalesOrderDetail] WHERE [ProductID] = @ProductID;', N'@ProductID INT', @ProductID=897; GO -- returns 2 records EXEC sp_executesql N'SELECT * FROM Sales.[SalesOrderDetail] WHERE [ProductID] = @ProductID;', N'@ProductID INT', @ProductID=870; GO -- returns 4688 records
The plan picked for the first query is cached and re-used for the second query, despite the very different numbers of records returned:
If I now reverse the order of execution in script 2, first clearing the procedure cache and filtering by ProductID 870 first and 897 second, I end up with the other plan for both:
Once again, the plan used for the first query is cached and re-used for the second query; in this case it is the plan with the index scan, irrespective of the individual record sizes returned in each query.
Probing the sys.dm_exec_query_stats dynamic management view returns only the latest plan used, consistent with the fact that no record of previous-plan history has been kept in the plan cache:
-----------
--Script 3:
-----------
SELECT
[Individual Query] =
SUBSTRING
(
qt.[text]
, qs.[statement_start_offset] / 2
, (
CASE WHEN qs.[statement_end_offset] = -1
THEN LEN( CONVERT ( NVARCHAR(MAX), qt.[text] ) ) * 2
ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset]
) / 2
)
, [Parent Query] = qt.[text]
, qp.[query_plan]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text ( qs.[sql_handle] ) AS qt
CROSS APPLY
sys.dm_exec_query_plan ( qs.[plan_handle] ) AS qp
WHERE
qt.[text] = '(@ProductID INT)
SELECT * FROM Sales.[SalesOrderDetail] WHERE [ProductID] = @ProductID;'
GO
Output:
Now let's enable the Query Store by right-clicking on the AdventureWorks2014 database in SQL Server Management Studio (SSMS), selecting Properties and navigating to the Query Store node:
After enabling it, the Query Store emerges as a new folder below Programmability in Object Explorer in SQL Server Management Studio:
Now that the Query Store is enabled I will need to collect some data, so I run a variation of script 2 a few times, first with the ProductID values in one sequence and then the other:
----------- --Script 4: ----------- DBCC FREEPROCCACHE; GO USE [AdventureWorks2014]; GO EXEC sp_executesql N'SELECT * FROM Sales.[SalesOrderDetail] WHERE [ProductID] = @ProductID;',N'@ProductID INT',@ProductID=897; GO -- returns 2 records EXEC sp_executesql N'SELECT * FROM Sales.[SalesOrderDetail] WHERE [ProductID] = @ProductID;',N'@ProductID INT',@ProductID=870; GO -- returns 4688 records -------------------------------------------------------------------------- DBCC FREEPROCCACHE; GO USE [AdventureWorks2014]; GO EXEC sp_executesql N'SELECT * FROM Sales.[SalesOrderDetail] WHERE [ProductID] = @ProductID;',N'@ProductID INT',@ProductID=870; GO -- returns 4688 records EXEC sp_executesql N'SELECT * FROM Sales.[SalesOrderDetail] WHERE [ProductID] = @ProductID;',N'@ProductID INT',@ProductID=897; GO -- returns 2 records
Selecting "Top Resource Consuming Queries" now opens a new tab, showing the top 25 resource consuming queries for AdventureWorks2014 over the past hour. Our query (script 4) is second from the top with query id 7 and the two plans shown by execution time and average duration in the middle pane titled "Plan Summary For Query 7":
Clicking on one of the data points in the middle pane refreshes the bottom pane with the graphical representation on the plan in question. Hovering over a data point brings up detailed statistical information about that plan:
plan forcing in action. The average duration of execution is lower for plan 7 than for plan 8, so it is conceivable that one might want to force plan 7 for all future executions. To do that, I simply click on the "Force Plan" button in the above figure and choose Yes in the popup dialog:
Plan 7 is now showing with a checkmark in the Plan Summary pane, signifying that it is now a forced plan. Re-running script 4 now returns the same plan for all executions, the plan I just forced:
If I now go back now to the "Top Resource Consumers" tab and unforce plan 7 (following similar steps as when I forced it), I see that the behavior reverts to that seen earlier: depending on which ProductID value I filter by first, I get the two different plans back:
Conclusion
In this tip I have shown how to use the Query Store in SQL Server 2016 to analyze plan history and execution statistics and force a plan deemed most optimal for subsequent query executions. This plan-forcing capability resembles that of the USE PLAN hint, available in earlier versions of SQL Server, but with one important difference: no changes in user-application code are needed. Apart from plan analysis, the Query Store has other capabilities that I have not touched on here and promises to be a powerful new tool in the performance-troubleshooting arsenal of database professionals when SQL Server 2016 is officially released next year.
Next Steps
- Download and install SQL Server 2016 (CTP2 or later) in your environment and explore the Query-Store feature on your own.
- Review these related links to learn more about what is new in SQL Server 2016 and about the Query Store and parameter sniffing:
About the author
Marios Philippopoulos has been a SQL Server DBA for over 10 years. He is based in the Toronto area, Canada.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
