Now that SSD is a standard de-facto. Can the maintenance job of rebuilding indexes and updating statistics, using Ola Hallengren https://ola.hallengren.com/ jobs for instance, be done only on demand instead of regularly such as on weekly basis with Microsoft SQL Server 2017 or lower?
If it is still required, what would be the threshold of the database size to decide when it is necessary ?
Best Answer
I'm going to disagree that SSDs are the de facto standard, though they certainly are far more common than a short time ago. I know my company doesn't set any standards, but nearly all of our virtual database servers still use an auto-tiering partial-flash SAN, against my better judgement
And unfortunately, I'm going to start my answer with an "it depends."
In general, index maintenance is far less important than it was in prior years and older versions - specifically, the long and deep index rebuilds that used to be offline-only. The days of "reorganize everything and rebuild if > 30% fragmented" are pretty much over. I think most would agree that rebuilds are no longer worth doing, and SSDs do play a part in that.
On the other hand, statistics have less to do with fragmentation and physical distribution of data on disks and more to do with logical distribution of data across/among the table(s), so this can still be important. Whether it's important for a given workload depends on a lot of factors.
If you can test stopping these maintenance routines in a non-Prod system, you can look for poorly-performing queries, then analyze the DB engine's steps in the Execution plan. All of the statistics issues I've seen (and I've been dealing with some recently) have been easily visible by looking at the operators in an execution plan and seeing huge differences between "estimated number of rows" vs "actual number of rows". If the queries perform poorly enough, execute a quick statistics update and try again. It's a bit of work going through all of these steps, but once you have a handle on the system you can tell if statistics maintenance jobs are worthless overhead or important and necessary.
Erin Stellato has several great blog posts about statistics that can help with background information on why fresh statistics can be important, and also when they're most useful and how to detect if they're not updated frequently enough.