Optimize for Ad Hoc Workloads - SQL Server 2008


Hoje irei falar sobre a nova funcionalidade que surgiu a partir do SQL Server 2008 chamada de “Optimize for Ad Hoc Workloads”.  


Para cada batch executada o SQL Server irá armazenar um plano de execução em cache, contudo, se uma grande quantidade de queries ad hoc que não são parametrizáveis é disparada contra uma instância do SQL Server, vários planos de execução serão gerados, ocupando grandes quantidades de memória roubada do buffer pool, sem que os planos armazenados sejam efetivamente reutilizados, isto é o que chamamos de procedure cache bloating, detalhes do mesmo podem ser vistos no artigo da Kimberly Tripp no link: http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/.


Habilitando esta nova funcionalidade que surgiu no SQL 2008, o mesmo só irá armazenar o plano de execução de uma query ad hoc em cache caso ela seja chamada uma segunda vez, na primeira execução ele armazenara apenas um “Compiled Plan Stub” ao invés do plano inteiro de execução “Full Compiled Plan”, o que traz um grande alivio na utilização da memória pelo “plan cache”.


Se a quantidade de queries ad hoc disparadas contra uma instância do SQL Server é muito grande, e se estas queries exigirem uma grande de memória para compilar, isto pode gerar bloqueios nos gateways de compilação (wait type RESOURCE_SEMAPHORE_QUERY_COMPILE) e problemas de performance. Os gateways de compilação aparecem na saída do DBCC MEMORYSTATUS, mais informações podem ser vistas no link: http://support.microsoft.com/kb/907877/en-us.


O valor de Configured Units para Small Gateway, Medium Gateway e Big Gateway indicam o número máximo de compilações simultâneas que podem ocorrer, de acordo com a complexidade.


Irei demonstrar abaixo o comportamento do SQL Server com e sem a opção, rodando o sp_configure perceba que o parâmetro está desabilitado:





Agora irei executar uma query ad hoc contra uma tabela de teste:


select * from SQLMAGU



Usando a query abaixo podemos perceber que o SQL armazenou o plano de execução inteiro para minha consulta:






Agora irei rodar os comandos abaixo para habilitar a nova funcionalidade:


sp_configure 'show advanced options',1

RECONFIGURE

go



sp_configure 'optimize for ad hoc workloads',1

RECONFIGURE



Irei limpar o cache usando as queries abaixo:


DBCC FREEPROCCACHE



DBCC DROPCLEANBUFFERS



E irei rodar novamente aquela minha consulta inicial, veja como ficou agora nosso cache, perceba que ele armazenou apenas o “stub” do plano:






Rodando a query uma vez mais, perceba que ai sim o SQL irá armazenar o seu plano inteiro de execução no cache:



 



Para saber se o seu cache é dominado por queries ad hoc você pode utilizar a query abaixo da Kimberly Tripp (SQL Skills) :



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

go



O retorno da query será igual da imagem abaixo:





Abaixo estão mais alguns links de referência que usei para escrever este post:









Por hoje é só pessoal, espero que tenham gostado do post!

Nenhum comentário:

Postar um comentário