블로그 이미지

테이터베이스내에 존재하는 모든 인덱스 현황 보기

2011.09.20 14:22 Posted by 건방진연이
sp_helpindex 와 DBCC 명령어를 사용하지 않고 만들려고 하다가;;; 점점 복잡해지는 바람에 그냥 sp_helpindex 와 DBCC 명령어를 사용하여 만들어 보았네요. 모든 인덱스의 조각화 정보, 컬럼 정보, 사용량을 표시해 줍니다.
/****************************************************************************************************
* 인덱스 이름, 인덱스 종료, 인덱스 컬럼 저장하기
****************************************************************************************************/
CREATE TABLE #AT_IndexInfo (
	mIndex_name NVARCHAR(500)
	, mIndex_description sysname
	, mIndex_keys NVARCHAR(2126)
)

INSERT INTO #AT_IndexInfo
EXEC sp_msforeachtable 'sp_helpindex ''?'' '


/****************************************************************************************************
* 데이터베이스의 모든 테이블의 모든 인덱스에 대한 전체 결과 집합 저장하기
****************************************************************************************************/
CREATE TABLE #AT_Index_Showcontig (
	mObject_name NVARCHAR(500)
	, mObject_Id INT
	, mIndex_Name NVARCHAR(500)
	, mIndex_Id TINYINT
	, mLevel TINYINT
	, mPages INT
	, mRows INT
	, mMinimumRecordSize INT
	, mMaximumRecordSize INT
	, mAverageRecordSize FLOAT
	, mForwardedRecords INT
	, mExtents INT
	, mExtentSwitches INT
	, mAverageFreeBytes FLOAT
	, mAveragePageDensity FLOAT
	, mScanDensity FLOAT
	, mBestCount INT
	, mActualCount INT
	, mLogicalFragmentation FLOAT
	, mExtentFragmentation FLOAT
)

INSERT INTO #AT_Index_Showcontig
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')

	-- ObjectName : 테이블이름
	-- ObjectId : 테이블번호
	-- IndexName : 인덱스이름
	-- IndexId : 인덱스번호0:Heap, 1:Clustered 2:Nonclustered
	-- Level : 인덱스수준0:인덱스의리프또는데이터, Heap 경우0
	-- Pages : 페이지수
	-- MinimumRecordSize : 최소레코드크기
	-- MaximumRecordSize : 최대레코드크기
	-- AverageRecordSize : 인덱스 또는 전체 힙의 해당 수준에 있는 평균 레코드 크기
	-- ForwardedRecords : 전달된레코드크기(8060byte가넘으면전달된레코드증가)
	-- Extents : 익스텐트수
	-- ExtentSwitches : 익스텐스간의이동횟수
	-- AverageFreeBytes : 검색된페이지에서사용가능한평균바이트수(값이클수록페이지사용률낮음)
	-- AveragePageDensity : 평균페이지밀도(백분율값이클수록좋음)
	-- ScanDensity : 값이작을수록조각화가많이일어난것임(데이터가인접해있는비율)
	-- BestCount : 모든데이터가인접해서연결되어있는경우인스텍트변경횟수
	-- ActualCount : 실제익스텐트변경횟수
	-- LogicalFragmentation : 논리적조각화(백분율이클수록안좋음)
	-- ExtentFragmentation
	

/****************************************************************************************************
* 인덱스 사용량 저장하기
****************************************************************************************************/
CREATE TABLE #AT_Index_Usage_Stats (
	mObject_Name NVARCHAR(500)
	, mIndex_Name NVARCHAR(500)
	, mIndex_id INT
	, mObject_id INT
	, mUser_seeks INT
	, mLast_user_seek DATETIME
	, mUser_scans INT
	, mLast_user_scans DATETIME
	, mUser_lookups INT
	, mLast_user_lookup DATETIME
)

INSERT INTO #AT_Index_Usage_Stats
SELECT
	object_name(A.object_id) AS 'table'
	, A.name AS 'index'
	, A.index_id AS 'id'
	, A.object_id
	, B.user_seeks
	, B.last_user_seek
	, B.user_scans
	, B.last_user_scan
	, B.user_lookups
	, B.last_user_lookup
FROM sys.indexes AS A
	INNER JOIN sys.dm_db_index_usage_stats AS B
		ON A.object_id = B.object_id
		AND A.index_id = B.index_id
		AND B.database_id = db_id()
	INNER JOIN sys.sysobjects AS D
		ON A.object_id = id
WHERE D.type = 'U'

SELECT * FROM #AT_IndexInfo
SELECT * FROM #AT_Index_Showcontig
SELECT * FROM #AT_Index_Usage_Stats


SELECT
	AIS.*
	, AIUS.mUser_seeks
	, AIUS.mLast_user_seek
	, AIUS.mUser_scans
	, AIUS.mLast_user_scans
	, AIUS.mUser_lookups
	, AIUS.mLast_user_lookup
	, AI.mIndex_description
	, AI.mIndex_keys	
FROM #AT_Index_Showcontig AS AIS
	INNER JOIN #AT_Index_Usage_Stats AS AIUS
		ON AIS.mObject_name = AIUS.mObject_name
		AND AIS.mIndex_name = AIUS.mIndex_name
	INNER JOIN #AT_IndexInfo AS AI
		ON AIS.mIndex_Name = AI.mIndex_name

위 쿼리를 실행하면 아래와 같은 결과물을 볼 수 있습니다.