Estatísticas de espera intermitentes de RESOURCE_SEMAPHORE_QUERY_COMPILE

Acredito que você verá esse sintoma se tiver MUITOS planos de consulta grandes que estão lutando por memória para compilar (isso tem muito pouco a ver com a execução da consulta em si). Para acertar isso, suspeito que você esteja usando um ORM ou algum tipo de aplicativo que gere muitas consultas exclusivas, mas relativamente complexas. O SQL Server pode estar sob pressão de memória devido a coisas como grandes operações de consulta, mas, pensando bem, é mais provável que seu sistema esteja configurado com muito menos memória do que precisa (ou nunca há memória suficiente para satisfazer todas as consultas que você 're tentando compilar, ou há outros processos na caixa que estão roubando memória do SQL Server).

Você pode dar uma olhada no que o SQL Server está configurado usando:

EXEC sp_configure 'max server memory';    -- max configured in MB

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN
  (
    'Total Server Memory (KB)',    -- max currently granted
    'Target Server Memory (KB)'    -- how much SQL Server wished it had
  );

Você pode identificar os planos em cache que exigiram mais memória de compilação com a seguinte consulta de Jonathan Kehayias , ligeiramente adaptada:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (10) CompileTime_ms, CompileCPU_ms, CompileMemory_KB,
  qs.execution_count,
  qs.total_elapsed_time/1000.0 AS duration_ms,
  qs.total_worker_time/1000.0 as cputime_ms,
  (qs.total_elapsed_time/qs.execution_count)/1000.0 AS avg_duration_ms,
  (qs.total_worker_time/qs.execution_count)/1000.0 AS avg_cputime_ms,
  qs.max_elapsed_time/1000.0 AS max_duration_ms,
  qs.max_worker_time/1000.0 AS max_cputime_ms,
  SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
    (CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
     END - qs.statement_start_offset) / 2 + 1) AS StmtText,
  query_hash, query_plan_hash
FROM
(
  SELECT 
    c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') AS QueryHash,
    c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)') AS QueryPlanHash,
    c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,
    c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,
    c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,
    qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)
) AS tab
JOIN sys.dm_exec_query_stats AS qs ON tab.QueryHash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY CompileMemory_KB DESC
OPTION (RECOMPILE, MAXDOP 1);

Você pode ver como o cache do plano está sendo usado com o seguinte:

SELECT objtype, cacheobjtype,
    AVG(size_in_bytes*1.0)/1024.0/1024.0,
    MAX(size_in_bytes)/1024.0/1024.0,
    SUM(size_in_bytes)/1024.0/1024.0,
    COUNT(*)
FROM sys.dm_exec_cached_plans
GROUP BY GROUPING SETS ((),(objtype, cacheobjtype))
ORDER BY objtype, cacheobjtype;

Quando você estiver enfrentando altas esperas de semáforo, verifique se esses resultados de consulta variam significativamente durante a atividade "normal":

SELECT resource_semaphore_id, -- 0 = regular, 1 = "small query"
  pool_id,
  available_memory_kb,
  total_memory_kb,
  target_memory_kb
FROM sys.dm_exec_query_resource_semaphores;

SELECT StmtText = SUBSTRING(st.[text], (qs.statement_start_offset / 2) + 1,
        (CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
         END - qs.statement_start_offset) / 2 + 1),
  r.start_time, r.[status], DB_NAME(r.database_id), r.wait_type, 
  r.last_wait_type, r.total_elapsed_time, r.granted_query_memory,
  m.requested_memory_kb, m.granted_memory_kb, m.required_memory_kb,
  m.used_memory_kb
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_query_stats AS qs
ON r.plan_handle = qs.plan_handle
INNER JOIN sys.dm_exec_query_memory_grants AS m
ON r.request_id = m.request_id
AND r.plan_handle = m.plan_handle
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) AS st;

E você também pode querer ver como a memória é distribuída:

DBCC MEMORYSTATUS;

E há algumas boas informações aqui sobre por que você pode estar vendo um grande número de compilações/recompilações (o que contribuirá para essa espera):

http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

http://technet.microsoft.com/en-us/library/cc293620.aspx

