Find Table Fragmentation

[cc lang=”sql”]

DECLARE @Database VARCHAR(255)
DECLARE @TableName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)

SET @Database = ‘SQLServerPlanet’
SET @TableName = ‘Users’
SET @IndexName = NULL

SELECT
avg_fragmentation_in_percent
,page_count
FROM sys.dm_db_index_physical_stats
(
DB_ID(@Database)
,OBJECT_ID(@TableName)
,OBJECT_ID(@IndexName)
,NULL
,NULL
)

[/cc]

Or you can still do it the old fashioned way. Just substitute the name of the table or index below. It should not be in quotes.

[cc lang=”sql”][/cc]
–Just the table
DBCC SHOWCONTIG (tablename)

–Table with the index
DBCC SHOWCONTIG (tablename, indexname)

–Do not block anything (run during prod hours)
DBCC SHOWCONTIG (tablename, indexname) WITH FAST –(2005)
[cc lang=”sql”][/cc]

One comment
Faiz 17 Dec 2014 at 10:00 am

Modified the query for all tables (with table name) and with frag more than equal to 50 in descending order.

DECLARE @DATABASE VARCHAR(255)
DECLARE @TableName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)

SET @DATABASE = DB_NAME()
SET @TableName = NULL
SET @IndexName = NULL

SELECT t.name TableName
,sp.avg_fragmentation_in_percent
,sp.page_count
FROM sys.dm_db_index_physical_stats
(
DB_ID(@DATABASE)
,OBJECT_ID(@TableName)
,OBJECT_ID(@IndexName)
,NULL
,NULL
) sp
INNER JOIN sys.tables t ON sp.object_id = t.object_id
WHERE sp.avg_fragmentation_in_percent >= 50
ORDER BY avg_fragmentation_in_percent DESC

Featured Articles

 Site Author

  • Thanks for visiting!