Find Table Fragmentation
-
Posted on May 31, 2009 by Derek Dieter
-
1
[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]
- Comments (RSS)
- Trackback
- Permalink
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
