1

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,

1 Answer 1

0

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).

4
  • btw , your url is broken 404 not found
    – Cell-o
    Mar 26, 2021 at 13:32
  • Thanks Cell-o! The link should be working now (I had to update Wordpress). Mar 27, 2021 at 11:38
  • How can I check the progress / status when index optimize and databasSee integrity job ? how can I write DMV script?
    – Cell-o
    Mar 27, 2021 at 19:04
  • To see the commands that were/are execution, you do SELECT from the CommandLog table that Ola's installation script created. Assuming you chose that the scripts would log to the table, of course. And for the DMV you can see the progress for some commands while a command is executing. Just do a SELECT from this DMV. It is documented here: learn.microsoft.com/en-us/sql/relational-databases/… Mar 28, 2021 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.