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