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

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

DMV에서 SQL Server 리소스 데이터베이스 값

 

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

 

DMV sys.dm_os_buffer_descriptors 통하여 시스템 데이터베이스 ID를 검색하여 보면 NULL 값이 나타난다. 어떻게 데이터베이스 이름이 NULL이 나타날 수 있을까?

 

SQL Server 2005부터 도입된 새로운 시스템 데이터베이스로 인해 시스템 DMV나 카탈로그 뷰 내에서 노출 할 때 데이터베이스는 항상 32767의 database_id 값을 표시한다. (그렇다고 모든 리소스 데이터베이스가 노출 되지는 않는다.) 이때 데이터베이스 이름을 NULL로 반환한다.

 

데이터 페이지를 디스크에서 읽으면 해당 페이지가 SQL Server 버퍼풀에 복사되며 다시 사용 할 수 있도록 캐시 된다. 각 캐시된 데이터페이지는 버퍼ID를 할당받게 되고 버퍼ID는 현재 SQL Server 인스턴스에 캐시된 각 데이터 페이지를 고유하게 식별한다.

Sys.dm_os_buffer_descriptors 는 SQL Server 버퍼풀에 있는 모든 데이터페이지에 대한 정보를 반환하는 동적관리 뷰 이다. 이때 Resource 데이터베이스와 연결된 페이지도 포함되어 나타난다.

NULL 데이터베이스 이름이 나타나는 것을 확인 할 수 있다.

SELECT

    DISTINCT DB_NAME(DATABASE_ID)

FROM SYS.DM_OS_BUFFER_DESCRIPTORS WITH (NOLOCK)

 

 

 

리소스 데이터베이스를 제외한 정보를 나타낼 때에는 sys.databases 과 조인하면 리소스 데이터베이스는 제외된다.

SELECT

    DISTINCT B.NAME

FROM SYS.DM_OS_BUFFER_DESCRIPTORS AS A

    INNER JOIN SYS.DATABASES AS B

    ON A.DATABASE_ID = B.DATABASE_ID

 

 

 

Sys.dm_os_buffer_descriptors를 사용할 때 NULL 값을 제거하기 위하여 database_id 의 값이 32767인 것을 case 구문을 이용하여 'resourceDB'라고 나타내었다.

SELECT

    DISTINCT CASE WHEN DATABASE_ID = 32767 THEN 'RESOURCEDB' ELSE DB_NAME(DATABASE_ID) END

FROM SYS.DM_OS_BUFFER_DESCRIPTORS WITH (NOLOCK)

GO

 

SELECT

    DISTINCT CASE WHEN A.DATABASE_ID = 32767 THEN 'RESOURCEDB' ELSE B.NAME END

FROM SYS.DM_OS_BUFFER_DESCRIPTORS AS A

    INNER JOIN SYS.DATABASES AS B

    ON A.DATABASE_ID = B.DATABASE_ID

GO

 

 

 

  • DMV를 사용하기 위해서는 서버에 대한 View Server State 권한이 필요하다.
  • Sys,dm_os_buffer_descriptors는 리소스데이터베이스에서 사용되고 DT는 페이지를 반환하나 사용 가능한 페이지 또는 빼앗긴 페이지, 오류가 있었던 페이지에 대한 정보는 반환하지 않는다.

 

참고링크 : http://msdn.microsoft.com/ko-kr/library/ms173442.aspx



강성욱 / 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 1244
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1264
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 866
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 714
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 2988
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1297
» DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1064
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1565
1705 BCP 사용 jevida(강성욱) 2016.09.13 3400
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1153
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 2486
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 7184
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1571
1700 SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1612
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1806
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1579
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 702
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1813
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 10978
1694 SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2061





XE Login