在執(zhí)行任何查詢(xún)時(shí)，SQL Server都會(huì)將數(shù)據(jù)讀取到內(nèi)存，數(shù)據(jù)使用之后，不會(huì)立即釋放，而是會(huì)緩存在內(nèi)存Buffer中，當(dāng)再次執(zhí)行相同的查詢(xún)時(shí)，如果所需數(shù)據(jù)全部緩存在內(nèi)存中，那么SQL Server不會(huì)產(chǎn)生Disk IO操作，立即返回查詢(xún)結(jié)果，這是SQL Server的性能優(yōu)化機(jī)制。

一，主要的內(nèi)存消費(fèi)者（Memory Consumer)

1，數(shù)據(jù)緩存（Data Cache）

Data Cache是存儲(chǔ)數(shù)據(jù)頁(yè)（Data Page）的緩沖區(qū)，當(dāng)SQL Server需要讀取數(shù)據(jù)文件（File）中的數(shù)據(jù)頁(yè)（Data Page）時(shí)，SQL Server會(huì)把整個(gè)Page都調(diào)入內(nèi)存（內(nèi)存中的一個(gè)Page叫做buffer），Page是數(shù)據(jù)訪(fǎng)問(wèn)的最小單元。

當(dāng)用戶(hù)修改了某個(gè)Page上的數(shù)據(jù)時(shí)，SQL Server 會(huì)先在內(nèi)存中修改Buffer，但是不會(huì)立即將這個(gè)數(shù)據(jù)葉寫(xiě)回硬盤(pán)，而是等到CheckPoint或lazy Writer進(jìn)程運(yùn)行時(shí)集中處理。當(dāng)用戶(hù)讀取某個(gè)Page后，如果SQL Server沒(méi)有內(nèi)存壓力，它不會(huì)在內(nèi)存中刪除這個(gè)Page，因?yàn)閮?nèi)存中的數(shù)據(jù)頁(yè)始終存放著數(shù)據(jù)的最新?tīng)顟B(tài)，如果有其他用戶(hù)使用這個(gè)Page，SQL Server 不需要從硬盤(pán)中讀取一次，節(jié)省語(yǔ)句執(zhí)行的時(shí)間。理想情況是SQL Server將用戶(hù)需要訪(fǎng)問(wèn)的所有數(shù)據(jù)都緩存在內(nèi)存中，SQL Server 永遠(yuǎn)不需要去硬盤(pán)讀取數(shù)據(jù)，只需要在CheckPoint 或 lazy Write運(yùn)行時(shí)把修改過(guò)的頁(yè)面寫(xiě)回硬盤(pán)即可

2，查詢(xún)計(jì)劃緩存（Query Plan Cache）

存儲(chǔ)查詢(xún)語(yǔ)句和存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃，以供重用，而不需要重新編譯（Compile），因?yàn)榫幾g查詢(xún)語(yǔ)句產(chǎn)生執(zhí)行計(jì)劃是一個(gè)非常耗費(fèi)資源的過(guò)程。

二，查看內(nèi)存消耗

在SQL Server中，只有內(nèi)存書(shū)記員（Memory Clerk）能夠分配內(nèi)存，Memory Clerk會(huì)記錄已經(jīng)分配內(nèi)存的數(shù)量，任何一個(gè)需要使用內(nèi)存的對(duì)象，必須創(chuàng)建自己的Memory Clerk，并使用該Memory clerk來(lái)分配內(nèi)存。

1，查看Memory clerk分配的內(nèi)存量

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'

對(duì)于內(nèi)存結(jié)點(diǎn)64，只在DAC中使用。

2，統(tǒng)計(jì)Memory Clerk分配的內(nèi)存總量

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

消耗內(nèi)存較大的Clerk是：

• MEMORYCLERK_SQLBUFFERPOOL：基本是Buffer Pool中page的大小
• OBJECTSTORE_LOCK_MANAGER：鎖結(jié)構(gòu)使用的內(nèi)存，當(dāng)發(fā)生嚴(yán)重的鎖阻塞時(shí)，這表明系統(tǒng)中，存儲(chǔ)大量鎖，造成鎖管理占用大量的內(nèi)存；
• CACHESTORE_OBJCP：觸發(fā)器和存儲(chǔ)過(guò)程等模塊（Module）的執(zhí)行計(jì)劃占用的緩存空間；
• CACHESTORE_SQLCP：動(dòng)態(tài)TSQL語(yǔ)句，即席（Adhoc）查詢(xún)和預(yù)編譯（Prepared） TSQL的執(zhí)行計(jì)劃緩存；
• CACHESTORE_COLUMNSTOREOBJECTPOOL：列存儲(chǔ)索引（ColumnStore Index）占用的緩存

3，查看緩存中的數(shù)據(jù)頁(yè)

當(dāng)數(shù)據(jù)頁(yè)從硬盤(pán)讀取到內(nèi)存之后，該數(shù)據(jù)頁(yè)被復(fù)制到緩沖池（Buffer Pool），供SQL Server重用。每個(gè)緩存的數(shù)據(jù)頁(yè)都有一個(gè)緩存描述器（Buffer Descriptor），用戶(hù)唯一標(biāo)識(shí)內(nèi)存中的數(shù)據(jù)頁(yè)，在SQL Server實(shí)例中緩存的每一個(gè)數(shù)據(jù)頁(yè)，都能從 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，查看計(jì)劃緩存

產(chǎn)生執(zhí)行計(jì)劃是十分消耗CPU資源的，SQL Server會(huì)在內(nèi)存的Plan Cache中存儲(chǔ)每個(gè)查詢(xún)計(jì)劃（Query Plan），及其占用的內(nèi)存空間，重用次數(shù)等信息。

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

三，清空緩存

