sp_Blitz, >1000 plans for CREATE PROCEDURE

Question

I'm using Brent Ozar's sp_Blitz, and one of its results is this:

Many Plans for One Query

1146 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.

The link in the result has this query:

SELECT q.PlanCount,
q.DistinctPlanCount,
qs.query_hash,
st.text AS QueryText,
qp.query_plan AS QueryPlan
FROM ( SELECT query_hash,
COUNT(DISTINCT(query_hash)) AS DistinctPlanCount,
COUNT(query_hash) AS PlanCount
FROM sys.dm_exec_query_stats
GROUP BY query_hash
) AS q
JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY q.PlanCount DESC, q.query_hash;

...which shows the queries with the highest number of plans.

When I run this, one of the top results I get (with about 1150 plans for the same query hash) baffles me:

Maybe it's a bit hard to recognize on the screenshot - this is a complete CREATE PROCEDURE definition including comments, like this:

-- =============================================
-- Author:      my username
-- Create date: 14.09.2017
-- Description: blah
-- =============================================
CREATE PROCEDURE [dbo].[spCalcSomeStuff]
    @Orders OrderList readonly
AS
BEGIN

    -- do stuff (see below for more details what the SP does)

END

Plus, they're all the same.
I copied QueryText from multiple rows into text files and made diffs, and they're all 100% identical.

Any idea why this happens?

Our database objects are in source control, so I know that this particular SP was last changed about two months ago. Even if we would delete and recreate it multiple times a day (which we don't do), I still don't understand why SQL Server creates that many plans for identical queries.

---

There's nothing special about this SP, except that it's one of the very few we have which uses Table-Valued Parameters.

Here's a simplified version of what the SP does:

create table #tmp
(
    [...]
)

insert into #tmp (...)
select ...
from tbOrders o
inner join @Orders x on o.Col1 = x.Col1 and o.Col2 = x.Col2


-- about 15 updates like this one (but more complex), 
-- getting stuff from lots of different tables:
update t
set foo = o.foo
from #tmp t
inner join OtherTable o on t.bar = o.bar

-- and a few very simple updates:
update #tmp set ordertype = 'A' where producttype = 4
update #tmp set ordertype = 'B' where producttype = 2

select * from #tmp

When I run Aaron Bertrand's modified query, it returns the two simple UPDATE statements at the end.

I.e. I still get ~1150 rows for the same query hash, and half of them have this query text:

update #tmp set ordertype = 'A' where producttype = 4

...and the others have this one:

update #tmp set ordertype = 'B' where producttype = 2

Answer 1

Question 1: "What's with CREATE PROCEDURE?!?" When you execute a stored procedure, SQL Server stores the entire text of the stored procedure as the thing you called.

You weren't CREATING the stored procedure - you were only executing it - but this can be a little confusing for folks who are just getting started analyzing the plan cache.

So hey, you're now past that hurdle! Yay, you!

Question 2: "How can 1 stored procedure have multiple plans?" Without seeing the full text of it, it's hard to tell, but I'd start with Erland Sommarskog's epic post, Slow in the App, Fast in SSMS. In particular, check the section titled Different Plans for Different Settings.

I actually don't think that's the problem - I'm betting there's something dynamic inside the proc text - but I understand that you don't want to post your exact code here. Without seeing the exact code, it's tough for outsiders to answer that particular question.

Update: mystery solved. You mentioned casually that this stored procedure happens to use table-valued parameters. This is a known issue with how TVPs can be called. This is a great example of why it's so important to include the complete code that you have questions about - sometimes even the tiniest things can have a big impact on the question you're asking.

Answer 2

This isn't an answer to the overall question, but shows how you can get better detail about the individual statements that are represented by those rows. Currently the query just retrieves the whole text of the procedure instead of narrowing in on the portion of the procedure that was collected for that query stats row. This would have been horrible as a comment.

Change:

st.text AS QueryText,

To:

SUBSTRING(st.[text],qs.statement_start_offset/2, 
  (CASE WHEN qs.statement_end_offset = -1 
  THEN LEN(CONVERT(nvarchar(max), st.[text])) * 2 
  ELSE qs.statement_end_offset + 4 END - 
  qs.statement_start_offset)/2) AS QueryText,

This will show you the individual statement text inside the procedure that was run for the given query stat row, instead of just copying the entire procedure body.