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