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: 
