Script: How to Identify Whether Optimizer is Reusing the Plans or Not

This blog post first appeared on SQLMaestros

If you want to identify non parameterized queries that are hogging your SQL Server plan cache then you are at the right place.

sys.dm_exec_query_stats DMV becomes very handy when we are looking out for statistical information related to queries. Upon executing the above DMV, we will see a lot of columns in the output, but for now, we should concentrate on query_hash & sql_handle columns. Queries having the same query_hash but different sql_handle are effectively the same queries that are stored as different objects.

In simple terms, queries having the same query_hash and different sql_handle values denotes that, the optimizer is not resuing the plans.

query_hash gives us the information related to logically identical queries and sql_handle helps us to get the query text.

If we JOIN sys.dm_exec_query_stats and sys.dm_exec_sql_text and then do some aggregations on top of few columns, then we will be able to extract meaningful information and at the same time can find out queries that are not able to reuse the plans.

Here is the query that helps us identify whether optimizer is reusing the plans or not:

SELECT eqs.query_hash AS QueryHash
    ,COUNT(DISTINCT sql_handle) AS CountOfSQLHandles
    ,SUM(execution_count) AS NoOfExecutions  
    ,SUM(eqs.total_logical_reads) AS TotalLogicalReads 
    ,SUM(eqs.total_worker_time) AS TotalCPUTime 
    ,SUM(eqs.total_elapsed_time) AS TotalDuration 
    ,MAX(est.[TEXT]) AS OneSuchQuery 
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est
GROUP BY eqs.query_hash
-- HAVING COUNT(DISTINCT sql_handle) > 2
-- Adding HAVING clause will help us to concentrate frequently executing queries
-- You can play with the number in the HAVING filter as per your requirement

 
We should start investigating the queries that have CountOfSQLHandles > 1.

If the ‘CountOfSQLHandles’ is 5, that means the plan cache contains 5 different sql_handle values with the same query_hash value, which means they are effectively the same queries. ‘OneSuchQuery’ column gives us one of the queries that have different sql_handle but same query_hash.

Now that we have identified the queries that are not reusing the plans, we may think of a couple of options to fix it.

1. How about putting the same code inside a stored procedure and call the procedure from the application instead of adhoc query?
2. How about using Forced Parameterization?

If you know any other technique to implement parameterization, please let me know in the comments section.

If you are not allowed to make any changes to the code or application, then how about considering to enable ‘Optimizing For Ad hoc Workloads’ option?

See you soon with another script.

This post is part of The Scripts Garage. You can find all scripts at The Scripts Garage