sys.dm_db_index_physical_stats to Rebuild Index or Reorganize Index on SQL Server Database

Using sys.dm_db_index_physical_stats dynamic management function to rebuild index or reorganize index on a SQL Server database can easily be managed with a scheduled task by database administrators. Index maintenance in SQL Server is one of important steps in database maintenance plans and important to increase the performance of sql queries running on database tables and views. A SQL Server database administrator should periodically take action on index maintenance by using reorganize index or rebuild index commands. To decide either to reorganize an index or to rebuild indes, database administrator must be following index fragmentation on indexes created on a database table.

In this SQL Server tutorial, using T-SQL development skills I'll show how to SELECT SQL Server indexes that require maintenance (either rebuild index or reorganize index) from sys.dm_db_index_physical_stats system dynamic management function. The tutorial will also demonstrate how to execute index maintenance commands dynamically with filtered table or view indexes after rebuild/reorganize index task selection is made.

I believe, SQL Server database administrators will execute index maintenance script periodically using with an scheduled SQL database job to keep their database performance at top.

sys.dm_db_index_physical_stats System Table Function

SQL Server provides sys.dm_db_index_physical_stats system dynamic management function which returns a list of indexes created on SQL Server instance with enough information for a database admin to decide if the sql index is fragmented or not. One of the most important return field with sys.dm_db_index_physical_stats function is avg_fragmentation_in_percent field. Also page_count shows the size of the data pages used for the related sql index.

As best practise, you should reorganize an SQL index if the avg_fragmentation_in_percent is bigger than 5%. On the other hand, if avg_fragmentation_in_percent is bigger than 30% for a SQL index, instead of running Alter Index Reorganize command you should rebuild index with Alter Index Rebuild command.

The following SQL Select statements query from sys.dm_db_index_physical_stats and sys.indexes system objects return list of indexes to maintain.


select

 object_schema_name(ps.object_id) as ObjectSchema,

 object_name (ps.object_id) as ObjectName,

 ps.object_id ObjectId,

 i.name as IndexName,

 ps.avg_fragmentation_in_percent,

 ps.page_count

from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps

inner join sys.indexes i

 on i.object_id = ps.object_id and

  i.index_id = ps.index_id

where

 avg_fragmentation_in_percent > 5 -- reorganize and rebuild

 and ps.index_id > 0

order by avg_fragmentation_in_percent desc

Code

Above SQL script has INNER JOIN between sys.dm_db_index_physical_stats and sys.indexes system view and function. Using this Inner Join, we can fetch the name of the sql index that requires a maintenance task with its fragmentation percent bigger than 5%.

index maintenance due to index fragmentation from sys.dm_db_index_physical_stats and sys.indexes

An important note for database administrators who are using sys.dm_db_index_physical_stats system table function with its @DatabaseId parameter equal to NULL value. Running with NULL value at @DatabaseId input parameter will cause the sys.dm_db_index_physical_stats system function to run for all SQL indexes defined on all databases on that SQL Server instance. If you have tens and hundreds of databases created on that SQL Server instance, the missing parameter value for database id will cause the above SQL Select statement on sys.dm_db_index_physical_stats slow to execute and last for a long time.

Here is the parameters that can be used to filter the return results of sys.dm_db_index_physical_stats table function:
@DatabaseId, @ObjectId, @IndexId, @PartitionNumber and @Mode

Although in this SQL tutorial, we have used the Mode parameter with NULL which is equal to LIMITED value, all valid scanning mode parameter values can be listed as follows:
DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED

In our SQL Server tutorial, since I target to create a database maintenance plan over a specific SQL database, I passed DB_ID() value for @DatabaseId parameter of sys.dm_db_index_physical_stats function to limit the indexes only with ones created on current database.

Please refer to SQL Server Books on Line (BOL) for more detail about sys.dm_db_index_physical_stats dynamic management function.

Cursor for Fragmented Indexes on SQL Server Database

Let's now create an SQL Server cursor to run ALTER INDEX command for each view or table index one by one.

Within each item in fragmenterd indexes cursor, we execute ALTER INDEX REBUILD command with ONLINE = ON option. According to the SQL Server version and edition, Online index rebuild function may not be allowed. In such a case, you should ommit "WITH (ONLINE = ON)" index rebuild option.


DECLARE

 @dbname sysname = db_name(),

 @schema_name sysname,

 @object_id int,

 @object_name sysname,

 @index_name sysname,

 @avg_fragmentation float,

 @sql nvarchar(max)



DECLARE indexFragmentation CURSOR FAST_FORWARD FOR

SELECT

 object_schema_name(ps.object_id) as ObjectSchema,

 object_name (ps.object_id) as ObjectName,

 ps.object_id ObjectId,

 i.name as IndexName,

 ps.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps

inner join sys.indexes i

 on i.object_id = ps.object_id and

  i.index_id = ps.index_id

WHERE

 avg_fragmentation_in_percent > 5 -- reorganize and rebuild

 and ps.index_id > 0

ORDER BY avg_fragmentation_in_percent DESC




OPEN indexFragmentation

FETCH NEXT FROM indexFragmentation INTO @schema_name, @object_name, @object_id, @index_name, @avg_fragmentation



WHILE @@FETCH_STATUS = 0

BEGIN



 IF @avg_fragmentation > 30 -- ReBuild index

  SELECT @sql = 'ALTER INDEX [' + @index_name +'] ON [' + @schema_name + '].[' + @object_name + '] REBUILD WITH (ONLINE = ON)';



 ELSE -- ReOrganize index

  SELECT @sql = 'ALTER INDEX [' + @index_name +'] ON [' + @schema_name + '].[' + @object_name + '] REORGANIZE';



--	PRINT @sql;

 EXEC (@sql);



 FETCH NEXT FROM indexFragmentation INTO @schema_name, @object_name, @object_id, @index_name, @avg_fragmentation



END

CLOSE indexFragmentation

DEALLOCATE indexFragmentation

Code

After you execute above SQL script, you will see that top 10 fragmented index list has changed. In some cases, although you have executed ALTER INDEX command, SQL Server engine decides that to reorganize or rebuild an index is not feasible and will not change the index structure because of the size of the index is not too big or it is not used heavily.

Index Maintenance Stored Procedure for Database Administrators

SQL Server database administrators can package the above SQL script into a stored procedure with database parameter in Master database. This index maintenance stored procedure can be used in every database maintenance plan with the database id value is used in place of DB_ID() value in sys.dm_db_index_physical_stats system function.

Please download SQL Server Index Maintenance stored procedure create script to create the SQL procedure on master database. You can later call this spKodyaz_Index_Maintenance stored procedure with desired database id parameter to perform ALTER INDEX Reorganize or ALTER INDEX Rebuild commands.