返回固定行數後查詢暫停

我有一個視圖可以快速（幾秒鐘）執行多達 41 條記錄（例如，TOP 41），但需要幾分鐘才能執行 44 條或更多記錄，如果使用TOP 42or執行，則會產生中間結果TOP 43。具體來說，它將在幾秒鐘內返回前 39 條記錄，然後在返回剩餘記錄之前停止近三分鐘。這種模式在查詢TOP 44or時是一樣的TOP 100。

這個視圖最初是從基礎視圖派生的，在基礎視圖中添加了一個過濾器，即下面程式碼中的最後一個過濾器。如果我將子視圖從基礎連結起來，或者我用基礎內聯的程式碼編寫子視圖，似乎沒有區別。基本視圖在幾秒鐘內返回 100 條記錄。我想我可以讓子視圖像基地一樣快地執行，而不是慢 50 倍。有沒有人見過這種行為？關於原因或解決方案的任何猜測？

在我測試所涉及的查詢時，這種行為在過去幾個小時內一直保持一致，儘管在事情開始變慢之前返回的行數略有上下波動。這並不新鮮。我現在正在查看它，因為總執行時間是可以接受的（<2 分鐘），但我已經看到相關日誌文件中的這種暫停至少有幾個月了。

阻塞

我從未見過查詢被阻止，即使數據庫上沒有其他活動（由 sp_WhoIsActive 驗證），問題仍然存在。基本視圖包括NOLOCK所有內容，這是值得的。

查詢

這是子視圖的簡化版本，為簡單起見，內嵌了基本視圖。它仍然在大約 40 條記錄處顯示執行時間的跳躍。

SELECT TOP 100 PERCENT
   Map.SalesforceAccountID AS Id,
   CAST(C.CustomerID AS NVARCHAR(255)) AS Name,
   CASE WHEN C.StreetAddress = 'Unknown' THEN '' ELSE C.StreetAddress                 END AS BillingStreet,
   CASE WHEN C.City          = 'Unknown' THEN '' ELSE SUBSTRING(C.City,        1, 40) END AS BillingCity,
                                                      SUBSTRING(C.Region,      1, 20)     AS BillingState,
   CASE WHEN C.PostalCode    = 'Unknown' THEN '' ELSE SUBSTRING(C.PostalCode,  1, 20) END AS BillingPostalCode,
   CASE WHEN C.Country       = 'Unknown' THEN '' ELSE SUBSTRING(C.Country,     1, 40) END AS BillingCountry,
   CASE WHEN C.PhoneNumber   = 'Unknown' THEN '' ELSE C.PhoneNumber                   END AS Phone,
   CASE WHEN C.FaxNumber     = 'Unknown' THEN '' ELSE C.FaxNumber                     END AS Fax,
   TransC.WebsiteAddress AS Website,
   C.AccessKey AS AccessKey__c,
   CASE WHEN dbo.ValidateEMail(C.EMailAddress) = 1 THEN C.EMailAddress END,  -- Removing this UDF does not speed things
   TransC.EmailSubscriber
   -- A couple dozen additional TransC fields
FROM
   WarehouseCustomers AS C WITH (NOLOCK)
   INNER JOIN TransactionalCustomers AS TransC WITH (NOLOCK) ON C.CustomerID = TransC.CustomerID
   LEFT JOIN  Salesforce.AccountsMap AS Map WITH (NOLOCK) ON C.CustomerID = Map.CustomerID
WHERE
       C.DateMadeObsolete IS NULL
   AND C.EmailAddress NOT LIKE '%@volusion.%'
   AND C.AccessKey IN ('C', 'R')
   AND C.CustomerID NOT IN (243566)  -- Exclude specific test records
   AND EXISTS (SELECT * FROM Orders AS O WHERE C.CustomerID = O.CustomerID AND O.OrderDate >= '2010-06-28')  -- Only count customers who've placed a recent order
   AND Map.SalesforceAccountID IS NULL  -- Only count customers not already uploaded to Salesforce
-- Removing the ORDER BY clause does not speed things up
ORDER BY
   C.CustomerID DESC

該過濾器會丟棄由;Id IS NULL返回的大部分記錄。BaseView如果沒有TOP子句，它們分別返回 1,100 條記錄和 267K。

統計數據

執行時TOP 40：

SQL Server parse and compile time:    CPU time = 234 ms, elapsed time = 247 ms.
SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.

(40 row(s) affected)
Table 'CustomersHistory'. Scan count 2, logical reads 39112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AccountsMap'. Scan count 1, logical reads 458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:   CPU time = 2199 ms,  elapsed time = 7644 ms.

執行時TOP 45：

(45 row(s) affected)
Table 'CustomersHistory'. Scan count 2, logical reads 98268, physical reads 1, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 1788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AccountsMap'. Scan count 1, logical reads 2152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 41980 ms,  elapsed time = 177231 ms.

我很驚訝地看到實際輸出的這種適度差異導致讀取次數增加了約 3 倍。

比較執行計劃，除了返回的行數之外，它們是相同的。與上面的統計數據一樣，TOP 45查詢中早期步驟的實際行數要高得多，而不僅僅是高出 12.5%。

概括地說，它是從 Orders 中掃描一個覆蓋索引，從 WarehouseCustomers 中尋找相應的記錄；將此循環連接到 TransactionalCustomers（遠端查詢，確切計劃未知）；並將其與 AccountsMap 的表掃描合併。遠端查詢是估計成本的 94%。

雜項說明

早些時候，當我將視圖的擴展內容作為獨立查詢執行時，它執行得非常快：100 條記錄需要 13 秒。我現在正在測試一個沒有子查詢的查詢的精簡版本，這個更簡單的查詢需要三分鐘才能返回超過 40 行，即使作為獨立查詢執行也是如此。

子視圖包含大量讀取（每個 sp_WhoIsActive 約 1M），但在這台機器上（8 個核心，32 GB RAM，95% 專用 SQL 框）這通常不是問題。

我已經多次刪除並重新創建了這兩個視圖，沒有任何變化。

數據不包括任何 TEXT 或 BLOB 欄位。一個領域涉及UDF；刪除它不會阻止暫停。

無論是在伺服器本身上查詢，還是在 1,400 英里外的我的工作站上查詢，時間都是相似的，因此延遲似乎是查詢本身固有的，而不是向客戶端發送結果。

一些事情要嘗試：

1. 檢查您的索引

• 是否所有JOIN關鍵欄位都已編入索引？如果您經常使用此視圖，我什至會為視圖中的條件添加過濾索引。例如…
• CREATE INDEX ix_CustomerId ON WarehouseCustomers(CustomerId, EmailAddress) WHERE DateMadeObsolete IS NULL AND AccessKey IN ('C', 'R') AND CustomerID NOT IN (243566)

2. 更新統計

• 過時的統計數據可能存在問題。如果你可以擺動它，我會做一個FULLSCAN。如果有大量行，則數據可能發生了顯著變化而沒有觸發自動重新計算。

3. 清理查詢

• 製作Map JOINa NOT EXISTS- 您不需要該表中的任何數據，因為您只需要不匹配的記錄
• 刪除ORDER BY. 我知道評論說沒關係，但我覺得這很難相信。由於數據頁已被記憶體，因此對於較小的結果集可能無關緊要。

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