Recuperar próximo item da fila

Tomando apenas a parte principal que identifica a idlinha a ser retornada, a consulta a seguir encapsula a lógica necessária:

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;

A execução dessa consulta com eficiência requer uma pequena alteração em um índice existente, cuja definição é atualmente:

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

A mudança envolve mover testrunidente filesequenceda INCLUDElista para as chaves de índice. O novo índice ainda oferece suporte a todas as consultas que o antigo fazia e, como efeito colateral da alteração, o índice redefinido agora pode ser marcado como UNIQUE. O script a seguir fará essa alteração (você pode fazer essa operação ONLINEse estiver executando o Enterprise Edition):

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
);

Com esse índice definido, o plano de execução para a consulta revisada é:

Para retornar dados da linha identificada, a consulta final é uma extensão simples:

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
    );

Segunda opçao

Existe outra opção porque você está usando o SQL Server 2012, que introduziu as funções LAGe LEADwindow :

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;

Esta consulta também precisa de um ajuste em um índice existente, desta vez adicionando processede failedcomo colunas incluídas:

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

Com esse índice definido, o plano de execução é:

Também devo mencionar que sua abordagem geral seria insegura se mais de um processo estivesse trabalhando na mesma fila ao mesmo tempo. Para obter mais informações sobre isso e o design geral da tabela de filas, consulte o excelente artigo de Remus Rusanu, Using Tables As Queues .

Análise adicional

O plano de execução original mostrou uma grande diferença entre o número de linhas que o SQL Server esperava processar e o número realmente encontrado durante a execução. Abrir o plano com o SQL Sentry Plan Explorer mostra essas diferenças claramente:

O SQL Server escolheu uma estratégia de execução que teria funcionado bem se o número de linhas realmente fosse tão pequeno quanto estimado :

Infelizmente, a estratégia escolhida não escalou bem quando as estimativas se mostraram muito baixas. Partes do plano de execução foram realmente executadas 458.260 vezes - não uma receita para resultados instantâneos. Se o otimizador do SQL Server conhecesse os números verdadeiros, provavelmente teria escolhido uma estratégia diferente.

É tentador pensar que uma discrepância entre as contagens de linhas estimadas e reais deve-se a informações estatísticas imprecisas, mas é provável que você tenha estatísticas automáticas de coluna única razoavelmente atualizadas nessas tabelas. Melhorias podem ser feitas fornecendo informações estatísticas adicionais, mas a causa raiz das estimativas imprecisas é algo bem diferente neste caso.

Por padrão, quando o SQL Server escolhe um plano de execução para uma consulta, ele assume que todas as linhas de resultados potenciais serão retornadas ao cliente. No entanto, quando o SQL Server vê um TOPoperador, ele considera corretamente o número de linhas especificadas ao estimar a cardinalidade. Esse comportamento é conhecido como definir uma meta de linha .

Essencialmente, uma meta de linha significa que o otimizador reduz as estimativas sob o operador Top para refletir o fato de que menos linhas serão necessárias do que o tamanho potencial total do conjunto de resultados. Essa escala assume implicitamente que os valores de interesse são distribuídos uniformemente dentro do conjunto.

Por exemplo, digamos que você tenha uma tabela com 1.000 linhas, das quais 10 satisfazem algum predicado de consulta. Usando estatísticas, o SQL Server sabe que 1% das linhas atendem aos critérios. Se você escrever uma consulta para retornar a primeira linha correspondente, o SQL Server assumirá que precisará ler 1% da tabela (= 10 linhas) para localizar a primeira correspondência. No pior caso, as dez linhas que atendem aos critérios podem aparecer por último (em qualquer ordem em que forem pesquisadas), portanto, 990 linhas que não correspondem serão lidas antes que o SQL Server encontre aquela que você deseja.

The improved query (using the improved index) still suffers from this problem to a certain extent, but the effects are much less marked:

The fundamental question we are asking of the optimizer's row goal logic here is: how many rows do we need to check before we find the first one (according to the order by clause specification) that is in sequence, unprocessed, and not marked as having failed. This is a tough question to answer based on the limited statistics SQL Server keeps.

In fact there is very little we can do to communicate all nuances of this complex problem to the optimizer effectively. The best we can do is to provide it with an efficient index that we expect to locate the qualifying row as early as possible.

In this case, that means providing an index that returns unprocessed, un-failed entries in order by clause order. We expect to have to check relatively few of these before finding the first one that is in sequence (i.e. the preceding row exists and is marked as processed and not marked as failed).

Both solutions shown above eliminate the Key Lookup operations seen in the original query because the new index now includes (covers) all needed columns. Also, the new index is likely to find the target row more quickly. The original execution plan scanned index IXd_testrun_sequence in (testrunident, filesequence) order, meaning it would encounter old test runs first, most of which will be marked processed. We are looking for unprocessed rows in the outer query, so this strategy was inefficient (we end up performing the sequence check on 458,260 rows).

Por fim, verificar um valor de sequência específico é muito mais eficiente do que encontrar o máximo de um conjunto potencialmente grande. Essa é a diferença que destaquei no código anterior ao procurar a linha anterior em sequência estrita. Há uma diferença semântica entre essas duas consultas e a solução MAXmostrada na pergunta. Pelo que entendi, você está interessado na primeira linha correspondente; essa linha não precisa ser a mais alta filesequencepara o arquivo testrunident.