Improve SQL Server Performance by Looking at Plan Cache (Part 3)

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’ve examined 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

Be sure to read this full series for more information about how you can improve SQL Server performance by minimizing single-use plans (Part 1) and plans that need tuning (Part 2).