The query execution plan is a map of work which SQL Server is going to perform to execute a query. It provides a lot of useful information like operators, cost, estimated rows, actual rows, predicate details, parallelism, and much more information to troubleshoot the performance issue.

During the troubleshooting of the performance issues, I would strongly request you to check one of the vital information which is “Parameter Complied Value”. There are two terms which we need to get familiar;

1) Input Parameter

The real power of stored procedures (SP) comes when it can handle the differing requests. It means it should allow you to pass different-different value so that it works with differing requests. The parameter value you supply to run a stored procedure called “Input Parameter”.

2) Parameter Compiled Value

When we initially execute a query, the values that we supply for that first execution; will be used by SQL Server to create and compile the execution plan. That value is called “Parameter Complied Value”.

If you analyze the reasons for the compilation of execution plan, you will find one of the usual reasons – SQL Server generates one plan with one set of input values, and then a different plan with a different set of input values because the query is a parameter sensitive query. This is the reason we hear from the end user that a query sometimes it executes quickly, and other times it’s slow. To understand the issue more, you need the parameter complied value. Let’s explore the different-different way to get the parameter compiled value.

Demonstration

I am going to use AdventureworksDW2016CTP3 database table [sales].[SalesOrderDetail] to demonstrate it. Let’s create the below Stored Procedure. Once it created successfully, enable the actual execution plan option (CTRL+M) and run the created stored procedure with input parameter value 870.

USE AdventureworksDW2016CTP3
GO
CREATE PROCEDURE [dbo].[sel_LinetotalByCarrierTrackingNumber]
	@ProductID INT
AS
	SELECT	CarrierTrackingNumber, SUM(LineTotal) 
	FROM sales.SalesOrderDetail 
	WHERE ProductID= @ProductID
	GROUP BY CarrierTrackingNumber
GO
EXEC sel_LinetotalByCarrierTrackingNumber 870

SQL Server generated below execution after running the stored procedure successfully.

Finding the Input Parameters 

• From SQL Server Management Studio

Let’s right click on the “SELECT” operator of the generated execution plan, click on the properties, Go the “Parameter List” category option and expand it; you will see the “Parameter Compiled Value”. In our case, it is showing 870 value this is what we supplied.

• From Execution Plan XML

Right click on the generated execution plan and click on the option “Show Execution Plan XML”. It will open the execution plan in XML format in a new window.

Search the text “CompiledValue”, it will show you the “Parameter Compiled Value”.

• From T-SQL

Microsoft has provided handy T-SQL script – If you suspect, you might be experiencing a parameter sniffing issue, and want to list the parameterized values in query plans quickly. You can follow the below script.

-- Querying the plan cache for parameterization
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), 
    PlanParameters AS (SELECT cp.plan_handle, qp.query_plan, qp.dbid, qp.objectid
                        FROM sys.dm_exec_cached_plans cp (NOLOCK)
                        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
                        WHERE qp.query_plan.exist('//ParameterList')=1
                            AND cp.cacheobjtype = 'Compiled Plan'
                        )
SELECT QUOTENAME(DB_NAME(pp.dbid)) AS database_name,
    ISNULL(OBJECT_NAME(pp.objectid, pp.dbid), 'No_Associated_Object') AS [object_name],
    c2.value('(@Column)[1]','sysname') AS parameter_name,
    c2.value('(@ParameterCompiledValue)[1]','VARCHAR(max)') AS parameter_compiled_value,
    pp.query_plan,
    pp.plan_handle
FROM PlanParameters pp
CROSS APPLY query_plan.nodes('//ParameterList') AS q1(c1)
CROSS APPLY c1.nodes('ColumnReference') as q2(c2)
WHERE pp.dbid > 4 AND pp.dbid < 32767
OPTION(RECOMPILE, MAXDOP 1); 
GO

That’s it 😉 hope you find it useful!