Maintenance plan - Reorganize data and index pages

  • What is the syntax for running "Reorganize data and index pages" in QA.

    Is it DBCC DBREINDEX(Eachtable)?

    Anything else?

    Is there a way that I can run DBREINDEX for each table in a database without having to specify tables individually.

    Reason for the question is that I am getting the following error message when the nightly Maintenance Plan rebuilds the database indexes:

    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.

    When I uncheck "Reorganize data and index pages" the maintenance plan runs successfully.

    I believe that I need to set up a new job something like following:

    SET ARITHABORT OFF

    SET QUOTED_IDENTIFIER OFF

    DBCC DBREINDEX(Eachtable)

    I am also wondering whether it would be better to update statistics on each table every night and just rebuild indexes once a week?

  • See, if this helps:

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

    Have you already searched the script section here? I bet you'll find a whole bunch of maintenance scripts there.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Frank, thanks for the link - will certainly help my understanding.

    Also looked through scripts and found two which seemed to apply:

    DBCC Reindex all tables and CreateDBReindex (see links below). The former took 10:39 and the latter 6:57 this compared to the complete optimisation job which only took about 2 minutes to run.

    Do these scripts do a similar thing to "Reorganize data and index pages" the maintenance plan?

    I guess that currently the duration is not too much of an issue because the database is currently only being used in Europe and therefore any performance impact overnight is not an issue. In this case I guess that I might as well set up a job using one of these scripts to reindex every night.

    The scripts I tried:

    http://www.sqlservercentral.com/scripts/contributions/139.asp

    http://www.sqlservercentral.com/scripts/contributions/935.asp

  • No one has really answered the question: What does "Reorganize data and index pages" do under the hood (DB Maint Plan, Optimizations tab - first checkbox)?  Is it just running DBCC DbReindex?  We've been running this optimization, but it doesn't take very long, and I'm wondering if it's really running DBCC DBREINDEX on all tables for all databses.

    Thanks,

    Matthew

  • I know this is way after the fact but I have also been looking for more info about what is going on under the hood in the maintenance plans.

    I just wanted to throw out the following site that has a pretty good explanation of what is happening in the maintenance plan wizard.

    http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp

    Good luck!

    hawg

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Viewing 5 posts - 1 through 4 (of 4 total)

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