Index and Statistics Maintenance Scheduling Questions
0
votes
0
answers
75
views
I'm working on setting up a maintenance plans for some SQL Server instances. I'm trying to find a good/safe staring point to start the maintenance plan on and I can adjust and make appropriate changes based on the results. I'm using Ola Hallengren's maintenance solution to create the maintenance plans and watched Brent Ozar's/Erik Darling's setting up Ola Hallengren's maintenance solution video among others.
My idea so far has been to set up an index maintenance job and a separate statistics update job. I wanted to start off more conservatively by running the index maintenance job monthly and the statistics job weekly. Here is what I have setup for each job:
Index (Runs monthly):
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE,INDEX_REORGANIZE',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@MinNumberOfPages = 5000,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'
Statistics (Runs weekly):
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y'
I'm using Standard edition and have maintenance windows through the weekend and evenings.
My questions are:
1. As a starting point with auto update statistics on, should I even create the weekly statistics job? Would auto update statistics and index maintenance with statistics maintenance once a month be a better starting point until I know for sure that some databases need more statistics updates?
2. If I should proceed with both the jobs, the index job runs on the first Friday of the month and statistics run on each Saturday. Should I flip the days around (statistics friday and index saturday), so the index job doesn't update statistics and get overwritten a day later with statistics updates? Ideally, I would like to do index optimize that first weekend and do statistics for the rest of the weekends when index isn't running but I'm not finding a way to schedule that with SSMS.
Asked by Solizmi
(1 rep)
Aug 30, 2024, 06:06 PM