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

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

差異

第一次查詢(使用 disk)
第二次以後查詢(使用 memory)

名詞介紹

Clean Buffer
data page cache 沒有修改過的
Dirty Buffer
data page cache 已修改過但未被寫入至磁碟中的部份
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 的情境如下

一般的 select

將資料載入 cache

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

檢查 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

使用 DBCC DROPCLEANBUFFERS
```
DBCC DROPCLEANBUFFERS
```

檢查 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

修改一筆資料

製造出 Dirty buffer

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

一般的 select

將資料載入 cache

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

檢查 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

使用 DBCC DROPCLEANBUFFERS
```
DBCC DROPCLEANBUFFERS
```

檢查 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

使用 CHECKPOINT 及 DBCC DROPCLEANBUFFERS
```
CHECKPOINT;
DBCC DROPCLEANBUFFERS ;
```

檢查 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

ckdbcc

文章目錄