DMV - 데이터베이스 버퍼 메모리 사용량 확인

DMV - 데이터베이스 버퍼 메모리 사용량 확인

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

흔히 데이터베이스가 어느 정도 메모리를 사용하는가를 확인 할 때 작업관리자에서 프로세스 할당 메모리를 확인 하는 경우가 많다. 하지만 좀더 자세하게 각 인스턴스별 SQL Server 메모리 사용량을 확인 하고 싶을 때는 어떻게 할까?

 

SQL Server 2005 부터 추가된 동적 관리 뷰(DMV)인 sys.dm_os_buffer_descriptors, sys.dm_os_performance_counter를 통하여 데이터베이스에 할당 된 버퍼 캐시 사용량 및 페이지 사용량을 확인 할 수 있다.

 

나의 로컬 머신에서 스크립트를 실행 해 보았다. 설치되어 있는 데이터베이스의 버퍼풀 사용량이 나타난다.

-- Note: querying sys.dm_os_buffer_descriptors -- requires the VIEW_SERVER_STATE permission.   DECLARE @total_buffer INT;   SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Total Pages';   ;WITH src AS ( SELECT database_id, db_buffer_pages = COUNT_BIG(*) FROM sys.dm_os_buffer_descriptors --WHERE database_id BETWEEN 5 AND 32766 GROUP BY database_id ) SELECT [db_name] = CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END, db_buffer_pages, db_buffer_MB = db_buffer_pages / 128, db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer) FROM src ORDER BY db_buffer_MB DESC;

 

 

 

위의 스크립트에서는 데이터베이스에 대한 버퍼풀 사용량을 나타내었다면 아래 스크립트는 특정 데이터베이스의 클러스터 인덱스와 힙에 대한 테이블 뷰 정보를 만들어 버퍼풀 사용량을 확인 해 볼 수 있다.

;WITH src AS ( SELECT [Object] = o.name, [Type] = o.type_desc, [Index] = COALESCE(i.name, ''), [Index_Type] = i.type_desc, p.[object_id], p.index_id, au.allocation_unit_id FROM sys.partitions AS p INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id] INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id WHERE au.[type] IN (1,2,3) AND o.is_ms_shipped = 0 ) SELECT src.[Object], src.[Type], src.[Index], src.Index_Type, buffer_pages = COUNT_BIG(b.page_id), buffer_mb = COUNT_BIG(b.page_id) / 128 FROM src INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id WHERE b.database_id = DB_ID() GROUP BY src.[Object], src.[Type], src.[Index], src.Index_Type ORDER BY buffer_pages DESC;

 

 

 

정기적으로 서버에서 어떤 항목이 가장 큰 버퍼풀을 사용하는지 확인하여 효율적으로 운용 할 수 있도록 방향을 설정 하자.

 

참고 자료

• http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/
• http://msdn.microsoft.com/ko-kr/library/ms173442.aspx
• http://technet.microsoft.com/ko-kr/library/ms187743.aspx

 

 

 

2013-01-14 / 강성욱 / http://sqlmvp.kr