본 게시물은 개인적인 의견으로 작성되었으니 절대적인 정보가 아닐 수 있습니다. 참고만 하시고 궁금한 사항이 있으시면 연락주세요.

티스토리 뷰

SQL Server 많은 데이터베이스중에서 많이 사용되는 데이터베이스를 어떻게 찾을까?

Disk I/O 로 확인 가능하지만 메모리(Buffer Pool)의 디비별 사용량으로도 대략 확인이 가능하다.

아래 쿼리 2개로 모니터링 가능하다.

 

-- DB별 메모리 사용량

SELECT

(CASE WHEN ([database_id] = 32767)

THEN N'Resource Database'

ELSE DB_NAME ([database_id]) END) AS [DatabaseName],

COUNT (*) * 8 / 1024 AS [MBUsed],

SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id];

GO

 

-- 테이블, 인덱스별 메모리 사용량

EXEC sp_MSforeachdb

N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]

FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')

BEGIN

USE [?]

SELECT

''?'' AS [Database],

OBJECT_NAME (p.[object_id]) AS [Object],

p.[index_id],

i.[name] AS [Index],

i.[type_desc] AS [Type],

--au.[type_desc] AS [AUType],

--DPCount AS [DirtyPageCount],

--CPCount AS [CleanPageCount],

--DPCount * 8 / 1024 AS [DirtyPageMB],

--CPCount * 8 / 1024 AS [CleanPageMB],

(DPCount + CPCount) * 8 / 1024 AS [TotalMB],

--DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],

--CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],

([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],

CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]

FROM

(SELECT

allocation_unit_id,

SUM (CASE WHEN ([is_modified] = 1)

THEN 1 ELSE 0 END) AS [DPCount],

SUM (CASE WHEN ([is_modified] = 1)

THEN 0 ELSE 1 END) AS [CPCount],

SUM (CASE WHEN ([is_modified] = 1)

THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],

SUM (CASE WHEN ([is_modified] = 1)

THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]

FROM sys.dm_os_buffer_descriptors

WHERE [database_id] = DB_ID (''?'')

GROUP BY [allocation_unit_id]) AS buffers

INNER JOIN sys.allocation_units AS au

ON au.[allocation_unit_id] = buffers.[allocation_unit_id]

INNER JOIN sys.partitions AS p

ON au.[container_id] = p.[partition_id]

INNER JOIN sys.indexes AS i

ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]

WHERE 1=1 and p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800-- Taking up more than 100MB

ORDER BY [FreeSpacePC] DESC;

END';


키워드 : 메모리사용량

댓글
댓글쓰기 폼
1 ··· 34 35 36 37 38 39 40 41 42 ··· 119