sp_cursorprepexec 導致 5300 萬次讀取？

我們正在使用 SQL Server 2012 執行 Dynamics AX 2012 安裝。我知道不應再使用游標，但 AX 正在使用它，我們無法更改此行為，因此我們必須使用它。

今天我發現了一個非常糟糕的查詢，讀取次數超過 5300 萬次，執行時間超過 20 分鐘。

我通過我們的監控工具 SentryOne 擷取了這個查詢。

declare @p1 int
set @p1=1073773227
declare @p2 int
set @p2=180158805
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=2
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 bigint,@P2 nvarchar(5),@P3 bigint,@P4 nvarchar(8),@P5 bigint,@P6 bigint,@P7 bigint,@P8 bigint,@P9 bigint,@P10 bigint,@P11 bigint,@P12 bigint,@P13 bigint,@P14 bigint,@P15 bigint,@P16 bigint,@P17 bigint,@P18 bigint,@P19 nvarchar(5),@P20 bigint,@P21 bigint,@P22 bigint,@P23 bigint,@P24 bigint',N'SELECT T1.PRODUCT,T1.EXTERNALVENDPARTY,T1.LIFECYCLESTATUS,T1.RECID,T2.ECORESPRODUCT,T2.ECORESDISTINCTPRODUCTVARIANT,T2.SGE,T2.ECORESREFORDERNUM,T2.ORDERNUM,T2.RECID,T3.ECORESREFORDERNUM,T3.NAME1,T3.NAME2,T3.NAME3,T3.RECID,T4.ECORESPRODUCT,T4.EXTERNALITEMID,T4.ECORESDISTINCTPRODUCTVARIANT,T4.RECID,T5.RECID,T5.PERSON,T6.RECID,T6.NAME,T6.INSTANCERELATIONTYPE,T7.RECID,T7.NAME,T7.INSTANCERELATIONTYPE,T8.PARTY,T8.ACCOUNTNUM,T8.RECID,T9.RECID,T9.DISPLAYPRODUCTNUMBER,T9.INSTANCERELATIONTYPE,T10.PRODUCT,T10.CATEGORY,T10.RECID,T11.RECID,T11.CODE,T11.NAME,T11.INSTANCERELATIONTYPE FROM INVENTTABLE T1 CROSS JOIN ECORESPRODUCTORDERNUM T2 CROSS JOIN ECORESPRODUCTORDERNUMTRANSLATION T3 LEFT OUTER JOIN VENDEXTERNALITEM T4 ON ((T4.PARTITION=5637144576) AND ((T2.ECORESPRODUCT=T4.ECORESPRODUCT) AND (T4.ECORESDISTINCTPRODUCTVARIANT=@P1))) CROSS JOIN HCMWORKER T5 CROSS JOIN DIRPARTYTABLE T6 CROSS JOIN DIRPARTYTABLE T7 CROSS JOIN VENDTABLE T8 CROSS JOIN ECORESPRODUCT T9 CROSS JOIN ECORESPRODUCTCATEGORY T10 CROSS JOIN ECORESCATEGORY T11 WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N''087'')) AND (T1.DATAAREAID=@P2)) AND ((T2.PARTITION=5637144576) AND ((T2.ECORESPRODUCT=T1.PRODUCT) AND (T2.SGE=@P3))) AND ((T3.PARTITION=5637144576) AND ((T3.ECORESREFORDERNUM=T2.ECORESREFORDERNUM) AND (T3.LANGUAGEID=@P4))) AND ((T5.PARTITION=5637144576) AND (T5.RECID=T2.PRODUCTMANAGER)) AND (((T6.PARTITION=5637144576) AND (T6.INSTANCERELATIONTYPE IN (@P5,@P6,@P7,@P8,@P9,@P10,@P11) )) AND (T6.RECID=T5.PERSON)) AND (((T7.PARTITION=5637144576) AND (T7.INSTANCERELATIONTYPE IN (@P12,@P13,@P14,@P15,@P16,@P17,@P18) )) AND (T1.EXTERNALVENDPARTY=T7.RECID)) AND (((T8.PARTITION=5637144576) AND (T8.DATAAREAID=N''087'')) AND ((T7.RECID=T8.PARTY) AND (T8.DATAAREAID=@P19))) AND (((T9.PARTITION=5637144576) AND (T9.INSTANCERELATIONTYPE IN (@P20,@P21,@P22) )) AND (T9.RECID=T1.PRODUCT)) AND ((T10.PARTITION=5637144576) AND (T10.PRODUCT=T9.RECID)) AND (((T11.PARTITION=5637144576) AND (T11.INSTANCERELATIONTYPE IN (@P23,@P24) )) AND (T11.RECID=T10.CATEGORY))',@p5 output,@p6 output,@p7 output,0,N'087',5637146082,N'de',41,2303,2377,2975,2978,5329,6886,41,2303,2377,2975,2978,5329,6886,N'087',3265,3266,3267,2665,4423
select @p1, @p2, @p5, @p6, @p7

