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.
I’m going to reword your questions a little:
Q: Should I log Agent jobs directly to a remote file server? No, because if you lose network connectivity or the file share goes down, your Agent job could fail. Your Agent job might not require network connectivity, like an update stats job, so that failure would suck.
Q: If I want to centralize Agent job history, how should I do it? Consider logging to a table (and Ola’s scripts support this too.) That way you can centralize the data in whatever method you like, like replication or log shipping. I like logging to a DBA utility database, and then restoring that from all my servers to one central server daily. Then I use union all views to combine the data from all my servers. It’s not up to the second, though.
Best Answer
Ola covers this scenario in the documentation.
For the
@Indexes
parameter:If you want to do "everything on the server, except one schema in one database" you'd supply parameters like
@Databases='ALL_DATABASES', @Indexes= 'ALL_INDEXES, -Db1.Schema1.%'
.If you want to do "everything on the server, except never the
staging
schema in any database" you'd supply parameters like@Databases='ALL_DATABASES', @Indexes= 'ALL_INDEXES, -%.staging.%'
.