1

We are using the Ola hallengren SQL Server Index and Statistics Maintenance solution from the past 6 months in our production system. Script is only used for Update Statistics not Index maintenance. Job used to take about 90-120 mins to complete which was completely normal considering the database size(1.8TB). All of a sudden the job started to take about 5-6hrs to complete from the past couple of weeks. We haven't made any changes to the system. Each Statistics used to take less than 5 secs before now they take about 60-250 secs to complete. All this happened within a couple of days, not gradually. We are using SQL Server Enterprise edition.

Has anyone experienced this kind of issue before? Any suggestions are greatly appreciated.

Below are the parameters used in SQL job.

EXECUTE dbo.IndexOptimize 
--@Databases = 'ALL_DATABASES', 
@Databases = 'User DB', 
@FragmentationLow = NULL, 
@FragmentationMedium = NULL, 
@FragmentationHigh = NULL, 
@UpdateStatistics = 'ALL', 
@OnlyModifiedStatistics = 'Y', 
@MAXDOP=2, 
@Indexes='ALL_INDEXES', 
@LogToTable='Y' 
--,@Indexes='ALL_INDEXES,-%.dbo.eventhistory,-%.dbo.eventhistoryrgc'
5
  • You are handling 1.8TB of databases with MAXDOP of 2? Could you please share specs of server - CPU cores and memory? Jul 15, 2021 at 8:07
  • @arun kumar, can you see is there any other maintenance task running those hours?
    – Biju jose
    Jul 15, 2021 at 9:10
  • Check the command log table. Perhaps you've been processing way more objects. Jul 15, 2021 at 13:25
  • @Learning_DBAdmin, we have 12 core CPU and 300GB memory. MAXDOP is set to 2 because our application is compatible with 2.
    – Arun Kumar
    Jul 16, 2021 at 18:53
  • Capture waits during the run of the job. If things are suddently a lot slower you're either processing more data ( More objects or perhaps doing a bigger sample ), or your queries are waiting for something. Jun 6 at 12:25

1 Answer 1

1

Since you are logging to table (@LogToTable='Y') - check to see if perhaps usage patterns changed and maybe you are possibly rebuilding more?

select count([Command]) [count_]
     , [CommandType]
     , convert(date, [StartTime]) [day_]
from [DBAJOBS].[dbo].[CommandLog]
where CommandType in ( 'UPDATE_STATISTICS', 'ALTER_INDEX' )
group by [CommandType]
       , convert(date, [StartTime])
order by [CommandType]
       , [day_];
1
  • Hi, Thanks for sharing the SQL script. The job is only used for Update Statistics not for Index maintenance. On an average job is updating 1300-1600 stats everyday since the beginning. We don't see more stats getting updated. We are using default settings of Sample and resample size in the Ola scripts.
    – Arun Kumar
    Jul 16, 2021 at 18:59

Your Answer

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

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