SQL Server Performance

SQL2K5 -- Fragmentation Report

Discussion in 'Contribute Your SQL Server Scripts' started by Haywood, Nov 9, 2005.

  1. Haywood New Member

    I'm putting this out here mostly to find out if I'm doing things 'right' in SQL2K5.

    This script is based on Kalen Delaneys` article from SQLMagazine last month (Instant DocID: 47667). There is also a MS Webcast that Kimberly Tripp goes over the same topic.

    It's reporting only at the moment, since we don't have a complete (yet) understanding of sys.dm_db_index_physical_stats.

    Anybody see anything I should be doing differently? Does SQL2K5 have an alternative to cursors and EXEC statements I should be using? Dynamic SQL is required for this script in order to associate the appropriate object_id with object_name(object_id).



    /****** BEGIN SCRIPT ******/

    USE Admin
    GO

    IF EXISTS (SELECT [name] FROM sys.objects WHERE [name] = 'usp_DB_Frag_Detailed' AND TYPE = 'P')
    DROP PROCEDURE usp_DB_Frag_Detailed
    GO

    CREATE PROCEDURE usp_DB_Frag_Detailed (@Percentage INT = NULL)
    -- Admin.dbo.usp_DB_Frag_Detailed 5

    AS
    /******************************************************************************
    ** ¼ Raylev Database Support & Consulting, 2005.
    ** This script is provided as is, and it's effects are not liable
    **for any impacts/damage to your systems.
    *******************************************************************************
    **
    **Name: usp_DB_Frag_Detailed.sql
    **
    **Description: Report table/index fragmentation (detailed) on all db's.
    **
    **
    **
    **Return values: 0 - Success
    **-1 - Error
    **
    **Author: G. Rayburn
    **
    **Date: 11/07/2005
    **
    **Depends on:
    **
    **
    **
    *******************************************************************************
    **Modification History
    *******************************************************************************
    **
    **Initial Creation: 11/07/2005 G. Rayburn
    **
    *******************************************************************************
    **
    ******************************************************************************/
    SET NOCOUNT ON

    IF @Percentage IS NULL
    SET @Percentage = 10

    DECLARE @DBName VARCHAR(50)
    , @DBID VARCHAR(3)
    , @DynSQL VARCHAR(1000)
    , @Msg VARCHAR(300)
    , @MsgRep VARCHAR(300)

    -- Header formatting...
    SELECT @Msg = 'Detailed fragmentation report for server: ' + @@SERVERNAME + ' on ' + CONVERT(CHAR(19),getdate(),100) + '.'
    PRINT @Msg
    SELECT @MsgRep=REPLICATE('=',LEN(@Msg))
    PRINT @MsgRep
    PRINT ''
    PRINT ''
    PRINT ''


    DECLARE curDBFrag CURSOR
    FOR SELECT [name]
    FROM master.sys.databases
    WHERE [name] NOT IN ('model', 'tempdb')
    ORDER BY [name]

    OPEN curDBFrag

    FETCH NEXT FROM curDBFrag INTO @DBName
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN

    SET @DBID = (SELECT database_id FROM master.sys.databases WHERE [name] = @DBName)

    SET @DynSQL = '

    USE [' + @DBName + ']

    PRINT ''' + @DBName + ':''

    PRINT ''''

    SELECT ISNULL(LEFT(object_name(object_id),40),object_id) AS [ObjName]
    , index_id
    , LEFT(index_type_desc,25) AS [Index Type]
    , CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent)) AS [Avg. Frag %]
    , CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragment_size_in_pages)) AS [Avg. frag size in pages]
    , fragment_count
    , page_count
    , record_count

    FROM sys.dm_db_index_physical_stats (' + @DBID + ', NULL, NULL, NULL, ''DETAILED'')
    WHERE avg_fragmentation_in_percent > ' + CONVERT(CHAR(2),@Percentage) + '

    ORDER BY CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent) DESC

    PRINT ''''
    PRINT ''''
    PRINT ''''

    '

    EXEC (@DynSQL);

    END
    FETCH NEXT FROM curDBFrag INTO @DBName
    END

    CLOSE curDBFrag
    DEALLOCATE curDBFrag
    --
    --sp_help 'sys.dm_db_index_physical_stats'
    --
    GO


    /****** END SCRIPT ******/

    FWIW, it would be REALLY nice if formatting could be maintained in this forum.
  2. satya Moderator

    Haywood
    I have edited your post and added HTML short forms to maintain formatting... anyway good script too.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Haywood New Member

    And here is the associated defrag script (which is subject to change)...

    Apologies for the formatting.

    Edit 12/28/2005: Added schema_owner to object qualifiers.


    /******************************************************************************
    ** ¼ Raylev Database Support & Consulting, 2005.
    ** This script is provided as is, and it's effects are not liable
    **for any impacts/damage to your systems.
    *******************************************************************************
    **
    **Name: usp_DB_DeFrag.sql
    **
    **
    **Description: Defragment tables/indexes based on output.
    **Based on usp_DB_Frag_Detailed procedure.
    **
    **This script can either be run direct and defragment, or it can be run
    ** and the output used as a defrag script. Simply change EXEC (@DynDeFragList)
    ** to PRINT (@DynDeFragList) for script output.
    **
    ** Added DATABASE DDL TRIGGER disable/enable because the developers are just
    ** dying to make my job harder...<sigh>
    **
    **
    **Author: G. Rayburn
    **Date: 12/27/2005
    **
    *******************************************************************************
    **Modification History
    *******************************************************************************
    **
    **Initial Creation: 12/27/2005 G. Rayburn
    **
    *******************************************************************************
    **
    ******************************************************************************/
    SET NOCOUNT ON
    GO

    IF EXISTS (SELECT [name] FROM tempdb.sys.objects WHERE [name] LIKE '#_FragList%')
    DROP TABLE #_FragList
    GO

    -- Disable ddl database triggers.
    DISABLE TRIGGER ALL ON DATABASE
    GO

    DECLARE @Percentage int
    , @DynFragList varchar(1000)
    -- Cursor objects:
    , @SchemaName varchar(100)
    , @ObjectName varchar(100)
    , @IndexName varchar(100)
    , @IndexType varchar(25)
    , @AvgFrag int

    SET @Percentage = 10


    -- Temp table for later iteration against.
    CREATE TABLE #_FragList
    (
    ObjectName varchar(100)
    ,Index_id int
    ,IndexType varchar(60)
    ,avg_fragmentation_in_percent float
    ,avg_fragment_size_in_pages float
    ,avg_page_space_used_in_percent float
    ,fragment_count bigint
    ,page_count bigint
    ,record_count bigint
    ,forwarded_record_count bigint
    )

    INSERT INTO #_FragList
    SELECT
    LEFT(object_name(object_id),100)
    , index_id
    , LEFT(index_type_desc,25)
    , CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent))
    , CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragment_size_in_pages))
    , CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_page_space_used_in_percent))
    , fragment_count
    , page_count
    , record_count
    , forwarded_record_count

    FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, 'DETAILED')
    WHERE avg_fragmentation_in_percent > CONVERT(CHAR(2),@Percentage)
    ORDER BY CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent) DESC;


    -- Do the defrag.
    DECLARE curDBFrag CURSOR
    FOR

    SELECT schema_name(so.schema_id) AS [Schema]
    ,fl.[ObjectName]
    ,si.[name]
    ,fl.IndexType
    ,fl.avg_fragmentation_in_percent
    FROM #_FragList fl
    , sys.indexes si
    , sys.objects so

    WHERE object_id(fl.ObjectName) = si.object_id
    AND fl.index_id = si.index_id
    AND object_id(fl.objectname) = so.object_id
    AND fl.Index_ID <> 0
    AND si.is_disabled = 0


    GROUP BY so.schema_id
    ,fl.[ObjectName]
    ,si.[name]
    ,fl.IndexType
    ,fl.avg_fragmentation_in_percent

    ORDER BY fl.avg_fragmentation_in_percent DESC

    OPEN curDBFrag

    FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN

    SET @DynFragList = '
    PRINT ''-- _.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._''
    PRINT ''Table: ' + @ObjectName + '
    Index: ' + @IndexName + ' of type: ' + @IndexType + ' is ' + CONVERT(varchar(5),@AvgFrag) + '% fragmented.''

    ALTER INDEX ' + @IndexName + '
    ON ' + @SchemaName + '.' + @ObjectName + ' REORGANIZE;

    PRINT ''''

    DBCC UPDATEUSAGE (''' + db_name() + ''', ''' + @SchemaName + '.' + @ObjectName + ''', ''' + @IndexName + '''); -- WITH NO_INFOMSGS;

    PRINT ''''

    UPDATE STATISTICS ' + @SchemaName + '.' + @ObjectName + ' ' + @IndexName + ';

    PRINT ''''
    PRINT ''''


    '
    PRINT (@DynFragList);

    END
    FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag
    END

    CLOSE curDBFrag
    DEALLOCATE curDBFrag;
    GO

    -- Enable ddl database triggers.
    ENABLE TRIGGER ALL ON DATABASE;
    GO


  4. Haywood New Member

    Thanks for the formatting again Satya. <img src='/community/emoticons/emotion-1.gif' alt=':)' />

Share This Page