Chris L's questions

Eu tenho uma tabela simples no SQL Server 2012 que implementa uma fila de processamento. Como os dados foram inseridos, a consulta para recuperar o próximo item passou de <100ms para uma constante de 5 a 6 segundos. Eu ficaria imensamente grato se alguém pudesse me apontar a causa dessa queda repentina no desempenho. (Parece ter sido uma queda quase da noite para o dia).

Aqui está a definição da tabela:

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

E aqui está a consulta usada para recuperar a próxima linha a ser processada:

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
        )

Existem várias linhas com o mesmo testrunident, cada uma tem um filesequenceque deve ser sequencial, no entanto, algumas podem estar faltando, portanto, a consulta deve retornar apenas a próxima linha onde a linha anterior possui processed = 1ou filesequence = 0que indica que esta é a primeira linha dentro de um testrunidentgrupo.

Aqui está um SQLFiddle para dar uma ideia: SQL Fiddle

Plano de consulta : plano de consulta XML

Existe uma maneira melhor de escrever a consulta?

EDIT 1 - Exemplo de como garantir que a linha anterior foi processada antes de selecionar uma linha:

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 destacando isso

Por que o servidor SQL usa paralelismo ao executar esta consulta que usa uma subconsulta, mas não ao usar uma junção? A versão de junção é executada em série e leva cerca de 30 vezes mais para ser concluída.

Versão de entrada: ~30 segundos

Versão da subconsulta: <1 segundo

EDIT: Versões XML do plano de consulta:

Versão JOIN

versão SUBQUERY