Check Fragmentation on All Indexes on Database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Augusto Alfonso » Check Fragmentation on All Indexes on...

Check Fragmentation on All Indexes on Database

Rate Topic

Display Mode

Topic Options

Author

Message

augusto.alfonso

Posted Thursday, July 10, 2008 12:30 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 21, 2013 9:37 AM
Points: 12, Visits: 128

Comments posted to this topic are about the item Check Fragmentation on All Indexes on Database

Post #531873

peymaster-660178

Posted Thursday, September 04, 2008 5:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:31 PM
Points: 6, Visits: 87

it doesn't work

Post #563693

badot

Posted Thursday, September 04, 2008 8:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:27 AM
Points: 2, Visits: 187

YEs It doesn't work

problem with object id and SAMPLE from proc !

Post #563881

Scott Grant

Posted Thursday, June 04, 2009 2:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:44 PM
Points: 42, Visits: 251

so what updates did you make to get the script to work then?

Post #729274

Scott Grant

Posted Thursday, June 04, 2009 2:48 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:44 PM
Points: 42, Visits: 251

so what updates did you make to get the script to work then? I can get the inidividual pieces to run, but cannot get the full script to produce resutls. I keep getting and invalid ' ' error message.

Post #729276

Scott Grant

Posted Thursday, June 04, 2009 3:11 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 12:44 PM
Points: 42, Visits: 251

I have found that there are invisible characters from the original copy and paste from the original posting. I copied the script into Notepad and removed the "Box Characters" representing the invisible characters and then pasted the cleaned up version into query analyzer and it runs.

Post #729298

aby_dvd

Posted Thursday, July 07, 2011 2:14 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 11, 2012 3:55 PM
Points: 86, Visits: 206

@scott : +1 --

But, i am not seeing any results though commands have been completed sucessfully .

PS.- i have many indexes for this database

Post #1137871

augusto.alfonso

Posted Thursday, July 07, 2011 9:22 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 21, 2013 9:37 AM
Points: 12, Visits: 128

Hi, It's proc works fine, because I sill use in production.
SELECT sysobj.name object_name,
sysobj.xtype object_type,
indexes.name index_name,
index_data.database_id,
index_data.object_id,
index_data.index_id,
index_data.avg_fragmentation_in_percent,
index_data.avg_fragment_size_in_pages,
index_data.avg_page_space_used_in_percent,
index_data.record_count
FROM sys.dm_db_index_physical_stats ('db_id', NULL,NULL, NULL,'SAMPLED') index_data
-- replace the table name by NULL
inner join sys.sysobjects sysobj
on index_data.object_id = sysobj.id
left outer join sys.sysindexes indexes
on index_data.index_id = indexes.indid
and index_data.object_id = indexes.id
WHERE (avg_fragmentation_in_percent > 10
OR avg_page_space_used_in_percent < 90)
-- avg_fragmentation_in_percent / logical scan fragmentation < 10%
-- avg_fragment_size_in_page/ Extent Scan Fragmentation
-- avg_page_space_used_in_percent / Avg. Page Density > 90 %

Post #1138225

Eric Willemstein

Posted Wednesday, July 27, 2011 9:41 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 07, 2013 7:43 PM
Points: 446, Visits: 144

This works, remember to get your database id first. Good tool to help identify fragmented indexes.
see http://msdn.microsoft.com/en-us/library/ms186274.aspx

Post #1149357

« Prev Topic | Next Topic »

Permissions