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

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

 

2013-0110 / 강성욱 / http://sqlmvp.kr