Tags: Database Index , MSSQL

Fragmentation
Fragmentation occurs when you perform any INSERTION, UPDATION or DELETION operation against table. Over the time this operation cause to data become scattered in database. Heavily fragmented indexes can degrade query performance and cause your application to respond very slowly.

Note: I'm using sample database(AdventureWorks2008R2) from Microsoft. Change database name and table name where required.

Finding Fragmentation On Table-Index
Execute following query to determine fragmentation on particular table of database.

SELECT ind.name, phy.avg_fragmentation_in_percent, phy.fragment_count, phy.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2008R2'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS phy
JOIN sys.indexes AS ind
ON phy.object_id = ind.object_id
AND phy.index_id = ind.index_id

As you can see in Image that index 'AK_Employee_LoginID' is Heavily fragmented. It will lead to lower performance of your database.

Column	Description
avg_fragmentation_in_percent	The percent of logical fragmentation (out-of-order pages in the index).
fragment_count	The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pages	Average number of pages in one fragment in an index.

Source: http://technet.microsoft.com/

REORGANIZE Index or REBUILD Index?

avg_fragmentation_in_percent value	Operation
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

What is Index Reorganize?
Database will Reorganize data pages on an index. Reorganizing an index is always executed with online. It means when you perform Reorganize operation on an Index, Database will keep the old index value to serve the incoming queries. When its completed, it'll drop the old index data.

What is Index Rebuild?
Database will drop the current index and create an index from scratch. It comes with two option online and offline. As I said online option will keep the data of old index to serve incoming queries until it completes the rebuild operation. Offline option will drop the old index data right away and create index again, index won't be available until it completes the rebuild operation.

To reorganize an index

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee
REORGANIZE;

To reorganize all indexes in a table

ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE;

To rebuild an index

ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee
REBUILD;

To rebuild all indexes in a table

ALTER INDEX ALL ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

Warning: This is part of database maintenance so please be carefully while you are dealing with live database server. I'd suggest perform this task under maintenance hours.

Tags: Database Index , MSSQL