Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check Fragmentation on All Indexes on Database Expand / Collapse
Author
Message
Posted Thursday, July 10, 2008 12:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, September 04, 2008 5:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 7:31 PM
Points: 6, Visits: 87
it doesn't work
Post #563693
Posted Thursday, September 04, 2008 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 5:44 AM
Points: 2, Visits: 204
YEs It doesn't work

problem with object id and SAMPLE from proc !
Post #563881
Posted Thursday, June 04, 2009 2:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:57 PM
Points: 43, Visits: 307
so what updates did you make to get the script to work then?
Post #729274
Posted Thursday, June 04, 2009 2:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:57 PM
Points: 43, Visits: 307
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
Posted Thursday, June 04, 2009 3:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:57 PM
Points: 43, Visits: 307
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
Posted Thursday, July 07, 2011 2:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, July 07, 2011 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, July 27, 2011 9:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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 »

Add to briefcase

Permissions Expand / Collapse