반응형
- /**********************************************************************************************
-- 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
반응형