dbcc memorystatus 의 분석

메모리 상황을 보기위해 현재 가장 편한것은 dbcc memorystatus 이다. 이것을 이용해 어떤 메모리 영역에 문제가 있을 수 있는지 한번에 알아볼 수 있는 좋은 방법을 소개한다.

DMV 는 Session 당 할당 메모리를 보거나 뭐 더 좋은 많은 결과를 볼 수 있는 쿼리가 존재 한다. 그러나 DMV 는 모든 정보를 제공하지 않고 오직 dbcc memorystatus 만 모든 정보를 제공한다.

 select granted_memory_kb, used_memory_kb, max_used_memory_kb
   from sys.dm_exec_query_memory_grants
  where session_id = 55

과거 버전의 서버에서 토탈 어떻게 사용하고 있는지 편리하게 제공하는 방법이 없다. 그래서 set nocount on 과 dbcc memorystatus 결과를 c:\dbcc.txt 라는 파일로 만들어 sql 로 파싱해서 편리하게 분석해 보자
눈으로 살펴보다가 내 눈이 심히 피곤했고, 이 결과를 파일로 만들어 넣은것은 헤더 정보를 같이 볼 방법은 이 방법밖에 없었다. 2005 이상에서는 편리하게 쿼리 할 수 있지만 2000 일 수 도 있어 쿼리를 과거 버전과 호환성 있게 만드느라 이렇게 만들었다.

동작 방법

xp_cmdshell 활성화가 필요하다.
강제로 설정하고 예전 설정으로 돌리면 되지만 작업 할려니 귀찮다.

모니터링 방법

use master
go
if object_id ('dbcc_memorystatus') is not null
drop proc dbcc_memorystatus
go

create proc dbcc_memorystatus
as
set nocount on
set transaction isolation level read uncommitted
-- script by minsouk kim
-- sqlsql.tistory.com
-- sqltag.org
-- 2011.08.30
-- xp_cmdshell 활성화 필요
declare @srvname varchar(1000)
   , @sql varchar(8000)
   , @ins_filename varchar(1000)
  
select @srvname = srvname from sysservers where srvid = 0
select @ins_filename = replace(@srvname,'\','_')
set @sql = 'sqlcmd -S '+@srvname+' -E -q "set nocount on; dbcc memorystatus;" -o c:\'+@ins_filename+'_dbcc_memorystatus.txt'
EXEC master..xp_cmdshell @sql ,no_output
if object_id ('tempdb..##dbcc_raw') is not null
drop table ##dbcc_raw
if object_id ('tempdb..##dbcc_memorystatus') is not null
drop table ##dbcc_memorystatus
if object_id ('tempdb..##dbcc_memorystatus_header') is not null
drop table ##dbcc_memorystatus_header
if object_id ('tempdb..##memorystatus') is not null
drop table ##memorystatus
create table ##dbcc_raw
(value varchar(1000))
set @sql = '
BULK INSERT ##dbcc_raw
FROM ''\\127.0.0.1\c$\'+@ins_filename+'_dbcc_memorystatus.txt''
WITH
(
TABLOCK
)'
exec (@sql)
create table ##dbcc_memorystatus
(idx int identity(1,1), value varchar(1000))
insert into ##dbcc_memorystatus
select * from ##dbcc_raw
create table ##dbcc_memorystatus_header
(idx int identity(1,1), header_idx int)
insert ##dbcc_memorystatus_header
select idx
  from ##dbcc_memorystatus
 where case when value like '%-%' then 1 else 0 end = 1
select * into ##memorystatus
from (
select h.description type, v.description, v.value, h.scale
  from (select b.header_idx - 1 header_idx, b.header_idx+1 st_idx, a.header_idx-3 ed_idx
    from ##dbcc_memorystatus_header a
    join ##dbcc_memorystatus_header b
   on a.idx = b.idx + 1) d
  join (select idx
    , substring(value,1, len(value) - charindex(' ',reverse(rtrim(value)))) description
    , substring(value,len(value) - charindex(' ',reverse(rtrim(value)))+1,100) scale
    from ##dbcc_memorystatus ) h
    on d.header_idx = h.idx
  join (select idx
    , substring(value,1, len(value) - charindex(' ',reverse(rtrim(value)))) description
    , substring(value,len(value) - charindex(' ',reverse(rtrim(value)))+1,100) value
    from ##dbcc_memorystatus ) v
    on v.idx between d.st_idx and ed_idx
 -- where h.description+v.description like '%sqlcp%'   
 --order by cast(v.value as bigint) desc
 ) a
PRINT 'select * from ##memorystatus where type+description like ''%sqlcp%'''
PRINT 'select * from ##memorystatus where type+description like ''%GLOBAL%'' ORDER BY CAST(VALUE AS BIGINT) DESC'
PRINT 'select * from ##memorystatus order by cast(value as bigint) desc '
-- select * from ##memorystatus
go

exec dbcc_memorystatus 

결과 예제 

SQL Server 2000 DBCC MEMORYSTATUS 에 대한 기술 정보
http://support.microsoft.com/?id=271624
SQL Server 2005 DBCC MEMORYSTATUS 에 대한 기술 정보
http://support.microsoft.com/?id=907877