Script to find the fragmentation level of specific table and its indexes in SQL Server

Posted by

This script is very useful when you have large tables and queries against those table usually runs slow then you can find if that is due to fragmentation of the table and its indexes or not. This script gives detailed information about the fragmentation in percentage.

DECLARE @object_id INT;
SET @db_id = db_id(N'Database_Name');
SET @object_id = OBJECT_ID(N'Table_Name');
   FROM sys.dm_db_index_physical_stats(@db_Id,@object_ID, NULL, NULL , 'DETAILED') AS IPS;



What is IN_ROW_DATA?

The allocation of data in a row is of three types

Allocation unit type Is used to manage
IN_ROW_DATA Data or index rows that contain all data, except large object (LOB) data.Pages are of type Data or Index.
LOB_DATA Large object data stored in one or more of these data types: text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), or CLR user-defined types (CLR UDT).Pages are of type Text/Image.
ROW_OVERFLOW_DATA Variable length data stored in varchar, nvarchar, varbinary, or sql_variant columns that exceed the 8,060 byte row size limit.Pages are of type Text/Image.

Here is beautiful script that gives the how much of data in you table is allocated into what type of allocation.

SELECT OBJECT_NAME(p.object_id) AS Object_Name
       , AS Index_Name
       , ps.in_row_used_page_count AS IN_ROW_DATA
       , ps.row_overflow_used_page_count AS ROW_OVERFLOW_DATA
       , ps.lob_used_page_count AS LOB_DATA
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
WHERE OBJECTPROPERTY (p.[object_id], 'IsUserTable') = 1
order by IN_ROW_DATA desc,LOB_DATA

By running the above query you can know whether that table is used for Image or text data.



One comment

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s