It is wide known that creating index on table column can speed up queries that has this column in it's Where clause. Table indexes are binary trees in most cases and they are stored in pages similar to stored data itself. Over time data changes which cause index changes and it require some sort of maintenance to keep database optimized and running as fast as possible.
Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.*
This can be fixed by either rebuilding index (by dropping existing and create new one) or reorganize it (or defrag it).
Rebuild indexes
Rebuilding indexes can be done by either one of these
I won't cover the details as you can look them up on MSDN. Just my favorite way is like following:
ALTER INDEX ALL ON Person.Address REBUILD WITH (ONLINE=ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF);
The advantage is this operation is online - meaning you can query table during index rebuild.
Reorganizing indexes
Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.
Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space.*
There are two ways to perform index reorganization:
Again my preferable is this:
ALTER INDEX ALL ON Person.Address REORGANIZE;
This is also online operation.
Note: Although both operation stated above should be online I've applied it on big tables (above 140M records on ~60 GB in two tables) and of course it was pretty I/O intensive which caused some delays in performed queries. Having in mind that default CommandTimeout in .NET Class Library is 30 seconds and application writing at least once per minute creates very challenging DB to maintain. Possible solution would be using MS SQL Server 2008 Resource Governor. Unfortunately the server was MS SQL 2005...
How to detect fragmentation
In order to apply techniques described above fragmentation should be detected. For this comes a new DMV (Dynamic Management View) sys.dm_db_index_physical_stats - that gives us fragmentation in percent (avg_fragmentation_in_percent).
These are the recommendations depending on returned value in avg_fragmentation_in_percent column:
|
avg_fragmentation_in_percent value |
Corrective statement |
|
> 5% and < = 30% |
ALTER INDEX REORGANIZE |
|
> 30% |
ALTER INDEX REBUILD WITH (ONLINE = ON)* |
Using sys.dm_db_index_physical_stats could not be very useful when used by itself so I prefer using it together with system tables sys.tables and sys.indexes:
---=== get index fragmentation
SELECT a.index_id, t.name as TableName, i.name as IndexName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) AS a
INNER JOIN sys.indexes AS i ON a.object_id = i.object_id AND a.index_id = i.index_id
join sys.tables t on t.object_id=i.object_id
ORDER BY avg_fragmentation_in_percent DESC
The result is like this (executed in AdventureWorks):
sys.dm_db_index_physical_stats can take time to execute so if you want to view all indexes with the table name this can be used:
select t.object_id, t.name as TableName, i.name as IndexName, i.type_desc as IndexType
from sys.indexes i
join sys.tables t on t.object_id=i.object_id
where i.object_id >1000
order by t.create_date asc
Which return following result:
And it can be used to generate detailed T-SQL queries for reorganizing indexes one at time:
select 'ALTER INDEX ' + i.name + ' ON ' + t.name + ' REORGANIZE;'
from sys.indexes i
join sys.tables t on t.object_id=i.object_id
where i.object_id >100
order by t.create_date desc
producing
Summary
So far we took a look at following
- Detect index fragmentation
- Rebuild indexes
- Reorganize indexes
- Use T-SQL to generate T-SQL to maintain indexes.
I hope this helps.
* quoted from MSDN article Reorganizing and Rebuilding Indexes.