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

book

Article ID: 180632

calendar_today

Updated On:

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;

Attachments

Database Fragmentation NS7_Report_v2.xml get_app
Altiris database fragmentation.xml get_app
Database Fragmentation NS7_Report_v2.xml get_app
Altiris database fragmentation.xml get_app