Sample Header Ad - 728x90

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