Table of Contents IntroduçãoExemplo e DemonstraçãoEm seu Ambiente!
Foi implementada a partir do SQL Server 2008 e permite se habilitado, quando o SQL Server precisar compilar um batch pela primeira vez, em vez de salvar um Plano de Execução Completo (Full Compiled Plan) como é realizado por padrão o mesmo ira armazenar do que chamamos de Stub Compiled Plan. Sendo assim o armazenamento desse plano é muito menos custoso para a Engine do banco de dados ocupando aproximadamente 18 Byte.
Cada Batch (T-SQL, Procedure, View…) quando executado cria um plano de execução no qual é armazenado dentro do banco de dados para caso utilizado novamente seja reusado. Por padrão quando passamos uma consulta para o banco de dados é necessário que o SQL Server busque essas informações e assim armazene um plano de execução. Porém, muitas consultas que são realizadas dentro do banco de dados são consultas nas quais provavelmente não serão executadas novamente, fazendo com que a mesma ocupe espaço e recurso da máquina, essas consultas são chamadas de Ad-Hoc.
Para demonstrar melhor esse recurso, iremos realizar o seguinte exemplo com o banco de dados AdventureWorks….
Primeiramente iremos limpar o Cache….
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
USE AdventureWorks
GO
Agora iremos realizar uma consulta simples no banco de dados
SELECT * FROM HumanResources.Shift
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
TEXT LIKE ‘SELECT * FROM HumanResources.Shift%’
ORDER BY usecounts DESC;
(Figura 1 –Informações do Plan Cache do SQL Server – Compiled Plan.)
Esse é o comportamento padrão do SQL Server. Sendo assim se essa consulta não for executada novamente, esse plano de execução ficará dentro do Plan Cache ocupando espaço e recurso.
Agora, alterando o comportamento padrão do SQL Server…..
sp_configure ‘show advanced options’,1
RECONFIGURE
go
sp_configure ‘optimize for ad hoc workloads’,1
Limpando o Cache….
(Figura 2 –Informações do Plan Cache do SQL Server – Compiled Plan Stub.)
Como alteramos o comportamento do SQL Server, na primeira vez que a consulta for realizada, a mesma criará um “Compiled Plan Stub” ou seja como explicado anteriormente, o SQL Server só fará desse plano um Plano de Execução FULL quando o mesmo for executado por mais de 2 vezes…
GO 2
(Figura 3 –Informações do Plan Cache do SQL Server – Consulta)
Sendo assim, depois de realizada mais de uma vez, a mesma criara um plano de execução completo para essa consulta.
Agora veremos um exemplo que mostrará a melhoria que podemos ter quando habilitado….
Iremos criar uma tabela e adicionar dados para a mesma.
USE tempdb
CREATE TABLE dbo.DadosAdHoc(DadosUnique UNIQUEIDENTIFIER);
INSERT dbo.DadosAdHoc
VALUES (NEWID())
GO 500
DBCC FREESYSTEMCACHE(‘SQL Plans’)
Agora iremos saber a quantidade de Single_Pages que possuímos dentro do Cache do SQL Server utilizando a seguinte consulta.
SELECT single_pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = ‘CACHESTORE_SQLCP’
Quantidade de Páginas = 160
Agora iremos criar um cursor no qual criará diversas consultas a partir do NEWID() que criamos, sendo assim todas as consultas serão realizadas somente uma vez, fazendo assim com que o Single_Pages_Kb cresçam isso porque são Consultas Ad-Hoc.
DECLARE @NEWID varchar(36)
DECLARE curDadosAdHoc CURSOR FOR
SELECT DadosUnique
FROM dbo.DadosAdHoc
ORDER BY DadosUnique
OPEN curDadosAdHoc
FETCH NEXT FROM curDadosAdHoc
INTO @NEWID;
EXEC (‘SELECT DadosUnique FROM dbo.DadosAdHoc WHERE DadosUnique = ‘ + ”” + @NEWID +””)
WHILE @@FETCH_STATUS = 0
BEGIN
END
CLOSE curDadosAdHoc;
DEALLOCATE curDadosAdHoc;
Quantidade de Páginas = 13.176
Vemos claramente que como cada consulta foi realizada somente uma vez, cada uma teve que possuir um plano de execução, fazendo assim com que a quantidade de páginas aumentassem absurdamente.
Agora, para otimizarmos esse cenário, iremos habilitar “Optimize for Ad Hoc Workloads”
limpando o cache….
Criando novamente a tabela e realizando as inserções vemos que…
Quantidade de Páginas = 880
Ou seja chegamos a seguinte conclusão……
Opção – Optimize For Ad Hoc Workloads Não Habilitado = 13.176 Single Pages
Opção – Optimize For Ad Hoc Workloads Habilitado = 880 Single Pages
Ou seja neste caso teriamos um ganho bem considerável em nosso cache, fazendo com que as consultas que fossem realizadas somente um vez não ocupassem tanto o data cache.
Para saber se seu ambiente necessita dessa configuração, realize o seguinte script…
SET NOCOUNT ON
SELECT objtype AS [Cache Store Type],
COUNT_BIG(*) AS [Total Num Of Plans],
SUM(CAST(size_in_bytes as decimal(14,2))) / 1048576 AS [Total Size In MB],
AVG(usecounts) AS [All Plans - Ave Use Count],
SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(14,2)))/1048576 AS [Size in MB of plans with a Use count = 1],
SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Number of of plans with a Use count = 1]
GROUP BY objtype
ORDER BY [Size in MB of plans with a Use count = 1] DESC
DECLARE @AdHocSizeInMB decimal (14,2), @TotalSizeInMB decimal (14,2)
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
SELECT @AdHocSizeInMB as [Current memory occupied by adhoc plans only used once (MB)],
@TotalSizeInMB as [Total cache plan size (MB)],
CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 as decimal(14,2)) as [% of total cache plan occupied by adhoc plans only used once]
IF @AdHocSizeInMB > 200 or((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 – 200MB or > 25%
SELECT ‘Switch on Optimize for ad hoc workloads as it will make a significant difference’ as[Recommendation]
ELSE
SELECT ‘Setting Optimize for ad hoc workloads will make little difference’ as[Recommendation]
Esse script foi retirado do seguinte site - http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/
Sendo assim você verá a quantidade de consultas Ad Hoc dentro do seu servidor e assim verá se é necessário que esse recurso seja habilitado, lembrando que se habilitado os planos que já estão em cache não serão tocados.
Estou precisando realmente realizar a otimização do meu Cache… Segue foto……
Vamos otimizar?………