關於清除 SQL Server 查詢快取的那些事

之前的文章 試著學會看懂 SQL Server IO 統計資訊 中，有粗略地介紹 SQL Server IO 的統計資訊，其中 logical reads(邏輯讀取) 是我們用來進行效能調校的重要依據，文中也提到 cache 來源是 physical reads(實體讀取)、read-ahead reads(讀取前讀取) 。在為了比較使用 memory 與使用 disk 的查詢時間差異，就得透過清除查詢 cache 來進行，接著就來看看該如何清除查詢 cache.

差異

1. 第一次查詢(使用 disk)

   

2. 第二次以後查詢(使用 memory)

   

名詞介紹

1. Clean Buffer

   data page cache 沒有修改過的

2. Dirty Buffer

   data page cache 已修改過但未被寫入至磁碟中的部份

3. Cold Buffer Cache

   data page 還沒載入 memory 中，需要從磁碟讀取

清除 Cache 語法

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

關於 DBCC DROPCLEANBUFFERS

• 僅清除 Clean Buffer，Dirty Buffer無法被清除
• 語法

  • Syntax for SQL Server
    DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]

  • Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
    DBCC DROPCLEANBUFFERS ( COMPUTE | ALL ) [ WITH NO_INFOMSGS ]

參數說明

• WITH NO_INFOMSGS

  隱藏所有參考訊息。Azure SQL Data Warehouse 跟 Parallel Data Warehouse 環境下預設隱藏

• COMPUTE

  • 僅 Azure SQL Data Warehouse 跟 Parallel Data Warehouse 有效
  • 清除計算節點 cache

• ALL

  • 僅 Azure SQL Data Warehouse 跟 Parallel Data Warehouse 有效
  • 清除所有節點 cache
  • 預設值

為什麼需要 CHECKPOINT

CHECKPOINT 會將 Dirty Buffer 強制寫入 disk

我們來模擬Dirty Buffer 的情境如下

1. 一般的 select

• 將資料載入 cache

  SELECT * FROM [AdventureWorks2014].[Sales].[Customer]
  

2. 檢查 cache

• cache 有 122 筆資料

  select sysObj.name,* 
  from sys.dm_os_buffer_descriptors bufferDescriptors
  INNER JOIN sys.allocation_units AllocUnits ON bufferDescriptors.allocation_unit_id = AllocUnits.allocation_unit_id
  INNER JOIN sys.partitions Partitions ON AllocUnits.container_id = Partitions.hobt_id
  INNER JOIN sys.objects sysObj ON Partitions.object_id = sysObj.object_id
  WHERE bufferDescriptors.database_id = DB_ID()
  AND sysObj.is_ms_shipped = 0
  

  

3. 使用 DBCC DROPCLEANBUFFERS

   DBCC DROPCLEANBUFFERS
   

4. 檢查 cache

• 因為僅有 Clean Buffer，所以可以全部清除

  select sysObj.name,*
  from sys.dm_os_buffer_descriptors bufferDescriptors
  INNER JOIN sys.allocation_units AllocUnits ON bufferDescriptors.allocation_unit_id = AllocUnits.allocation_unit_id
  INNER JOIN sys.partitions Partitions ON AllocUnits.container_id = Partitions.hobt_id
  INNER JOIN sys.objects sysObj ON Partitions.object_id = sysObj.object_id
  WHERE bufferDescriptors.database_id = DB_ID()
  AND sysObj.is_ms_shipped = 0
  

  

5. 修改一筆資料

• 製造出 Dirty buffer

  UPDATE [Sales].[Customer]
  SET [ModifiedDate] = GETDATE()
  WHERE [CustomerID]=1
  GO
  

6. 一般的 select

• 將資料載入 cache

  SELECT * FROM [AdventureWorks2014].[Sales].[Customer]
  

7. 檢查 cache

• cache 數量不變， 有 122 筆資料

  select sysObj.name,* 
  from sys.dm_os_buffer_descriptors bufferDescriptors
  INNER JOIN sys.allocation_units AllocUnits ON bufferDescriptors.allocation_unit_id = AllocUnits.allocation_unit_id
  INNER JOIN sys.partitions Partitions ON AllocUnits.container_id = Partitions.hobt_id
  INNER JOIN sys.objects sysObj ON Partitions.object_id = sysObj.object_id
  WHERE bufferDescriptors.database_id = DB_ID()
  AND sysObj.is_ms_shipped = 0
  

  

8. 使用 DBCC DROPCLEANBUFFERS

   DBCC DROPCLEANBUFFERS
   

9. 檢查 cache

• 因為有 Dirty Buffer，cache 無法全部清除

  select sysObj.name,* 
  from sys.dm_os_buffer_descriptors bufferDescriptors
  INNER JOIN sys.allocation_units AllocUnits ON bufferDescriptors.allocation_unit_id = AllocUnits.allocation_unit_id
  INNER JOIN sys.partitions Partitions ON AllocUnits.container_id = Partitions.hobt_id
  INNER JOIN sys.objects sysObj ON Partitions.object_id = sysObj.object_id
  WHERE bufferDescriptors.database_id = DB_ID()
  AND sysObj.is_ms_shipped = 0
  

  

10. 使用 CHECKPOINT 及 DBCC DROPCLEANBUFFERS

    CHECKPOINT;
    DBCC DROPCLEANBUFFERS ;
    

11. 檢查 cache

• CHECKPOINT 將 Dirty buffer 被寫入 disk
• DBCC DROPCLEANBUFFERS清除 Clean buufer
• cache 即可全數清除

  select sysObj.name,* 
  from sys.dm_os_buffer_descriptors bufferDescriptors
  INNER JOIN sys.allocation_units AllocUnits ON bufferDescriptors.allocation_unit_id = AllocUnits.allocation_unit_id
  INNER JOIN sys.partitions Partitions ON AllocUnits.container_id = Partitions.hobt_id
  INNER JOIN sys.objects sysObj ON Partitions.object_id = sysObj.object_id
  WHERE bufferDescriptors.database_id = DB_ID()
  AND sysObj.is_ms_shipped = 0
  

  

參考資料

1. SQL Server: What is a COLD, DIRTY or CLEAN Buffer?
2. DBCC DROPCLEANBUFFERS and CHECKPOINT
3. DB_ID
4. Checkpoint
5. DBCC DROPCLEANBUFFERS