sys.dm_exec_procedure_stats DMV contents contradict BOL

Question

I am doing some analysis of long running stored procedures and have come across something of an anomaly, according to books online sys.dm_exec_procedure_stats should only display one row per cached stored procedure plan, however there is one User Stored Procedure that appears twice in the table.

Both 'versions' of this stored procedure have wildly different execution times / counts.

Is this just due to different plans being cached and used? If so I didn't realise that sql server cached multiple plans for the same object_id, I thought it was sort of a one-in one-out deal? Plus if this is the case, it seems to contradict BOLS definition of the DMV?

Further to this, if there are two versions of the plan in use, how can I tell what plan is being used in what situations?

Anyone else encountered this?

Answer 1

Just working from Paul's comment. The key difference between the statement in Books Online and your interpretation:

It says: ... one row per cached stored procedure plan.

You read: ... one row per cached stored procedure.

You can check which plan attributes are different and thus leading to different copies of the plan by looking at the contents of the attributes DMV, ordering by attribute name. You should see at least one attribute where two rows for the same text have different values in the value column.

SELECT t.[text], pa.attribute, pa.value
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
WHERE LOWER(t.[text]) LIKE N'%create%procedure%procedure_name%'
  AND LOWER(t.[text]) NOT LIKE N'%sys.dm%'
  AND pa.is_cache_key = 1
ORDER BY t.[text], pa.attribute;

Typically, you will see differences in one of these two attributes:

set_options

This is due to runtime differences in settings like ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, etc. I talk about this in the following post:

• Multiple Plans for an "Identical" Query

user_id

This isn't really the user running the query, but rather it is due to two different users having different default schemas (the value is actually schema_id), and at least one of them is calling the stored procedure without the schema prefix (SQL Server caches a different plan because the search path is different - it must check the default schema first). I talk about this in the above post as well as the following:

• Bad habits to kick : avoiding the schema prefix

(Also, be sure you are looking at the overarching plan for the procedure, not two plans for two completely different statements within the stored procedure, e.g. WHERE p.objtype = N'Proc'.)