SQL Server性能調(diào)優(yōu)之緩存
在執(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è)。
參考文檔:
What is a COLD, DIRTY or CLEAN Buffer
以上就是本文的全部?jī)?nèi)容,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助,同時(shí)也希望多多支持腳本之家!
- Mysql調(diào)優(yōu)Explain工具詳解及實(shí)戰(zhàn)演練(推薦)
- 關(guān)于MySQL性能調(diào)優(yōu)你必須了解的15個(gè)重要變量(小結(jié))
- sql server性能調(diào)優(yōu) I/O開(kāi)銷(xiāo)的深入解析
- MySQL的常見(jiàn)存儲(chǔ)引擎介紹與參數(shù)設(shè)置調(diào)優(yōu)
- SQL Server 性能調(diào)優(yōu)之查詢(xún)從20秒至2秒的處理方法
- 千萬(wàn)級(jí)用戶(hù)系統(tǒng)SQL調(diào)優(yōu)實(shí)戰(zhàn)分享
相關(guān)文章
sqlserver 手工實(shí)現(xiàn)差異備份的步驟
sqlserver 手工實(shí)現(xiàn)差異備份的步驟,需要的朋友可以參考下。2011-04-04REPLICATE 以指定的次數(shù)重復(fù)字符表達(dá)式
sql server以指定的次數(shù)重復(fù)字符表達(dá)式REPLICATE函數(shù):REPLICATE函數(shù)用于以指定的次數(shù)重復(fù)字符表達(dá)式。2010-06-06SQLServer Execpt和not in 性能區(qū)別
網(wǎng)上有很多 except 和 not in的返回結(jié)果區(qū)別這里就就提了2012-01-01SQL?Server備份數(shù)據(jù)庫(kù)的完整步驟
備份是指對(duì)SQL?Server數(shù)據(jù)庫(kù)或事務(wù)日志進(jìn)行的復(fù)制,下面這篇文章主要給大家介紹了關(guān)于SQL?Server備份數(shù)據(jù)庫(kù)的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06sql多表行轉(zhuǎn)列、級(jí)聯(lián)行轉(zhuǎn)列示例代碼
這篇文章主要介紹了sql多表行轉(zhuǎn)列、級(jí)聯(lián)行轉(zhuǎn)列示例代碼,需要的朋友可以參考下2014-03-03在安裝了Sql2000的基礎(chǔ)上安裝Sql2005的詳細(xì)過(guò)程 圖文
在安裝了Sql2000的基礎(chǔ)上安裝Sql2005的詳細(xì)過(guò)程 圖文方法,需要的朋友可以參考下。2011-03-03MSSQL 2000 使用幫助(sql server簡(jiǎn)明教程)
這篇文章主要介紹了MSSQL 2000 使用幫助(sql server簡(jiǎn)明教程),需要的朋友可以參考下2017-04-04SQL 比較一個(gè)集合是否在另一個(gè)集合里存在的方法分享
SQL 比較一個(gè)集合是否在另一個(gè)集合里存在的方法分享,需要的朋友可以參考下。2011-11-11windows11安裝sqlserver?2016數(shù)據(jù)庫(kù)報(bào)錯(cuò)等待數(shù)據(jù)庫(kù)引擎恢復(fù)句柄失敗解決辦法
最近安裝SQL?Server遇到這個(gè)問(wèn)題,試過(guò)網(wǎng)上幾乎所有辦法,都安裝不上,查了很久才解決,下面這篇文章主要給大家介紹了關(guān)于windows11安裝SQL?server數(shù)據(jù)庫(kù)報(bào)錯(cuò)等待數(shù)據(jù)庫(kù)引擎恢復(fù)句柄失敗的解決辦法,需要的朋友可以參考下2023-06-06