none
Update Stats and Rebuild Indexes RRS feed

  • Question

  • HI All,

    We are planning to standardize our newly deployed sql server, As a part of it we have configured 2 maintenance plans 1) Update Statistics which runs daily and 2) Index Reorganize which runs on weekly.

    Apart from above things please let us know if any other things to be in place for better maintenance of the sql server.

    Also please let me know Index Rebuild activity for clustered indexes requires any downtime.

    Thanks,

    Varun

    Monday, September 28, 2015 9:54 AM

Answers

  • 1. One small query, does sql server has any in-build  update stats feature which runs on regular basis?

    2. Also index rebuild/reorganize jobs  (Maintenance Plan) runs on fragmentation level or simply executes irrespective of frag. level?

    3. Also update stats job (Maintenance Plan) runs in full scan mode or normal mode please let me know..

    1. Yes it has this is called Auto update stats Also read BOL about it.

    2. rebuild/reorganize you can simpley execute them whatever is fragmentation level they will do what they are meant to do. ideally should only be executed when index is fragmented.

    3. By default update stats will run with full scan unless you use SAMPLE clause


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    • Edited by Shanky_621MVP Tuesday, September 29, 2015 7:03 AM
    • Marked as answer by Raje14 Tuesday, September 29, 2015 9:15 AM
    Tuesday, September 29, 2015 7:03 AM
  • @Shanky agreed, there was very little to go on in the question, so it was merely an alternative to a question even the most experienced DBA's ask themselves everyday, how can I get more out of my server!

    I hadn't actually spotted that I had removed the below 10% threshold on that script, so I will amend it on the blog...


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    • Marked as answer by Raje14 Tuesday, September 29, 2015 9:15 AM
    Monday, September 28, 2015 2:48 PM

All replies

  • There are lot many options available as an SQL SERVER maintenance plan. Refer the Maintenance Plan wizard.

    Regarding Rebuild Index online, I would recommend you to have downtime and rebuild it offline because online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server Database Engine will select the user or application activity as a deadlock victim.

    https://msdn.microsoft.com/en-us/library/ms190981(v=sql.110).aspx


    Regards, RSingh


    Monday, September 28, 2015 10:28 AM
  • HI All,

    We are planning to standardize our newly deployed sql server, As a part of it we have configured 2 maintenance plans 1) Update Statistics which runs daily and 2) Index Reorganize which runs on weekly.

    Apart from above things please let us know if any other things to be in place for better maintenance of the sql server.

    You should not use MP for rebuildinf indexes and updating stats it uses sledge hammer approach of doing it for all table sand indexes. Instead use a smart script or use Ola Hallengreen script

    If you rebuild index with full scan for particular index, you dont need to run update stats for that index

    Also please let me know Index Rebuild activity for clustered indexes requires any downtime.

    Online index rebuild DOES NOT requires any downtime. Online rebuild is there in Enterprise, Dev and Evaluation edtion of SQl Server. Hope you are using 2005 +.

    If you have standard edition that would require application to not access the database. This goes same for both clustered and non clustered index.

    
    

    
    

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Monday, September 28, 2015 10:35 AM
  • I have written a number of stored procedures that can handle this. Rebuild/Reorganise Indexes, which is a sub-article on custom maintenance plans, which covers backup and restore as well.

    Variations of the rebuild/reorganise procedure are dotted all over the internet.

    Hope this may give you some insight.


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    Monday, September 28, 2015 11:43 AM
  • I have written a number of stored procedures that can handle this. Rebuild/Reorganise Indexes, which is a sub-article on custom maintenance plans, which covers backup and restore as well.

    Variations of the rebuild/reorganise procedure are dotted all over the internet.

    Hope this may give you some insight.


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    And your query does same what I have pointed, you are rebuilding any index which has fragmentation >10 % which is incorrect. if fragmentation is between 10 to 30 reorganize is suggested. Plus you are not even looking at page count which is very much important to configure.

    In all This is same as what MP will do and I would not suggest this script.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Monday, September 28, 2015 12:59 PM
  • And your query does same what I have pointed, you are rebuilding any index which has fragmentation >10 % which is incorrect. if fragmentation is between 10 to 30 reorganize is suggested. Plus you are not even looking at page count which is very much important to configure.

    In all This is same as what MP will do and I would not suggest this script.

    @Shanky

    It does reorganise anything less than 30% (unfortunately it was required at the time!), and rebuild anything over that, although the stuff below 10% could be skipped in the majority of instances, but as I pointed out, there are variations of these scripts all over the place and the one by Ola is probably one of the best, however these scripts are still working perfectly for a solution I set up in 2010 although these databases are between 300 and 600 GB. It also may give the questioner a few other ideas he hadn't thought of.

    @Varun,

    Unfortunately these sorts of questions evoke a very opinionated response from people, myself included! Dependent on your database sizes and finances available to you there are also some pretty nifty plugins for SQL server around on the net.


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    Monday, September 28, 2015 2:10 PM
  • Gavin,

    Please consider my comments as suggesstions. Whenever I try to post on this forum I ususally stick to best practice as we have very little information about OP's arch. I always tend to stick to best practice and mostly give advises which are widely used and accepted. Most people post here as per there exp, in your case the script might work well. But for very big database it might cause log file bloating and unnecessary rebuild of *small* indexes.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Monday, September 28, 2015 2:36 PM
  • @Shanky agreed, there was very little to go on in the question, so it was merely an alternative to a question even the most experienced DBA's ask themselves everyday, how can I get more out of my server!

    I hadn't actually spotted that I had removed the below 10% threshold on that script, so I will amend it on the blog...


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    • Marked as answer by Raje14 Tuesday, September 29, 2015 9:15 AM
    Monday, September 28, 2015 2:48 PM
  • @Shanky agreed, there was very little to go on in the question, so it was merely an alternative to a question even the most experienced DBA's ask themselves everyday, how can I get more out of my server!

    I hadn't actually spotted that I had removed the below 10% threshold on that script, so I will amend it on the blog...


    Gavin Clayton Claytabase Ltd www.claytabase.co.uk

    HI gsclayton,

    One small query, does sql server has any in-build  update stats feature which runs on regular basis?

    Also index rebuild/reorganize jobs  (Maintenance Plan) runs on fragmentation level or simply executes irrespective of frag. level?

    Also update stats job (Maintenance Plan) runs in full scan mode or normal mode please let me know..

    Regards,

    Varun

    Tuesday, September 29, 2015 5:46 AM
  • 1. One small query, does sql server has any in-build  update stats feature which runs on regular basis?

    2. Also index rebuild/reorganize jobs  (Maintenance Plan) runs on fragmentation level or simply executes irrespective of frag. level?

    3. Also update stats job (Maintenance Plan) runs in full scan mode or normal mode please let me know..

    1. Yes it has this is called Auto update stats Also read BOL about it.

    2. rebuild/reorganize you can simpley execute them whatever is fragmentation level they will do what they are meant to do. ideally should only be executed when index is fragmented.

    3. By default update stats will run with full scan unless you use SAMPLE clause


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    • Edited by Shanky_621MVP Tuesday, September 29, 2015 7:03 AM
    • Marked as answer by Raje14 Tuesday, September 29, 2015 9:15 AM
    Tuesday, September 29, 2015 7:03 AM