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.

SQL SERVER - Script to Get Compiled Plan with Parameters From Cache compiledplan

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.

SQL SERVER - Script to Get Compiled Plan with Parameters From Cache querywithparameters

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)

, , , , , , ,
Previous Post
SQL SERVER 2008 R2 – I Support You Beyond July 9 2019
Next Post
SQL SERVER – How Do I Evaluate Monitoring Tools?

Related Posts

3 Comments. Leave new

  • Not work . Getting error Try_convert is not recoginzed

    Reply
  • Compile parameter values are important. Be aware that on systems with huge memory, the plan cache can also be huge (somewhat more than 5% of memory). In environments without SQL discipline, the plan cache could have millions of entries. Using SQL to parse the XML plan can be slow. First test some filter condition on the query to sys.dm_exec_query_stats or the proc/fn equiv before doing the plan parse.
    In my ExecStats tool on qdpma . com, I bring the plan to a C# client app for parsing thousands of plans

    Reply

Leave a Reply

Menu