Query Performance | Update Statistics | Ola Hallengren SPs

Question

I recently read or listened to something Tara Kizer said about developers coming to her on timeouts or slow running queries, and the resolution was usually updating stats. I think she went on to say something about eventually she worked with developers to update their code, but I can't find the article or podcast (it was something to the extent of her talking about early in her career, IIRC).

Anyway, I am experiencing the same issue after recently switching to using Ola Hallengren's scripts for maintenance. I started with a new company and I am working on moving everything over from maintenance plans (weekly index rebuilds) to using Ola Hallengren's stored procs with jobs (still doing weekly index/stats maintenance unless there is a reason to do it more frequently). The specific code I am using for this is:

EXECUTE dbo.IndexOptimize
 @Databases = 'ALL_DATABASES',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y',
 @LogToTable = 'Y'

Oddly enough, after switching to this I am getting developers approaching me, exactly as Tara mentioned, and the fix for every single case has been updating stats (it has been 4 or 5 situations, and repeating periodically). Without discussing specifics of each case, any idea why this would surface all of sudden? Its too coincidental that I never saw any issues before, and it's only happened after switching to Ola's scripts. I am sure I could dig into each case and find bad code behind all of them, but holistically, what would be different about the two situations (MPs vs. Ola's scripts)? Maintenance plans are ugly, and I don't want to go back, but I am having a hard time expressing a business value for going to jobs with Ola's SPs, when we're having production issues since switching.

I appreciate any thoughts anyone has on this.

Answer 1

Depending on your version of SQL Server, maintenance plans reorganize or rebuild all of the indexes (I believe that starting w/ 2016 you can set thresholds to only process indexes with a certain level of fragmentation).

So, with Ola's scripts, you've got indexes that aren't badly fragmented enough to be rebuilt. While Ola's scripts allow you to update all statistics (depending the parameters you provide), the job may be finishing up faster than it did in the past. Therefore, some other job that makes many changes may not be done at the point when the statistics are being updated, causing enough changes to throw off the validity of the statistics.

(Aside - you noted that you were using @OnlyModifiedStatistics = 'Y'; you might see what happens if you change that to 'N', just as an experiment.)

Alternately, if (for example) you've limited the set of indexes to be considered, Ola's script will put the same limits on the statistics to be updated.

You can set up a job to update statistics using either T-SQL or maintenance plans. That could be run more often than the rebuild job.

UPDATE: I had originally presumed that Ola's script would only update statistics for indexes it had rebuilt/reorganized; it does not look like that's the case. Pulled in info I'd added to the comments.

The suggestion remains the same: have a separate job to update statistics, and run that during the week (daily, if you want to and the job doesn't take too long), and complaints should go down.

Answer 2

Although not specifically answering the question itself, we were able to mitigate this issue by setting trace flag 2371 as noted here:

https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server