How can I get a report that shows me the database fragmentation on a SQL 2005 server?

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
How can I get a report that shows me the database fragmentation on a SQL 2005 server?

This article include both an Altiris 6 and an Altiris 7 report, as well as the base SQL code, on from these reports were created

Answer
Import the attached report. The report is to run on SQL 2005 server. This report shows the table name, index name, and page count.

The base SQL query is:

select
 [Table] = object_name(stats.[object_id])
,[Index]= si.[name]
,[Avg Fragmentation] = cast(stats.[avg_fragmentation_in_percent] as numeric (6,2))
,[Page Count] = stats.[page_count]
,[Index Type] = [index_type_desc]
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats
join sys.indexes as si on stats.object_id = si.object_id
 and stats.[index_id] = si.[index_id]
where stats.[avg_fragmentation_in_percent] > 10.0
  and si.[index_id] > 0
  -- and stats.[page_count] >= 50
order by [Avg Fragmentation] desc,[Page Count] desc;