Por que NÃO usaria a opção do SQL Server "otimizar para cargas de trabalho ad hoc"?

A equipe de desenvolvimento do SQL Server trabalha com o princípio da menor surpresa - portanto, o SQL Server geralmente tem novos recursos desabilitados para manter o comportamento das versões anteriores.

Sim, otimizar para cargas de trabalho adhoc é ótimo para reduzir o inchaço do cache do plano - mas sempre teste-o primeiro!

Kalen Delaney conta uma história interessante de que ela perguntou a um de seus amigos engenheiros da Microsoft se haveria circunstâncias em que não seria apropriado habilitar isso. Ele volta vários dias depois para dizer - imagine um aplicativo que tenha muitas consultas diferentes e cada consulta seja executada exatamente duas vezes no total. Então pode ser inadequado. Basta dizer que não há muitos aplicativos como esse!

Se a maioria de suas consultas for executada mais de uma vez (não exatamente duas); provavelmente seria inadequado. A regra geral seria ativá-lo se houver muitas consultas ad hoc de uso único no banco de dados; no entanto, ainda não existem muitos aplicativos como esse.

Abaixo está um pequeno código que o ajudará a decidir se "ativar /desativar a otimização para cargas de trabalho ad hoc " será benéfico ou não. Normalmente verificamos isso como parte de nossa verificação de integridade para servidores internos e clientes.

É a opção mais segura para habilitar e é bem descrita por Brad aqui e por Glenn Berry aqui .

--- for 2008 and up .. Optimize ad-hoc for workload 
IF EXISTS (
        -- this is for 2008 and up
        SELECT 1
        FROM sys.configurations
        WHERE NAME = 'optimize for ad hoc workloads'
        )
BEGIN
    DECLARE @AdHocSizeInMB DECIMAL(14, 2)
        ,@TotalSizeInMB DECIMAL(14, 2)
        ,@ObjType NVARCHAR(34)

    SELECT @AdHocSizeInMB = SUM(CAST((
                    CASE 
                        WHEN usecounts = 1
                            AND LOWER(objtype) = 'adhoc'
                            THEN size_in_bytes
                        ELSE 0
                        END
                    ) AS DECIMAL(14, 2))) / 1048576
        ,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
    FROM sys.dm_exec_cached_plans

    SELECT 'SQL Server Configuration' AS GROUP_TYPE
        ,' Total cache plan size (MB): ' + cast(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' + cast(@AdHocSizeInMB AS VARCHAR(max)) + '.  Percentage of total cache plan occupied by adhoc plans only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) AS VARCHAR(max)) + '%' + ' ' AS COMMENTS
        ,' ' + CASE 
            WHEN @AdHocSizeInMB > 200
                OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 25%
                THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference. Ref: http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/. http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx'
            ELSE 'Setting Optimize for ad hoc workloads will make little difference !!'
            END + ' ' AS RECOMMENDATIONS
END

Ao ativar a opção " Otimizar para cargas de trabalho ad hoc ", você fará com que as consultas ad-hoc executadas na 2ª vez sejam tão lentas quanto na 1ª, porque você estará compilando um plano de execução e puxando os mesmos dados ( sem ele armazenado em cache) nas primeiras 2 vezes.
Isso pode não ser grande coisa, mas você notará isso ao testar as consultas.
Então, o que acontece agora, sem essa opção ativada e um cache cheio de consultas 1-Off Ad-Hoc?

O algoritmo de gerenciamento de cache:

À medida que esse recurso de Otimização foi introduzido, o algoritmo de gerenciamento de cache também foi atualizado.
O artigo de Kimberly Tripp também faz referência ao post de Kalen Delaney sobre essa mudança de algoritmo.
Ela explica melhor:

A alteração, na verdade, calcula um tamanho de cache de plano no qual o SQL Server reconhece que há pressão de memória e começará a remover planos do cache. Os planos a serem removidos são os planos baratos que não foram reutilizados, e isso é uma COISA BOA.

