Optimize For Ad Hoc Workloads

Posted on maio 24, 2012

6


Essa opção aumenta a eficiência do Plan Cache em relação a consultas Ad-Hoc.

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;

GO

image

(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

RECONFIGURE

Limpando o Cache….

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

 

USE AdventureWorks

GO

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;

GO

image

(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…

SELECT * FROM HumanResources.Shift

GO 2

 

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;

GO

image

(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

go

 

CREATE TABLE dbo.DadosAdHoc(DadosUnique UNIQUEIDENTIFIER);

GO

 

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

 

FETCH NEXT FROM curDadosAdHoc

INTO @NEWID;

 

EXEC (‘SELECT DadosUnique FROM dbo.DadosAdHoc WHERE DadosUnique = ‘ + ”” + @NEWID + ””)

END

CLOSE curDadosAdHoc;

DEALLOCATE curDadosAdHoc;

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = ‘CACHESTORE_SQLCP’

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”

sp_configure ‘show advanced options’,1

RECONFIGURE

go

sp_configure ‘optimize for ad hoc workloads’,1

RECONFIGURE

limpando o cache….

DBCC FREESYSTEMCACHE(‘SQL Plans’)

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = ‘CACHESTORE_SQLCP’

Quantidade de Páginas = 160

Criando novamente a tabela e realizando as inserções vemos que…

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

FETCH NEXT FROM curDadosAdHoc

INTO @NEWID;

EXEC (‘SELECT DadosUnique FROM dbo.DadosAdHoc WHERE DadosUnique = ‘ + ”” + @NEWID + ””)

END

CLOSE curDadosAdHoc;

DEALLOCATE curDadosAdHoc;

SELECT single_pages_kb

FROM sys.dm_os_memory_clerks

WHERE type = ‘CACHESTORE_SQLCP’

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.

Em seu Ambiente!

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]

        FROM sys.dm_exec_cached_plans

        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

        FROM sys.dm_exec_cached_plans

 

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……

image

Vamos otimizar?………