Olá,
Você conhece a opção avançada 'optimize for ad hoc workloads'? Sabia que esse recurso pode aperfeiçoar o cache de planos de execuções compilados e evitar o consumo desnecessário de recursos do seu servidor SQL? Acompanhe abaixo, que eu explico rapidinho!
Talvez não tão conhecida, mesmo já existindo desde o SQL Server 2008, a opção de otimizar para cargas de trabalho ad hoc serve para aumentar a eficiência do cache dos planos de execuções, quando muitos lotes de queries ad hoc são utilizados de forma exclusiva, ou seja, batchs executados uma única vez, seja por T-SQL, procedures ou views.
O SQL Server sempre cria e compila planos de execução completos para as queries executadas. No entanto, quando o volume de queries ad hoc — instruções SQL enviadas diretamente ao servidor — é expressivo, o consumo de memória para criação e compilação dos planos de execuções pode ser inadequado, pois esse cache fica totalmente alocado na memória do servidor.
Diante do supracitado, com a otimização de queries ad hoc para o SQL Server, que é um recurso avançado nativo, mudamos cada batch executado pela primeira vez, que teria um plano de execução completo compilado, para um stub (rascunho). Desta forma um stub só se torna um plano de execução completo e compilado, se for invocado mais de uma vez, mantendo assim a eficiência do cache, pois o espaço ocupado por um stub é incrivelmente menor.
sp_configure 'show advanced options',1Em seguida ativar a opção:
sp_configure 'optimize for ad hoc workloads',1Para desativar utilizamos as mesmas instruções, trocando "1" por "0".
Exemplificando, temos o seguinte cenário:
- Com a opção desativada, limpamos o cache e executamos uma query. Veja o tamanho:
Para limpar o cache usamos: DBCC FREEPROCCACHE. Repare que o tamanho do plano compilado ficou em 16384 bytes.
- Repetindo a execução (incluindo a limpeza do cache), mas com a opção ativada:
Agora, podemos observar a efetividade, pois o tamanho ficou em apenas 352 bytes. Ou seja, 97,85% menor.
- Ainda com a opção ativada, mas executando pela segunda vez:
Observe que o tamanho do plano compilado agora é de 16384 bytes novamente, pois foi invocado por mais de uma vez. Repare também na coluna 'usecounts', que mesmo sendo a segunda execução o valor considerado ainda é "1", pois somente agora o plano de execução completo foi compilado.
Nas próximas execuções da mesma query, o valor 'usecounts' continuará contabilizando, até que o cache seja limpo ou a instância reiniciada. Outro detalhe importante, é que essa opção pode ser proveitosa em quase todos os servidores SQL Server, mas para facilitar ainda mais, vou compartilhar um script que uso para validar se a ativação vai ter melhoras significativas ou não. Execute-o somente após um bom período de trabalho da instância (6hs comerciais), do contrário os números podem não ser íntegros.
IF EXISTS (
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 ' Tamanho total do cache (MB) : ' + cast(@TotalSizeInMB AS VARCHAR(max)) + '. Memória ocupada por planos de adhoc, usados somente uma vez (MB) : ' + cast(@AdHocSizeInMB AS VARCHAR(max)) + '. Percentagem de plano de cache total ocupada pelos planos adhoc, usados somente uma vez : ' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) AS VARCHAR(max)) + '%' + ' ' AS 'Informações'
,' ' + CASE
WHEN @AdHocSizeInMB > 200
OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 25%
THEN 'Ativar a opção Optimize for ad hoc workloads vai fazer uma diferença significativa'
ELSE 'Ativar a opção Optimize for ad hoc workloads não vai fazer grande diferença'
END + ' ' AS 'Recomendações'
END
Com isso, finalizamos mais um artigo da série #respirandoSQL. E aí, o que achou? Gostou? Ficou com dúvidas? Deixe-me saber, envie uma mensagem aqui.
Postar um comentário