SQL: How to Identify Fragmented Indexes

(To be modified later...)



SELECT OBJECT_NAME(OBJECT_ID) as tableName,index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), Object_Id(‘Emp’), Default, Default,Default)

avg_fragmentation_in_percent :-This column value represents the %age of logical fragmentation of indexes or extent fragmentation of heap.

avg_page_space_used_in_percent:-This column represents the internal fragmentation within the page.


If avg_fragmentation_in_percent(%age) >5 and avg_fragmentation_in_percent <30 % then we should re-organize the indexes to minimize the fragmentation.


--------------------------------------------------

Script to re-organize index:
alter index xx on emp reorganize

To reorganize particular index(“xx”) on Emp table
alter index all on emp reorganize

To reorganize all the indexes on emp table.

If avg_fragmentation_in_percent(%age) >30 then we should re-build the indexes to minimize the fragmentation.

To rebuild all the indexes on emp table.
alter index all on emp rebuild
To rebuild particular index(“xx”) on Emp table
alter index xx on emp rebuild


 ------------------------------

SELECT index_id,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
DB_ID(‘AdventureWorks’),
OBJECT_ID(‘AdventureWorks’),
NULL, NULL, ‘DETAILED’
)


-------------------------------------


SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

------------------------------

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_countFROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')ORDER BY avg_fragmentation_in_percent DESC


Reducing Fragmentation in a Heap: To reduce the fragmentation of a heap, create a clustered index on the table. Creating the clustered index, rearrange the records in an order, and then place the pages contiguously on disk.
Reducing Fragmentation in an Index: There are three choices for reducing fragmentation, and we can choose one according to the percentage of fragmentation:
If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER INDEXREORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. As this is an online operation, the index is available while the statement is running.
If avg_fragmentation_in_percent > 30%, then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to rebuild the index online or offline. In such case, we can also use the drop and re-create index method.


-----------------------------------------


DECLARE @DATABASE VARCHAR(255)
DECLARE @TableName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
SET @DATABASE = 'SQLServerPlanet'
SET @TableName = 'Users'
SET @IndexName = NULL

SELECT
    avg_fragmentation_in_percent
    ,page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(@DATABASE)
    ,OBJECT_ID(@TableName)
    ,OBJECT_ID(@IndexName)
    ,NULL
    ,NULL
)

----------------------------------


Script recently used in checking for fragmented indexes/heaps:


SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
 FROM sys.dm_db_index_physical_stats (DB_ID('winips'), NULL, NULL, NULL, NULL) AS indexstats
 INNER JOIN winips.sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
 INNER JOIN winips.sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
 INNER JOIN winips.sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
 AND indexstats.index_id = dbindexes.index_id
 WHERE indexstats.database_id = DB_ID('winips')
 ORDER BY indexstats.avg_fragmentation_in_percent desc