/************************************************************************************************
-- sp_showcontig0
-- 인덱스 조각화 정보를 전체와 기준값을 기준으로 보여준다.
-- 처음 파라미터는 조각화 수치 기준으로 필터링.
-- 두번째 파라미터는 해당 테이블만 적용.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_showcontig0') IS NOT NULL
DROP PROCEDURE sp_showcontig0
GO
CREATE PROC sp_showcontig0
@value decimal(3) = 0
, @objname varchar(4) = NULL
AS
SET NOCOUNT ON
IF (@objname IS NOT NULL)
BEGIN
IF NOT EXISTS(SELECT ID
FROM sysobjects
WHERE id = object_id(@objname)
AND xtype IN ('S','U', 'V')
)
BEGIN
RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)
RETURN(1)
END
END
DECLARE @dbname SYSNAME
DECLARE @sql VARCHAR(300)
DECLARE @tblname SYSNAME
DECLARE @idxname SYSNAME
CREATE TABLE #tableindex
( tblname SYSNAME
, idxname SYSNAME
, idxdesc VARCHAR(1000)
, idxcol VARCHAR(1000)
)
CREATE TABLE #fraglist
( ObjectName SYSNAME
, ObjectId INT
, IndexName SYSNAME
, IndexId INT
, Lvl INT
, CountPages INT
, CountRows INT
, MinRecSize INT
, MaxRecSize INT
, AvgRecSize INT
, ForRecCount INT
, Extents INT
, ExtentSwitches INT
, AvgFreeBytes INT
, AvgPageDensity INT
, ScanDensity DECIMAL
, BestCount INT
, ActualCount INT
, LogicalFrag DECIMAL
, ExtentFrag DECIMAL
)
SET @dbname = db_name()
SET @sql = 'INSERT INTO #tableindex EXEC ' + @dbname + '..sp_index0'
EXEC (@sql)
IF (@objname IS NULL)
DELETE FROM #tableindex WHERE tblname = 'dtproperties'
ELSE
DELETE FROM #tableindex WHERE tblname != @objname
DECLARE cur_contig CURSOR READ_ONLY
FOR SELECT tblname, idxname
FROM #tableindex
OPEN cur_contig
FETCH NEXT FROM cur_contig
INTO @tblname, @idxname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ' + @dbname
SET @sql = @sql +' DBCC SHOWCONTIG (''' + @tblname + ''','''
SET @sql = @sql + @idxname + ''') WITH TABLERESULTS'
INSERT INTO #fraglist
EXEC (@sql)
FETCH NEXT FROM cur_contig
INTO @tblname, @idxname
END
CLOSE cur_contig
DEALLOCATE cur_contig
IF (@value = 0)
SELECT objectname "TblName"
, indexname "IdxName"
, countpages "ScanPage"
, extents "ScanExtent"
, extentswitches "SwitchExtent"
, CASE WHEN extents = 0 THEN 0
ELSE CAST(CAST(countpages AS DEC)
/ CAST(extents AS DEC)
AS DEC(4,1))
END "Avg.Page/Extent"
, CAST(scandensity AS VARCHAR(6)) + '% ['
+ CAST(bestcount AS VARCHAR(10)) + ':'
+ CAST(actualcount AS VARCHAR(10)) + ']'
"ScanDensity[Best:Actual]"
, CAST(logicalfrag AS Varchar(3)) + '%' "LogicalScanFrag"
, CAST(extentfrag AS vARCHAR(3)) + '%' "ExtentScanFrag"
, avgfreebytes "Avg.FreeBytes/Page"
, CAST(avgpagedensity AS VARCHAR(3)) + '%' "Avg.PageDensity(Full)"
, ForRecCount "ForwardedRec."
FROM #fraglist
ELSE
SELECT objectname "TblName"
, indexname "IdxName"
, countpages "ScanPage"
, extents "ScanExtent"
, extentswitches "SwitchExtent"
, CASE WHEN extents = 0 THEN 0
ELSE CAST(CAST(countpages AS DEC)
/ CAST(extents AS DEC)
AS DEC(4,1))
END "Avg.Page/Extent"
, CAST(scandensity AS VARCHAR(6)) + '% ['
+ CAST(bestcount AS VARCHAR(10)) + ':'
+ CAST(actualcount AS VARCHAR(10)) + ']'
"ScanDensity[Best:Actual]"
, CAST(logicalfrag AS Varchar(3)) + '%' "LogicalScanFrag"
, CAST(extentfrag AS vARCHAR(3)) + '%' "ExtentScanFrag"
, avgfreebytes "Avg.FreeBytes/Page"
, CAST(avgpagedensity AS VARCHAR(3)) + '%' "Avg.PageDensity(Full)"
, ForRecCount "ForwardedRec."
FROM #fraglist
WHERE LogicalFrag >= @value
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
DROP TABLE #tableindex
DROP TABLE #fraglist
RETURN(0) --sp_showcontig0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_showcontig0 TO PUBLIC
GO
'Programming > MSSQL' 카테고리의 다른 글
MSSQL 테이블 인덱스, 인덱스 사이즈, row count 조회 (0) | 2018.04.16 |
---|---|
ORACLE TO MSSQL 테이블 스키마 변경하기 (0) | 2018.04.16 |
MSSQL OJBECT 보기 (DBA 관리용 시스템 프로시저) (0) | 2018.04.13 |
MSSQL 물리적 드라이브 가용량 보기 (DBA 관리용 시스템 프로시저) (0) | 2018.04.12 |
MSSQL 테이블 ROW 길이 보기 (DBA 관리용 시스템 프로시저) (0) | 2018.04.11 |