欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQL Server性能調(diào)優(yōu)之緩存

 更新時(shí)間:2017年03月19日 09:15:58   作者:悅光陰  
本篇文章主要介紹了SQL Server性能調(diào)優(yōu)中緩存的相關(guān)知識(shí),具有很好的參考價(jià)值。下面跟著小編一起來(lái)看下吧

在執(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>

  • 凈緩存頁(yè)(Clean Buffer) 是指內(nèi)存中未被修改的數(shù)據(jù)頁(yè),DBCC DROPCLEANBUFFERS 用于從緩沖池(Buffer Pool)移除干凈頁(yè),釋放Buffer。
  • 臟緩存頁(yè)(Dirty Buffer)是指數(shù)據(jù)頁(yè)在內(nèi)存中被修改,但是還沒(méi)有寫(xiě)入到硬盤(pán)中,導(dǎo)致硬盤(pán)中的數(shù)據(jù)不同于內(nèi)存,通常情況下,臟頁(yè)通過(guò)CHECKPOINT進(jìn)程來(lái)自動(dòng)同步,CHECKPOINT 將臟頁(yè)數(shù)據(jù)寫(xiě)入到硬盤(pán)中,使內(nèi)存和硬盤(pán)文件中的數(shù)據(jù)保持一致,能夠減少數(shù)據(jù)還原的時(shí)間。
  • 冷緩存頁(yè)(Cold Buffer)是指,在數(shù)據(jù)處理階段,最近沒(méi)有被使用的緩存頁(yè)。
  • 熱緩存頁(yè)(Hot Buffer)是指,在數(shù)據(jù)處理階段,最近經(jīng)常被使用的緩存頁(yè)。

參考文檔:

Recompile a Stored Procedure

What is a COLD, DIRTY or CLEAN Buffer

以上就是本文的全部?jī)?nèi)容,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助,同時(shí)也希望多多支持腳本之家!

相關(guān)文章

最新評(píng)論