Finding Input Parameters in an Execution Plan

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.

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.

That’s it 😉 hope you find it useful!

Leave a comment

Your email address will not be published. Required fields are marked *