SQL Server 改進了在 PAGEIOLATCH_SH/記憶體/CPU 上等待的查詢

作為我公司的一名“意外 DBA”，我的任務之一是辨識我們的 ERP 系統可能存在的性能問題。由於復雜的原因，數據庫後端必須是SQL Server 2000。

從 Windows 的 perflog 中，我可以看到儲存正在被錘擊。因此，我嘗試“深入研究”原因。我已經安裝了 Solarwinds DPA（以前稱為 Confio Ignite），並在讓它執行了幾天后，發現了兩個具有“極端”等待時間的查詢。

這是第一個查詢；它的問題是PAGEIOLATCH_SH佔用了將近 100% 的等待時間：

(@P1 varchar(1000),@P2 varchar(1000),@P3 datetime,@P4 datetime,@P5 varchar(1000)) 
SELECT
 -- A HUGE list of columns here 
FROM INVENTTRANS A(INDEX(I_177DIMIDIDX))  
WHERE ((DATAAREAID=@P1)  
AND ((((ITEMID=@P2)  
AND (DATEPHYSICAL>=@P3))  
AND (DATEPHYSICAL<=@P4))  
AND (INVENTDIMID=@P5)))  
ORDER BY A.DATAAREAID, 
  A.ITEMID, 
  A.DATEPHYSICAL, 
  A.INVENTDIMID, 
  A.VOUCHERPHYSICAL OPTION(FAST 20)

這是第二個查詢；它的問題是記憶體/CPU等待：

(@P1 varchar(1000),@P2 varchar(1000),@P3 datetime) 
SELECT SUM(A.NOMINAL)  
FROM SMI_PL_HEADER A(INDEX(I_30301CUSTGROUPIDX))  
WHERE ((DATAAREAID=@P1)  
AND ((CUSTACCOUNT=@P2)  
AND (DEADLINEDELIVERY=@P3))) OPTION(FAST 2)

以下是I_177DIMIDIDX索引的列（根據 SQL Server Management Studio）：

• 數據區
• 發明人
• 項目ID

以下是由I_30301CUSTGROUPIDX索引的列：

• 數據區
• 項目組
• 客戶賬戶

現在，我的問題：

1. 我有一種感覺，通過重新創建索引以包含遺漏的列，可以顯著減少等待時間。我的預感對嗎？
2. 如何最好地創建這樣的索引？我不太了解“聚集”和“非聚集”索引之間的區別。

---

抱歉沒有盡快更新；週末進入計劃:)

無論如何，第一個查詢的執行計劃：

Sort(ORDER BY:([A].[DATEPHYSICAL] ASC, [A].[VOUCHERPHYSICAL] ASC))
 EstimateRows=1.0, EstimateIO=0.011261261, EstimateCPU=1.0048E-4, AverageRowSize=480, TotalSubtreeCost=0.014695659, Parallel=false, EstimateExecutions=1.0

 |--Filter(WHERE:([A].[DATEPHYSICAL]<=Convert('DUMMY_VAL') AND [A].[DATEPHYSICAL]>=Convert('DUMMY_VAL')))
      EstimateRows=1.0, EstimateCPU=8.8E-7, AverageRowSize=487, TotalSubtreeCost=0.0033338175, Parallel=false, EstimateExecutions=1.0

      |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([INVENTTRANS] AS [A]))
           EstimateRows=1.0, EstimateIO=4.882658E-5, EstimateCPU=1.1E-6, AverageRowSize=487, TotalSubtreeCost=0.0033329376, Parallel=false, EstimateExecutions=1.0

           |--Index Seek(OBJECT:([INVENTTRANS].[I_177DIMIDIDX] AS [A]), SEEK:([A].[DATAAREAID]='DUMMY_VAL' AND [A].[INVENTDIMID]='DUMMY_VAL' AND [A].[ITEMID]='DUMMY_VAL') ORDERED FORWARD)
                EstimateRows=1.0, EstimateIO=0.003203401, EstimateCPU=7.961E-5, AverageRowSize=49, TotalSubtreeCost=0.003283011, Parallel=false, EstimateExecutions=1.0