我注意到的第一件事是這個查詢使用了游標。出於好奇，我複制了該語句並在 Management Studio 中執行它，沒有使用游標（我不得不承認我替換了查詢的參數，以便可以執行它）。在 SSMS 中，查詢在 30 秒內完成。不是很快，但仍然比游標替代品快。

在這裡，我為您提供兩個計劃：

• 用游標計劃： https ://www.brentozar.com/pastetheplan/?id=Sk0aMY-Y-
• 沒有游標的計劃：https ://www.brentozar.com/pastetheplan/?id=HJ6ImtWK-

沒有游標的計劃仍然是一個非常糟糕的計劃，但它要好得多。我的問題是：有人可以向我解釋為什麼游標版本需要 5300 萬次讀取嗎？

使用游標查詢的統計資訊：

Duration    CPU         Reads       Writes  Est Rows    Actual Rows
1.396.212   1.379.157   53.270.895  3.878   30          2

無游標查詢的統計資訊：

Duration    CPU         Reads       Writes  Est Rows    Actual Rows
23.337      1.703       665.113     13      4.287       34.813

得到 34,813 行而不是 2 行似乎很奇怪；但我很確定我填寫了正確的參數。我認為這可能是 SQL Sentry 的一個有趣的怪癖，因為我只是從那裡複製了統計資訊。

我希望我能為您提供所有必要的資訊。此外，如果有人有一些好的讀數，那麼更好地理解游標會很棒。

首先，令我吃驚的是，來自 SQL Sentry 的兩個查詢的實際行數並不相同。

第二。在沒有實際計劃的情況下，使用游標很難判斷您的估計在計劃中有多正確，但有些事情對我來說很突出。（PS：請參閱我的回答以獲得實際計劃）。

話雖如此，從您的估計計劃中可以注意到幾件事。

由於參數化，存在關於不匹配索引的警告。刪除參數化以便 SQL Server 可以使用那些不匹配的參數可以顯著改善 I/O。

兩個計劃之間的估計行數也顯著偏離。在帶有游標的計劃中，vendexternalitem 的估計行數為 11。在沒有游標的計劃中，估計和實際的行數接近 200K。如果您的 200K 記錄實際上進入該假離線操作員，那可能會很痛苦。

所有運算符的估計值都大不相同（在帶有游標的計劃中小得多），因此您的計劃可能是使用不同的參數值編譯和記憶體的，而不是在沒有游標的查詢中使用的。（稱為參數嗅探）

在 invent 表上的 index seek + key lookup 還有一個很奇怪的選擇。該計劃使用 typeIdx，然後對聚集索引 (itemidx) 進行鍵查找。如果您的估計不正確，並且 SQL Server 必須進行大量關鍵查找，這也可以解釋大量 IO。我不熟悉您的計劃中沒有游標的 stopidx，但它看起來好像在覆蓋，所以對於您提供的參數來說，這可能是一個更好的選擇。我想它選擇了 typeidx 因為它要窄得多，但這可能是由於編譯時間值與您提供的有問題的執行不同。

簡而言之，我將在 AX 中刪除此查詢的參數化，因此它會生成一個具有實際值的計劃，因此它會選擇 SSMS 中的計劃（和執行時間）證明的“更好”索引。這也將允許 SQL Server 使用過濾的索引。為此，請讓開發人員在執行此查詢的應用程式碼中添加forceliterals關鍵字，然後查看會發生什麼。

那可能仍然會給您留下一個需要 30 秒的查詢（類似於您在 SSMS 中的查詢），但這只是調整的問題。您的計劃中缺少索引警告，我認為 ecoresproductordernum.sge 上的索引可能會有所幫助，但我不知道這些表並認為它們是通過自定義添加的。一般的調整原則在這裡會有所幫助，但對於這個答案來說可能太寬泛了（涵蓋索引，……）

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