sp_Blitz，> 1000 個 CREATE PROCEDURE 計劃

我正在使用Brent Ozar 的sp_Blitz，其結果之一是：

一個查詢的多個計劃

計劃記憶體中的單個查詢存在 1146 個計劃——這意味著我們可能存在參數化問題。

結果中的連結具有以下查詢：

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;

…它顯示了計劃數量最多的查詢。

當我執行它時，我得到的最重要的結果之一*（對於相同的查詢雜湊有大約 1150 個計劃）*讓我感到困惑：

也許在螢幕截圖上有點難以辨識 -這是一個包含評論的完整CREATE PROCEDURE定義，如下所示：

-- =============================================
-- 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

另外，它們都是一樣的。

我將 QueryText 從多行複製到文本文件中並製作了差異，它們都是 100% 相同的。

知道為什麼會這樣嗎？

我們的數據庫對像在原始碼控制中，所以我知道這個特定的 SP 最後一次更改是大約兩個月前。即使我們每天多次刪除並重新創建它*（我們不這樣做）*，我仍然不明白為什麼 SQL Server 會為相同的查詢創建這麼多計劃。

---

這個 SP 沒有什麼特別之處，只是它是我們擁有的極少數使用Table-Valued Parameters的之一。

這是 SP 功能的簡化版本：

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

當我執行Aaron Bertrand’s modified query時，它最後返回兩個簡單的UPDATE語句。

即，對於相同的查詢雜湊，我仍然得到 ~1150 行，其中一半有這個查詢文本：

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

…和其他人有這個：

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

問題 1：“CREATE PROCEDURE 有什麼用？！？” 當您執行儲存過程時，SQL Server 將儲存過程的整個文本儲存為您呼叫的內容。

你沒有創建儲存過程——你只是在執行它——但這對於剛開始分析計劃記憶體的人來說可能有點混亂。

所以，嘿，你現在已經越過了那個障礙！耶，你！

問題2：“1個儲存過程怎麼會有多個計劃？” 沒有看到它的全文，很難說，但我會從 Erland Sommarskog 的史詩文章開始，Slow in the App, Fast in SSMS。特別是，檢查標題為不同設置的不同計劃的部分。

我實際上不認為這是問題所在 - 我打賭 proc 文本中有一些動態的東西 - 但我知道你不想在這裡發布你的確切程式碼。沒有看到確切的程式碼，外人很難回答這個特定的問題。

**更新：謎團解開了。**您隨便提到，這個儲存過程恰好使用了表值參數。這是一個關於如何呼叫 TVP 的已知問題。這是一個很好的例子，說明為什麼包含您有疑問的完整程式碼如此重要 - 有時即使是最微小的事情也會對您提出的問題產生重大影響。

這不是對整個問題的回答，而是說明如何更好地了解由這些行表示的各個語句的詳細資訊。目前，查詢只檢索過程的整個文本，而不是縮小為該查詢統計行收集的過程部分。作為評論，這將是可怕的。

改變：

st.text AS QueryText,

到：

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,

這將向您顯示為給定查詢統計行執行的過程中的單個語句文本，而不是僅複製整個過程主體。

引用自：https://dba.stackexchange.com/questions/210605