May 4, 2009 at 2:02 pm
I query DMV sys.dm_exec_cached_plans to get cached query plans for each procedure in my system, but I do not seem being able to find the actual values of the procedure input parameters for which a specific query plan was compiled and cached.
Is this information somewhere in the XML of the cached plan?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 4, 2009 at 2:18 pm
It's in the XML. Right at the bottom you'll find two lines like this.
<ParameterList>
<ColumnReference Column="@PageNo" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />
<ColumnReference Column="@ForumID" ParameterCompiledValue="(8)" ParameterRuntimeValue="(8)" />
</ParameterList>
If you're looking at an estimated plan, or a plan extracted from cache, you'll only see this, because those plans have no run-time info in them
<ParameterList>
<ColumnReference Column="@PageNo" ParameterCompiledValue="(2)" />
<ColumnReference Column="@ForumID" ParameterCompiledValue="(8)" />
</ParameterList>
You can also see that info in the properties of the top-most operator (select, insert, update, delete) if you don't want to go delving in the XML. See attached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2009 at 2:23 pm
Thank you, I'm actually looking at a cached plan but can't find an XML tag called PARAMETERLIST.
I also looked for "ParameterCompiledValue" but got no hits in the XML.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 4, 2009 at 2:28 pm
Here is what I'm running to get the cached plan for the proc of interest:
DECLARE @procName VARCHAR(1000);
SET @procName = '%xxxxxxxxxxxxx%'
SELECT
P.usecounts
,P.plan_handle
,H.query_plan
,LEFT([sql].[text], 1000) as [text]
FROM
sys.dm_exec_cached_plans P
CROSS APPLY
sys.dm_exec_query_plan(plan_handle) H
OUTER APPLY
sys.dm_exec_sql_text (p.plan_handle) [sql]
WHERE objtype ='Proc' AND LEFT([sql].[text], 1000) LIKE @procName;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 4, 2009 at 2:29 pm
Then that query had no parameters. Either it had variables or constants that weren't auto parameterised.
Those lines should be right at the bottom of the xml, just a couple lines above the end of the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2009 at 2:37 pm
Don't suppose you can post the exec plan for the proc you're struggling with?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2009 at 2:49 pm
GilaMonster (5/4/2009)
Don't suppose you can post the exec plan for the proc you're struggling with?
I'd love to but don't want to get in trouble. 🙂
It's odd, this proc is taking 3 input params, I just don't see why this portion is not listed.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 4, 2009 at 3:29 pm
Marios Philippopoulos (5/4/2009)
It's odd, this proc is taking 3 input params, I just don't see why this portion is not listed.
Not sure either. I just hauled a plan out of cache to check, the same proc who's xml I extracted from earlier and the two lines are there at the bottom of the XML.
Are they optional parameters? If so is it possible that the first call had all of them omitted?
Are they used within queries in the proc or are they used for control flow? I don't know if it'll make a difference, will test.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2009 at 3:52 pm
GilaMonster (5/4/2009)
Marios Philippopoulos (5/4/2009)
It's odd, this proc is taking 3 input params, I just don't see why this portion is not listed.Not sure either. I just hauled a plan out of cache to check, the same proc who's xml I extracted from earlier and the two lines are there at the bottom of the XML.
Are they optional parameters? If so is it possible that the first call had all of them omitted?
Are they used within queries in the proc or are they used for control flow? I don't know if it'll make a difference, will test.
Thank you for looking into it.
2 of the params take default values but the 3rd parameter does not.
If I have something new on this, I will post it.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply