본문 바로가기
Programming/MSSQL

MSSQL INDEX 조각화 정보 (DBA 관리용 시스템 프로시저)

by 제타 2018. 4. 14.
반응형

/************************************************************************************************

-- 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



반응형