Questions about the @DatabasesInParallel parameter for the Ola Hallengren Index and Statistics Maintenance scripts?

Question

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.

Answer 1

Solution

If you want to perform Ola jobs in parallel, then you have to watch out for a couple of things. It's not very clear in the documentation.

1. You need to download two additional scripts:
   • Queue.sql (Ola's Github)
   • QueueDatabase.sql (Ola's Github)
2. Run these scripts to create two additional tables in the same database where you created the base objects for Ola's solution. (Default: master, but you did use msdb, right?)
3. You have to copy the existing optimizing job (as you already noticed).
4. You have to pass the parameter @DatabasesInParallel to each job step that you wish to execute in parallel in both jobs.

   You can process databases in parallel by creating multiple jobs with the same parameters, and add the parameter @DatabasesInParallel = 'Y'.

You're done.

However, the above solution does not account for huge databases and small databases. So you might think that ...

... in order to split the jobs between a huge database and a couple of small ones, then you could pass the additional parameter @Databases in each job step and add the relevant database name(s).

However, this would defeat the purpose of having multiple parallel jobs just working through a list of databases.

Instead Solution

1. Don't create the additional tables.
2. Copy the job you want to perform against a huge database and a couple of small databases.
3. Add the parameter @Databases in both job steps and ...
4. ... assign the huge database in one job like this:
   @Databases='HugeDatabase'
5. ... assign the other databases in the other job like this:
   @Databases='USER_DATABASES,-HugeDatabase'

You are essentially telling one job to perform the task against the huge database and the other job to perform the tasks against all other user databases, except for the HugeDatabase.

Answering Your Questions

Sorry, I missed answering your actual questions.

1. ...simple solution...

The simple solution was explained above.

2. Can I set up 3 or more jobs to run in parallel, or is it limited to 2?

I have never done this myself. I only ever built one additional job for parallel processing. However, you should be able to create multiple jobs as stated in the documentation:

You can process databases in parallel by creating multiple jobs with the same parameters, and add the parameter @DatabasesInParallel = 'Y'.

The additional tables that have to be created, which I mentioned in my initial answer at the top, handle how the jobs select an available database. A job will look into the table and select the first available database and mark it as being processed. The next job will look at the same table and take the next free database, that isn't already being processed, mark it as being processed, and so on.

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?

No. One job will take on one database and fully process that database. (see description in my answer to your second question)