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

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

差異

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

    fromdisk

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

    frommemory

名詞介紹

  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 BufferDirty 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 WarehouseParallel Data Warehouse 環境下預設隱藏

    • COMPUTE

      • Azure SQL Data WarehouseParallel Data Warehouse 有效
      • 清除計算節點 cache
    • ALL

      • Azure SQL Data WarehouseParallel 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
      

      NORAMLCACHE

  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
      

      cleanbufferdbcc

  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
      

      update

  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
      

      updatedbcc

  10. 使用 CHECKPOINTDBCC DROPCLEANBUFFERS

    CHECKPOINT;
    DBCC DROPCLEANBUFFERS ;
    
  11. 檢查 cache

    • CHECKPOINTDirty 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
      

      ckdbcc

參考資料

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