Eu sei o que esta opção faz e como habilitá-la. Minha pergunta é o que aconteceria se eu permitisse isso.
Sem dar muitas informações, nosso sistema de contabilidade é um produto Microsoft Dynamics e usa uma VM, 32 GB de RAM (28 GB disponíveis para SQL Server [2008 R2]). Eles pediram que o fornecedor viesse e desse uma olhada em nossa configuração para resolver certos problemas de desempenho que a equipe de contabilidade tem observado. Eles até pediram a outro DBA para dar uma olhada em nossa configuração. Uma de suas recomendações foi "olhar para os índices ausentes". Poderíamos dizer que quase todas as instâncias do servidor SQL existentes, cada tabela tem um índice não clusterizado exclusivo, não é minha escolha, mas me disseram que fazer qualquer alteração nos índices nas tabelas que o software acessa pode causar problemas de acordo com o fornecedor. A segunda foi habilitar a 'otimização para cargas de trabalho ad hoc'. EU'
Com a otimização para cargas de trabalho ad hoc, sei que os planos de uso único são armazenados como um stub e todo o plano não é realmente mantido em cache até que o plano seja executado duas vezes. Com um sistema como esse, REALMENTE veremos algum tipo de ganho de desempenho? De acordo com o artigo de Kimberly Tripp, executei esta consulta:
SELECT objtype AS [CacheType]
,count_big(*) AS [Total Plans]
,sum(cast(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs]
,avg(usecounts) AS [Avg Use Count]
,sum(cast((
CASE
WHEN usecounts = 1
THEN size_in_bytes
ELSE 0
END
) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs - USE Count 1]
,sum(CASE
WHEN usecounts = 1
THEN 1
ELSE 0
END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
e aqui está o resultado que obtenho:
Agora, como podemos ver, temos aproximadamente 1,7 GB de planos de uso único. Provavelmente vale a pena ativar essa opção, pois a caixa tem 28 GB disponíveis para o servidor SQL, certo? Bem, estamos falando de liberar talvez 1-1,25 GB, pois os stubs ainda ocupam espaço, mas não tanto. Fiz uma consulta para extrair todos os planos de uso único e obter seu tamanho, nossos maiores planos têm cerca de 1-1,5 MB.
Então, aqui estão minhas perguntas (desculpe, demorou um pouco para chegar a elas):
- Realmente achamos que veremos um ganho de desempenho mensurável aqui? Em caso afirmativo, como podemos quantificá-lo além de olhar para o espaço usado na consulta acima?
- Compreensivelmente, hesito em lançar interruptores como este em sistemas de produção. De quais implicações devo ser cauteloso? O cache do procedimento será limpo e reconstruído? Há algum problema sobre o qual devo estar ciente ao ativar isso?
Kris,
Isso depende, mas meu instinto com os dados que você forneceu é - não. Claro, você potencialmente economizará parte desse espaço, pois um stub de plano ainda ocupará memória, mas não tanto (em comparação com seus planos de 1 MB). Então você vai net memory, nós entendemos isso. No entanto, não sabemos quantos desses planos foram executados uma única vez e, em algum momento depois, enquanto ainda estavam no cache, foram executados novamente. Isso levanta a questão sobre compilações/recompilações e a utilização da CPU para acompanhar. Se você tiver um bom espaço livre, pode ser um problema trivial (trocadilho intencional).
Se o seu servidor não estiver sob pressão de memória, não esperaria ver muita melhoria em termos de "desempenho", dependendo de como você deseja classificar isso. Se você estiver trocando e tendo uma leve pressão de memória, isso pode aliviá-la por alguns momentos - embora aumentar a memória da VM tenha o mesmo efeito em uma implementação muito mais rápida sem custo de efeito colateral negativo.
De acordo com o BOL, isso não afetará nada atualmente no cache do plano: "Definir a otimização para cargas de trabalho ad hoc como 1 afeta apenas os novos planos; os planos que já estão no cache do plano não são afetados". http://msdn.microsoft.com/en-us/library/cc645587(v=sql.105).aspx
Você pode ver o potencial (dependendo da eventual reutilização) de acertos da CPU para compilações do plano (já que teria que fazer isso duas vezes, uma para a execução original e outra para a segunda quando for armazenada).
Algumas outras "estranhezas" incluiriam ferramentas de monitoramento, especialmente se elas estiverem capturando planos de execução, pois os esboços de planos não têm nenhum associado a eles. Alguns resultados estranhos podem vir de ferramentas que esperam que haja um associado o tempo todo.
Não estou muito familiarizado com o Dynamics, mas o IIRC tem um guia de configuração específico da Microsoft, como o SharePoint. Eu verificaria se isso não invalidaria sua capacidade de suporte para o produto.