locked
sql Maintenance Jobs RRS feed

  • Question

  • Hi 

    I am using SQL 2012 Instance , in which i have 7 DB's in total which is application specific. The size of first 3 DBs is less than 10 GB where in the rest is around 250 GB each . 

    I now have to configure the Maintenance jobs , where i need you help on deciding on schedules . 

    Rebuilt index job : should configure 1st or Update statistics 

    To the size of the DB's mentioned above , can we schedule it daily or 2 times a week or weekly once

    Update statistics : should we configure this job first or after executing the rebuilt index job we need to execute this job

    schedules , do we need to fix it daily or weekly or 2 times a week 

    Backup jobs , any ways is not been configured as there is a separate tool which will take care of this  activity. 

    do we need to configure any other jobs for better performance apart from the mentioned two above.

    please explain . 


    hemadri

    Thursday, November 15, 2018 6:17 AM

All replies

  • Rebuilt index job : should configure 1st or Update statistics 

    Hello,

    An index rebuild updates also all statistic related to that indizes, so it's not quite necessary to update stats as well.

    Most DBA don't rely on the Standard maintenenace plan, they are not efficent, they other Solutions like the script from Ola Hellengren:

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
    https://ola.hallengren.com/sql-server-backup.html

    In common Index rebuild/reorg once per week is enough


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 15, 2018 6:27 AM
  • Thank you . 

    so can i conclude that if we can schedule the rebuilt index job , its not require to set the update statistics jobs please?

    Any other jobs to be included for better maintenance.


    hemadri

    Thursday, November 15, 2018 6:35 AM
  • its not require to set the update statistics jobs please?


    It's not required to update all stats, only the not index-related & outdated stats; use Ola's script for this.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 15, 2018 7:06 AM
  • Thank you . 

    so can i conclude that if we can schedule the rebuilt index job , its not require to set the update statistics jobs please?

    Any other jobs to be included for better maintenance.


    hemadri

    You need to understand that an index rebuild of an index will ONLY update statistics of that index if a column is not part of that index its statistics will not be updated. For that you have to manually update stats this functionality has been taken care in Ola Hallengren script which identifies rebuild indexes and does not update stats for that index.

    There is a exception though if you are using SQL Server 2012 and above and have partitioned table.  If a table is partitioned, ALTER INDEX REBUILD will only update statistics for that index with default sampling rate.  In other words, it is no longer a FULLSCAN.  This is documented in http://technet.microsoft.com/en-us/library/ms188388.aspx.  If you want fullscan, you will need to run UPDATE STATISTCS WITH FULLSCAN.   This change was made because we started to support large number of partitions up to 15000 by default.  Previous versions did support 15000 partitions.  But it’s not on by default.  Supporting large number of partitions will cause high memory consumption if we track the stats with old behavior (FULLSCAN).  With partitioned table, ALTER INDEX REBUILD actually first rebuilds index and then do a sample scan to update stats in order to reduce memory consumption.

    See This Blog for more details


    Cheers,

    Shashank

    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 Articles

    MVP

    Thursday, November 15, 2018 7:07 AM