性能調優之：緩存

在執行任何查詢時，SQL Server都會將數據讀取到內存，數據使用之后，不會立即釋放，而是會緩存在內存Buffer中，當再次執行相同的查詢時，如果所需數據全部緩存在內存中，那么SQL Server不會產生Disk IO操作，立即返回查詢結果，這是SQL Server的性能優化機制。

一，主要的內存消費者（Memory Consumer)

1，數據緩存（Data Cache）

Data Cache是存儲數據頁（Data Page）的緩沖區，當SQL Server需要讀取數據文件（File）中的數據頁（Data Page）時，SQL Server會把整個Page都調入內存（內存中的一個Page叫做buffer），Page是數據訪問的最小單元。

當用戶修改了某個Page上的數據時，SQL Server 會先在內存中修改Buffer，但是不會立即將這個數據葉寫回硬盤，而是等到CheckPoint或lazy Writer進程運行時集中處理。當用戶讀取某個Page后，如果SQL Server沒有內存壓力，它不會在內存中刪除這個Page，因為內存中的數據頁始終存放著數據的最新狀態，如果有其他用戶使用這個Page，SQL Server 不需要從硬盤中讀取一次，節省語句執行的時間。理想情況是SQL Server將用戶需要訪問的所有數據都緩存在內存中，SQL Server 永遠不需要去硬盤讀取數據，只需要在CheckPoint 或 lazy Write運行時把修改過的頁面寫回硬盤即可

2，查詢計劃緩存（Query Plan Cache）

存儲查詢語句和存儲過程的執行計劃，以供重用，而不需要重新編譯（Compile），因為編譯查詢語句產生執行計劃是一個非常耗費資源的過程。

二，查看內存消耗

在SQL Server中，只有內存書記員（Memory Clerk）能夠分配內存，Memory Clerk會記錄已經分配內存的數量，任何一個需要使用內存的對象，必須創建自己的Memory Clerk，并使用該Memory clerk來分配內存。

1，查看Memory clerk分配的內存量

 View Code

對于內存結點64，只在DAC中使用。

2，統計Memory Clerk分配的內存總量

 View Code

消耗內存較大的Clerk是：

• MEMORYCLERK_SQLBUFFERPOOL：基本是Buffer Pool中page的大小

• OBJECTSTORE_LOCK_MANAGER：鎖結構使用的內存，當發生嚴重的鎖阻塞時，這表明系統中，存儲大量鎖，造成鎖管理占用大量的內存；

• CACHESTORE_OBJCP：觸發器和存儲過程等模塊（Module）的執行計劃占用的緩存空間；

• CACHESTORE_SQLCP：動態TSQL語句，即席（Adhoc）查詢和預編譯（Prepared） TSQL的執行計劃緩存；

• CACHESTORE_COLUMNSTOREOBJECTPOOL：列存儲索引（ColumnStore Index）占用的緩存

3，查看緩存中的數據頁

當數據頁從硬盤讀取到內存之后，該數據頁被復制到緩沖池（Buffer Pool），供SQL Server重用。每個緩存的數據頁都有一個緩存描述器（Buffer Descriptor），用戶唯一標識內存中的數據頁，在SQL Server實例中緩存的每一個數據頁，都能從 sys.dm_os_buffer_descriptors 查看緩存描述的信息。

 View Code

4，查看計劃緩存

產生執行計劃是十分消耗CPU資源的，SQL Server會在內存的Plan Cache中存儲每個查詢計劃（Query Plan），及其占用的內存空間，重用次數等信息。

 View Code

三，清空緩存

在調優存儲過程性能時，清空緩存是必需的，緩沖池（Buffer Pool）是SQL Server的緩存管理器，包含了SQL Server的絕大部分緩存數據（Cache），例如，執行計劃緩存（Plan cache），數據緩存（Data cache）等。

清空緩存常用的命令有如下三個：

 FREEPROCCACHE

Checkpoint和DBCC DROPCLEANBUFFERS 用于清理數據緩存（Data Cache）中的臟頁（dirty pages）和干凈頁（clean pages），而DBCC FREEPROCCACHE 用于清空所有的計劃緩存（Plan Cache）。

1，清空數據緩存

