Questions about the @DatabasesInParallel parameter for the Ola Hallengren Index and Statistics Maintenance scripts?
1
vote
1
answer
782
views
I've used Ola Hallengren's Maintenance Solution for a while, but I've never used the @DatabasesInParallel parameter. I am considering using this on the Index and Statistics Maintenance jobs where I have a single job that is running long (4+ hours) and have a couple of questions:
1. For setting this up, I assume the simplest method would be to add
@DatabasesInParallel='Y',
to my parameters list on my existing SQL Server agent job, then in SSMS perform a Script Job As > Create To... on that job so I'll have a script to create an exact copy and then just change the @job_name in the script to the new job name so that the new job gets created with the same parameters and uses the same schedule?
2. Can I set up 3 or more jobs to run in parallel, or is it limited to 2?
3. The server I want to set this up on has 5 user databases on it. If I set up parallel jobs for index maintenance on @databases='USER_DATABASES'
, can two of the jobs perform indexing on the same database simultaneously? For instance, Job 1 is working on a large database with a lot of heavily fragmented indexes, and Job 2 works through the other 4 databases fairly quickly, if Job 1 is still running, will Job 2 end, or will it start working on the database that Job 1 has already been working on?
Alternatively, I may set up separate jobs and instead of using @databases='USER_DATABASES'
on them I'll specify what user databases each should perform index maintenance on and not use @DatabasesInParallel
.
Asked by Wayne Cochran
(35 rep)
Nov 27, 2023, 03:56 PM
Last activity: Nov 29, 2023, 08:08 AM
Last activity: Nov 29, 2023, 08:08 AM