November 3, 2009 at 12:24 pm
I'm using the rebuild/reorganize script located here (http://www.sqlservercentral.com/scripts/Rebuild+Index/65933/, thanks Farhan) for nightly maintenance but am confused. I've run this script and it repeatedly gives me the same fragmentation stats for the table. I know this is my ignorance of the relationship between fill factor and fragmentation and rebuilding vs reorganizing, can someone explain it in relatively easy terms? Too open ended to discuss?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 3, 2009 at 12:37 pm
If an index is below a certain size, reorganizing it won't get rid of fragmentation, because the fragmentation really doesn't matter. Is that what you're running into?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 2:29 pm
Yeah, I think so. But it was done with a REBUILD...again, I'm fuzzy on the fragmentation understanding:
Day 1:
-- Processing STANDARD table dbo.FOCUSRequests, STANDARD index [Name], -- partition 1, avg frag in percent 53.8462, avg page space used in percent 88.8731 -- Executed: ALTER INDEX [Name] ON [optix_bak].[dbo].[FOCUSRequests] REBUILD WITH (FILLFACTOR = 90, MAXDOP = 0) ; -- Results: avg frag in percent 53.8462, avg page space used in percent 88.8731
Day 2:
-- Processing STANDARD table dbo.FOCUSRequests, STANDARD index [Name], -- partition 1, avg frag in percent 53.8462, avg page space used in percent 88.8731 -- Executed: ALTER INDEX [Name] ON [optix_bak].[dbo].[FOCUSRequests] REBUILD WITH (FILLFACTOR = 90, MAXDOP = 0) ; -- Results: avg frag in percent 53.8462, avg page space used in percent 88.8731
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 3, 2009 at 3:16 pm
Yeah, I'm a jughead, just realized my FILLFACTOR is 90 and the space used is only 88%.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 4, 2009 at 8:57 am
Back to this...any ideas why the fragmentation remained at +50% when I did a rebuild on the index?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 4, 2009 at 9:01 am
How big is the index?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2009 at 2:20 pm
Thanks for your replies, G. Dunno if this is what you're asking:
avg_frag_in_percent - 53.84615385
fragment_count - 8
avg_frag_size_in_pages - 1.625
page_count - 13
avg_page_space_used_in_percent - 88.87305411
record_count - 1559
I couldn't find a size in sys.indexes, hoping it's record_count.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 5, 2009 at 6:53 am
Page count is the one that matters. 13 pages is WAY too small for fragmentation to matter, so SQL is pretty much ignoring you and just doing what it wants to on that point. If I'm not mistaken, the cutoff is 1,000 pages.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2009 at 11:06 am
"nightly maintenance" sounds a bit on the too frequent side to me, unless there's an absolute need.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply