檢索下一個隊列項

我在 SQL Server 2012 中有一個簡單的表，它實現了一個處理隊列。插入數據後，檢索下一項的查詢已從 <100 毫秒變為恆定的 5-6 秒。如果有人能指出性能突然下降的原因，我將不勝感激。（這似乎是一夜之間的下跌）。

這是表定義：

CREATE TABLE [dbo].[messagequeue] (
   [id]             INT             IDENTITY (1, 1) NOT NULL,
   [testrunident]   VARCHAR (255)   NOT NULL,
   [filesequence]   INT             NOT NULL,
   [processed]      BIT             NOT NULL,
   [dateentered]    DATETIME        NULL,
   [filedata]       VARBINARY (MAX) NULL,
   [retries]        INT             NOT NULL,
   [failed]         BIT             NOT NULL,
   [msgobject]      VARBINARY (MAX) NULL,
   [errortext]      VARCHAR (MAX)   NULL,
   [sourcefilename] VARCHAR (MAX)   NULL,
   [xmlsource]      VARCHAR (MAX)   NULL,
   [messagetype]    VARCHAR (255)   NULL
);

CREATE NONCLUSTERED INDEX [messagequeue_sequenc_failed_idx]
   ON [dbo].[messagequeue]([processed] ASC, [failed] ASC)
   INCLUDE([id], [testrunident], [filesequence]);

CREATE NONCLUSTERED INDEX [messagequeue_sequence_idx]
   ON [dbo].[messagequeue]([testrunident] ASC, [processed] ASC)
   INCLUDE([filesequence]);

CREATE UNIQUE NONCLUSTERED INDEX [IXd_testrun_sequence]
   ON [dbo].[messagequeue]([testrunident] ASC, [filesequence] ASC);

這是用於檢索要處理的下一行的查詢：

select messagequeue.id, messagequeue.testrunident, messagequeue.filesequence,
   messagequeue.processed, messagequeue.filedata, messagequeue.retries, messagequeue.failed, 
   messagequeue.msgobject, messagequeue.xmlsource 
   from messagequeue where id = (
       select top 1 id from messagequeue mqouter
       where processed = 0
       AND failed = 0
       AND (filesequence = 0 OR
       filesequence = (
               select max (filesequence) + 1
               from messagequeue mqinner 
               where mqinner.testrunident = mqouter.testrunident
               and mqinner.processed = 1
           )
       )
       order by testrunident, filesequence
       )

有多個相同的行testrunident，每行都有一個filesequence應該是連續的，但是有些可能會失去，因此查詢應該只返回前一行具有的 NEXT 行，processed = 1或者filesequence = 0表示這是testrunident組中的第一行。

這是一個 SQLFiddle 給出一個想法：SQL Fiddle

查詢計劃：查詢計劃 XML

有沒有更好的方法來編寫查詢？

編輯 1 - 確保在選擇行之前處理前一行的範例：

Where `id` = testrunident and `fs` = filesequence

id | fs | processed 
1  | 0  |  1
1  | 1  |  1
1  | 2  |  1
1  | 4  |  0 -- this shouldn't be next as no row with seqeuence = 3 and processed = 1
2  | 0  |  0 --this should be the next row
2  | 1  |  0

SQLFiddle 強調了這一點

id僅採用標識要返回的行的核心部分，以下查詢封裝了所需的邏輯：

SELECT TOP (1)
   MQ.id
FROM dbo.messagequeue AS MQ
WHERE
   -- Current row
   MQ.processed = 0
   AND MQ.failed = 0
   AND 
   (
       EXISTS
       (
           -- Previous row in strict sequence
           SELECT * 
           FROM dbo.messagequeue AS MQ2
           WHERE
               MQ2.testrunident = MQ.testrunident
               AND MQ2.processed = 1
               AND MQ2.failed = 0
               AND MQ2.filesequence = MQ.filesequence - 1
       )
       OR MQ.filesequence = 0
   )
ORDER BY 
   MQ.testrunident ASC,
   MQ.filesequence ASC;

有效地執行此查詢需要對現有索引進行少量更改，其定義目前為：

CREATE NONCLUSTERED INDEX [messagequeue_sequenc_failed_idx]
   ON [dbo].[messagequeue]([processed] ASC, [failed] ASC)
   INCLUDE([id], [testrunident], [filesequence]);

更改涉及從列表移動testrunident和移動到索引鍵。新索引仍然支持舊索引所做的所有查詢，並且作為更改的副作用，重新定義的索引現在可以標記為. 以下腳本將執行此更改（如果您執行的是企業版，則可以執行此操作）：filesequence``INCLUDE``UNIQUE``ONLINE

CREATE UNIQUE NONCLUSTERED INDEX [messagequeue_sequenc_failed_idx]
ON [dbo].[messagequeue]
(
   [processed] ASC, 
   [failed] ASC, 
   testrunident ASC, 
   filesequence ASC
)
INCLUDE 
(
   [id]
)
WITH 
(
   DROP_EXISTING = ON
   --, ONLINE = ON
);

有了這個索引，修改後的查詢的執行計劃是：

要從辨識的行返回數據，最後的查詢是一個簡單的擴展：

SELECT
   MQ3.id,
   MQ3.testrunident,
   MQ3.filesequence,
   MQ3.processed,
   MQ3.filedata,
   MQ3.retries,
   MQ3.failed,
   MQ3.msgobject,
   MQ3.xmlsource
