Defrag All Indexes On a Database

,

I recently had the need to look up all fragmented indexes on a specific database, to determine if they were the reason for less than optimal performance. I wrote the following script, which allows you to pick a specific database to check for fragmented indexes, and decide to just view them or reorganize/rebuild them, and also the fragmentation threshold percentage at which to run these operations. Then I thought it might be useful to others too, so here it is.

Usage involves setting the following five parameters (technically, variables) at the top of the script:
@DATABASE_NAME: set to whatever database you want to check for fragmented indexes. This must be on the same server that you are running the script on.
@REBUILD_THRESHOLD_PERCENT: the fragmentation percent above which you want to rebuild indexes.
@REORGANIZE_THRESHOLD_PERCENT: the fragmentation percent above which you want to reorganize indexes.
@EXECUTE_REBUILD: set to 0 to only view fragmented indexes. Set to 1 to actually execute the rebuild. Note that this goes hand in hand with the variable @REBUILD_THRESHOLD_PERCENT
@EXECUTE_REORGANIZE: set to 0 to only view fragmented indexes. Set to 1 to actually execute the reorganization. Just like the rebuild threshold percent variable above, this acts in sync with the variable @REORGANIZE_THRESHOLD_PERCENT
The included screenshot shows a sample run of this script against the AdventureWorks2012 database, with the top 10 records returned by the script. If the execute variables were set to 1, these results would not be immediately repeatable because the fragmented indexes would get rebuilt / reorganized after these results were displayed.
/****************************************************************************************************
Hakim Ali (hakim.ali@sqlzen.com) 2012/05/04
****************************************************************************************************/



-----------------------------------------------------------------------------------------------------
------------------------------------------------------- SET DESIRED VALUES HERE ---------------------
-----------------------------------------------------------------------------------------------------
declare @Database_Name nvarchar(100);                    set @DATABASE_NAME = 'AdventureWorks2012'
declare @Rebuild_Threshold_Percent nvarchar(10);        set @REBUILD_THRESHOLD_PERCENT = 30
declare @Reorganize_Threshold_Percent nvarchar(10);        set @REORGANIZE_THRESHOLD_PERCENT = 30
declare @Execute_Rebuild bit;                            set @EXECUTE_REBUILD = 0        -- set to 1 to rebuild
declare @Execute_Reorganize bit;                        set @EXECUTE_REORGANIZE = 0        -- set to 1 to reorganize. If rebuilding, no need to do this.
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------



----------------------------------
-- Initial checking
----------------------------------
declare @Error nvarchar(500)
declare @SQL nvarchar(max)
declare @Online nvarchar(50)

set @Database_Name = ltrim(rtrim(isnull(@Database_Name,'')))

set @Error = 'Database name required.'
if (@Database_Name = '')
begin
    raiserror(@Error,16,1)
    goto the_end
end

set @Error = 'Database "' + @Database_Name + '" does not exist.'
if not exists (select name from sys.databases where name = @Database_Name)
begin
    raiserror(@Error,16,1)
    goto the_end
end

set @Online = ''
if (@@version like '%enterprise edition%')
begin
    set @Online = ' with (online = on)'
end



set @SQL = '
----------------------------------
-- Create table to hold results
----------------------------------
declare @fragmented_indexes table
(            ID                            int identity(1,1)
            ,Database_Name                nvarchar(1000)
            ,[Schema_Name]                nvarchar(1000)
            ,Table_Name                    nvarchar(1000)
            ,Index_Name                    nvarchar(1000)
            ,Fragmentation_Percent        money
            ,Num_Rows                    int
            ,Page_Count                    int
            ,Index_Type                    nvarchar(1000)
            ,Reorganize_SQL                nvarchar(4000)
            ,Rebuild_SQL                nvarchar(4000)
)



----------------------------------
-- Populate table
----------------------------------
insert into @fragmented_indexes
(            Database_Name
            ,[Schema_Name]
            ,Table_Name
            ,Index_Name
            ,Fragmentation_Percent
            ,Num_Rows
            ,Page_Count
            ,Index_Type
)
select        distinct
            Database_Name
                = db_name(database_id)
            ,[Schema_Name]
                = sch.name
            ,Table_Name
                = parent.Name
            ,Index_Name
                = indx.name
            ,Fragmentation_Percent
                = left(isnull(phys_stats.avg_fragmentation_in_percent,0),5)
            ,Num_Rows
                = x.rowcnt
            ,Page_Count
                = phys_stats.page_count
            ,Index_Type
                = phys_stats.index_type_desc
from        sys.dm_db_index_physical_stats( 
                db_id('''+@Database_Name+'''),
                default,
                default,
                default,
                ''detailed''
            ) phys_stats
inner join    ['+@Database_Name+'].sys.indexes indx
            on indx.object_id = phys_stats.object_id
            and indx.index_id = phys_stats.index_id 
inner join    ['+@Database_Name+'].sys.objects parent
            on parent.object_id = phys_stats.object_id
inner join    ['+@Database_Name+'].dbo.sysindexes x
            on x.id = indx.object_id
inner join    ['+@Database_Name+'].sys.schemas sch
            on sch.schema_id = parent.schema_id
where        1 = 1
and            isnull(indx.name,'''') <> ''''
and            x.rowcnt > 0
and            (phys_stats.avg_fragmentation_in_percent >= '+@Rebuild_Threshold_Percent+'
            or
            phys_stats.avg_fragmentation_in_percent >= '+@Reorganize_Threshold_Percent+')



update        @fragmented_indexes
set            Reorganize_SQL =
                ''alter index [''+Index_Name+''] on [''+Database_Name+''].[''+[Schema_Name]+''].[''+Table_Name+''] reorganize''
            ,Rebuild_SQL =
                ''alter index [''+Index_Name+''] on [''+Database_Name+''].[''+[Schema_Name]+''].[''+Table_Name+''] rebuild'+@Online+'''



----------------------------------
-- View results
----------------------------------
select        ID
            ,Database_Name
            ,[Schema_Name]
            ,Table_Name
            ,Index_Name
            ,Fragmentation_Percent
            ,Num_Rows
            ,Page_Count
            ,Index_Type
from        @fragmented_indexes
order by    Database_Name
            ,convert(money,Fragmentation_Percent) desc
            ,[Schema_Name]
            ,Table_Name
'

----------------------------------
-- If rebuild/reorganize option set...
----------------------------------
if (@Execute_Rebuild = 1)
begin
    set @SQL = @SQL + '
    declare @current_sql nvarchar(max)
    while exists (select top 1 Rebuild_SQL from @fragmented_indexes)
    begin
        set @current_sql = (select top 1 Rebuild_SQL from @fragmented_indexes)
        execute sp_executesql @current_sql
        --select @current_sql
        delete @fragmented_indexes where Rebuild_SQL = @current_sql
    end
    '
end
else if (@Execute_Reorganize = 1)
begin
    set @SQL = @SQL + '
    declare @current_sql nvarchar(max)
    while exists (select top 1 Reorganize_SQL from @fragmented_indexes)
    begin
        set @current_sql = (select top 1 Reorganize_SQL from @fragmented_indexes)
        execute sp_executesql @current_sql
        --select @current_sql
        delete @fragmented_indexes where Reorganize_SQL = @current_sql
    end
    '
end



----------------------------------
-- Go!
----------------------------------
execute sp_executesql @SQL
the_end:

Rate

4.14 (7)

Share

Share

Rate

4.14 (7)