You can use the information in the SQL Server plan cache to research performance issues and investigate opportunities for performance improvement.
In this three-part article series, we’ll examine several important opportunities you have in mining information from the plan cache, including eliminating single-use plans (Part 1) and finding plans that can be easily tuned (Part 2). In this third part, we’ll look at how, starting in SQL Server 2008, you can examine the plan cache to quickly identify plans that are similar and may be optimized.
Finding similar plans in the plan cache
SQL Server 2008 introduced the query hash, which allows grouping of queries that are similar in function but that different in literal values. Knowing this can help you maximize query plan reuse.
For example, in the following simple queries, the queries differ only by the literal value being searched for:
SELECT P.FirstName, P.LastName FROM Person.Person AS P WHERE P.FirstName = 'Amanda' GO SELECT P.FirstName, P.LastName FROM Person.Person AS P WHERE P.FirstName = 'Logan' GO
Each of these two queries generated a separate plan in the plan cache. You can see the query hash for each query using the following example code:
SELECT st.text, qs.query_hash FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st WHERE st.text = 'SELECT P.FirstName, P.LastName FROM Person.Person AS P WHERE P.FirstName = ''Amanda'' ' OR st.text = 'SELECT P.FirstName, P.LastName FROM Person.Person AS P WHERE P.FirstName = ''Logan'' ' GO
When you issue this code, you should see a result set like this:
Next, you can use the query hash to find all the queries that are logically equivalent, as shown in the sample code below:
SELECT COUNT(*) AS [Count], query_stats.query_hash, query_stats.statement_text AS [Text] FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash, query_stats.statement_text ORDER BY 1 DESC
The result is a list of hash values and associated SQL text, shown in the figure below, and you can see the frequency with which some statements are being executed over time.
Replace similar queries with parameterized statements
If you have many statements with similar query hash and query plan hash values, you can create one parameterized statement to use instead. A parameterized statement allows the engine to store and then reuse one plan instead of many separate plans, and this is a good thing. In general, the more plans you can store, the fewer will need to be compiled, and the more performance will improve.
If creating one parameterized query isn’t an option because you can’t alter the code, then you might consider using plan guides to maximize plan reuse. Read more about the use of plan guides at Specifying Query Parameterization by Using Plan Guides.
More opportunities in the plan cache: single-use plans and plans that need tuning