INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Microsoft SQL Server: Setup and Administration FAQ

Maintaining Indexes

Setup a configurable Re-Index Solution by hmckillop
Posted: 10 Aug 04

This is going to be a long faq, but it should be worth it if you spend the time. This faq will display a potential method of maintaining your database indices to the precise level you require. It will allow for managing indices at a table level or at an individual index level,also re-indexing using indexdefrag or dbreindex depending on the requirements, and all of this on a series of relatively small tables.
We use this as our overall database index strategy across multiple databases.

Firstly we need to create the tables to hold the data.
Table1 is used to hold the list of indices you require to have non-default configuration. The re-indexing works on the understanding
that anything not in this table will be re-indexed when it meets the default conditions - shown later

CODE

use msdb
go

if exists (select * from msdb.dbo.sysobjects where name = 'Fragmented_Indexes_XConfig' and type = 'U')
    drop table dbo.Fragmented_Indexes_XConfig
go

create table msdb.dbo.Fragmented_Indexes_XConfig (
    DBName         sysname         NOT NULL,
    ObjectName     varchar(255)    NOT NULL,
    IndexName     varchar(255)    NOT NULL,               
    NewFill     tinyint            NULL,
    ScanDensity tinyint         NULL)
go

alter table msdb.dbo.Fragmented_Indexes_XConfig
add constraint PK_XConfig primary key clustered
    (DBName, ObjectName, IndexName)
with fillfactor = 70

The second and third tables hold the results from the DBCC ShowContig and will be populated every time the job usp_Defrag_report is run.

CODE

-- Must get rid of any old v1 & 2 Fragmented_Indexes table
if object_id('msdb.dbo.Fragmented_Indexes') is not null
    drop table msdb.dbo.Fragmented_Indexes
go

-- Table modified v2 & v3
create table msdb.dbo.Fragmented_Indexes (
                DBName sysname,
                ObjectName VARCHAR (255),
                ObjectId INT,
                IndexName VARCHAR (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 INT,
                ActualCount INT,
                LogicalFrag DECIMAL,
                ExtentFrag DECIMAL,
                OrigFill tinyint)
go

CODE

if exists (select * from msdb..sysobjects where name = 'Fragmented_Indexes_History' and type = 'U')
    drop table Fragmented_Indexes_History
go

create table msdb..Fragmented_Indexes_History (
    DBName sysname,
    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 INT,
    ActualCount INT,
    LogicalFrag DECIMAL,
    ExtentFrag DECIMAL,
    Origfill Tinyint,
    start_time datetime,
    end_time   datetime,
    newfill int)

The actual code to populate these tables and re-index the database(s) are managed through 3 stored procedures. We have created jobs
on our production server to execute these procs nightly, but you can get them to run as often as you like.

The first procedure is described in the header.

CODE

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_frag_report]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_frag_report]
GO

/*=========================================================================================

Description:
    Calls usp_get_dnsty for each user DB in instance to fill msdb..Fragmented_Indexes with
    all indexes with scan density less than @target

Notes:
    Still need to look into _WA_Sys auto-statistics to see if they should be excluded. Also
    need to investigate phenomena of fragmented non-clustered indexes on non-fragmented
    clustered tables.

Installation:    
    msdb
    
Processing Steps:
    If msdb..Fragmented_Indexes doesn't exist, create it
    open cursor for all user DB names
    call usp_get_dnsty for each with specified target scan density

Tables Used:
    msdb.dbo.Fragmented_Indexes
    master.dbo.sysdatabases
    
Parameters:
    @target (tinyint, def: 85) scan density below which table/index is considered as execessively fragmented
    @alert (bit, def: 1) 1: raiserror if fragmentation found; 0: silent execution - no raiserror
    @minpages (int, def: 40)
=========================================================================================*/

