locked
Optimizing tables RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I use to optimize tables in my db using 2 instructions:

    execute sp_msforeachtable ''alter index all on ? reorganize''
    exec sp_updatestats

    Can you please tell me if there are other ways/commands to use against my tables to optimize them ?

    Thanks.

    • Moved by Tom Phillips Friday, September 10, 2010 4:09 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Friday, September 10, 2010 7:29 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    If you want to reorganize/rebuild your indexes, there are better ways to do this. Please look at the scripts created by Ola Hallengren, they are widely used (http://ola.hallengren.com)

    Tom

    Tom Van Zele | Blog | Twitter | LinkedIn
    • Proposed as answer by Naomi N Sunday, September 12, 2010 3:36 AM
    • Marked as answer by Kalman Toth Wednesday, September 15, 2010 1:26 PM
    Friday, September 10, 2010 7:35 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can do a rebuild of youre indexes as well, that will insure that both data and statisics are alligned and up-to-date.

    But this should be driven by a need and not something that you just do.

    Fragmentation identification is a bit different depending on the version of SQL Server you are running.

    This article is a good start and from what you have written i would say you are at least running SQL 2005 so the DMVs should be the one you should use.

    http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx 

    Btw, dont you have maintinance plans setup to do these kind of things automaticaly?

    If not have a look at this guys work, its great!

    http://ola.hallengren.com/

     

    - Raoul

    • Marked as answer by Kalman Toth Wednesday, September 15, 2010 1:27 PM
    Friday, September 10, 2010 7:43 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    If you want to reorganize/rebuild your indexes, there are better ways to do this. Please look at the scripts created by Ola Hallengren, they are widely used (http://ola.hallengren.com)

    Tom

    Tom Van Zele | Blog | Twitter | LinkedIn
    • Proposed as answer by Naomi N Sunday, September 12, 2010 3:36 AM
    • Marked as answer by Kalman Toth Wednesday, September 15, 2010 1:26 PM
    Friday, September 10, 2010 7:35 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can do a rebuild of youre indexes as well, that will insure that both data and statisics are alligned and up-to-date.

    But this should be driven by a need and not something that you just do.

    Fragmentation identification is a bit different depending on the version of SQL Server you are running.

    This article is a good start and from what you have written i would say you are at least running SQL 2005 so the DMVs should be the one you should use.

    http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx 

    Btw, dont you have maintinance plans setup to do these kind of things automaticaly?

    If not have a look at this guys work, its great!

    http://ola.hallengren.com/

     

    - Raoul

    • Marked as answer by Kalman Toth Wednesday, September 15, 2010 1:27 PM
    Friday, September 10, 2010 7:43 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I will for sure take a look, thanks.

    However I think it is a bigger solution than I need.

    Regarding tables without LOB datatype ... are there other consideration to do other than rebuild indexes and update statistics ?

    Thanks !

    Friday, September 10, 2010 7:43 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Raoul,

    I have sql agent jobs that do all the work for me, of course :)

    I see your point "should be driven by a need" ... but what should be at least one negative effect to rebuild indexes and update statistics to ALL objects in my instance ? Probably backup size should be one... but it is not my case.

    Thanks.

    Friday, September 10, 2010 8:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    execute sp_msforeachtable ''alter index all on ? reorganize''
    exec sp_updatestats


    Generally, you should REBUILD (not reorg!) indexes during weekend maintenance window.

    For dynamic tables, consider using FILLFACTOR 70-90. That will provide space for fast growing during the week.

    Related index REBUILD script: http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

    Let us know if helpful.

     

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, September 15, 2010 1:31 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Unfortunately we don't have Enterprise version, so I can't rebuild so often.

    I have a script that check for defrag % and if it is > 30% I rebuild the index.

    Thanks.

    Thursday, September 16, 2010 7:24 AM