How to find compiled parameter values for SQL Server cached plans

By: Bhavesh Patel   |   Updated: 2017-08-18   |   Comments (8)   |   Related: > Performance Tuning

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:

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.

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

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.

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.

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

Next Steps

• Check out these other query plan related tips.

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips