데이터베이스 개발자 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
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 713
» DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 2904
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1277
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1061
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1541
1705 BCP 사용 jevida(강성욱) 2016.09.13 3279
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1152
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 2372
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 6292
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1535
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1600
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1695
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1570
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 702
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1767
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 10620
1694 SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2061
1693 SQL Server Net Libraries jevida(강성욱) 2016.09.13 1103
1692 SQL Server 별칭 설정 및 사용 jevida(강성욱) 2016.09.13 2560
1691 SQL Server에 연결된 어플리케이션 드라이버 버전 확인 jevida(강성욱) 2016.09.13 864





XE Login