create procedure usp_frag_report (
    @target decimal(5,2) = 85.00,
    @alert bit = 1,
    @minpages int = NULL
)
as
begin

    declare @dbname sysname
    declare @frag_count int
    declare @message varchar(30)
    declare @err int

    set nocount on

    -- clear report table
    if exists (select * from msdb..sysobjects where name = 'Fragmented_Indexes' and type = 'U')
        truncate table Fragmented_Indexes
    else
        create table msdb..Fragmented_Indexes (
               DBName sysname,
               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 INT,
               ActualCount INT,
               LogicalFrag DECIMAL,
               ExtentFrag DECIMAL,
               Origfill Tinyint)
    
    declare iw_dbcursor cursor for
        select name
            from master.dbo.sysdatabases
            where name not in ('master', 'model', 'tempdb', 'distribution', 'msdb')
                and (status & 1024) = 0 -- exclude DBs with read-only flag set - includes log shipped
            order by dbid

    open iw_dbcursor
    
    fetch next from iw_dbcursor into @dbname
    
    while @@fetch_status = 0
    begin
        print @dbname
        
        if @minpages is null
            exec @err = usp_get_dnsty @dbname, @target
        else
            exec @err = usp_get_dnsty @dbname, @target, @minpages

        fetch next from iw_dbcursor into @dbname
    end

    close iw_dbcursor
    deallocate iw_dbcursor

    if object_id('msdb.dbo.Fragmented_Indexes') is not null    
        select @frag_count = count(distinct dbname) from Fragmented_Indexes

    if isnull(@frag_count, 0) > 0 and @alert = 1
    begin
        set @message = convert(varchar, @frag_count) + ' database' + case when @frag_count > 1 then 's' else '' end + ' on Server ' + @@SERVERNAME
        --raiserror(50016, 18, 1, @message) -- SQL_Admin error 50016 must be installed
    end
end

GO

This next proc is called from the usp_defrag proc and is used to get the scan densities for all table indexes where they are below a threslhold
The threshold is either the default - specified in parameter "target", or it is captured from the Fragmented_indexes_XConfig. Again
further description is in the header.

CODE

use msdb
go

if object_id('usp_get_dnsty') is not null
    drop proc usp_get_dnsty
go
/*=========================================================================================

Description:
    Get scan densities for all table indexes in a SQL2K DB where scandensity < @target
    Store results in (v3.0) msdb..Fragmented_Indexes

Depends On:
    msdb..Fragmented_Indexes

Notes:
    uses undocumented procedure sp_MSForEachTable

Installation:    
    msdb
    
Processing Steps:
    create temp table #fraglist
    fill with dbcc showcontig for all tables in target DB (with tableresults, all_indexes, all_levels)
    insert into msdb..Fragmented_Indexes all records (except BLOB and heap pointers) where scandensity is below specified @target,
        extentswitches > 0 and extents >= specified minimum extents (i.e. if your table only takes up 0.5 an extent it'll always look
        50% fragmented even though it's not - similar distortion is also seen with tiny tables under 3 or 4 extents)

Tables Used:
    msdb..Fragmented_Indexes
    creates and drops #fraglist
    
Parameters:
    @dbname (sysname) name of database to get densities for
    @target (numeric 5,2 def: 85.00) scan density below which table/index is considered as execessively fragmented
    @minpages (int def: 40) minimimum number of pages below which table/index fragmentation not significant

Return Value:
    -1: error - invalid target DB; 0: OK

Called By:
    usp_frag_report

Calls:
    *sp_MSForEachTable

CHANGE NOTES:
Add Fragmented_Indexes_XConfig to allow specification of
divergent specific scandensities for individual tables
Removed WITH ALL_LEVELS option from DBCC SHOWCONTIG call
cos it's buggy (Level column is always 0) and it doesn't
really help us anyway.
    
=========================================================================================*/