在調(diào)優(yōu)存儲(chǔ)過(guò)程性能時(shí)，清空緩存是必需的，緩沖池（Buffer Pool）是SQL Server的緩存管理器，包含了SQL Server的絕大部分緩存數(shù)據(jù)（Cache），例如，執(zhí)行計(jì)劃緩存（Plan cache），數(shù)據(jù)緩存（Data cache）等。

清空緩存常用的命令有如下三個(gè)：

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

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

1，清空數(shù)據(jù)緩存

checkpoint 用于將臟頁(yè)（Dirty Pages）寫(xiě)入硬盤(pán)，臟頁(yè)（Dirty Pages）是指數(shù)據(jù)頁(yè)讀入緩存后，被修改過(guò)，導(dǎo)致內(nèi)存中數(shù)據(jù)頁(yè)和硬盤(pán)中的數(shù)據(jù)頁(yè)中的內(nèi)容不同；干凈頁(yè)（Clean Pages）是指數(shù)據(jù)頁(yè)被讀入緩存后，沒(méi)有被修改過(guò)，所以，內(nèi)存中的數(shù)據(jù)頁(yè)和硬盤(pán)中的數(shù)據(jù)頁(yè)中的內(nèi)容相同。不管是Dirty pages 還是 Clean pages 都是Data Cache，在性能調(diào)優(yōu)時(shí)，都必須從內(nèi)存中清理掉，否則，查詢(xún)性能將忽略掉數(shù)據(jù)從硬盤(pán)加載到內(nèi)存的IO消耗，影響查詢(xún)語(yǔ)句的執(zhí)行情況。

CHECKPOINT 命令用于產(chǎn)生冷緩存（Cold buffer Cache），該命令將當(dāng)前數(shù)據(jù)庫(kù)產(chǎn)生的所有臟頁(yè)寫(xiě)入到硬盤(pán)，并清理內(nèi)存buffer；在執(zhí)行CHECKPOINT命令之后，執(zhí)行 DBCC DROPCLEANBUFFERS 用于從緩沖池中清空所有的干凈頁(yè)。

在性能測(cè)試時(shí)，使用DBCC DROPCLEANBUFFERS從SQLSERVER的數(shù)據(jù)緩存池中清除所有的clean緩存數(shù)據(jù)，需要注意的是該命令只移走干凈的緩存，不移走臟緩存。因此，在執(zhí)行這個(gè)命令前，應(yīng)該先執(zhí)行CheckPoint，將所有臟頁(yè)寫(xiě)入磁盤(pán)，這樣在運(yùn)行DBCC RROPCLEANBUFFERS 時(shí)，可以保證所有的數(shù)據(jù)緩存被清理，而不是其中的一部分。

2，清空計(jì)劃緩存

計(jì)劃緩存（Plan Cache）用于緩存查詢(xún)語(yǔ)句的執(zhí)行計(jì)劃，每一條查詢(xún)語(yǔ)句在執(zhí)行之后，其查詢(xún)計(jì)劃都會(huì)緩存Plan Cache中。在產(chǎn)品環(huán)境中，不要輕易清理掉Plan Cache。如果檢測(cè)到某個(gè)Plan Cache產(chǎn)生參數(shù)嗅探問(wèn)題，導(dǎo)致性能十分低下，推薦修改查詢(xún)語(yǔ)句，重新編譯存儲(chǔ)過(guò)程，以單獨(dú)刷新該SP的計(jì)劃緩存。

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle} ) ]

計(jì)劃緩存，之前叫做過(guò)程緩存（procedure cache），執(zhí)行DBCC FREEPROCCACHE 命令，釋放所有的計(jì)劃緩存，這回導(dǎo)致存儲(chǔ)過(guò)程，AdHoc 查詢(xún)等查詢(xún)必須重新編譯，產(chǎn)生新的計(jì)劃緩存。

四，強(qiáng)制重新編譯執(zhí)行計(jì)劃

修改存儲(chǔ)過(guò)程，觸發(fā)器等模塊（Module）能夠使其執(zhí)行計(jì)劃重新編譯，除此之外，還有其他方法，能夠強(qiáng)制重新編譯執(zhí)行計(jì)劃

1，標(biāo)記，下次重新編譯

使用該存儲(chǔ)過(guò)程，標(biāo)記一個(gè)執(zhí)行模塊（SP，Trigger，User-Defined Function）在下次執(zhí)行時(shí)，重新編譯執(zhí)行計(jì)劃

sys.sp_recompile [ @objname = ] 'object'

2，不復(fù)用執(zhí)行計(jì)劃

在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，使用WITH RECOMPILE 選項(xiàng)，在每次執(zhí)行SP時(shí)，都重新編譯，使用新的執(zhí)行計(jì)劃。

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

3，執(zhí)行時(shí)重新編譯

在執(zhí)行存儲(chǔ)過(guò)程時(shí)，重新編譯存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃

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

4，語(yǔ)句級(jí)別的重新編譯

在SP中，使用查詢(xún)選項(xiàng) option（recompile），只重新編譯該語(yǔ)句級(jí)別的執(zhí)行計(jì)劃

select column_name_list
from dbo.tablename
option(recompile)

SQL Server在執(zhí)行查詢(xún)之后，查詢(xún)提示（RECOMPILE）指示存儲(chǔ)引擎將計(jì)劃緩存拋棄，在下次執(zhí)行存儲(chǔ)過(guò)程時(shí)，強(qiáng)制查詢(xún)優(yōu)化器重新編譯，生成新的執(zhí)行計(jì)劃。在重新編譯時(shí)，SQL Server 優(yōu)化器使用當(dāng)前的變量值生成新的計(jì)劃緩存。

附：

冷緩存，熱緩存，臟緩存和干凈緩存名詞解釋?zhuān)?/strong>