[2k5] 인덱스 통계 및 조각화 정보 보기

1. /**********************************************************************************************
   -- Title : [2k5] 인덱스 통계 및 조각화 정보 보기
   -- Reference : mcpworld.com
   -- Key word : index 인덱스 sys.indexes sys.stats dbcc showcontig dm_db_index_physical_stats
   **********************************************************************************************/
   use adventureworks;
   go
   
   -- 1. 테이블 생성
   if objectproperty(object_id('salesorderdetail_test'), 'isusertable') = 1
    drop table salesorderdetail_test;
   go
   
   select * into salesorderdetail_test from sales.salesorderdetail;
   go
   
   -- 2. 인덱스 생성
   alter table dbo.salesorderdetail_test
   add constraint pk_salesorderdetail_test
    primary key clustered (salesorderid,salesorderdetailid);
   go
   
   -- 3. 인덱스 및 통계 정보 확인
   exec sp_helpindex 'dbo.salesorderdetail_test';
   go
   
   select * from sys.indexes
   where object_id = object_id('salesorderdetail_test'); -- catalog views
   go
   
   select * from sys.stats
   where object_id = object_id('salesorderdetail_test'); -- catalog views
   go
   
   -- 4. 인덱스 조각화 정보
   dbcc showcontig('salesorderdetail_test') with tableresults;
                                         -- with fast, limited scan
   go
   
   --입력 가능한 모드 : default, null, limited, sampled, detailed
   --               default = null = limited
   --입력 가능한 인덱스id : null, 0, 1, 2-250
   --                   null = all index, 0 = heap, 1 = clustered, 2-250 = non-clustered
   select *
   from sys.dm_db_index_physical_stats
    (db_id(), object_id('adventureworks.dbo.salesorderdetail_test')
    , 1, null, 'sampled');
   go
   
   select *
   from sys.dm_db_index_physical_stats
    (db_id(), object_id('adventureworks.dbo.salesorderdetail_test')
    , 1, null, null);  -- 'limited';
   go
   
   select *
   from sys.dm_db_index_physical_stats
    (db_id(), object_id('adventureworks.dbo.salesorderdetail_test')
    , 1, null, 'detailed');
   go