Você pode verificar se há altas contagens de compilação/recompilação usando os seguintes contadores:

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN 
  (
    'SQL Compilations/sec',
    'SQL Re-Compilations/sec'
  );

E você pode verificar se há pressão de memória interna levando a despejos - contadores diferentes de zero aqui indicam que algo não é bom está acontecendo com o cache do plano:

SELECT * FROM sys.dm_os_memory_cache_clock_hands 
  WHERE [type] IN (N'CACHESTORE_SQLCP', N'CACHESTORE_OBJCP');

OBSERVAÇÃO A maioria dessas métricas não tem uma mágica "oh meu Deus, preciso entrar em pânico ou fazer alguma coisa!" limite. O que você precisa fazer é fazer medições durante a atividade normal do sistema e determinar onde estão esses limites para seu hardware, configuração e carga de trabalho. Quando você entra em pânico , faça alguma coisa é quando duas condições são verdadeiras:

1. as métricas variam significativamente dos valores normais; e,
2. na verdade, há um problema de desempenho ocorrendo (como picos de CPU) - mas apenas se eles estiverem realmente interferindo em alguma coisa. Além de ver o aumento das CPUs, você está vendo algum outro sintoma? Em outras palavras, o pico é o sintoma ou o pico está causando outros sintomas? Os usuários do sistema perceberiam? Muita gente sempre vai atrás do consumidor que espera mais alto, simplesmente porque é o mais alto. Algo sempre será o maior consumidor de espera - você deve saber que está variando o suficiente da atividade normal para indicar um problema ou alguma mudança significativa.

Optimize for ad hoc workloadsé uma ótima configuração para 99% das cargas de trabalho existentes, mas não será muito útil para reduzir os custos de compilação - visa reduzir o inchaço do cache do plano, impedindo que um plano de uso único armazene todo o plano até que seja executado duas vezes . Mesmo quando você armazena apenas o stub no cache do plano, ainda precisa compilar o plano completo para a execução da consulta. Talvez o que @Kahn quisesse recomendar fosse definir a parametrização no nível do banco de dados como forçada , o que potencialmente fornecerá uma melhor reutilização do plano (mas realmente depende de quão exclusivas são todas essas consultas de alto custo).

Também algumas boas informações neste white paper sobre armazenamento em cache e compilação de planos.

De longe, o motivo mais comum pelo qual vi essas esperas aparecerem é resultado de índices fragmentados ou insuficientes e estatísticas que têm tamanho de amostra insuficiente ou são obsoletas. Isso resulta em verificações maciças de tabelas completas sobrecarregando toda a memória, o que, por sua vez, produz um sintoma que costumamos ver como RESOURCE_SEMAPHORE_QUERY_COMPILE.

A maneira mais fácil de verificar isso é verificar se as consultas executam varreduras completas de tabela/índice, quando deveriam estar fazendo buscas de índice. Se você tiver uma consulta de problema com a qual pode reproduzir o problema - torna-se muito fácil diagnosticar e corrigir isso.

Eu verificaria os índices nas tabelas afetadas por essas consultas problemáticas - ou seja. verifique a fragmentação do índice, possíveis índices filtrados que não são usados, índices ausentes que você pode querer criar, etc. Além disso, atualize suas estatísticas com FULLSCAN o mais rápido possível.

Um bom ponto a lembrar é que sua tabela de problemas pode não ser a única que precisa disso. Por exemplo, se você tem uma consulta que busca dados de 10 tabelas, o planejador de execução pode ocasionalmente mostrar que não está usando o índice da tabela 1, mas quando você verifica o índice da tabela 1, na verdade está ok. O planejador de consulta pode resolver buscar dados na tabela 1 com uma varredura completa da tabela corretamente, porque um índice com falha/insuficiente na tabela 7, por exemplo, retornou tantos dados que esta seria a opção mais rápida. Portanto, diagnosticar isso às vezes pode ser complicado.

Além disso, se você tiver muitas consultas code-behind com apenas algumas alterações nos valores das variáveis, por exemplo, convém verificar a ativação da otimização para cargas de trabalho ad hoc . Basicamente, o que ele faz é armazenar um esboço do plano compilado em vez do plano inteiro, economizando recursos quando você nunca obtém exatamente os mesmos planos todas as vezes.