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.