A consulta simples foi serial após a otimização, precisa dela paralela

Eu herdei um sistema legado que possui um procedimento armazenado que oferece suporte a um aplicativo da Web. Ele é executado milhares de vezes por dia. O aplicativo permite que um usuário insira detalhes parciais do cliente e o procedimento armazenado subjacente executará comandos LIKE para retornar uma lista restrita de possíveis clientes ao aplicativo Web.

Ele ainda permite a entrada parcial de um ID de cliente (valor inteiro), portanto, se o valor '123' for inserido no aplicativo, o procedimento armazenado deverá retornar todos os clientes com um ID contendo '123', ou seja, '612345' ou '222123 '.....sim, eu sei, louco, digitar '1' retorna um conjunto de dados enorme, mas não podemos alterar o App.

A pesquisa do ID do cliente é a parte mais lenta do procedimento armazenado. Eu "otimizei" o procedimento armazenado (em DEV) para que ele use menos E/S e menos CPU....

Veja como recriar o problema.

--CREATE TEST TABLE
CREATE TABLE dbo.Test(ID INT IDENTITY(1,1) PRIMARY KEY
                        , Val Float
                        , CodeVal CHAR(100)
                        )
GO

--GENERATE A FEW MILLION TEST RECORD
INSERT INTO dbo.Test
SELECT
    RAND()
    ,CONVERT(varchar(255), NEWID())
FROM 
    sys.objects --Contains 639 Rows
GO 10000

--CREATE INDEX ON ID FIELD
CREATE UNIQUE NONCLUSTERED INDEX idx ON dbo.Test(ID)

A consulta existente executa um Parallel INDEX SCAN.

DECLARE @ID VARCHAR(20) = '123456'

--DROP TABLE #TMP1
CREATE TABLE #TMP1(ID INT)

INSERT INTO #TMP1
SELECT  ID 
FROM    dbo.Test
WHERE   CONVERT(VARCHAR(20),ID) LIKE '%'+@ID+'%'

Então percebi que, se um usuário inserir uma ID de cliente parcial de '123456', o conjunto de resultados nunca poderá conter um valor menor que '123456'. Então adicionei uma linha extra ao Código (veja abaixo), agora tenho um INDEX SEEK com IO reduzido e tempo de CPU reduzido. Mas, para meu horror, acabou Serial, então agora leva uma eternidade. Se isso for ativado, o tempo de espera do usuário saltará de 4 segundos para 20 segundos.

INSERT INTO #TMP1
SELECT  ID 
FROM    dbo.Test
WHERE   CONVERT(VARCHAR(20),ID) LIKE '%'+@ID+'%'
AND     ID >= @ID   --New line of code

Agora cheguei ao ponto em que estou codificando cegamente na esperança de que seja paralelo.

Alguém pode explicar por que isso está acontecendo (além da resposta de estoque de "o otimizador decidiu que era melhor")?

E alguém pode descobrir como fazer a consulta ficar paralela?

O sinalizador de rastreamento 8649 não é uma opção para mim.

E eu já li este artigo muito útil de Paul White .

ATUALIZAR:

Parece que o exemplo fornecido produz resultados variados dependendo de uma combinação de System Spec e Row count na tabela de teste.

Desculpe se você não pode recriar o problema. Mas isso é parte da resposta ao meu problema.

ATUALIZAÇÃO: (Plano de Execução)

Aaron: Desculpe por, por exemplo, ser frustrante se não funcionar no seu sistema. (Não posso compartilhar o plano de execução real da minha empresa por motivos de InfoSec)

Eu recriei o problema no meu sistema doméstico.

Aqui está o Plano de Execução, (vou fazer o upload completo para Post-the-Plan)

minha contagem de linhas atual de dbo.Test é 2124160, o índice não agrupado tem 2627 páginas e 0,04% de fragmentação, abaixo estão minhas informações de estatísticas.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
Table 'Test'. Scan count 3, logical reads 2652, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 688 ms,  elapsed time = 368 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
Table 'Test'. Scan count 1, logical reads 1106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

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

O procedimento armazenado é usado cerca de 5.000 vezes por dia pelo pessoal do Call-center que lida com os clientes. Uma espera maior de 16 segundos custará pouco mais de 22 horas por dia de funcionários esperando por um resultado. Em um turno de 8 horas, isso equivale a 3 funcionários em tempo integral, custando aproximadamente £ 36k anualmente. Lá se vai o dinheiro para atualizar para as licenças do SQL Sentry (grande fã), atualmente temos o Spotlight (ainda um bom produto) mas quero algo com maior profundidade.

ATUALIZAÇÃO: (Número Mágico)

Com 12.000 linhas, ambas as consultas eram seriais.

Com 2.124.160 linhas, uma consulta fica paralela.

Com 66.000.000 de linhas, ambas ficam paralelas.

Definitivamente, há uma regra/métrica que o otimizador usa, e é diferente se você tiver um Index Scan ou Index Seek. Isso significa que a conversão de uma consulta de Scan para Seek pode ter um efeito adverso em tabelas/índices de um determinado tamanho?

ATUALIZAÇÃO: (Solução)

Obrigado Joe, você acertou em cheio com o Grau Máximo de Paralelismo.

Eu usei:

OPTION (OPTIMIZE FOR (@ID  = 1))

UNKNOWN não fez o truque para o meu ambiente.

Para quem estiver interessado, aqui está um artigo útil de Kendra Little .