September 3, 2012 at 6:56 am
Hi,
When querying sys.dm_exec_procedure_stats, I noticed that some procedures appear multiple times in the result set because they have different cached plans.
When joining with sys.dm_exec_plan_attributes, I could see that the difference between cache keys is sometimes the value in the column optional_spid.
However, I could not find precise information on that column. Would anyone know how to write a simple stored proc and execute it in such a way that it appears more than once in sys.dm_exec_procedure_stats with different values in the sys.dm_exec_plan_attributes.optional_spid column?
Thank you
September 3, 2012 at 8:24 am
calling the sproc with different settings on you connection will be enough for sqlserver to generate another plan. ( ansi settings )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 4, 2012 at 2:00 am
Thank you for your response, Johan, but I'm really looking at how to generate a duplicate plan because of a difference in the optional_spid column, not the SET options.
September 4, 2012 at 2:55 am
I only see that column being non-zero for sproc
msdbsp_delete_job_references
At this moment I cannot tell you what that actually means or why it defers from the others.
did you have a look at http://ask.sqlservercentral.com/questions/41100/schema-qualifying-temporary-objects.html
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 4, 2012 at 6:33 am
Hi Johan,
yes, I had a look at that and now I understand.
Here's a way to reproduce:
On connection 1, execute the following:
[font="Courier New"]CREATE PROCEDURE dbo.TestProc
AS
INSERT #t1
SELECT 1 AS N;
GO
CREATE TABLE #t1(N INT NOT NULL);
EXEC TestProc;[/font]
On connection 2, execute this:
[font="Courier New"]CREATE TABLE #t1(N INT NOT NULL);
EXEC TestProc;[/font]
Then run this to see the cached plans for the stored proc:
[font="Courier New"]SELECT OBJECT_NAME(object_id, database_id) AS Name, *
FROM sys.dm_exec_procedure_stats
WHERE OBJECT_NAME(object_id, database_id) = N'TestProc'
ORDER BY OBJECT_NAME(object_id, database_id)[/font]
You should see 2 cached plans. To see what differs in the cache key, run this:
[font="Courier New"]SELECT OBJECT_NAME(object_id, database_id) AS Name, a.*
FROM sys.dm_exec_procedure_stats s
cross apply sys.dm_exec_query_plan(s.plan_handle) p
cross apply sys.dm_exec_plan_attributes(s.plan_handle) a
WHERE OBJECT_NAME(object_id, database_id) = N'TestProc'
AND a.is_cache_key = 1
ORDER BY a.attribute, s.plan_handle[/font]
This reveals that the optional_spid column is used because the temporary table in the proc refers to an existing outer temporary table, therefore the plan cannot be reused.
Thanks for your help.
Laurent
September 4, 2012 at 7:22 am
Thank you for the feedback.
Now I get it too :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply