Até que ponto as compilações SQL afetam o desempenho do SQL Server?

Compilações de SQL/s é uma boa métrica, mas somente quando combinada com Solicitações em lote/s . Por si só, as compilações por segundo não dizem muito.

Você está vendo 170. Se o lote req por segundo for apenas 200 (um pouco exagerado para efeito), então sim, você precisa ir ao fundo da causa (provavelmente um uso excessivo de consultas ad hoc e planos de uso único). Mas se o seu lote req por segundo está medindo cerca de 5000, então 170 compilações por segundo não é nada ruim. É uma regra geral que as compilações/s devem ser 10% ou menos do que o total de solicitações em lote/s .

Se você realmente deseja detalhar o que está sendo armazenado em cache, execute a seguinte consulta que utiliza os DMVs apropriados:

select
    db_name(st.dbid) as database_name,
    cp.bucketid,
    cp.usecounts,
    cp.size_in_bytes,
    cp.objtype,
    st.text
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st

Para obter todos os planos de uso único (uma contagem):

;with PlanCacheCte as 
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
)
select count(*)
from PlanCacheCte
where usecounts = 1

Para obter uma proporção de quantos planos de contagem de uso único você tem em comparação com todos os planos em cache:

declare @single_use_counts int, @multi_use_counts int

;with PlanCacheCte as 
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
    where cp.cacheobjtype = 'Compiled Plan'
)
select @single_use_counts = count(*)
from PlanCacheCte
where usecounts = 1

;with PlanCacheCte as 
(
    select
        db_name(st.dbid) as database_name,
        cp.bucketid,
        cp.usecounts,
        cp.size_in_bytes,
        cp.objtype,
        st.text
    from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
    where cp.cacheobjtype = 'Compiled Plan'
)
select @multi_use_counts = count(*)
from PlanCacheCte
where usecounts > 1

select
    @single_use_counts as single_use_counts,
    @multi_use_counts as multi_use_counts,
    @single_use_counts * 1.0 / (@single_use_counts + @multi_use_counts) * 100
        as percent_single_use_counts

Quanto às durações capturadas por meio de um rastreamento do SQL Server, ele não está disponível para os eventos de recompilação. Não é tão significativo ver a duração ou a dor que a compilação do plano está causando, pois não há muito o que fazer para uma situação caso a caso. A solução é tentar limitar compilações e recompilações por meio de reutilização de planos (consultas parametrizadas, procedimentos armazenados, etc.).

Existem três contadores relevantes que devem ser registrados usando PerfMon (ou outra solução de terceiros). O ponto chave é registrar essas estatísticas de alguma forma.

• Estatísticas SQL\Solicitações em lote/s
• Estatísticas SQL\Compilações SQL/s
• Estatísticas SQL\Recompilações SQL/s

Como Thomas Stringer mencionou , é bom ficar de olho na proporção de compilações/solicitação em lote. Obviamente, quanto mais baixo melhor, mas existem apenas diretrizes para o que é "bom", e só você pode decidir o que é aceitável. A quantidade absoluta de ganho de desempenho que você verá reduzindo o número de compilações depende de muitos fatores.

Também gosto de observar a proporção de recompilations/compilation , para ter uma noção da quantidade de reutilização do plano de consulta. Novamente, menor é melhor. Nesse caso, no entanto, você deseja que as recompilações aconteçam no sistema à medida que as estatísticas mudam (se o banco de dados for somente leitura e você tiver recompilações... algo pode estar errado). Assim como eu disse anteriormente, existem apenas diretrizes para o que é "bom".

O que você realmente quer fazer é tender esses números ao longo do tempo, portanto, se você vir um grande pico em qualquer uma das proporções, algo foi implantado que não está usando os planos de consulta corretamente (idealmente, isso é detectado durante o teste) - use o Shark's consultas de análise para encontrar os culpados. Além disso, aqui está um para encontrar consultas recompiladas com frequência:

SELECT TOP 50
    qs.plan_generation_num,
    qs.execution_count,
    qs.statement_start_offset,
    qs.statement_end_offset,
    st.text
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    WHERE qs.plan_generation_num > 1
    ORDER BY qs.plan_generation_num DESC

Se você também estiver gravando estatísticas para uso da CPU, todas as estatísticas podem ser correlacionadas para descobrir o quanto dói e o quanto suas correções ajudam. Na prática, descobri que mesmo uma única estratégia de plano de consulta ruim em um sproc central pode deixar um servidor de joelhos; obviamente YMMV.