DIRTY_PAGE_POLL Wait Type SQL Server 2012

Posted on novembro 1, 2012

2


Introdução

Para entendermos como esse novo wait type funciona, teremos que passar por alguns conceitos como: Wait Types, Dirty Pages, LazyWriter, CheckPoint.

Wait Types

Umas das maneiras mais eficientes para se resolver problemas de performance é visualizar os Waits que o SQL Server disponibiliza. Entendendo e analisando essa métrica, você saberá em qual ponto sua instância está mais vunerável e assim atacará o problema mais eficientemente. Os Wait Types são processos que aguardam sempre alguma coisa como: uma espera de uma busca no disco, assim como uma escrita, um acesso externo, uma chamada de requisição ou seja sempre está relacionando a uma espera. Para cada espera é atrelado um Wait Type, assim sabemos que esse determinado recurso está esperando, sendo assim, diminuindo a espera teremos uma melhor performance para o ambiente.  A algum tempo, foi lançado pelos engenheiros do SQL Server um repositório no qual explica cada espera que é sinalizada no SQL Server, você pode visualizar aqui – http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

 

Dirty Pages

Quando uma página é lida do disco e colocada na memória a mesma é chamada de “Clean Page”. Isso porque essa página é exatamente a mesma do que a página que está em disco. Porém quando essa página que já esta em cache é alterada a mesma é marcada como “Dirty Page”. Agora que temos essas páginas sujas em disco, o SQL Server terá que escrever essas páginas em disco após basicamente o processo de checkpoint acontecer ou o “Free Buffer List” estiver baixo.

LazyWriter

O LazyWriter é um thread que realiza a verificação contínua do tamanho do “Free Buffer List”, com isso quando está baixo ele realiza uma busca em todo o data cache para encontrar páginas que não estão sendo usadas. Se houver alguma página suja no cache, então a mesma é persistida em disco.

 

Checkpoint

O Checkpoint é um processo interno utilizado pela engine do SQL Server que verifica se há alguma transação concluída que ainda não foi persistida em disco, ou seja o CheckPoint garante que se alguma página suja estiver associada a uma transação commitada a mesma será colocada em disco. Por padrão  o CheckPoint é ativado a cada 1 minuto. Durante um processo de Restart, o processo de recuperação “Recovery Processs” irá começar a ler do último instante no qual o CheckPoint foi executado.

 

DIRTY_PAGE_POLL

Esse novo Wait Type é uma espera específica para um processo chamado de recovery time, que está totalmente ligado ao processo de CheckPoint. O recovery time é um processo que acontece quando o SQL Server é desligado repentinamente. No SQL Server 2012 temos um novo modo de realização de CheckPoint, o “CheckPoint Indirect”, permite que você configure em nível banco de dados o tempo de CheckPoint do seu banco. Com isso, uma Thread irá buscar sempre Dirty Pages para que sejam persistidas em disco fazendo com que esse Wait esteva elevado. Independente de habilitado, você poderá ter esse Wait aparecendo, porém esse é um recurso interno do SQL Server, e não é necessário a preocupação com o mesmo. O CheckPoint indirect  pode ser um ótimo recurso, iremos enteder ele em um outro post, porém é necessário que seje feito testes antes de habilitá-lo em seu banco de dados.

 

USE master

go

 

IF OBJECT_ID(‘dbo.fn_TopWaits’) IS NOT NULL

     DROP FUNCTION dbo.fn_TopWaits

GO

 

CREATE FUNCTION dbo.fn_TopWaits

(@Limite INT) RETURNS TABLE

AS

RETURN

WITH    Waits

          AS ( SELECT   wait_type ,

                        wait_time_ms / 1000. AS wait_time_sec ,

                        100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct ,

                        ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn

               FROM     sys.dm_os_wait_stats

               WHERE    wait_type NOT IN ( ‘CLR_SEMAPHORE’, ‘LAZYWRITER_SLEEP’,

                                           ‘RESOURCE_QUEUE’, ‘SLEEP_TASK’,

                                           ‘SLEEP_SYSTEMTASK’,

                                           ‘SQLTRACE_BUFFER_FLUSH’, ‘WAITFOR’,

                                           ‘LOGMGR_QUEUE’, ‘CHECKPOINT_QUEUE’,‘FT_IFTS_SCHEDULER_IDLE_WAIT’,‘FT_IFTS_SCHEDULER_IDLE_WAIT’)

             )

    SELECT TOP 100

                wait_type AS [Wait Type],

            CAST(wait_time_sec AS DECIMAL(12, 2)) AS [Wait Time (s)] ,

            CAST(pct AS DECIMAL(12, 2)) AS [Wait Time (%)]

    FROM    Waits

    WHERE   pct > @Limite

    ORDER BY wait_time_sec DESC

SELECT * FROM dbo.fn_TopWaits(10)

image

(Figura 1 – Dirty Page Pool – Wait Stats no SQL Server 2012.)

Posted in: SQL Server 2012