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.
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”
You will see the compiled and runtime parameter values for both @LoginID and @JobTitle in the properties window.
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.
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.
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.
These are the different ways to capture compiled value of parameters.
Very well explained.
Waiting for next article.
excellent tips. this is very useful in troubleshooting performance issues.
A must read for those who are looking forward to troubleshoot performance issues.. 👍
Thanks guys. There are other alternatives as well, but I find it quite useful so thought to share with everyone
Another great Article on Performance troubleshooting
I got this website from my buddy who told me on the
topic of this website and now this time I
am browsing this site and reading very informative articles here.
Hallo Amazing! Its really awesome paragraph, I have got much clear idea regarding from this post. vielen dank
Your mode of telling all in this post is in fact
nice, all be able to effortlessly be aware of it, Thanks