T-SQL Query to find the index fragmentation of SQL Server for a particular table.
​
As we know , Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. Some times it becomes necessary for us to find the index fragmentaion level of a table in a SQL Server database without running the query for the entire databases as a whole which will consume huge amount of time and resources.
​
Here is a query to find out the fragmentation level of individual table.
​
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'DBName');
SET @object_id = OBJECT_ID(N'TableName');
IF @object_id IS NULL
BEGIN
PRINT N'Invalid object';
END
ELSE
BEGIN
SELECT dbindexes.NAME,IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'SAMPLED') AS IPS
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = IPS.[object_id]
AND IPS.index_id = dbindexes.index_id;
END
GO