SQL Server性能調優之緩存。本站提示廣大學習愛好者：（SQL Server性能調優之緩存）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server性能調優之緩存正文
在執行任何查詢時，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分配的內存量
select memory_node_id, type, pages_kb, virtual_memory_reserved_kb, virtual_memory_committed_kb, shared_memory_reserved_kb, shared_memory_committed_kb, page_size_in_bytes from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLQERESERVATIONS'
對於內存結點64，只在DAC中使用。
2，統計Memory Clerk分配的內存總量
select mc.type,mc.name, sum(mc.pages_kb) as AllocatedPages_KB, sum(mc.virtual_memory_reserved_kb) as VM_Reserved_KB, sum(mc.virtual_memory_committed_kb) as VM_Committed_KB, --sum(mc.shared_memory_reserved_kb) as ShareMem_Reserved_KB, --sum(mc.shared_memory_committed_kb) as ShareMem_Committed_KB, max(mc.page_size_in_bytes)/1024 as SinglePageSize_KB from sys.dm_os_memory_clerks mc group by mc.type,mc.name order by AllocatedPages_KB desc,mc.type,mc.name
消耗內存較大的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 查看緩存描述的信息。
select DB_NAME(bd.database_id) as dbname, OBJECT_NAME(p.object_id) as ObjectName, i.name as IndexName, count(0) as BufferCounts, sum(bd.free_space_in_bytes)/1024 as TotalFreeSpace_KB, cast(sum(bd.free_space_in_bytes)/(8*1024.0)/count(0) as decimal(10,4))*100 as FreeSpaceRatio, sum(cast(bd.is_modified as int)) as TotalDirtyPages, sum(bd.row_count) as TotalRowCounts from sys.allocation_units au inner join sys.dm_os_buffer_descriptors bd on au.allocation_unit_id=bd.allocation_unit_id inner join sys.partitions p on au.container_id=p.hobt_id inner join sys.indexes i on p.object_id=i.object_id and p.index_id=p.index_id inner join sys.objects o on p.object_id=o.object_id where bd.database_id=DB_ID(N'database_name') and o.type<>N'S' group by bd.database_id,p.object_id,i.name order by BufferCounts desc,dbname,ObjectName
4，查看計劃緩存
產生執行計劃是十分消耗CPU資源的，SQL Server會在內存的Plan Cache中存儲每個查詢計劃（Query Plan），及其占用的內存空間，重用次數等信息。
select cp.objtype,cp.cacheobjtype, sum(cp.size_in_bytes) as TotalSize_B, COUNT(cp.bucketid) as CacheCounts, sum(cp.refcounts) as TotalRefCounts, sum(cp.usecounts) as TotalUseCounts from sys.dm_exec_cached_plans cp group by cp.objtype,cp.cacheobjtype order by TotalSize_B desc
三，清空緩存
在調優存儲過程性能時，清空緩存是必需的，緩沖池（Buffer Pool）是SQL Server的緩存管理器，包含了SQL Server的絕大部分緩存數據（Cache），例如，執行計劃緩存（Plan cache），數據緩存（Data cache）等。
清空緩存常用的命令有如下三個：
CHECKPOINT DBCC DROPCLEANBUFFERS DBCC 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_list from dbo.tablename option(recompile)
SQL Server在執行查詢之後，查詢提示（RECOMPILE）指示存儲引擎將計劃緩存拋棄，在下次執行存儲過程時，強制查詢優化器重新編譯，生成新的執行計劃。在重新編譯時，SQL Server 優化器使用當前的變量值生成新的計劃緩存。
附：
冷緩存，熱緩存，髒緩存和干淨緩存名詞解釋：
淨緩存頁（Clean Buffer） 是指內存中未被修改的數據頁，DBCC DROPCLEANBUFFERS 用於從緩沖池（Buffer Pool）移除干淨頁，釋放Buffer。 髒緩存頁（Dirty Buffer）是指數據頁在內存中被修改，但是還沒有寫入到硬盤中，導致硬盤中的數據不同於內存，通常情況下，髒頁通過CHECKPOINT進程來自動同步，CHECKPOINT 將髒頁數據寫入到硬盤中，使內存和硬盤文件中的數據保持一致，能夠減少數據還原的時間。 冷緩存頁（Cold Buffer）是指，在數據處理階段，最近沒有被使用的緩存頁。 熱緩存頁（Hot Buffer）是指，在數據處理階段，最近經常被使用的緩存頁。參考文檔：
Recompile a Stored Procedure
What is a COLD, DIRTY or CLEAN Buffer
以上就是本文的全部內容，希望本文的內容對大家的學習或者工作能帶來一定的幫助，同時也希望多多支持！
