Check SQL Server fragmentation – rebuild/reorganize


If you have performance issues on your database, one of the first things you should check is the fragmentation.  If the fragmentation is high, SQL Server has the possibility to either reorganize or rebuild indexes. You can detect index fragmentation by using the Dynamic Management View (DMV) sys.dm_db_index_physical_stats and check the avg_fragmentation_in_percent column.

Use [MyDB];
SELECT a.index_id, name, avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
   JOIN sys.indexes AS b ON a.object_id = b.object_id 
AND a.index_id = b.index_id
ORDER BY avg_fragmentation_in_percent DESC;

If the value avg_fragmentation_in_percent between 5% and 30% you should perform a reorganize of your indexes, not a total rebuild. A rebuild you only ne invoked if the fragmentation percentage is greater than 30%. No action should be taken if the fragmentation percentage is lower tha 5% which is a normal level of fragmentation.

The following command will rebuild all indexes With default fill factor :

Use [MyDB]; 
EXEC sp_MSforeachtable 
  @command1="print '?'", 
  @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"
Advertisements

One thought on “Check SQL Server fragmentation – rebuild/reorganize

  1. Pingback: Never set “AutoShrink = true” | Sveroa's Developer Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s