Sample Header Ad - 728x90

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