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

Question

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 ?

Answer 1

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

Answer 2

to check the progress of the index optimise job I have this script below, that can be useful for you.

check it out:

 SELECT 
                  [Server Name]                = @@servername
                 ,s.host_name
                 ,TheUser                      = s.original_login_name
                 ,r.session_id
                 , r.command
                 ,[Percent Complete]           = CONVERT(NUMERIC(6,2), r.percent_complete)
                 ,total_elapsed_time           = CAST( (r.total_elapsed_time/1000.00) AS VARCHAR(30)) + ' Seconds' 
                 ,estimated_completion_time    = CAST( (r.estimated_completion_time/1000.00) AS VARCHAR(30)) + ' Seconds' 
                 ,[Start time]                 = CONVERT(VARCHAR(30), r.start_time ,113)
                 ,[ETA Completion Time]        = CONVERT(VARCHAR(30), DATEADD(ms,r.estimated_completion_time,GetDate()),113)
                 ,sdest.Query
                         FROM sys.dm_exec_requests r WITH(NOLOCK)
                   INNER JOIN sys.dm_exec_sessions s WITH(NOLOCK)
                           ON r.session_id = s.session_id
                  OUTER APPLY (

                                SELECT COALESCE((
                                            SELECT TEXT AS [processing-instruction(_)]
                                            FROM sys.dm_exec_sql_text(r.sql_handle) 
                                            FOR XML PATH('')
                                                ,TYPE
                                            ), '') AS Query

                                FROM sys.dm_exec_sql_text(r.sql_handle) 

                    ) sdest
                    WHERE 1=1
                     AND r.command IN ('RESTORE DATABASE', 
                                       'BACKUP DATABASE',
                                       'DbccFilesCompact',
                                       'DbccSpaceReclaim',
                                       'RESTORE LOG',
                                       'ALTER INDEX')