3

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:

query result

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
3
  • Can you give some clue about what -- do stuff is? Does the procedure have multiple statements? Dynamic SQL? Cursors? Magic? We could guess but that's not very useful. What I suspect is happening with this query is that it's ignoring the start/end offset for the statement inside the module and is just presenting every statement as the module body. – Aaron Bertrand Jun 26 '18 at 12:35
  • @AaronBertrand: Just edited the question. Basically: multiple statements filling a temp table, no voodoo. Would you elaborate on "it's ignoring the start/end offset for the statement inside the module and is just presenting every statement as the module body"? – Christian Specht Jun 26 '18 at 12:50
  • Does #tmp have an index on ProductType? Also you could consider using local variables there, like update #tmp set ordertype = @ordertype where producttype = @producttype; - might help to force those statements to get parameterized and reused. – Aaron Bertrand Jun 26 '18 at 13:57
8

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.

4
  • There's definitely nothing dynamic inside the proc. I edited my question to show a simplified version, and I assure you that the proc does nothing else but insert/update stuff into a temp table and return the table in the end. With Aaron's help, I found the queries inside the proc which actually belong to that query plan (see edited question), but I don't understand why SQL Server creates so many query plans for them. Thanks for the Sommarskog post, I'll read it now, maybe I'll find an answer there. – Christian Specht Jun 26 '18 at 13:42
  • 1
    @ChristianSpecht ah, mystery solved - it's TVPs. This is why it's so important to include the EXACT code that you have questions about. Something that doesn't seem important to you can actually be the key to your question. Hope that helps! – Brent Ozar Jun 26 '18 at 13:49
  • @BrentOzar The word "issue" IMO implies something that should be reported to MS and fixed by them - maybe "gotcha" is a more appropriate term here? – Ian Kemp Jun 26 '18 at 18:24
  • @Ian "known issue" <> "bug"... it actually translates more roughly to "unexpected, but working as designed" or "understood limitation" than to "should be reported." IMHO. – Aaron Bertrand Jun 27 '18 at 15:25
3

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.

1
  • Nice! I ran your query and found the culprits, the two simple UPDATE statements at the end - even though I still don't understand why SQL Server generates so many plans for them. – Christian Specht Jun 26 '18 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.