SQL SERVER – Script to Get Compiled Plan with Parameters From Cache

Lots of people often ask me as I share pretty everything on the blog – Do I have any Business Secrets. Actually, the answer is no, I never keep any secrets. I always share everything I know and I want my customers and readers learn along with me. Today I will share another script which I have written which I frequently use with my consulting engagement Comprehensive Database Performance Health Check. In this blog post, I will discuss Script to Get Compiled Plan with Parameters From Cache.

Let me ask you a question – have you ever faced a situation where you have a query which is running very very slow and when you try to run it in the SSMS (SQL Server Management Studio) it runs fast. Have you ever faced a scenario where you run one query at a certain time it runs faster and the same query start running slower after a while?

Well, there can be many reasons for the behavior but one of the primary reason for the same is – Parameter Snoofing. There are many ways to solve Parameter Snoofing but before we solve Parameter Snoofing, we must know that we are actually facing the same situation.

Here is the query which I run for my customers to know what are the queries and stored procedures they are running. Along with the query, I can see how many parameters were passed in and what were the parameters when the query actually ran.

SELECT cvalue.DBName, cvalue.ObjectName,
SUBSTRING(cvalue.text,cvalue.statement_start_offset,
cvalue.statement_end_offset) AS sql_text,
cvalue.query_plan,
pc.compiled.value('@Column', 'nvarchar(128)') AS Parameterlist,
pc.compiled.value('@ParameterCompiledValue', 
'nvarchar(128)') AS [compiled Value]
FROM (SELECT OBJECT_NAME(est.objectid) ObjectName,
DB_NAME(est.dbid) DBName,
eqs.plan_handle,
eqs.query_hash,
est.text, eqp.query_plan,
eqs.statement_start_offset/2 +1 as statement_start_offset,
( CASE WHEN eqs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), est.text)) * 2
ELSE eqs.statement_end_offset END - eqs.statement_start_offset
) / 2 as statement_end_offset,
TRY_CONVERT(XML,SUBSTRING(etqp.query_plan,
CHARINDEX('<ParameterList>',etqp.query_plan), 
CHARINDEX('</ParameterList>',
etqp.query_plan) + LEN('</ParameterList>') - 
CHARINDEX('<ParameterList>',etqp.query_plan) )) AS Parameters
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est
CROSS APPLY sys.dm_exec_text_query_plan(eqs.plan_handle, 
eqs.statement_start_offset, eqs.statement_end_offset) etqp
CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp
WHERE est.ENCRYPTED <> 1) cvalue
OUTER APPLY cvalue.parameters.nodes('//ParameterList/ColumnReference') 
AS pc(compiled)
GO

Please note that if you are running ad-hoc queries you will find the columns database name and object name as empty. However, if you are using Stored Procedure you can for sure see the name of the SP there.

Do you have such scripts in your toolbox? if you have, please share with us. If you do not have, please add a current script to it as it will help you big time to understand optimize parameter sniffing with the help of query cache.

We will cover in the future blog post how to overcome parameter sniffing.

Reference: Pinal Dave (https://blog.sqlauthority.com)