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

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 @db_id INT;
DECLARE @object_id INT;
SET @db_id = db_id(N'Database_Name');
SET @object_id = OBJECT_ID(N'Table_Name');
BEGIN
   SELECT *
   FROM sys.dm_db_index_physical_stats(@db_Id,@object_ID, NULL, NULL , 'DETAILED') AS IPS;
END
GO

Sample:

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
       , i.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.

Follow @sqlfrndz