SQL Server Performance

Get the list of fragmented tables...

Discussion in 'Contribute Your SQL Server Scripts' started by ramkumar.mu, May 3, 2006.

  1. ramkumar.mu New Member

    IF object_id('SP_GetFragmentedTables') IS NOT NULL
    BEGIN
    RAISERROR('Procedure SP_GetFragmentedTables dropped',0,1)
    DROP PROCEDURE SP_GetFragmentedTables
    END
    Go

    /***********************************************************************************************************
    Procedure Name : SP_GetFragmentedTables
    Author Name : Ramkumar Murugesan
    Creation Date : 24 March 2006

    Parameters
    ----------
    1) Fragmentation level: Tables below this level would be displayed - Optional

    Description:
    ------------
    This SP displays the most fragmented tables for the current database i.e. tables that have fragmentation
    level below the defragmentation level that is passed as parameter. If no parameter is passed, 80 would be the
    default value. For the complete list of tables and their fragmentation levels, you can get the
    values from ##frag table.

    Execution:
    ----------
    1) SP_GetFragmentedTables 70
    2) SP_GetFragmentedTables

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


    CREATE PROCEDURE SP_GetFragmentedTables
    (
    @Fraglevel int = NULL
    )
    AS

    DECLARE @TableNames varchar(200)
    ,@Object varchar(40)
    ,@Error varchar(200)
    ,@Action varchar(20)
    ,@ErrMsg varchar(20)
    ,@Type varchar(20)

    SELECT @Fraglevel = coalesce(@Fraglevel,80)

    IF object_id('tempdb..##FragTables') IS NOT NULL DROP TABLE ##FragTables
    CREATE TABLE ##FragTables
    (
    ObjectName char(255)
    ,ObjectId int
    ,IndexName char(255)
    ,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 numeric(6,2)
    ,ActualCount numeric(6,2)
    ,LogicalFrag decimal
    ,ExtentFrag decimal
    )
    SELECT @Object='##FragTables',@Type='Table',@Action='Create',@Error=@@error
    RAISERROR('Table Created',0,1)
    IF @Error <> 0 GOTO Crash

    DECLARE FragTablesList CURSOR FOR
    SELECT Table_Name
    FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK)
    WHERE TABLE_TYPE = 'BASE TABLE'

    OPEN FragTablesList

    FETCH NEXT
    FROM FragTablesList
    INTO @TableNames

    WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT INTO ##FragTables
    EXECUTE ('DBCC SHOWCONTIG (''' + @TableNames + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
    FETCH NEXT
    FROM FragTablesList
    INTO @tablenames
    END

    -- Close and deallocate the cursor
    CLOSE FragTablesList
    DEALLOCATE FragTablesList

    SELECT ObjectName As TableName
    ,IndexName
    ,(BestCount/ActualCount) * 100 As DeFragmentationLevel
    FROM ##FragTables
    WHERE ActualCount <> 0
    AND ((BestCount/ActualCount)* 100) < @Fraglevel
    ORDER BY DeFragmentationLevel DESC

    RETURN

    Crash:

    SELECT @ErrMsg = 'Error trying to ' + @Action + ' ' + @Type + ' ' + @Object
    RETURN -200

    Go
    IF @@error = 0
    RAISERROR('Procedure SP_GetFragmentedTables created',0,1)
    Go

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, May 3, 2006
    #2

  2. Madhivanan Moderator

    This is other method


    Create PROCEDURE SP_GetFragmentedTables_New
    (
    @Fraglevel int = NULL
    )
    AS

    DECLARE @TableNames varchar(200)
    ,@Object varchar(40)
    ,@Error varchar(200)
    ,@Action varchar(20)
    ,@ErrMsg varchar(20)
    ,@Type varchar(20)

    SELECT @Fraglevel = coalesce(@Fraglevel,80)

    IF object_id('tempdb..##FragTables') IS NOT NULL DROP TABLE ##FragTables

    CREATE TABLE ##FragTables
    (
    ObjectName char(255)
    ,ObjectId int
    ,IndexName char(255)
    ,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 numeric(6,2)
    ,ActualCount numeric(6,2)
    ,LogicalFrag decimal
    ,ExtentFrag decimal
    )

    Declare @table_name varchar(100)
    set @table_name=''
    WHILE exists
    (
    Select * from information_schema.tables where table_type='BASE TABLE'
    and table_name>@table_name
    )

    Begin
    select @table_name=min(table_name) from information_schema.tables where table_type='BASE TABLE'
    and table_name>@table_name

    INSERT INTO ##FragTables
    EXECUTE ('DBCC SHOWCONTIG (''[' + @Table_Name + ']'') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

    End

    SELECT ObjectName As TableName,IndexName,(BestCount/ActualCount) * 100 As DeFragmentationLevel
    FROM ##FragTables WHERE ActualCount <> 0 AND ((BestCount/ActualCount)* 100) < @Fraglevel
    ORDER BY DeFragmentationLevel DESC


    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, May 3, 2006
    #2

  3. Haywood New Member

    How about one that generates a script for you to execute? Or just change the final PRINT statements to EXEC and it'll defrag automatically. Myself, I prefer to generate scripts first prior to execution on a 'large' system.<br /><br />This script is based on ScanDensity and not Logical Frag. Although I have the same script for that if requested...<br /><br />You'll want to reformant the string print statements as the software here kills cr/lf's...<br /><br /><br />SET NOCOUNT ON<br />GO<br /><br />DECLARE @tablename VARCHAR (255)<br />DECLARE @execstr VARCHAR (255)<br />DECLARE @objectid INT<br />DECLARE @indexid INT<br />DECLARE @scandensity DECIMAL<br />DECLARE @indname VARCHAR (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />DECLARE @Rows INT<br />DECLARE @UpdateUsage VARCHAR(255)<br />DECLARE @UpdateStats VARCHAR(255)<br />DECLARE @MaxFrag DECIMAL<br /><br />SELECT @MaxFrag = 90<br /><br />-- Check Index Scan Density.<br />-- <br />-- <br />-- G. Rayburn. 3/25/2003<br />-- Stolen from BOL and hotrodded to suit.<br />-- <br />-- USE WITH CAUTION ON LARGE TABLES!<br />-- BLOCKING WILL OCCUR!<br />-- <br /><br />PRINT '-- ************************************'<br />PRINT '-- * Table/Index Scan Density Report *'<br />PRINT '-- * ------------------------------- *'<br />PRINT '-- * IndID:[1] Clustered PK *'<br />PRINT '-- * IndID:[2] Non-Clustered PK *'<br />PRINT '-- * -------------------------------- *'<br />PRINT '-- * Only reports DBO owned tables - *'<br />PRINT '-- * fsck all others. G. Rayburn *'<br />PRINT '-- ************************************'<br />PRINT ''<br /><br />IF EXISTS (SELECT * FROM Tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##FragList' ) DROP TABLE ##FragList<br /><br /><br /><br />-- Declare cursor<br />DECLARE tables CURSOR FOR<br /> SELECT TABLE_NAME<br /> FROM INFORMATION_SCHEMA.TABLES<br /> WHERE TABLE_TYPE = 'BASE TABLE'<br /> AND TABLE_SCHEMA = 'dbo'<br /><br />-- Create the table<br />CREATE TABLE ##FragList (<br /> ObjectName CHAR (255),<br /> ObjectId INT,<br /> IndexName CHAR (255),<br /> IndexId INT,<br /> Lvl INT,<br /> CountPages INT,<br /> CountRows INT,<br /> MinRecSize INT,<br /> MaxRecSize INT,<br /> AvgRecSize INT,<br /> ForRecCount INT,<br /> Extents INT,<br /> ExtentSwitches INT,<br /> AvgFreeBytes INT,<br /> AvgPageDensity INT,<br /> ScanDensity DECIMAL,<br /> BestCount INT,<br /> ActualCount INT,<br /> LogicalFrag DECIMAL,<br /> ExtentFrag DECIMAL)<br /><br />-- Open the cursor<br />OPEN tables<br /><br />-- Loop through all the tables in the database<br />FETCH NEXT<br /> FROM tables<br /> INTO @tablename<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />-- Do the showcontig of all indexes of the table<br /> INSERT INTO ##FragList <br /><br /> EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')<br /><br /><br /> FETCH NEXT<br /> FROM tables<br /> INTO @tablename<br />END<br /><br />-- Close and deallocate the cursor<br />CLOSE tables<br />DEALLOCATE tables<br /><br /><br /><br /><br /><br /><br />-- -- Report objects & optionally exec dynsql.<br /><br />-- DECLARE @tablename VARCHAR (255)<br />-- DECLARE @execstr VARCHAR (255)<br />-- DECLARE @objectid INT<br />-- DECLARE @indexid INT<br />-- DECLARE @scandensity DECIMAL<br />-- DECLARE @indname VARCHAR (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />-- DECLARE @Rows INT<br />-- DECLARE @UpdateUsage VARCHAR(255)<br />-- DECLARE @UpdateStats VARCHAR(255)<br />-- DECLARE @MaxFrag DECIMAL<br />-- <br />-- SELECT @MaxFrag = 90<br /><br /><br />-- Begin formatting output.<br />DECLARE @Cnt Int<br />SELECT @Cnt = (SELECT count(*)<br />FROM ##FragList<br />WHERE ScanDensity &lt; @maxfrag<br /> AND IndexName NOT LIKE '_WA_%'<br /> AND IndexID BETWEEN 1 AND 254<br />AND CountPages &gt; 1000)<br /><br /><br />PRINT '-- Count of indexs needing to be defragged - ' + CONVERT(VarChar(5),(RTRIM(@Cnt))) + '.'<br />PRINT ''<br /><br />DECLARE @Msg VarChar(255)<br />SET @Msg = (SELECT '-- Indices w/scan density less than ' + CONVERT(VarChar(3),@MaxFrag) + '% for database ' + DB_NAME() + '.')<br /><br />DECLARE @Len Int<br />SET @Len = (SELECT DATALENGTH(@Msg))<br /><br />PRINT @Msg<br />PRINT REPLICATE('=',@Len)<br />PRINT ''<br />PRINT ''<br />-- End formatting output.<br /><br />-- Declare cursor for list of indexes to be defragged.<br />DECLARE indexes CURSOR FOR<br /> SELECT ObjectName, ObjectId, IndexId, ScanDensity, IndexName<br /> FROM ##FragList<br /> WHERE ScanDensity &lt; @maxfrag<br /> AND IndexName NOT LIKE '_WA_%'<br /> AND IndexID BETWEEN 1 AND 254<br /> AND CountPages &gt; 1000<br /> ORDER BY ScanDensity<br /><br />-- Open the cursor<br />OPEN indexes<br /><br />-- loop through the indexes<br />FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectid, @indexid, @scandensity, @indname<br /><br /><br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /><br /><br /> SELECT @Rows = (SELECT rows FROM sysindexes WHERE id = @objectid AND indid = @indexid)<br /><br />IF @Rows &gt; 0<br /><br />BEGIN<br /><br /><br /> PRINT '-- _.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._'<br /> PRINT ''<br /> PRINT '-- (Table: ' + RTRIM(@tablename) + '<br />-- IndID:[' + RTRIM(@indexid)+ ']' + RTRIM(@indname) + ', has ' + CONVERT(VarChar(10),@Rows) + ' rows,) - scan density currently is '<br /> + RTRIM(CONVERT(varchar(15),@scandensity)) + '%.' <br /> PRINT ' '<br /> PRINT ' '<br /><br /> SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'<br /><br /> SELECT @UpdateUsage = 'DBCC UPDATEUSAGE (''' + RTRIM(db_name()) + ''',''[' + RTRIM(@tablename) + ']'')'<br /><br /> SELECT @UpdateStats = 'UPDATE STATISTICS ' + RTRIM(@tablename)<br /><br />SET NOCOUNT ON<br /><br /> PRINT @execstr<br /> PRINT ''<br />-- EXEC (@execstr)<br /> PRINT ''<br /><br /> PRINT @UpdateUsage<br /> PRINT ''<br />-- EXEC (@UpdateUsage)<br /> PRINT ''<br /><br /> PRINT @UpdateStats<br /> PRINT ''<br />-- EXEC (@UpdateStats)<br /> PRINT ''<br /><br /> FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectid, @indexid, @scandensity, @indname<br /><br />END<br /><br />ELSE<br /><br />BEGIN<br /><br /> FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectid, @indexid, @scandensity, @indname<br /><br />END<br /><br />END<br /><br /><br />-- Close and deallocate the cursor<br />CLOSE indexes<br />DEALLOCATE indexes<br /><br /><br /> PRINT ''<br />PRINT '-- Done!'
    Haywood, May 4, 2006
    #2

Share This Page