checkpoint 用于將臟頁（Dirty Pages）寫入硬盤，臟頁（Dirty Pages）是指數據頁讀入緩存后，被修改過，導致內存中數據頁和硬盤中的數據頁中的內容不同；干凈頁（Clean Pages）是指數據頁被讀入緩存后，沒有被修改過，所以，內存中的數據頁和硬盤中的數據頁中的內容相同。不管是Dirty pages 還是 Clean pages 都是Data Cache，在性能調優時，都必須從內存中清理掉，否則，查詢性能將忽略掉數據從硬盤加載到內存的IO消耗，影響查詢語句的執行情況。

CHECKPOINT 命令用于產生冷緩存（Cold buffer Cache），該命令將當前數據庫產生的所有臟頁寫入到硬盤，并清理內存buffer；在執行CHECKPOINT命令之后，執行 DBCC DROPCLEANBUFFERS 用于從緩沖池中清空所有的干凈頁。

在性能測試時，使用DBCC DROPCLEANBUFFERS從SQLSERVER的數據緩存池中清除所有的clean緩存數據，需要注意的是該命令只移走干凈的緩存，不移走臟緩存。因此，在執行這個命令前，應該先執行CheckPoint，將所有臟頁寫入磁盤，這樣在運行DBCC RROPCLEANBUFFERS 時，可以保證所有的數據緩存被清理，而不是其中的一部分。

2，清空計劃緩存

計劃緩存（Plan Cache）用于緩存查詢語句的執行計劃，每一條查詢語句在執行之后，其查詢計劃都會緩存Plan Cache中。在產品環境中，不要輕易清理掉Plan Cache。如果檢測到某個Plan Cache產生參數嗅探問題，導致性能十分低下，推薦修改查詢語句，重新編譯存儲過程，以單獨刷新該SP的計劃緩存。

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle} ) ]

計劃緩存，之前叫做過程緩存（procedure cache），執行DBCC FREEPROCCACHE 命令，釋放所有的計劃緩存，這回導致存儲過程，AdHoc 查詢等查詢必須重新編譯，產生新的計劃緩存。

四，強制重新編譯執行計劃

修改存儲過程，觸發器等模塊（Module）能夠使其執行計劃重新編譯，除此之外，還有其他方法，能夠強制重新編譯執行計劃

1，標記，下次重新編譯

使用該存儲過程，標記一個執行模塊（SP，Trigger，User-Defined Function）在下次執行時，重新編譯執行計劃

sys.sp_recompile [ @objname = ] 'object'

2，不復用執行計劃

在創建存儲過程時，使用WITH RECOMPILE 選項，在每次執行SP時，都重新編譯，使用新的執行計劃。

CREATE PROCEDURE dbo.usp_procname 
    @Parameter_Name varchar(30) = 'Parameter_default_value'WITH RECOMPILE

3，執行時重新編譯

在執行存儲過程時，重新編譯存儲過程的執行計劃

exec dbo.usp_procname @Parameter_name='Parameter_value' WITH RECOMPILE

4，語句級別的重新編譯

在SP中，使用查詢選項 option（recompile），只重新編譯該語句級別的執行計劃

select column_name_listfrom dbo.tablenameoption(recompile)

SQL Server在執行查詢之后，查詢提示（RECOMPILE）指示存儲引擎將計劃緩存拋棄，在下次執行存儲過程時，強制查詢優化器重新編譯，生成新的執行計劃。在重新編譯時，SQL Server 優化器使用當前的變量值生成新的計劃緩存。

附：

冷緩存，熱緩存，臟緩存和干凈緩存名詞解釋：

• 凈緩存頁（Clean Buffer） 是指內存中未被修改的數據頁，DBCC DROPCLEANBUFFERS 用于從緩沖池（Buffer Pool）移除干凈頁，釋放Buffer。

• 臟緩存頁（Dirty Buffer）是指數據頁在內存中被修改，但是還沒有寫入到硬盤中，導致硬盤中的數據不同于內存，通常情況下，臟頁通過CHECKPOINT進程來自動同步，CHECKPOINT 將臟頁數據寫入到硬盤中，使內存和硬盤文件中的數據保持一致，能夠減少數據還原的時間。

• 冷緩存頁（Cold Buffer）是指，在數據處理階段，最近沒有被使用的緩存頁。

• 熱緩存頁（Hot Buffer）是指，在數據處理階段，最近經常被使用的緩存頁。