data-konzept

'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.'...

http://www.sqlservercentral.com/blogs/sql-geek/2017/11/11/finding-input-parameters-in-a-execution-plan/