locked
Rebuild Indexes RRS feed

  • Question

  • How can I schedule tasks for rebuilding indexes every week? And what are the precausions should I take for that?
    Friday, February 3, 2012 6:46 AM

Answers

  • Hello Ronit,

    You can create a maintenance plan for rebuilding indexes.

    Just goto Management folder, then open Maintenance Plans in Object Explorer window when you are connected to the target server instance.

    If you use the Maintenance plan wizard it will be easier for you. Choose Rebuild index task and your database for this task.


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials
    • Marked as answer by Ronit Roy Friday, February 3, 2012 6:09 PM
    Friday, February 3, 2012 6:51 AM
  • With a query you have more flexibility since you can customize it. Maintenance plan has checkmark selection for options. Otherwise, both methods use the same underlying indexing software.

    If a table is in index REBUILD, users maybe blocked for the duration like 5 minutes for a large table; web application usually times out. Unless ONLINE option is used (Enterprise Edition required). Generally, you want to do index REBUILD when nothing else is running and no users if this is an option.

    Related optimization article:

    http://www.sqlusa.com/articles/query-optimization/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES


    • Proposed as answer by Mr. Wharty Friday, February 3, 2012 9:02 AM
    • Edited by Kalman Toth Friday, February 3, 2012 1:33 PM
    • Marked as answer by Ronit Roy Friday, February 3, 2012 6:08 PM
    Friday, February 3, 2012 8:49 AM

All replies

  • Hello Ronit,

    You can create a maintenance plan for rebuilding indexes.

    Just goto Management folder, then open Maintenance Plans in Object Explorer window when you are connected to the target server instance.

    If you use the Maintenance plan wizard it will be easier for you. Choose Rebuild index task and your database for this task.


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials
    • Marked as answer by Ronit Roy Friday, February 3, 2012 6:09 PM
    Friday, February 3, 2012 6:51 AM
  • You can build a database maintenance plan with the wizard (SSMS Object Explorer) and create an execution schedule.

    You can also create a job with the indexing script in SQL Server Agent.

    Indexing article:

    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

    Generally, it is better to do it off hours with no users. Regular indexing will block table access. ONLINE indexing option available in Enterprise Edition.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    • Proposed as answer by Mr. Wharty Friday, February 3, 2012 9:02 AM
    Friday, February 3, 2012 6:51 AM
  • what are the consequencies of rebuilding indexes on other users and other maintenance plans like back up etc.? And what is the best way to follow, to use maintenance plan or to use Query for rebuilding indexes based on fragmentation?
    Friday, February 3, 2012 7:43 AM
  • With a query you have more flexibility since you can customize it. Maintenance plan has checkmark selection for options. Otherwise, both methods use the same underlying indexing software.

    If a table is in index REBUILD, users maybe blocked for the duration like 5 minutes for a large table; web application usually times out. Unless ONLINE option is used (Enterprise Edition required). Generally, you want to do index REBUILD when nothing else is running and no users if this is an option.

    Related optimization article:

    http://www.sqlusa.com/articles/query-optimization/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES


    • Proposed as answer by Mr. Wharty Friday, February 3, 2012 9:02 AM
    • Edited by Kalman Toth Friday, February 3, 2012 1:33 PM
    • Marked as answer by Ronit Roy Friday, February 3, 2012 6:08 PM
    Friday, February 3, 2012 8:49 AM
  • Hi,

    My client wants to implement scheduled rebuilding of indexes. But, my concern is that the tables are very huge with 30 million rows and would the server sustain that much of workload because we are bounded with the transaction logs. We left with 20 GB on transaction logs. And if it can sustain then I believe it will take very long and all the other processes will get blocked due to the rebuilding process of the indexes. Can you give me suggestions on this matter and if we cannot implement it then what can be the alternative of rebuilding the indexes. All the indexes has avg of 50% fragmentation.

    Saturday, February 4, 2012 9:38 PM
  • Which SQL Server Edition is it?

    I suggest you get more disk space.

    Alternative to index REBUILD is updating statistics every night. See article for reference: http://www.sqlusa.com/articles/query-optimization/ .

    Sooner or later you need to REBUILD though preferably with FILLFACTOR for dynamic tables.

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Saturday, February 4, 2012 10:13 PM
  • 30 millions rows are necessarily not "very huge". It depends on how wide the rows are. What does sp_spaceused return for these tables?

    The transaction log can be a problem. One possibility is to set the database to bulk_recovery while the reindexing job is running, and then flip back to full recovery when its down. That reduces the toll on the log considerably.

    Another important question is the nature of the business. Is this a 24/7 database, or is it nine-to-five weekdays?

    Reindexing will indeed block other processes, but if the database is idle in the wee hours of night, this is no concern. It also possible to make an "online" rebuild if you are on Enterprise Edition, that does not block other users.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 4, 2012 10:33 PM
  • Database has total of 885 indexes out of which 224 are clustered, 300 Heap and 361 nonclustered. I also know there are so many unneccessary indexes. Manager does not want to remove those indexes. So, I am also considering them into the rebuilding of indexes. I am trying go ahead with stored procedure which rebuild or reorganize the indexes according to the fragmentation level so, we do not work on those indexes which are less fragmented and thus i can reduce little bit load on the server. I just want to know what fillfactor is ideal? If I will keep it below 100 it will take more space but, does it impove performance?

    Tuesday, February 7, 2012 5:06 AM
  • Fill factor is related with the nature of your table data.

    For example for clustered indexes with insert time, 100% fill factor will work fine

    But for a index on customer surname, it may be good to use 70% - or 80% fill factor.

    The best percentage can only be determined by observing different fillfactors and performance relation

    And for unnecessary indexes, they bring overhead to the system performance. It is best to remove them if they're not used


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Tuesday, February 7, 2012 6:23 AM