DBCC DROPCLEANBUFFERS and dirty pages

É comum que, durante a optimização de uma query, façamos a limpeza da cache do SQL Server para conseguirmos comprar resultados semelhantes.
Para isso é normal serem utilizados os seguintes comandos:

DBCC FREEPROCCACHE;
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO

Contudo, tal como diz o nome do comando DROPCLEANBUFFERS, este comando apenas limpa as clean pages.
Para que se possa “limpar” também as dirty pages é necessário utilizar o comando CHECKPOINT para que todas as dirty pages sejam escritas em disco.

Assim, ficamos com os seguintes comandos:

DBCC FREEPROCCACHE;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO

Neste contexto, segue um script que nos informa quantas clean e dirty pages estão no buffer pool:

WITH CountCleanAndDiryPagesInBufferPool (DatabaseID, NumberOfCleanPages, NumberOfDirtyPages)
AS
  (
  SELECT 
      BD.database_id AS 'DatabaseID'
    , SUM (CASE WHEN BD.is_modified = 0 THEN 1 ELSE 0 END) AS 'NumberOfCleanPages'
    , SUM (CASE WHEN BD.is_modified = 1 THEN 1 ELSE 0 END) AS 'NumberOfDirtyPages'
  FROM
      sys.dm_os_buffer_descriptors AS BD
  WHERE
      BD.database_id NOT IN (1, 2, 3, 4, 32767) --to remove system databases
  GROUP BY
      BD.database_id 
  )
SELECT 
    DatabaseID
  , CASE WHEN DatabaseID = 32767 
      THEN 'Resource DB'
      ELSE DB_NAME (DatabaseID)
    END AS 'DatabaseName'
  , NumberOfCleanPages
  , NumberOfDirtyPages
  , NumberOfCleanPages * 8 / 1024 AS 'CleanPagesMB'
  , NumberOfDirtyPages * 8 / 1024 AS 'DirtyPageMB'
FROM 
    CountCleanAndDiryPagesInBufferPool
ORDER BY
    DatabaseID ASC
--, DatabaseName ASC

Saliento porém que, para o caso da optimização de queries podemos utilizar a opção plan_handle do comando DBCC FREEPROCCACHE para limpar apenas o plano de execução da query que estamos a optimizar.
Para obter o plan_handle da query podemos utilizar o seguinte script:

SELECT
    CachedPlans.plan_handle
  , SQLText.text
FROM 
  sys.dm_exec_cached_plans AS CachedPlans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS SQLText
WHERE
  SQLText.text LIKE '%TABLE_NAME%';
GO

Após obter o plan_handle de uma determinada query podemos apagar o seu plano de execução deste modo:

DBCC FREEPROCCACHE (plan_handle);
GO

Referências: