Sql-server – Schedule suggestion and index rebuild / database integrity progress for Ola Hallengren’s Maintenance Script

jobsola-hallengrensql server

I have to configure Ola Hallengren's Maintenance Scripts for my SQL Server 2017. SQL Database size about total 400GB.Total 10 Dbs. But I have some questions.

1- I will use these schedules like below. What is the best practices for this ? or What do you recommended?

DatabaseBackup – SYSTEM_DATABASES – FULL job – Daily at 12:05AM
DatabaseIntegrityCheck – SYSTEM_DATABASES job – Daily at 12:10AM
IndexOptimize – USER_DATABASES job – Daily at 1:00AM

2- How can I check the progress / status when index optimize and databasSee integrity job ?

Kind Regards,

Best Answer

We can't answer the first two since that is for you to decide. Or rather, your Recovery Point Objective. How much data can you afford to lose? That is the deciding factor for the first two.

As for IndexOptimize, You might end up defragmenting indexes without seeing much gain from it. If it doesn't hurt you, then sure do it every day. But if it does, then perhaps less frequently. As for defrag, some consider not doing it at all on modern disk subsystems (I've wrote about this here: http://sqlblog.karaszi.com/fragmentation-the-final-installment/).

Updating stats might be more beneficial than defrag. However, Ola's default job focuses on defrag and not update stats (you'll only get new stats for the indexes where you have more than 30% fragmentation as a side effect of index rebuild). OTOH, some consider updating stats not being smart since you'll end up with new plans after that and plan generation costs, and you might get a worse plan than before. OTTH better stats can give better quality plans - so there's no one consensus on how to do these tings.

You can create a secondary job to update statistics only.

Ola logs what we does in the CommandLog table, checkin in on that is probably the best progress you can get, aside from sys.dm_exec_requests (some commands will show progress in this DMV).

Related Solutions

SQL Server – How to Modify Backup Directory Path in Ola Hallengren Maintenance Script

You can search the command column of the sysjobsteps table for the path you have in your current jobs with the following statement:

SELECT * FROM msdb.dbo.sysjobsteps AS s WHERE s.command LIKE '%\\comp1\path%'

You should then be able to update the sysjobsteps with the following statement:

USE msdb
GO
UPDATE sysjobsteps SET command = REPLACE(command, '<your_old_path_here>', '<your_new_path_here>')

You have to be careful to run the SELECT first to check if the correct commands are being returned. You could also backup the msdb before you start as a precaution.

To be on the safer side, you can right click a job and "Script Job As | Drop and Create To | New Query Editor Window" and then modify the path and execute.

Be sure to remove the line containing @schedule_uid=N'<GUID>' in the sp_add_jobschedule part before you attempt to recreate the job.

Sql-server – Ola Hallengren Maintenance script – Full database backup delays

It will depend on the resources available on the server.

Does it take same amount of time even when the backups are made to run at the same time at 2AM(by different jobs) due to IO overhead?

You can set up parallel jobs and test. You will need to watch, how many of full backups can run in parallel before other processes (if any) are effected (cpu, io, network bandwidth etc).

Recently there was another question where Brent Ozar explained the phases of backup and how you can test.

I will not repeat his words but you can check details here.

There are built in switches in SQL Server that you can use to speed up backup. See this blog post.

I did not understand this question? Did you mean if full backup run in serialized way? Depends how you job is set up. You can make them parallel as @Ghauan explained in comment, you can make multiple jobs to run in parallel.

Is this the normal way the FULL backups are made (with delays)?

Best Answer

Related Solutions

SQL Server – How to Modify Backup Directory Path in Ola Hallengren Maintenance Script

Sql-server – Ola Hallengren Maintenance script – Full database backup delays

Related Question