DBA and SysAdmin World

December 6, 2008

Index Fragmentation in SQL 2005

Filed under: SQL Server — Tags: , , — solihinho @ 11:40 PM
fragment Last Tuesday, our email administrators had a problem. One of their 3rd tools became slow from one day to another day. After they contacted the vendor, the vendor asked to check the database which is using SQL 2005. Because our email administrators do not have knowledge about database, it’s time for DBA take an action Cool.

Money Eyes Some companies think that they don’t need a DBA. These days almost all windows applications using SQL Server as their database. Unfortunately almost of these applications do not provide a task for maintenance their own database. My company has many 3rd party applications that using SQL Server. If there is a performance issue, the vendor always ask the customer to check the database and the box health. So as DBA is possible that we have to maintenance an application of another section like email, citrix, proxy, web application, sharepoint, K2 workflow, Business Object etc besides our in house database application .

After this issue came up, I ask the email admin to granted me as sysadmin role to their database which is located on their box. When I checked the index fragmentation, some of indexes were very fragmented.

Detecting Index Fragmentation
In versions prior to SQL Server 2005, for displaying fragmentation information for the data and indexes of specified table or view using DBCC SHOWCONTIG command. We still can use this command for SQL Server 2005 with some limitations but this feature will be removed in the next version according to MSDN. For the replacement, SQL Server 2005 and next version provide dynamic management function to display information about index fragmentation is sys.dm_db_index_physical_stats. This function need parameters such as database id, table id, index id, partition number and the mode. For further information just visit the MSDN or see SQL Server book online. There are many enhancement from this function than DBCC SHOWCONTIG command such as handle index partition information which is a new feature on SQL Server 2005.

The most important information from this function is column avg_fragmentation_in_percent. The range value from 0 to 100. 100 means the index is fragmented and DBA should reorganize or rebuild the index.

Here is an example how to display the index fragmentation for active database. From function sys.dm_index_physical_stats join to table sys.objects to get the table name, join to sys.indexes to get the index name and current fill factor setting and join to sys.partitions to get the approximate number of rows in the index partition.

  db_name() AS DatabaseName
, B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent, A.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B ON A.object_id = B.object_id
INNER JOIN sys.indexes C ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D ON B.object_id = D.object_id AND A.index_id = D.index_id

Reorganize OR Rebuild Index
In SQL Server 2000, we use DBCC INDEXDEFRAG to reorganize index and DBCC DBREINDEX to rebuild index. This feature is still available on SQL Server 2005 but this feature will be removed in future version of Microsoft SQL Server. In SQL 2005 and next version, these commands will be replaced with ALTER INDEX command. For reorganize the index use REORGANIZE option and for rebuild the index use REBUILD option with ALTER INDEX command.

Rebuild :
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;

Reorganize :

ALTER INDEX [IndexName] ON [SchemaName].[TableName] REORGANIZE;

If you have reorganized or rebuilt the index, you can check again avg_fragmentation_in_percent and the page_count value will reduce.
Reorganize index uses minimal system resources and other processes such as update, insert and delete on this table won’t be blocked.  But if you rebuild the index, other processes on this table won’t be proceeded until the rebuild process is finished.
Rebuild the index is better rather than reorganize the index. But if it is hard to find the right time to rebuild the index especially the huge table so just reorganize the index. It is much better rather doing nothing Batting Eyelashes.

Online Rebuild Index
When working on SQL Server 2000, as our production server must online 24×7, it is little difficult to do rebuild index on big table because during this process other processes will not be able to do DML activities on this table. This is a major hindrance of rebuilding index.

SQL Server 2005 introduces the ability for DBA to do online rebuild index so other processes can access the table when the rebuild is occurring. This feature is really helpful so we can rebuild our index any time. But of course it is better to do it on off peak hours Winking. When doing online rebuild index, just make sure that you have enough disk space on your box because during this process, actually SQL Server create a new index that mimics of current index. After this processing is finished, the old index will be dropped and replace with the new one.

Coffee Online rebuild index is available only on SQL Server 2005 Enterprise Edition.
Online Rebuild Index :
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD WITH (ONLINE = ON);

Best Practice
When collecting index fragmentation status, it is recommended to keep the data in table. Add timestamp column to this table. Just schedule this task to do it either weekly or monthly.

IdeaJust modified above query for detecting index fragmentation. You can make it loop to all database and keep the result on DBA database. We have a database called SQLDBAdb to keep all information that we need to keep and review.

From this data we can review how fast the indexes become fragmented so we can adjust the fill factor of the index. (See fill factor information from BOL for further information). To see current setting fill factor of the index, we can query from table sys.indexes. To change fill factor value of the index, use ALTER INDEX with FILLFACTOR option.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: