2012/05/12

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 (below).

I am the original developer of this script. I offer it freely to anybody to use, just leave my name in the header where it is.

Thanks,
Hakim Ali


/****************************************************************************************************
Hakim Ali (hakim.ali@sqlzen.com) 2012/05/04
****************************************************************************************************/



-----------------------------------------------------------------------------------------------------
------------------------------------------------------- SET DESIRED VALUES HERE ---------------------
-----------------------------------------------------------------------------------------------------
declare @Database_Name nvarchar(100);                    set @DATABASE_NAME = ''
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
            ,[Schema_Name]
            ,convert(money,Fragmentation_Percent) desc
            ,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:


No comments:

Post a Comment