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’
Best Regards, Arun