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
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:
This is due to runtime differences in settings like
ANSI_NULLS, etc. I talk about this in the following post:
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:
(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'.)