程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SQL Server性能調優之緩存

SQL Server性能調優之緩存

編輯:MSSQL

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

以上就是本文的全部內容,希望本文的內容對大家的學習或者工作能帶來一定的幫助,同時也希望多多支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved