What parameters were used when compiling your execution plan?

~ franklinyamamoto

In this post I will show you a simple way to see what parameters were used when an execution plan was compiled.  This can be valuable when trying to diagnose parameter sniffing problems.

Our Example Plan

The following is a screenshot of our example plan.  You can see the query is parameterized.

How do I find the parameters used?

Simple.  Assuming you already have the execution plan in question.  View the execution plan as XML.  If you do not have the plan as XML, right click on the plan and you’ll get the option to show the Execution Plan XML.

Once the plan is open as XML search for the text <ParameterList>.  Nested within this you will see the ParameterCompiledValues.

Happy tuning!

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store:

SQLGrease can help you quickly identify if maintenance tasks are interfering with your production workload.