Capture Compiled parameter values from cached execution plans

I am sure we all must have faced performance issues where queries/procedures some days run great, but on other days run much slower. There can be many factor causing the slowness however in most of the cases, this is caused by query execution plans being compiled with non-optimal parameter values.

Basically when a query executes first time, the SQL Server optimiser creates the query plan based on the parameter values passed in the query and for subsequent calls, SQL Server normally reuses cached plan based on the compiled parameter values.

There are different solutions to avoid/reduce the impact of this behaviour, I will cover them in my upcoming articles. For now, I will demonstrate the different ways to capture compiled parameter values from cached plan. To illustrate this, I have created following stored procedure and also executed it couple of times with different parameters.

CREATE PROC CompiledParamterTesting
    @JobTitle VARCHAR(100) ,
    @LoginID VARCHAR(100)
AS
    BEGIN
        SELECT  NationalIDNumber
               ,OrganizationLevel
        FROM    AdventureWorks2012.HumanResources.Employee
        WHERE   BusinessEntityID IN (
                SELECT  BusinessEntityID
                FROM    AdventureWorks2012.HumanResources.Employee
                WHERE   JobTitle = @JobTitle
                        AND LoginID = @LoginID )
    END

EXEC CompiledParamterTesting @JobTitle='Chief Executive Officer',
     @LoginID='adventure-works\ken0'
GO
EXEC CompiledParamterTesting @JobTitle='Research and Development Manager',
     @LoginID='adventure-works\dylan0'
GO

There are four easy ways to capture the parameter values from cached plan:

Capture compiled parameter values using DMVs (preferred): I have created below script to capture compiled parameter values from cached plans. You need to provide “stored procedure name” as an input parameter.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

WITH XMLNAMESPACES   
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')  

SELECT 
		DB_NAME(eqp.dbid) AS [DatabaseName]
		, OBJECT_NAME(eqp.objectid) AS [ProcedureName]
		, cached_time
		, par.o.value('@Column', 'nvarchar(128)') AS Parameterlist
		, par.o.value('@ParameterCompiledValue', 'nvarchar(128)') AS compiledValue     
		, query_plan AS QueryPlan
FROM	sys.dm_exec_procedure_stats
		OUTER APPLY sys.dm_exec_query_plan(plan_handle) AS eqp 
		OUTER APPLY query_plan.nodes('//ParameterList/ColumnReference') AS par(o) 
WHERE	OBJECT_NAME(eqp.objectid, eqp.dbid) = 'CompiledParamterTesting' --Give stored procedure name here

Execute the script and you will see compiled parameter values of stored procedure in results window.

Capture compiled parameter values using SSMS: You can capture the compiled parameter values from the properties of cached or runtime plan as below:

• Turn on “Include Actual Execution Plan”
• Execute stored procedure
• Go to “Execution plan” tab
• Right click on the SELECT operator and select “Properties”

You will see the compiled and runtime parameter values for both @LoginID and @JobTitle in the properties window.

Capture Compiled Parameter values from plan XML: If you have execution plan in XML form, then open the XML and search as below. You will find the compiled parameter value in XML.

Last but not least, we can capture the parameter values using Sentryone Plan Explorer.

Capture Compiled Parameter values using Plan Explorer: This is one of my favourite tool to analyse the complex execution plans. I will give demo of the tool in my future articles but for now, I am only focusing on compiled parameters. You can download this free tool from above mentioned product website. After installing this tool on your workstation, you will see following additional option in your SSMS.

Click “View with SentryOne Plan Explorer” option and then tool will open the plan in a separate window. Click on parameters in the bottom of the tool and then you can find the compiled parameters value.

These are the different ways to capture compiled value of parameters.