We’ve been utilizing Ola Hallengren's maintenance scripts for our index optimization for years on our OLTP databases. (we are on sql server 2008 R2)
2 weeks ago though one of the DBA’s here has created a maintenance job to update statistics separately and this runs weekly before the Index optimization job.
It seems that after this was created we are getting performance issues on a handful of stored procedures. I am not too familiar with stat maintenance, so I am wondering, is it really necessary to run a weekly job separately to update all statistics?
The DBA here seemed to think this was needed but I feel that we started having badly performing stored procedures the morning after the maintenance jobs are running and I constantly needed to recompile the same stored procedures at least 1 or 2 times per day for the following few days then the issue just stops. My theory is pointing to the statistics updates that occurred before but I am having trouble finding the reason why. Don't statistics get updated after a certain amount of changes or during an index rebuild anyway? Why would recalculating them separately cause an issue?
Would anyone be able to offer some clarity as to why this would happen to the same stored procedures every time statistics get updated?
This is the command being used for the statistics job:
EXECUTE dbo.IndexOptimize @Databases = 'DB_PRO', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y' , @TimeLimit = 3600, @LogToTable = N'Y';
Then the Index Maintenance job runs with the following :
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'DB_PRO', @TimeLimit = 10800, @LogToTable = 'Y'" -b