FROM dbo.messagequeue AS MQ3
WHERE
   MQ3.id =
   (
       SELECT TOP (1)
           MQ.id
       FROM dbo.messagequeue AS MQ
       WHERE
           MQ.processed = 0
           AND MQ.failed = 0
           AND 
           (
               EXISTS
               (
                   SELECT * 
                   FROM dbo.messagequeue AS MQ2
                   WHERE
                       MQ2.testrunident = MQ.testrunident
                       AND MQ2.filesequence = MQ.filesequence - 1
                       AND MQ2.processed = 1
                       AND MQ2.failed = 0
               )
               OR MQ.filesequence = 0
           )
       ORDER BY 
           MQ.testrunident ASC,
           MQ.filesequence ASC
   );

第二種選擇

還有另一種選擇，因為您使用的是 SQL Server 2012，它引入了LAG和LEAD視窗函式：

SELECT TOP (1)
   ML.id
FROM 
(
   SELECT 
       M.id, 
       M.testrunident,
       M.filesequence,
       M.processed,
       M.failed,
       PreviousProcessed = LAG(M.processed) OVER (
           ORDER BY M.testrunident, M.filesequence),
       PreviousFailed = LAG(M.failed) OVER (
           ORDER BY M.testrunident, M.filesequence),
       PreviousFileSequence = LAG(M.filesequence) OVER (
           ORDER BY M.testrunident, M.filesequence)
   FROM dbo.messagequeue AS M
) AS ML
WHERE
   -- Current row
   ML.processed = 0
   AND ML.failed = 0
   -- Previous row in strict order
   AND ML.PreviousProcessed = 1
   AND ML.PreviousFailed = 0
   AND ML.PreviousFileSequence = ML.filesequence - 1
ORDER BY 
   ML.testrunident, 
   ML.filesequence;

此查詢還需要對現有索引進行調整，這次通過添加processed和failed作為包含列：

CREATE UNIQUE NONCLUSTERED INDEX [IXd_testrun_sequence]
ON [dbo].[messagequeue]
(
   [testrunident] ASC, 
   [filesequence] ASC
)
INCLUDE 
(
   [processed],
   [failed]
)
WITH 
(
   DROP_EXISTING = ON
   --, ONLINE = ON
);

有了該索引，執行計劃是：

我還應該提到，如果不止一個程序同時在同一個隊列上工作，那麼您的一般方法將是*不安全的。*有關此和一般隊列表設計的更多資訊，請參閱 Remus Rusanu 的優秀文章Using Tables As Queues。

進一步的分析

原始執行計劃顯示 SQL Server 預期處理的行數與執行期間實際遇到的行數之間存在很大差異。使用SQL Sentry Plan Explorer打開計劃清楚地顯示了這些差異：

SQL Server 選擇了一種執行策略，如果行數真的像它估計的那樣小，這種策略會很有效：

不幸的是，當估計值太低時，所選擇的策略並不能很好地擴展。部分執行計劃實際上被執行了 458,260 次——不是立竿見影的方法。如果 SQL Server 優化器知道真實數字，它可能會選擇不同的策略。

很容易認為估計行數和實際行數之間的差異一定是由於統計資訊不准確造成的，但您很可能在這些表上擁有合理的最新單列自動統計資訊。通過提供額外的統計資訊可能會進行改進，但在這種情況下，估計不准確的根本原因是完全不同的。

預設情況下，當 SQL Server 為查詢選擇執行計劃時，它假定所有潛在的結果行都將返回給客戶端。但是，當 SQL Server 看到一個TOP運算符時，它會在估計基數時正確地考慮指定的行數。這種行為稱為設置行目標。

本質上，行目標意味著優化器縮減 Top 運算符下的估計值，以反映需要的行數少於結果集的全部潛在大小的事實。這種縮放隱含地假設感興趣的值在集合內均勻分佈。

例如，假設您有一個包含 1000 行的表，其中 10 行滿足某個查詢謂詞。使用統計資訊，SQL Server 知道 1% 的行符合條件。如果您編寫查詢以返回匹配的第一行，SQL Server 假定它需要讀取表的 1%（= 10 行）才能找到第一個匹配項。在最壞的情況下，符合條件的 10 行可能會出現在最後（以任何搜尋順序），因此將在 SQL Server 遇到您想要的行之前讀取 990 行不匹配的行。

改進後的查詢（使用改進後的索引）在一定程度上仍然存在這個問題，但效果就沒有那麼明顯了：

我們在這裡詢問優化器的行目標邏輯的基本問題是：在找到第一個（根據 order by 子句規範）按順序排列、未處理且未標記為具有的行之前，我們需要檢查多少行失敗的。根據 SQL Server 保留的有限統計資訊，這是一個很難回答的問題。

事實上，我們幾乎無法將這個複雜問題的所有細微差別有效地傳達給優化器。我們能做的最好的事情就是為它提供一個高效的索引，我們希望儘早找到符合條件的行。

在這種情況下，這意味著提供一個按order by子句順序返回未處理的、未失敗的條目的索引。我們希望在找到按順序排列的第一個（即前一行存在並標記為已處理但未標記為失敗）之前，必須檢查其中相對較少的部分。

上面顯示的兩種解決方案都消除了在原始查詢中看到的鍵查找操作，因為新索引現在包括（覆蓋）所有需要的列。此外，新索引可能會更快地找到目標行。原來的執行計劃IXd_testrun_sequence按 ( testrunident, filesequence) 順序掃描索引，這意味著它會首先遇到舊的測試執行，其中大部分會被標記為已處理。我們正在外部查詢中尋找未處理的行，因此這種策略效率低下（我們最終對 458,260 行執行了序列檢查）。

最後，檢查一個特定的序列值比找到一個潛在的大集合的最大值要有效得多。這就是我在前面的程式碼中強調的不同之處，即按照嚴格的順序查找前一行。這兩個查詢與問題中顯示的解決方案之間存在語義差異MAX。我的理解是您對第一個匹配行感興趣；該行不必是 的最高filesequence行testrunident。

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