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.

Parameters

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”

Plan

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

runtime

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.

runtime

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.

runtime

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.

Planex

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

8 thoughts on “Capture Compiled parameter values from cached execution plans

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: