June 4, 2010 at 8:35 am
I just noticed that there are 12(!) cached exec plans for a single stored procedure in my database.
Here is the query I used to get this info:
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;
Is this a reflection of widely diverse parameters being passed to the sproc, and how should I go about fixing this?
This doesn't seem like an efficient use of the procedure cache.
Thanks for any suggestions.
__________________________________________________________________________________
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]
June 4, 2010 at 8:40 am
Are all 12 plan handles different?
Could you perhaps attach the results as an excel sheet? Without seeing results, hard to tell you why there are multiple rows. Can you also include the following column: cacheobjtype
If they are 12 plans, it'll be a result of different SET options or possibly different user contexts, not different parameter values
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
June 4, 2010 at 8:59 am
GilaMonster (6/4/2010)
Are all 12 plan handles different?Could you perhaps attach the results as an excel sheet? Without seeing results, hard to tell you why there are multiple rows. Can you also include the following column: cacheobjtype
If they are 12 plans, it'll be a result of different SET options or possibly different user contexts, not different parameter values
I'm a little hesitant to post the exec plans for fear of exposing company proprietary information, but, suffice it to say, these are all different plans.
I copied the results of the query and pasted them on a query window and no 2 lines look the same.
__________________________________________________________________________________
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]
June 4, 2010 at 9:11 am
Cacheobjtype for all plans is "Compiled 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]
June 4, 2010 at 9:19 am
Edit: On second thoughts....
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
June 4, 2010 at 9:30 am
What does this return? Should be safe to post, there's no info on the query or plan anywhere in there.
SELECT cp.plan_handle, cp.cacheobjtype, cp.refcounts, cp.usecounts,
pa.attribute, pa.value
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE objtype ='Proc' AND LEFT([sql].[text], 1000) LIKE @procName
AND attribute in ('user_id','set_options');
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
June 4, 2010 at 9:55 am
GilaMonster (6/4/2010)
What does this return? Should be safe to post, there's no info on the query or plan anywhere in there.
SELECT cp.plan_handle, cp.cacheobjtype, cp.refcounts, cp.usecounts,pa.attribute, pa.value
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE objtype ='Proc' AND LEFT([sql].[text], 1000) LIKE @procName
AND attribute in ('user_id','set_options');
Thanks, here is the result (attached).
__________________________________________________________________________________
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]
June 4, 2010 at 10:22 am
Interesting.
What I suggest is that you go through all of the attributes in sys.dm_exec_plan_attributes, one of the columns in there is 'is_cache_key'. If any attributes with that column = 1 are different between two plans, that's enough to result in separate cached plans
Does this proc have dynamic SQL within it?
Could I perhaps ask for one more data set?
SELECT cp.plan_handle, cp.cacheobjtype, cp.refcounts, cp.usecounts,
pa.attribute, pa.value, pa.is_cache_key,
CHECKSUM(st.text) AS Cksum
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE objtype ='Proc' /*AND LEFT([sql].[text], 1000) LIKE @procName*/
AND attribute in ('user_id','set_options', 'objectid', 'dbid', 'dbid_execute');
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
June 4, 2010 at 10:32 am
Sure, pls give me some time to get you the info, much appreciated.
__________________________________________________________________________________
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]
June 4, 2010 at 11:36 am
GilaMonster (6/4/2010)
Interesting.What I suggest is that you go through all of the attributes in sys.dm_exec_plan_attributes, one of the columns in there is 'is_cache_key'. If any attributes with that column = 1 are different between two plans, that's enough to result in separate cached plans
Does this proc have dynamic SQL within it?
Could I perhaps ask for one more data set?
SELECT cp.plan_handle, cp.cacheobjtype, cp.refcounts, cp.usecounts,pa.attribute, pa.value, pa.is_cache_key,
CHECKSUM(st.text) AS Cksum
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE objtype ='Proc' /*AND LEFT([sql].[text], 1000) LIKE @procName*/
AND attribute in ('user_id','set_options', 'objectid', 'dbid', 'dbid_execute');
Results are attached.
__________________________________________________________________________________
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]
June 4, 2010 at 11:46 am
Ack, part of the where clause was filtered out, hence that was useless. Sorry.
SELECT cp.plan_handle, cp.cacheobjtype, cp.refcounts, cp.usecounts,
pa.attribute, pa.value, pa.is_cache_key,
CHECKSUM(st.text) AS Cksum
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE objtype ='Proc' AND LEFT([sql].[text], 1000) LIKE @procName
AND attribute in ('user_id','set_options', 'objectid', 'dbid', 'dbid_execute');
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply