Nosso sistema tem aproximadamente 500 "Clientes" que possuem diferenças extremas nas contagens de registros. Aqui está uma consulta de exemplo (bastante simplificada) que pode retornar de 0 a 100.000 linhas, dependendo do parâmetro passado. Esta consulta é executada, mas tenho quase certeza de que ela sofre de detecção de parâmetro, dependendo de qual parâmetro é armazenado em cache.
exec sp_executesql N'
SELECT *
FROM Widgets
WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'
Se eu modificar a consulta para incluir o parâmetro como uma seleção, parece armazenar em cache a consulta para cada cliente individual.
exec sp_executesql N'
SELECT ''bda43162-2d98-4e79-8e81-7056f6df5e51'', *
FROM Widgets
WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'
O desempenho é bastante aprimorado porque cada cliente obtém sua própria versão da consulta armazenada em cache. Existem efeitos colaterais dessa abordagem?
Suposições:
- Deve ser SQL dinâmico para esta parte do sistema
- Esta consulta é executada com frequência
- O número de clientes não crescerá rapidamente
EDIT: Considerei usar OPTION (RECOMPILE), mas não queria recompilar todas as vezes se pudesse obter os benefícios da consulta compilada com essa abordagem.
A detecção de parâmetros significa que um conjunto de parâmetros produz um plano de execução dramaticamente diferente do outro e que, se o plano errado for armazenado em cache, você obterá efeitos adversos no desempenho.
Esta resposta é baseada em sua consulta simplificada - para obter conselhos precisos para sua consulta, você precisará postar a consulta e os dois planos diferentes que resultaram da detecção de parâmetros. (Eu sempre prefiro chegar à causa raiz precisa em vez de solucionar um exemplo simplificado, mas tenho que trabalhar com o código que você postou, então aqui vai.)
Sua consulta possui apenas uma tabela (supondo que Widgets não seja uma visualização):
Isso significa que se você tiver um índice não clusterizado em CustomerID, alguns valores de CustomerID podem produzir um plano com uma busca de índice não clusterizado seguida por uma pesquisa de chave, enquanto outros parâmetros farão uma verificação de índice clusterizado na tabela Widgets.
Existem algumas maneiras de corrigir esse cenário, e vou listá-las da maneira mais segura para a mais arriscada:
Use OPTION (RECOMPILE) na consulta. Isso requer uma alteração de código para adicionar a linha à consulta, mas toda execução dessa consulta deve obter o plano mais apropriado. O risco é maior uso da CPU para a execução do plano (embora isso geralmente não importe em uma consulta de tabela única e predicado único como esta, em que o plano será extremamente simples de gerar).
Cache cada variedade do plano. Você observou que passar a consulta como uma string fará com que cada parâmetro armazene em cache seu próprio plano individual. Embora isso funcione hoje, ele incha o cache do plano (consumindo mais memória do SQL Server). O risco aqui é que alguém ativará a parametrização forçada, uma opção no nível do banco de dados que irá parametrizar todas as suas consultas, sejam elas enviadas como strings ou não, e de repente você voltará a solucionar esse problema novamente.
O restante são soluções válidas, mas não para sua consulta de tabela única e predicado único. Estou apenas listando-os aqui para posteridade e clareza:
Use a dica de consulta OPTIMIZE FOR UNKNOWN ou, como gostamos de chamá-la, otimize para medíocre. Requer uma alteração na consulta e fornece um plano geralmente bom o suficiente. Isso evitará alterações aleatórias do plano de consulta devido à detecção de parâmetros, mas o risco é que ainda não seja o plano de melhor desempenho.
Use a dica de consulta OPTIMIZE FOR com um CustomerID específico. Isso também requer uma mudança de código e você otimizaria a consulta para um de seus clientes maiores. Isso obterá um plano de consulta ótimo para clientes grandes e não tão bom para clientes pequenos. O desempenho do cliente pequeno diminuirá, mas os clientes grandes não prejudicarão o sistema. O risco é que sua distribuição de clientes mude e esse não seja mais o plano certo para o aplicativo como um todo.
Use um guia de plano de consulta. Você pode obter exatamente o plano de consulta que deseja e fixar o guia do plano na memória. Aqui está a seção de livros on-line sobre guias de planos. Normalmente não sou um grande fã disso porque, se seus índices mudarem, o plano de consulta não aproveitará os novos índices. Se sua consulta mudar, o guia de plano não estará mais em vigor. De repente, o sistema pode ter um desempenho terrível e as pessoas terão esquecido que um guia de plano estava ajudando antes.
Use um procedimento armazenado com lógica manual. Tenha filiais que chamem procedimentos armazenados diferentes, um para grandes clientes e outro para pequenos clientes. Isso é usado apenas para consultas muito maiores e mais complexas que podem ter variações entre minutos e horas (ou não serem concluídas).