Identifying Index Fragmentation on SQL Server

The first thing you are doing when identifying poor query performances is to take a look at execution plan and indexes usage. Even that you see, everything is fine, your index can be fragmented so that SQL Server needs to make unnecessary data reads to execute query. At the end, your query is slow.
At the moment when index is created, little or no fragmentation is present. During the time, when updates, inserts and deletes occur indexes get fragmented what is a real bottleneck in a SQL Server performances.

There are two ways how to fix fragmented indexes: reorganizing or rebuilding them. Which operation is necessary depends on the level of fragmentation. Reorganization of index is suggested if fragmentation level is less than 30%. If it is more than 30% than rebuilding index is better choice.

Identifying fragmented indexes

Starting from version 2005, SQL Server contains a number of DMVs and DMFs qhich allow us to retrieve informations about SQL Server health and performances and identifying problems. One of them, allow us to take a look at the index fragmentation level - sys.dm_db_index_physical_stats DMF. It is important to said here that it places intent shared lock (IS) on the affected tables during execution.

sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)

The following query will get fragmentation of all indexes in the database

DECLARE @dbId int

SET @dbId = db_id('YOUR_DB_NAME')

SELECT s.[name] AS SchemaName, t.[name] AS TableName, i.[name] AS IndexName, p.[index_type_desc], p.[avg_fragmentation_in_percent]
FROM [sys].[dm_db_index_physical_stats](@dbId, NULL, NULL, NULL , 'DETAILED') p
INNER JOIN [sys].[tables] t  ON p.[object_id] = t.[object_id]
INNER JOIN [sys].[schemas] s  ON t.[schema_id] = s.[schema_id]
INNER JOIN [sys].[indexes] i  ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id
WHERE t.[is_ms_shipped] = 0

To get more proper candidates for rebuilding or reorganizing indexes it is necessary to consult other fields returned back from [sys].[dm_db_index_physical_stats] like avg_page_space_used_in_percent which indicates on average how full each page in index is. The higher number is better while but it is necessary here to balance fullness against the
number of inserts into index pages in order to keep the number of page splits to the absolute minimum. This exceeds the topic of this blog and requires adjustments of index fillfactor and monitoring of page splits.