Agent Jobs

Find Tables With Heavily Fragmented Indexes

Here’s quick way to find Tables that have heavily fragmented indexes. This is designed to find tables that are above 70% fragmentation. It’s a good idea to incorporate this into a regularly scheduled maintenance job, and just REBUILD those that are above 70% to 80% fragmentation. Anything below this point can simply be REORGANIZED.

select
'database_name' = db_name(sddips.database_id)
,	'table_name' = object_name(sddips.object_id)
,	'object type' = ist.table_type
,	'fragmentation' = left(sddips.avg_fragmentation_in_percent, 4) from
sys.dm_db_index_physical_stats (db_id(), null, null, null, null) sddips
join information_schema.TABLES ist on object_name(sddips.object_id) = ist.table_name where
ist.table_type = 'base table' and sddips.avg_fragmentation_in_percent > 70 order by
sddips.avg_fragmentation_in_percent desc;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s