Fragmentation report query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Fragmentation report query

Author

Topic

ss1011
Starting Member

8 Posts

Posted - 2009-01-21 : 12:57:20

Hi,

I'm an "involuntary dba" and would like to get some feedback on the following query that I wrote:

use mydb

SELECT so.name as 'Table Name' , si.name as 'Index Name', dm.avg_fragmentation_in_percent as 'Average Fragmentation In %'
FROM sys.dm_db_index_physical_stats(DB_ID('mydb'), NULL, NULL, NULL , NULL) dm,
sys.sysindexes si,
sys.sysobjects so
where si.id = dm.object_id
and dm.index_id = si.indid
and so.id=si.id
and dm.avg_fragmentation_in_percent > 50
order by object_id, index_id, avg_fragmentation_in_percent;

I think this is giving me what I want which is a list of avg fragmentation > 50 from a particular db (I want the index and the table name to which the index belongs to).

Your thoughts?

Many thanks.

Sid.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-21 : 13:04:12

Use Standard Reports within database to get detailed information instead.