Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to find compiled parameter values for SQL Server cached plans


By:   |   Read Comments (5)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


Problem

In SQL Server, the first time a query executes the SQL Server optimizer builds the query plan based on the parameter values passed in the query.  For subsequent calls, the query plan is generally reused from the cached plan based on the queries compiled parameter values. Sometimes the cached plan is not optimal when different values are passed, so in this tip I will demonstrate how to get the compiled query parameter values using both SQL Server Management Studio (SSMS) and DMVs.

Solution

I prepared a sample script to show how we can find this information from the SSMS GUI and using T-SQL. 

Script to Create SQL Server Parameter Sniffing Test

This creates a new database, table, inserts some data and creates a stored procedure.

USE Master
GO

CREATE DATABASE MyTest
GO

USE MyTest
GO

CREATE TABLE Customer
(
  id INT PRIMARY KEY IDENTITY(1,1),
  custcode INT,
  custName VARCHAR(200),
  contactNumber INT,
  address VARCHAR(MAX)
)
GO

INSERT INTO Customer
Select '101','BVP','111111111','At-Test area'
UNION ALL
Select '102','ABC','222222222','At-Test area'
GO

CREATE PROCEDURE USP_GetCustomer
(
  @custID INT,
  @custName VARCHAR(200)
)
AS
BEGIN
   SELECT 
     id,
     custcode,
     custName,
     contactNumber,
     address
  FROM
     Customer
  WHERE ( custcode = @custID OR custName = @custName)
END
GO

Capture Compiled Parameters Using SQL Server Management Studio

Now I am going to execute the procedure “USP_GetCustomer” for the first time as follows.

exec USP_GetCustomer '101','' 

The procedure is executed and the query was compiled using these parameter values @custID = 101 and @custName = ''.  Here are the results:

execute procedure USP_GetCustomer - Description: Proceure is executed on first time

Now I am going to run this again with different parameters, but also include the actual execution plan from SSMS.  To do this, from the SSMS menus select Query > Include Actual Execution Plan or just use CTRL + M.

Now I am going to execute the same procedure with these different parameters.

exec USP_GetCustomer 0,'BVP' 

The results are below.

execute procedure USP_GetCustomer - Description: The Procedure is executed on second time with different parameters

Now check the Execution plan tab and right click on the SELECT operator and select Properties.

Procedure executed with plan details - Description: Determine the plan cached details from procedure

The Properties window opens and we can see the compiled and runtime parameter values for both @custID and @custName. We can see the complied parameter values match what was run the first time the stored procedure was run as compared to the new runtime values for the second execution.

exploring the properties - Description: Based on operator use , get compiled parameter value from the propery.

 

Capture Compiled Parameters Using T-SQL

Now I am going to get the compiled parameter values using a script.

 IF OBJECT_ID('tempdb..#compiledValue') IS NOT NULL
    DROP TABLE #compiledValue
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  OBJECT_NAME(est.objectid) ObjectName,
   DB_NAME(est.dbid) DBName,
   eqs.plan_handle,
   eqs.query_hash,
   est.text,
   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
INTO #compiledValue
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
WHERE est.ENCRYPTED <> 1
   AND OBJECT_NAME(est.objectid) = 'USP_GetCustomer'
   AND est.dbid = db_id('MyTest')

SELECT cvalue.ObjectName,
  cvalue.plan_handle,
  cvalue.query_hash,
  SUBSTRING(cvalue.text,cvalue.statement_start_offset,cvalue.statement_end_offset) AS sql_text,
  pc.compiled.value('@Column', 'nvarchar(128)') AS Parameterlist,
  pc.compiled.value('@ParameterCompiledValue', 'nvarchar(128)') AS [compiled Value]
FROM #compiledValue cvalue
OUTER APPLY cvalue.parameters.nodes('//ParameterList/ColumnReference') AS pc(compiled)

GO

In this query I have used system DMVs (dynamic management views). This DMV sys.dm_exec_query_stats is used for getting query statistics. In addition, the sys.dm_exec_text_query_plan DMV shows the query plan based on the plan_handle. Using the sys.dm_exec_sql_text DMV, we can get the query text details from the cache based on the sql_handle.

I am also running this just where the object name matches the stored procedure name, so I can filter out all of the other queries.

Executed prepared query in mytest DB - Description: I have prepared query for get compiled parameter value and executed it

As a result, I have the compiled parameter values as shown in the above image.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel has been working with SQL Server since 2007 as a Senior DBA.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, September 05, 2017 - 12:17:07 AM - K.B Patel Back To Top

Bhavesh, It's really very nice tips to getting compiled value. Recently I have faced issue on production related parameter sniffing. I was finding compiled parameter using it and solved out this issue. Many Thanks....

 


Tuesday, August 29, 2017 - 4:22:32 PM - Jeremy Kadlec Back To Top

Everyone,

The main script in this tip has been updated with a Temp Table to address the contention concerns.

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, August 25, 2017 - 3:38:44 AM - Bhavesh patel Back To Top

If you face any difficulty to getting compiled value in terms of performance so you can also add DB filter as well as isolation read uncommitted into it.

I have attached following query with these two options.

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

  t1.ObjectName,

  t1.plan_handle,

  t1.query_hash,

  t1.sql_text,

  pc.r.value('@Column', 'nvarchar(128)') AS Parameterlist,

  pc.r.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,

     SUBSTRING (est.text,eqs.statement_start_offset/2 +1,                                    

                 (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 sql_text,

     est.text as Whole_Batch,

     TRY_CONVERT(XML,SUBSTRING(etqp.query_plan,CHARINDEX('<ParameterList>',etqp.query_plan), CHARINDEX('</ParameterList>',etqp.query_plan) + LEN('</ParameterList>') - CHARINDEX('<ParameterList>',etqp.query_plan) )) 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

   WHERE est.ENCRYPTED <> 1

     AND OBJECT_NAME(est.objectid) = 'USP_GetCustomer'

AND est.dbid = db_id('MyTest')

 ) t1

OUTER APPLY t1.parameters.nodes('//ParameterList/ColumnReference') AS pc(r)

 


Friday, August 18, 2017 - 6:19:11 PM - Randy Pitkin Back To Top

 The Cost seems prohibitive in any significant environment

The query has been canceled because the estimated cost of this query (2557181)

You should not use this method on Instances serving customers

 


Friday, August 18, 2017 - 8:13:34 AM - Ankit Back To Top

very Intresting Topic and information.

i have no idea about this.

thank you for Sharing.


Learn more about SQL Server tools