데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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;

 

 

 

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

 

참고 자료


강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1245
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1267
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 866
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 718
» DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 2997
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1305
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1066
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1575
1705 BCP 사용 jevida(강성욱) 2016.09.13 3434
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1153
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 2550
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 7468
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1577
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1619
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1831
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1589
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 703
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1823
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 11062
1694 SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2063





XE Login