DMV 를 통한 인덱스 통계 및 조각화 현황 분석(100225)

목적 : DMV에 대하여 인덱스 통계 부터 차례로 분석예정

 

ERD

Ps. 파일그룹 및 파티셔닝을 사용하지 않으므로 간략하게 작성한 ERD(파일그룹 및 파니셔닝 사용시 중간에 교차실체 발생)

 

 

 

Dumy 테이블 작성

if object_id('dbo.IN_TB,'u') is not null

drop table dbo. IN_TB

go

 

With Temp1(num)

As

(

       select 1 num

       union all

       select num+1 from temp1

       where num < 10000

)

Select Identity(int,1,1) SEQ, newid() AS DUMY, newid() AS DUMY1, dateadd(d, num, getdate())AS DT

INTO dbo.IN_TB

FROM Temp1

OPTION (MAXRECURSION 0)

 

go

Create index NC_DT on dbo.IN_TB(DT)

Create Unique Clustered index CL_SEQ on dbo.IN_TB(SEQ)

 

 

 

index_usage_stats DMV 쿼리 실행

  Select * from sys.dm_db_index_usage_stats

Where database_id = db_id() and object_id = object_id(N'dbo.IN_TB', N'U')

 

아무런 레코드 포함하지 않음. SQLServer는 그 인덱스가 처음 엑세스 된 후 통계정보 수집

CL Scan(FullScan 실행)

Select * from dbo.IN_TB

Table Scan 실행 후 index_usage_stats DMV 쿼리 재실행 결과 User_scna(Full Scan 1회 발생)

 

 

NC Scan 후 Key lookup  실행 후 index_usage_stats DMV 쿼리 재실행 결과(nc스캔1회 후(1증가) keylookup 떄문에 CL의 User_looups1회 증가)

 Select SEQ, DUMY1 From dbo.TSTTB where year(DT) = 2010

 

 

 

 

 

 

 

 

NC SEEK 후 Key lookup  실행 후 index_usage_stats DMV 쿼리 재실행 결과(NC SEEK 1회 후(1증가) keylookup 떄문에 CL의 User_looups 1회 증가)

Select SEQ, DUMY1 From dbo.TSTTB where DT = '2010-11-13'

 

 

 

상기의 ERD를 통하여 Index 통계 및 조각화 정보를 알아보는 쿼리

Select db_name(A.database_id) AS DBNAME, object_name(A.object_id) as OBNAME,B.NAME,

       A.User_seeks, A.User_Scans, A.User_lookups, A.User_Updates, C.Index_type_desc,

       C.Index_depth, C.avg_fragmentation_in_percent, C.avg_page_space_used_in_percent

From sys.dm_db_index_usage_stats A

INNER JOIN SYS.INDEXES B

ON A.object_id = B.object_id

AND A.INDEX_ID = B.INDEX_ID

INNER JOIN sys.dm_db_index_physical_stats (db_id(), object_id(N'dbo.TestTable'), null, null, 'SAMPLED' ) C

ON A.object_id = C.object_id

AND A.INDEX_ID = C.INDEX_ID

 

C.avg_fragmentation_in_percent --> 인덱스의 조각화 정도(%)를 나타냅니다. 0에 가까울수록 좋은 상태를 나타내지만, 대게 0~10% 정도의 값이면 적당.

 

 C.lastsystemscan.. 통계업데이트 날짜인지...