create procedure usp_get_dnsty (
    @dbname sysname,
    @target decimal(5,2) = 85.00,
    @minpages int = 40
)
as
    set nocount on
    declare @err int, @cmd varchar(1000)

    -- Leave system databases alone    
    if @dbname = 'master'
        or  @dbname = 'model'
        or  @dbname = 'tempdb'
        or  @dbname = 'distribution'
        or  @dbname = 'msdb'
        or     isnull(@dbname, '') = ''
        return -1 -- error

    -- Create temporary table to hold DBCC SHOWCONTIG output
    CREATE TABLE #fraglist (
               ObjectName VARCHAR (255),
               ObjectId INT,
               IndexName VARCHAR (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 INT,
               ActualCount INT,
               LogicalFrag DECIMAL,
               ExtentFrag DECIMAL)
    /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err
    
    -- Update statistics first
    set @cmd = 'use ' + @dbname + '; exec sp_updatestats'
    exec(@cmd)
    /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err
    
    -- Insert DBCC SHOWCONTIG output for all tables in database into #fraglist
    set @cmd = 'use ' + @dbname + '; exec sp_MSForEachTable @command1 = ''insert into #fraglist exec(''''dbcc showcontig([?]) with tableresults, all_indexes'''')'''    
    exec (@cmd)
    /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err

    -- Insert individually specified scandensity fragmented indexes
    insert into msdb..Fragmented_Indexes
        (DBName, ObjectName,ObjectId, IndexName, IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize, AvgRecSize, ForRecCount, Extents,
        ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFrag, ExtentFrag)
        select @dbname, rtrim(t.ObjectName), t.ObjectId, rtrim(t.IndexName), t.IndexId, t.Lvl, t.CountPages, t.CountRows, t.MinRecSize, t.MaxRecSize,
            t.AvgRecSize, t.ForRecCount, t.Extents, t.ExtentSwitches, t.AvgFreeBytes, t.AvgPageDensity, t.ScanDensity, t.BestCount, t.ActualCount,
            t.LogicalFrag, t.ExtentFrag
        from #fraglist t inner join msdb..Fragmented_Indexes_XConfig x
            on (t.ObjectName = x.ObjectName and t.IndexName = x.IndexName)
        where x.DBName = @dbname
            and t.extentswitches > 0
            and t.indexid not in (0, 255)
            and t.scandensity < x.ScanDensity

    -- Remove specified scandensity fragmented indexes from general SHOWCONTIG list
    delete from #fraglist
    where exists (select x.ObjectName, x.IndexName
                    from Fragmented_Indexes_XConfig x
                    where x.DBName = @dbname
                        and x.ObjectName = #fraglist.ObjectName
                        and x.IndexName = #fraglist.IndexName)
    -- TODO - test above

    -- Add all indexes falling below @target to Fragmented_Indexes
    insert into msdb..Fragmented_Indexes
        (DBName, ObjectName,ObjectId, IndexName, IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize, AvgRecSize, ForRecCount, Extents, ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFrag, ExtentFrag)
        select @dbname as DBName, rtrim(ObjectName), ObjectId, rtrim(IndexName), IndexId, Lvl, CountPages, CountRows, MinRecSize, MaxRecSize,
            AvgRecSize, ForRecCount, Extents, ExtentSwitches, AvgFreeBytes, AvgPageDensity, ScanDensity, BestCount, ActualCount, LogicalFrag,
            ExtentFrag
            from #fraglist
            where extentswitches > 0
                and indexid not in (0, 255)
                and scandensity < @target
                and CountPages >= @minpages
            order by objectid, indexid, lvl
    /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err
    
    -- Clean up temporary SHOWCONTIG table
    drop table #fraglist
    /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err

    -- Query appropriate sysindexes table to get OrigFill settings for indexes
    set @cmd = 'update msdb..Fragmented_Indexes set origfill = i.OrigFillFactor from msdb..Fragmented_Indexes f inner join ' + @dbname
                + '..sysindexes i on (f.ObjectId = i.id and f.indexid = i.indid) where f.origfill is null and f.DBName = ''' + @dbname + ''''
    
    exec (@cmd)
    /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err

    return 0


The last proc is then used to query the fragmented_indexes table and then perform any defragging necessary

CODE

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_defrag]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[usp_defrag]
GO

/*=========================================================================================

Description:
    Defragment indexes based on information stored in msdb.dbo.Fragmented_Indexes
    by running usp_frag_report

Depends On:
    usp_get_dnsty(e.g. prior script installations, exes, registry settings, etc.

Notes:
    Assumes dbo owner of all objects

Installation:    
    msdb
    
Processing Steps:
    *
    
Tables Used:
    msdb..Fragmented_Indexes
    msdb..Fragmented_Indexes_History
    msdb..Fragmented_Indexes_XConfig
            
Parameters:
        @dbname (sysname def: NULL)        MyDbName: Database to defragment; NULL: All writeable user databases
        @eachindex (bit def: 0)         1: defrag each index individually; 0: Just do clustered
        @defaultfill (tinyint def: 85)    0-100 Fill factor % to use when rebuilding
        @useorigfill (bit def: 0)         1: use original fill factor (if > 0); 0: use default regardless
        @rebuild (bit def: 1)            1: use DBCC DBREINDEX; 0: use DBCC INDEXDEFRAG


Return Value:
    0
Called By:
    Defragmentation job
Calls:
    usp_get_dnsty

CHANGE NOTES
Add fragmented_indexes_history table, and
fragmented_indexes_fillfactor table to override applying
default fillfactor. This code has been added in the
"Each index individually" section only. Also, after each index
has been rebuilt, the row for that index is deleted from the
fragmented_indexes table.

Commented out table defns (now in separate files) and changed
Fragmented_Indexes_Fillfactor to Fragmented_Indexes_XConfig
Refactored to remove split paths based on eachindex. Now only
one main loop/path. However, now means that if eachindex = 0,
only fragmentation in the clustered index will be processed
(i.e. fragmentation in nonclustered indexes on a table for which
the clustered index is within tolerance will not trigger a defrag)
    
=========================================================================================*/

create proc usp_defrag (
    @dbname         sysname = NULL,
    @eachindex         bit = 0,     -- 0 = no 1 = yes
    @defaultfill    tinyint = 80,
    @useorigfill     bit = 0,     -- 0 = no, use default; 1 = yes (except when origfill = 0 then use @defaultfill)
    @rebuild        bit = 1        -- 0 = DBCC INDEXDEFRAG; 1 = DBCC DBREINDEX
)
as
    declare @cmd varchar(500),
        @targdb sysname,
        @tabname sysname,
        @origfill tinyint,
        @newfill tinyint,
        @indexname sysname,
        @err int,
        @count int,
        @start_time datetime,
        @end_time   datetime

    -- Troubleshout erroneous defaultfill params
    if @defaultfill < 0 or @defaultfill > 100
        set @defaultfill = 80
    
    set @cmd = ''

    -- Define TARGETS cursor for indexes to defrag
    if @dbname is null
        -- All databases in instance
        if @eachindex = 0
            declare TARGETS cursor for
                select distinct dbname, objectname, indexname, origfill
                from msdb..Fragmented_Indexes
                where indexid = 1 -- Clustered Indexes only
        else
            declare TARGETS cursor for
                select distinct dbname, objectname, indexname, origfill
                from msdb..Fragmented_Indexes
    else
        -- Specified database only
        if @eachindex = 0
            declare TARGETS cursor for
                select distinct dbname, objectname, indexname, origfill
                from msdb..Fragmented_Indexes
                where dbname = @dbname
                and indexid = 1 -- Clustered Indexes only
        else
            declare TARGETS cursor for
                select distinct dbname, objectname, indexname, origfill
                from msdb..Fragmented_Indexes
                where dbname = @dbname
            
    /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err

    -- Open TARGETS Cursor
    open TARGETS
    fetch next from TARGETS into @dbname, @tabname, @indexname, @origfill
    /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err

    -- Loop for each index to defrag
    while @@fetch_status = 0
    begin
        if @rebuild = 0
        begin
            -- just do an INDEXDEFRAG
            set @cmd = 'dbcc indexdefrag (''' + rtrim(@dbname) + ''', ''' + rtrim(@tabname) + '''' + ', '+ '''' + rtrim(@indexname) + '''' + ')'
        end
        else
        begin
            -- Do DBREINDEX
            -- sort out what fill factor to rebuild the index to
            select @newfill = newfill
            from msdb..Fragmented_Indexes_XConfig
            where          dbname = rtrim(@dbname)
                and     objectname = rtrim(@tabname)
                and     indexname  = rtrim(@indexname)
                
            -- if a row is returned for this index from Fragmented_Indexes_XConfig table
            -- then use that FILLFACTOR, otherwise use the CASE statement below to
            -- decide which FILLFACTOR to use
            if @newfill is null   
               select @newfill = case when @useorigfill * @origfill > 0 then @origfill else @defaultfill end
                   
            print 'Index: ' + @indexname +' newfill ' + convert(varchar(5), isnull(@newfill, 'NULL'))
            /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err
        
            -- now rebuild this specific index to chosen fill factor
            set @cmd = 'dbcc dbreindex (''' + rtrim(@dbname) + '..' + rtrim(@tabname) + ''', ''' + rtrim(@indexname) + ''' , '
                            + convert(varchar(3), @newfill) + ')'
        end

        set @start_time = getdate()

        exec (@cmd)
        /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err

        set @end_time = getdate()
        
        -- Move processed indexes record to History table and remove from Fragmented_Indexes
        begin tran

        insert into msdb..Fragmented_Indexes_History
                     (DBName, ObjectName,ObjectId, IndexName,
                     IndexId, Lvl, CountPages, CountRows,
                     MinRecSize, MaxRecSize, AvgRecSize,
                     ForRecCount, Extents, ExtentSwitches,
                     AvgFreeBytes, AvgPageDensity, ScanDensity,
                     BestCount, ActualCount, LogicalFrag, ExtentFrag, origfill,
                     start_time, end_time, newfill)
        select DBName, ObjectName,ObjectId, IndexName,
                     IndexId, Lvl, CountPages, CountRows,
                     MinRecSize, MaxRecSize, AvgRecSize,
                     ForRecCount, Extents, ExtentSwitches,
                     AvgFreeBytes, AvgPageDensity, ScanDensity,
                     BestCount, ActualCount, LogicalFrag, ExtentFrag, origfill,
                     @start_time, @end_time, @newfill
        from msdb.dbo.fragmented_indexes
        where  dbname     = rtrim(@dbname)
        and    objectname = rtrim(@tabname)
        and    indexname  = rtrim(@indexname)
 
        delete from msdb.dbo.Fragmented_Indexes
        where  dbname     = rtrim(@dbname)
        and    objectname = rtrim(@tabname)
        and    indexname  = rtrim(@indexname)

        set @count = @@rowcount

        if @count = 1
        begin
            print 'Row deleted from msdb..fragmented_indexes for index ' + rtrim(@indexname)
                    + ' on table ' + rtrim(@tabname) + ' on database ' + rtrim(@dbname)
            commit tran
        end
        else
            rollback tran

        fetch next from TARGETS into @dbname, @tabname, @indexname, @origfill
        /*ERRTRAP*/    select @err = @@ERROR if @err <> 0 return @err
    end
    
    -- Clean up TARGETS Cursor
    close TARGETS
    deallocate TARGETS

GO

This series of procs/jobs has evolved over time - things like configuration at an individual index level using the XConfig table was added
to give the flexibility to the DBA or programmers to maintain indices individually.
I hope this helps and you found it worthwhile.


Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close