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.
Like this:
Like Loading...
Related
Pingback: How To Unlock A Locked Table In Sql Server 2005 | Information