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)
4 Comments. Leave new
Not work . Getting error Try_convert is not recoginzed
It does work on SQL Server 2017.
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
I don’t see any of the values of the parameters from this query. It shows the query text, and execution plan, but the parameters come in as @p1 etc.