以及第二個查詢的執行計劃：

Compute Scalar(DEFINE:([Expr1001]=If ([Expr1007]=0) then NULL else [Expr1008]))
 EstimateRows=1.0, EstimateCPU=2.5E-7, AverageRowSize=24, TotalSubtreeCost=0.0033404902, Parallel=false, EstimateExecutions=1.0

 |--Stream Aggregate(DEFINE:([Expr1007]=Count(*), [Expr1008]=SUM([A].[NOMINAL])))
      EstimateRows=1.0, EstimateCPU=2.5E-7, AverageRowSize=24, TotalSubtreeCost=0.0033404902, Parallel=false, EstimateExecutions=1.0

      |--Filter(WHERE:([A].[DEADLINEDELIVERY]=Convert('DUMMY_VAL')))
           EstimateRows=1.0, EstimateCPU=4.8E-7, AverageRowSize=214, TotalSubtreeCost=0.00334024, Parallel=false, EstimateExecutions=1.0

           |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([SMI_PL_HEADER] AS [A]))
                EstimateRows=1.0, EstimateIO=4.882658E-5, EstimateCPU=1.1E-6, AverageRowSize=214, TotalSubtreeCost=0.00333976, Parallel=false, EstimateExecutions=1.0

                |--Index Seek(OBJECT:([SMI_PL_HEADER].[I_30301CUSTGROUPIDX] AS [A]), SEEK:([A].[DATAAREAID]='DUMMY_VAL'),  WHERE:([A].[CUSTACCOUNT]='DUMMY_VAL') ORDERED FORWARD)
                     EstimateRows=1.0, EstimateIO=0.003203401, EstimateCPU=8.40272E-5, AverageRowSize=38, TotalSubtreeCost=0.0032874283, Parallel=false, EstimateExecutions=1.0

幫助您入門的想法：

1. 這是一個絕妙的主意，也是我首先要考慮的。除了，SQL Server 2000 不支持索引中包含的列。
2. 聚群/非聚群索引完全是一個不同的討論——簡單地說，聚群索引實際上是數據的物理儲存“順序”（索引就是數據），而非聚群索引獨立並引用通過指針獲取數據。您可以將其視為一個庫，其中聚集索引是實際的書架（按類型、作者、標題排序），而非聚集索引只是一個“rolodex”。如果您設計一個非聚集索引，使其已經包含查詢所需的所有資訊（所謂的“覆蓋索引”），您就不必對數據進行查找，從而從根本上提高查詢性能。

我會先看看為什麼會有明確的索引提示。

FROM INVENTTRANS A(INDEX(I_177DIMIDIDX))  

.. 告訴 SQL Server 始終選擇索引I_177DIMIDIDX，如果索引不能正確覆蓋您要檢索的所有數據，包括列INVENTDIMID、、DATEPHYSICAL以及 SELECT 子句中的巨大列列表，這絕對會終止您的查詢ORDER BY 中的列。

馬上，我會嘗試創建這樣的索引來嘗試解決問題。

CREATE INDEX ... ON INVENTTRANS
   (DATAAREAID, ITEMID, INVENTDIMID, DATEPHYSICAL)

如果可以通過添加 example 來實現唯一索引，則可能會更好VOUCHERPHYSICAL。您可能仍然有一個問題，即巨大的選定列列表意味著數據庫必須將索引與（聚集）表“連接”以查找這些列，但請嘗試一下。

第二個表更簡單，主要是由於搜尋/輸出列的數量較少：

CREATE INDEX ... ON SMI_PL_HEADER
   (DATAAREAID, CUSTACCOUNT, DEADLINEDELIVERY)
   --- INCLUDE (NOMINAL)

還添加NOMINAL到索引列（你不能包括它）當然不是很漂亮，但如果你很絕望，它可能會幫助你。

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