Optimization job

  • When my optimization job runs with following script:EXECUTE master.dbo.xp_sqlmaint N'-PlanID 05C26610-E745-4EF0-9788-D7CCB8A02939 -Rpt "E:\MSSQL\LOG\SAVVIS Full Recovery Model DB Maintenance Plan0.txt" -WriteHistory -RebldIdx 10 '

    All of my websides,databases gets locked up.Is there script manually that I can run, so it would be no locks on the tables.

    Thank you

  • The SQL 2000 optimization maintenance plan executes DBCC DBREINDEX which will rebuild the index and cause table locks. Use DBCC INDEXDEFRAG instead. BOL can give you the pro's and con's for each method. Generally the indexdefrag will not be as thorough of a job as the rebuild. You can run the indexdefrag during the week and rebuild on the weekends. Better yet, if you have time, create a script to track index fragmentation levels and run the indexdefrag/rebuild just on the indexes that are heavily fragmented.

    Look up DBCC SHOWCONTIG in BOL and there is a good starting script for defraging your indexes.

    - David

  • SQL 2000 or SQL 2005? What edition?

    Go to the maintenance plan and have a look at what it does and then describe what the plan is doing. The command that you pasted says very little about the details of the maint plan.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It is 2000 edition, it locks all of the tables in the database.Reorginizes data and index pages.Change persantage to 10 of free space per page.

  • Krasavita (8/24/2009)


    It is 2000 edition

    Then why is it posted in a sQL 2005 forum?

    it locks all of the tables in the database.Reorginizes data and index pages.Change persantage to 10 of free space per page.

    Index rebuilds are offline in SQL 2000. You can remove the maint plan and add a manual script that only rebuilds the indexes that need it, does rebuilds on the more fragmented indexes and reorgs on others. Lots of scripts that do that here, also check Michelle's website. That should reduce the impact.

    Only way to get fully online index rebuilds is to upgrade to SQL 2005 enterprise.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sorry, I posted here

    Just to understand clearlerly.

    1.The only way in 2000 to run optimization job:is offline?

    2.If I remove the maint plan and add a manual script that only rebuilds the indexes that need it,

    it is still have to be offline right?

    3. How to know on which needs to be rebuild done and if rebuilds on the more fragmented indexes and reorgs on others?

    4.If I upgrade to SQL 2005 enterprise, is it still have to be offline?

    Thank you

  • Krasavita (8/24/2009)


    1.The only way in 2000 to run optimization job:is offline?

    I can't recall what settings the 2000 maint plan uses (if it uses) to decide index reorg vs index rebuild. Rebuilds are offline, reorgs are online. Usually slightly fragmented indexes are reorganised, badly fragmented ones are rebuilt

    2.If I remove the maint plan and add a manual script that only rebuilds the indexes that need it,

    it is still have to be offline right?

    Yes, but you'll only be rebuilding indexes that need it?

    3. How to know on which needs to be rebuild done and if rebuilds on the more fragmented indexes and reorgs on others?

    Did you check the excellent index mainentance script on Michelle's website?

    4.If I upgrade to SQL 2005 enterprise, is it still have to be offline?

    2005 and higher enterprise edition allow online index rebuilds

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • DBCC INDEXDEFRAG is an online process for SQL Server 2000, although it does not actually rebuild the indexes. Instead it tries to defrag the leaf level of the indexes which will help index scans. An index rebuild is better but as Gail mentioned, you can not rebuild and index online in SQL Server 2000.

    Thers is no option in the maintenenace plan to choose between an INDEXDEFRAG or DBREINDEX so your only option is to create scripts. You can run the DBCC INDEXDEFRAG during the week to help improvement and then run the rebuild during a maitenence window where you can lock the tables... if you have one.

    Review DBCC SHOWCONTIG in BOL to get more information on determining fragementation levels in SQL Server 2000.

    - David

Viewing 8 posts - 1 through 7 (of 7 total)

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