Index Defragmentation (2005, 2008, 2012)

  • Comments posted to this topic are about the item Index Defragmentation (2005, 2008, 2012)

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • Monday's SSC email features another script on index defragmentation (certainly a worthy topic for community understanding and action), so now we have several contenders [my pref order!]

    http://ola.hallengren.com

    http://sqlfool.com/2011/06/index-defrag-script-v4-1

    http://www.sqlservercentral.com/scripts/Fragmentation/86879/

    http://blogs.msdn.com/b/blogdoezequiel/archive/2011/07/03/adaptive-index-defrag.aspx

    http://msdn.microsoft.com/en-us/library/ms189858.aspx

    http://msdn.microsoft.com/en-us/library/ms186878.aspx

    I suggest that it would be good to have an objective ranking of these alternatives, to guide SSC community on what/why/how

    As humorous aside, whilst researching MSDN/BOL topics I found this

    http://connect.microsoft.com/SQLServer/feedback/details/321628/closing-view-tsql-dialog-early-for-maintenance-plan-reindex-task-throws-incorrect-error

    showing MS took 3 years and 2 months to decide not to fix the reported bug. No wonder SQL2011 has become SQL2012 !

  • Gregory, nice script. But I have some suggestions:

    1. When you rebuild an index then statistics are updated with FULL SCAN, so it's not necessary and even harmful to run sp_updatestats afterwards since it will update stats with some sample rate.

    2. I'm not sure about best practise but instead of rebuild/reorganize all indexes and then update all statistics, I would prefer to do it table by table. It makes more sense in terms of performance. For the server it's easy to do an update stats while some of the indexes are still cached rather than reading everything from disk again after rebuilding all the indexes for the DB.


    Alex Suprun

  • sp_updatestats only updates statistics that requires updating. hence, the ones that were rebuilt will be skipped.

  • richard mascarenhas (1/7/2012)


    sp_updatestats only updates statistics that requires updating. hence, the ones that were rebuilt will be skipped.

    If you run rebuild and update stats when nobody works with DB then it will be like you said, but if you have 24/7 system who will guarantee that nobody update at least one record between rebuild and sp_updatestats operations? And if such update happens then sp_updatestats will update your statistics with sample rate because based on rowmodctr that statistics requires updating. You can test it yourself:

    CREATE DATABASE UpdateStatsTest

    GO

    USE UpdateStatsTest

    CREATE TABLE dbo.T (ID int NOT NULL)

    ALTER TABLE dbo.T ADD CONSTRAINT PK_T PRIMARY KEY CLUSTERED (ID )

    INSERT dbo.T(ID) VALUES(1)

    GO

    ALTER INDEX [PK_T] ON [dbo].[T] REBUILD

    INSERT dbo.T(ID) VALUES(2) -- Insert one record between rebuild and updatestats

    EXEC sp_updatestats

    Here is result, stats has been updated:

    Updating [dbo].[T]

    [PK_T] has been updated...

    1 index(es)/statistic(s) have been updated, 0 did not require update.


    Alex Suprun

  • Very good point. Hadn't thought about that.

  • Nicely Done!

  • I'm confused, somehow I have version:

    Version 1.9

    Removed option for all database in simple recovery model

    Listed out all 80 (or earlier) compatibility databases

    15 Aug 2011

    Yet, this is a December version of 1.4?

    The reason I'm writing is, I have several databases that use row level locking. When I try the stored proc I get on some indexes: Number: 2552 [SQLSTATE 01000]

    --*****Error: The index "PK__ICMUT013__6CFA4C671550F9CF" (partition 1) on table "ICMUT01331001" cannot be reorganized because page level locking is disabled. [SQLSTATE 01000]

    --*****Object Name: [authjcrdb].[authjcrsch].[ICMUT01331001] [SQLSTATE 01000]

    --*****Error Code: alter index [PK__ICMUT013__6CFA4C671550F9CF] on [authjcrdb].[authjcrsch].[ICMUT01331001] reorganize [SQLSTATE 01000]

    I'm looking for the ability or changing to set to page level locking for the reindex and then go back to row level.

  • Also, what does:

    "NB:

    @Fill_Factor, @PAD_Index will only be applied to index that are rebuilt (Fragmentation >= @ReBuildTheshold)

    ***There is a possible issue with database names containing GUID's***"

    What's the possible issue? You mean if you use a GUId in the name there may be problems/

  • Sailor (3/12/2012)


    Also, what does:

    "NB:

    @Fill_Factor, @PAD_Index will only be applied to index that are rebuilt (Fragmentation >= @ReBuildTheshold)

    ***There is a possible issue with database names containing GUID's***"

    What's the possible issue? You mean if you use a GUId in the name there may be problems/

    For whatever reason, I have found that the sharepoint databases, which by default contain, GUID's always cause the SP to exit ungracefully. I wish I knew what caused the problem, but I have not yet found the issue.

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • Thanks for the script.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply