November 24, 2014 at 12:05 pm
Can someone throw some light please? I ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. I would like to find out why and is that a bad thing? When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called? Thank you!
SELECT o.name, o.object_id,
ps.last_execution_time ,
ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds,
ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds,
ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds
FROM sys.dm_exec_procedure_stats ps
INNER JOIN
sys.objects o
ON ps.object_id = o.object_id
WHERE DB_NAME(ps.database_id) = 'XXXX'
ORDER BY
o.name
OUTPUT
nameobject_idlast_execution_timelast_elapsed_timeINSecondsmin_elapsed_timeINSecondsmax_elapsed_timeINSeconds
InsertItems5518812332014-11-20 14:12:31.3830.0289580.0020060.196040
InsertItems5518812332014-11-20 14:11:32.7470.0140130.0140130.014013
InsertItems5518812332014-11-20 14:05:18.2570.0370010.0099990.037001
Select * from sys.dm_exec_procedure_stats where object_id = 551881233
OUTPUT:
database_idobject_idtypetype_descsql_handleplan_handlecached_timelast_execution_timeexecution_counttotal_worker_timelast_worker_timemin_worker_time
8551881233P SQL_STORED_PROCEDURE0x03000800110AE520C021B300D2A30000010000000000000000000000000000000000000000000000000000000x05000800110AE520A03B330404000000010000000000000000000000000000000000000000000000000000002014-11-20 14:11:47.2632014-11-20 14:12:31.3833999939582006
8551881233P SQL_STORED_PROCEDURE0x03000800110AE520C021B300D2A30000010000000000000000000000000000000000000000000000000000000x05000800110AE520103A330404000000010000000000000000000000000000000000000000000000000000002014-11-20 14:11:32.7372014-11-20 14:11:32.7471301530153015
8551881233P SQL_STORED_PROCEDURE0x03000800110AE520C021B300D2A30000010000000000000000000000000000000000000000000000000000000x05000800110AE520D067733B04000000010000000000000000000000000000000000000000000000000000002014-11-20 14:03:23.9172014-11-20 14:05:18.2573999329992000
November 24, 2014 at 1:47 pm
i believe if you call the same procedure with different ansi settings(ansi_null, etc), you get a different plan cache for each combination, which is created as they are called the first time.
so you might se a cache plan that is for a developer's SSMS settings, that has different cache plan that one generated form an applications.
SELECT SESSIONPROPERTY('ANSI_NULLS') AS IsAnsiNulls,
SESSIONPROPERTY('ANSI_PADDING') AS IsAnsiPadding,
SESSIONPROPERTY('ANSI_WARNINGS') As isAnsiWarnings,
SESSIONPROPERTY('ARITHABORT') As isAritribort,
SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS IsConcatNullYierldNull,
SESSIONPROPERTY('NUMERIC_ROUNDABORT') As IsNumericAritribort,
SESSIONPROPERTY('QUOTED_IDENTIFIER') As IsQuotedIdentifier
Lowell
November 24, 2014 at 2:20 pm
Thank you very much. That makes sense.
November 25, 2014 at 3:10 pm
You can also get different plans if the users executing the code don't have the same default schema - and the objects in the code are not schema qualified.
SELECT * FROM MyTable;
Called by user Joe with default schema Joe - will get one plan.
Called by user Jim with default schema Jim - will get a separate plan.
...
SELECT * FROM dbo.MyTable;
Called by user Joe with default schema Joe - will get one plan.
Called by user Jim with default schema Jim - will get same plan as Joe.
...
Execute MyStoredProcedure vs. Execute dbo.MyStoredProcedure can also generate separate plans for each user if those users have different default schema.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 25, 2014 at 3:13 pm
Thank you for the pointer. I will check to make sure the db objects referenced in the sprocs are schema qualified.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply