During the implementation of Ola Hallengren’s SQL Server Maintenance Solution, we had a requirement to be able to set different parameters at different times to be able to fit the work in the available maintenance window.
For example during the index maintenance part, we would set higher fragmentation thresholds during weekdays than during the weekend, since we have less time available.
Similarly, when running integrity checks, we use @PHYSICAL_ONLY=’Y’ during weekday maintenance windows, and @PHYSICAL_ONLY=’N’ during weekend maintenance windows.
The maintenace windows are not strictly aligned to the day boundaries: the weekend maintenance interval starts Friday at 20:00, and ends Sunday at 20:00. Jobs running at that time are allowed to finish, but the jobs starting after Sun 20:00 will receive the ‘weekday’ parameters.
To meet the requirements and to not add too much complexity, we use the number of minutes since the start of the week to determine the interval boundaries, so the check for which interval we’re in, is reduced to a simple datetime calculation.
In our particular case, Friday 20:00 to Sunday 20:00 means between 6960 and 9840
- Select the integer number of the minute at ‘2015-01-09 20:00:00.000’ (this Friday) after the start of that day’s week:
SELECT DATEDIFF(MINUTE, dateadd(week, datediff(week, 0,’2015-01-09 20:00:00.000′), 0),’2015-01-09 20:00:00.000′ )
- Select the integer number of the current minute after the start of the current week:
SELECT DATEDIFF(MINUTE, dateadd(week, datediff(week, 0,GETDATE()), 0),GETDATE() )
How we use it (simplified):
DECLARE @Dynamic_FragmentationLevel1 int DECLARE @Dynamic_FragmentationLevel2 int SELECT @Dynamic_FragmentationLevel1 = CASE WHEN DATEDIFF(MINUTE, dateadd(week, datediff(week, 0,GETDATE()), 0),GETDATE() ) BETWEEN 6960 AND 9840 THEN 5 ELSE 30 END, @Dynamic_FragmentationLevel2 = CASE WHEN DATEDIFF(MINUTE, dateadd(week, datediff(week, 0,GETDATE()), 0),GETDATE() ) BETWEEN 6960 AND 9840 THEN 30 ELSE 50 END EXECUTE DBA_Tools.dbo.[IndexOptimize] @Databases = 'All_DATABASES' ,@FragmentationLevel1 = @Dynamic_FragmentationLevel1 ,@FragmentationLevel2 = @Dynamic_FragmentationLevel2 ,@LogToTable = 'Y'
Reference table of some values:
Mon BETWEEN 0 AND 1439
Tue BETWEEN 1440 AND 2879
Wed BETWEEN 2880 AND 4319
Thu BETWEEN 4320 AND 5759
Fri BETWEEN 5760 AND 7199
Sat BETWEEN 7200 AND 8639
Sun BETWEEN 8640 AND 10079
friday 20:00 to sunday 20:00 – BETWEEN 6960 AND 9840