SQL Server性能調(diào)優(yōu)之緩存
在執(zhí)行任何查詢時,SQL Server都會將數(shù)據(jù)讀取到內(nèi)存,數(shù)據(jù)使用之后,不會立即釋放,而是會緩存在內(nèi)存Buffer中,當再次執(zhí)行相同的查詢時,如果所需數(shù)據(jù)全部緩存在內(nèi)存中,那么SQL Server不會產(chǎn)生Disk IO操作,立即返回查詢結(jié)果,這是SQL Server的性能優(yōu)化機制。
一,主要的內(nèi)存消費者(Memory Consumer)
1,數(shù)據(jù)緩存(Data Cache)
Data Cache是存儲數(shù)據(jù)頁(Data Page)的緩沖區(qū),當SQL Server需要讀取數(shù)據(jù)文件(File)中的數(shù)據(jù)頁(Data Page)時,SQL Server會把整個Page都調(diào)入內(nèi)存(內(nèi)存中的一個Page叫做buffer),Page是數(shù)據(jù)訪問的最小單元。
當用戶修改了某個Page上的數(shù)據(jù)時,SQL Server 會先在內(nèi)存中修改Buffer,但是不會立即將這個數(shù)據(jù)葉寫回硬盤,而是等到CheckPoint或lazy Writer進程運行時集中處理。當用戶讀取某個Page后,如果SQL Server沒有內(nèi)存壓力,它不會在內(nèi)存中刪除這個Page,因為內(nèi)存中的數(shù)據(jù)頁始終存放著數(shù)據(jù)的最新狀態(tài),如果有其他用戶使用這個Page,SQL Server 不需要從硬盤中讀取一次,節(jié)省語句執(zhí)行的時間。理想情況是SQL Server將用戶需要訪問的所有數(shù)據(jù)都緩存在內(nèi)存中,SQL Server 永遠不需要去硬盤讀取數(shù)據(jù),只需要在CheckPoint 或 lazy Write運行時把修改過的頁面寫回硬盤即可
2,查詢計劃緩存(Query Plan Cache)
存儲查詢語句和存儲過程的執(zhí)行計劃,以供重用,而不需要重新編譯(Compile),因為編譯查詢語句產(chǎn)生執(zhí)行計劃是一個非常耗費資源的過程。
二,查看內(nèi)存消耗
在SQL Server中,只有內(nèi)存書記員(Memory Clerk)能夠分配內(nèi)存,Memory Clerk會記錄已經(jīng)分配內(nèi)存的數(shù)量,任何一個需要使用內(nèi)存的對象,必須創(chuàng)建自己的Memory Clerk,并使用該Memory clerk來分配內(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'
對于內(nèi)存結(jié)點64,只在DAC中使用。
2,統(tǒng)計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)存,當發(fā)生嚴重的鎖阻塞時,這表明系統(tǒng)中,存儲大量鎖,造成鎖管理占用大量的內(nèi)存;
- CACHESTORE_OBJCP:觸發(fā)器和存儲過程等模塊(Module)的執(zhí)行計劃占用的緩存空間;
- CACHESTORE_SQLCP:動態(tài)TSQL語句,即席(Adhoc)查詢和預編譯(Prepared) TSQL的執(zhí)行計劃緩存;
- CACHESTORE_COLUMNSTOREOBJECTPOOL:列存儲索引(ColumnStore Index)占用的緩存
3,查看緩存中的數(shù)據(jù)頁
當數(shù)據(jù)頁從硬盤讀取到內(nèi)存之后,該數(shù)據(jù)頁被復制到緩沖池(Buffer Pool),供SQL Server重用。每個緩存的數(shù)據(jù)頁都有一個緩存描述器(Buffer Descriptor),用戶唯一標識內(nèi)存中的數(shù)據(jù)頁,在SQL Server實例中緩存的每一個數(shù)據(jù)頁,都能從 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,查看計劃緩存
產(chǎn)生執(zhí)行計劃是十分消耗CPU資源的,SQL Server會在內(nèi)存的Plan Cache中存儲每個查詢計劃(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)存儲過程性能時,清空緩存是必需的,緩沖池(Buffer Pool)是SQL Server的緩存管理器,包含了SQL Server的絕大部分緩存數(shù)據(jù)(Cache),例如,執(zhí)行計劃緩存(Plan cache),數(shù)據(jù)緩存(Data cache)等。
清空緩存常用的命令有如下三個:
CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE
Checkpoint和DBCC DROPCLEANBUFFERS 用于清理數(shù)據(jù)緩存(Data Cache)中的臟頁(dirty pages)和干凈頁(clean pages),而DBCC FREEPROCCACHE 用于清空所有的計劃緩存(Plan Cache)。
1,清空數(shù)據(jù)緩存
checkpoint 用于將臟頁(Dirty Pages)寫入硬盤,臟頁(Dirty Pages)是指數(shù)據(jù)頁讀入緩存后,被修改過,導致內(nèi)存中數(shù)據(jù)頁和硬盤中的數(shù)據(jù)頁中的內(nèi)容不同;干凈頁(Clean Pages)是指數(shù)據(jù)頁被讀入緩存后,沒有被修改過,所以,內(nèi)存中的數(shù)據(jù)頁和硬盤中的數(shù)據(jù)頁中的內(nèi)容相同。不管是Dirty pages 還是 Clean pages 都是Data Cache,在性能調(diào)優(yōu)時,都必須從內(nèi)存中清理掉,否則,查詢性能將忽略掉數(shù)據(jù)從硬盤加載到內(nèi)存的IO消耗,影響查詢語句的執(zhí)行情況。
CHECKPOINT 命令用于產(chǎn)生冷緩存(Cold buffer Cache),該命令將當前數(shù)據(jù)庫產(chǎn)生的所有臟頁寫入到硬盤,并清理內(nèi)存buffer;在執(zhí)行CHECKPOINT命令之后,執(zhí)行 DBCC DROPCLEANBUFFERS 用于從緩沖池中清空所有的干凈頁。
在性能測試時,使用DBCC DROPCLEANBUFFERS從SQLSERVER的數(shù)據(jù)緩存池中清除所有的clean緩存數(shù)據(jù),需要注意的是該命令只移走干凈的緩存,不移走臟緩存。因此,在執(zhí)行這個命令前,應該先執(zhí)行CheckPoint,將所有臟頁寫入磁盤,這樣在運行DBCC RROPCLEANBUFFERS 時,可以保證所有的數(shù)據(jù)緩存被清理,而不是其中的一部分。
2,清空計劃緩存
計劃緩存(Plan Cache)用于緩存查詢語句的執(zhí)行計劃,每一條查詢語句在執(zhí)行之后,其查詢計劃都會緩存Plan Cache中。在產(chǎn)品環(huán)境中,不要輕易清理掉Plan Cache。如果檢測到某個Plan Cache產(chǎn)生參數(shù)嗅探問題,導致性能十分低下,推薦修改查詢語句,重新編譯存儲過程,以單獨刷新該SP的計劃緩存。
DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle} ) ]
計劃緩存,之前叫做過程緩存(procedure cache),執(zhí)行DBCC FREEPROCCACHE 命令,釋放所有的計劃緩存,這回導致存儲過程,AdHoc 查詢等查詢必須重新編譯,產(chǎn)生新的計劃緩存。
四,強制重新編譯執(zhí)行計劃
修改存儲過程,觸發(fā)器等模塊(Module)能夠使其執(zhí)行計劃重新編譯,除此之外,還有其他方法,能夠強制重新編譯執(zhí)行計劃
1,標記,下次重新編譯
使用該存儲過程,標記一個執(zhí)行模塊(SP,Trigger,User-Defined Function)在下次執(zhí)行時,重新編譯執(zhí)行計劃
sys.sp_recompile [ @objname = ] 'object'
2,不復用執(zhí)行計劃
在創(chuàng)建存儲過程時,使用WITH RECOMPILE 選項,在每次執(zhí)行SP時,都重新編譯,使用新的執(zhí)行計劃。
CREATE PROCEDURE dbo.usp_procname @Parameter_Name varchar(30) = 'Parameter_default_value' WITH RECOMPILE
3,執(zhí)行時重新編譯
在執(zhí)行存儲過程時,重新編譯存儲過程的執(zhí)行計劃
exec dbo.usp_procname @Parameter_name='Parameter_value' WITH RECOMPILE
4,語句級別的重新編譯
在SP中,使用查詢選項 option(recompile),只重新編譯該語句級別的執(zhí)行計劃
select column_name_list from dbo.tablename option(recompile)
SQL Server在執(zhí)行查詢之后,查詢提示(RECOMPILE)指示存儲引擎將計劃緩存拋棄,在下次執(zhí)行存儲過程時,強制查詢優(yōu)化器重新編譯,生成新的執(zhí)行計劃。在重新編譯時,SQL Server 優(yōu)化器使用當前的變量值生成新的計劃緩存。
附:
冷緩存,熱緩存,臟緩存和干凈緩存名詞解釋:
- 凈緩存頁(Clean Buffer) 是指內(nèi)存中未被修改的數(shù)據(jù)頁,DBCC DROPCLEANBUFFERS 用于從緩沖池(Buffer Pool)移除干凈頁,釋放Buffer。
- 臟緩存頁(Dirty Buffer)是指數(shù)據(jù)頁在內(nèi)存中被修改,但是還沒有寫入到硬盤中,導致硬盤中的數(shù)據(jù)不同于內(nèi)存,通常情況下,臟頁通過CHECKPOINT進程來自動同步,CHECKPOINT 將臟頁數(shù)據(jù)寫入到硬盤中,使內(nèi)存和硬盤文件中的數(shù)據(jù)保持一致,能夠減少數(shù)據(jù)還原的時間。
- 冷緩存頁(Cold Buffer)是指,在數(shù)據(jù)處理階段,最近沒有被使用的緩存頁。
- 熱緩存頁(Hot Buffer)是指,在數(shù)據(jù)處理階段,最近經(jīng)常被使用的緩存頁。
參考文檔:
What is a COLD, DIRTY or CLEAN Buffer
以上就是本文的全部內(nèi)容,希望本文的內(nèi)容對大家的學習或者工作能帶來一定的幫助,同時也希望多多支持腳本之家!
相關(guān)文章
SQLServer Execpt和not in 性能區(qū)別
網(wǎng)上有很多 except 和 not in的返回結(jié)果區(qū)別這里就就提了2012-01-01sql多表行轉(zhuǎn)列、級聯(lián)行轉(zhuǎn)列示例代碼
這篇文章主要介紹了sql多表行轉(zhuǎn)列、級聯(lián)行轉(zhuǎn)列示例代碼,需要的朋友可以參考下2014-03-03在安裝了Sql2000的基礎(chǔ)上安裝Sql2005的詳細過程 圖文
在安裝了Sql2000的基礎(chǔ)上安裝Sql2005的詳細過程 圖文方法,需要的朋友可以參考下。2011-03-03MSSQL 2000 使用幫助(sql server簡明教程)
這篇文章主要介紹了MSSQL 2000 使用幫助(sql server簡明教程),需要的朋友可以參考下2017-04-04windows11安裝sqlserver?2016數(shù)據(jù)庫報錯等待數(shù)據(jù)庫引擎恢復句柄失敗解決辦法
最近安裝SQL?Server遇到這個問題,試過網(wǎng)上幾乎所有辦法,都安裝不上,查了很久才解決,下面這篇文章主要給大家介紹了關(guān)于windows11安裝SQL?server數(shù)據(jù)庫報錯等待數(shù)據(jù)庫引擎恢復句柄失敗的解決辦法,需要的朋友可以參考下2023-06-06