Pages

Monday, May 14, 2007

SQLServer 2005: DMV's and determiniing fragmentation

Dynamic Management Views (which are not all views, some are stored procedures / functions) are very useful and if you're a DBA working with SQLServer, you should know them inside and out, and have you're list of scripts ready at hand.

Here's a couple that work well for determinining fragmentaion issues:

*Note: Though these can be done on line, the due use CPU and heavy READ I/O - be sure to know your application and how that affects performance, and carfully track performance while they are running the first few times. (Use the DMV options to control how much I/O is done - in the example we use the 'LIMITED' option)


SELECT substring (b.name,1,40) + '.' +
substring (c.name,1,50) AS Object,
a.avg_fragmentation_in_percent AS fragmentation_pct
FROM sys.dm_db_index_physical_stats
(
db_id ('{DBNAME}'),
object_id ('{TABLE/OBJECT NAME}'),
NULL,
NULL,
'LIMITED'
) a
JOIN sysobjects b
ON a.object_id = b.id
LEFT OUTER JOIN sysindexes c
ON a.object_id = c.id
AND a.index_id = c.indid
Post a Comment