rebuild/reorganize script

  • 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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • 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

  • 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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • 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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • 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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • 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

  • 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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • 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

  • "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