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
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:
[Table] = object_name(stats.[object_id])
,[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;