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