Isso significa que esses planos irritantes serão os primeiros a desaparecer quando você precisar liberar recursos.

Então agora a pergunta se torna:

    " Por que PRECISAMOS de 'Otimizar para Cargas de Trabalho Ad Hoc' quando o SQL Server cuida da remoção de planos não utilizados quando necessário? "

-hoc, então faz todo o sentido ativar esse recurso.
Você deseja evitar sobrecarregar os recursos do sistema, de modo que force a remoção do plano/dados em cache depois de usar o espaço máximo de memória do cache.

Como sei quando preciso ativar isso?

Aqui está uma consulta que escrevi para mostrar quantos Planos Ad-Hoc você atualmente armazenou em cache e quanto espaço em disco eles estão consumindo (os resultados mudarão ao longo do dia - portanto, teste-o durante um período de carga pesada):

--Great query for making the argument to use "Optimize for Ad Hoc Workloads":
SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
       S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
       CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) )[Pct_Plan_1_Use],
       S.Total_MB_1_Use,   S.Total_MB,
       CAST( (S.Total_MB_1_Use   * 1.0 / S.Total_MB  ) as Decimal(18,2) )[Pct_MB_1_Use]
  FROM
  (
    SELECT CP.objtype[CacheType],
           COUNT(*)[Total_Plan],
           SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
           SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
           SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END)[Total_Plan_1_Use],
           CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) as Decimal(12,2) )[Total_MB],
           CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END)
                      / 1024 / 1024) as Decimal(18,2) )[Total_MB_1_Use],
           CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
           CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0)
                         ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
      FROM sys.dm_exec_cached_plans as CP
     GROUP BY CP.objtype
  ) AS S
 ORDER BY S.CacheType

Resultados:

Não vou dizer " Quando você tiver X MB " ou " Se X% do seu Ad Hoc for de uso único " para ativar isso.
Não afeta Sprocs, Triggers, Views ou SQL Parametrizado/Preparado - apenas as consultas Ad-Hoc.
Minha recomendação pessoal é apenas ativar em seu ambiente de desenvolvimento, mas considere deixá-lo desativado em seu ambiente de desenvolvimento.
Digo isso apenas para o Dev, porque se você estiver otimizando uma consulta que leva um minuto ou mais para ser executada, você não deseja executá-la 3 vezes antes de ver o quão rápido ela será com ela armazenada em cache - a cada única vez que você o edita para encontrar o melhor design de otimização.
Se o seu trabalho não envolve fazer isso o dia todo, enlouqueça e peça ao seu DBA para ativá-lo em todos os lugares.

Pense em um servidor de produção que atende apenas 5 consultas diferentes, mas vários milhares delas por segundo. Você é a equipe de desenvolvimento do Microsoft SQL Server. Você vai mexer com o cache do plano. Você ativa esse comportamento por padrão, quando sabe que alguns de seus maiores e mais críticos clientes (por exemplo, implementação SAP interna da Microsoft) trabalham no mesmo campus e usam a mesma cafeteria que você?

"Por que NÃO devo usar..." No decorrer de algumas investigações de desempenho, retirar os planos do cache de planos quase em tempo real, enquanto observa a utilização dos recursos, pode ser muito útil. "Otimizar para cargas de trabalho adhoc" pode atrapalhar isso, pois os planos de stub adhoc não retornarão um plano ao consultar o cache. Em um caso como esse, se a consulta e o plano não puderem ser identificados, a configuração poderá ser desativada e ativada novamente para fins de investigação. Observe que uma alteração na configuração afeta as consultas compiladas desse ponto em diante. Além disso, sempre que alterar uma propriedade de 'servidor', verifique uma instância não-prod na mesma versão para verificar se a alteração liberará ou não o cache do plano. Eu pessoalmente odeio ser surpreendido por isso. (Por exemplo, alterar o maxdop no nível do servidor normalmente libera o cache do plano,

"O stub do plano compilado não tem um plano de execução associado a ele e a consulta do identificador do plano não retornará um XML Showplan." http://technet.microsoft.com/en-us/library/cc645587.aspx