Senior DBA Class - Index Maintenance for Enterprise Environments

Thanks Brent, any recommendations on running Ola Hallengren index maintenance scripts for VLDBs on daily basis

lol I just built some new SQL servers just after we added a few hundred TB of SSD allowing us to get rid of our spinners and I set up every log drive in its own volume not even thinking about it.

On index re-orgs - does SQL 2017 and 19 update statistics now?

Saving your playlist.. ❤️

Thank you sir

Ah... be real careful about REORGANIZE, folks. It does NOT "Re-Apply" the Fill Factor. Reapplying the Fill Factor would reduce the page fullness of any pages that were fuller than the Fill Factor. REORGANIZE simply cannot do that because that would require new pages to be created and REORGANIZE cannot and will not create new pages. To wit, having a Fill Factor assigned to prevent page splits works great (especially for Random GUIDs and similar keyed indexes) BUT REORGANIZE put's the screws to that notion very early on because it will NOT "clear the area above the Fill Factor". That quickly results in "rapid, massive, and permanent" logical and physical fragmentation. Done correctly, even Random GUID Clustered Indexes can go months with <1% fragmentation even when the insert rate is 100K rows per day. However, that will NOT work if you use REORGANIZE. You MUST either commit to using ONLY REBUILDs or doing nothing at all because you get fewer bad page splits per day by doing no index maintenance than by doing it wrong and using REORGANIZE on such indexes is not only doing it wrong but it guarantees that you have "morning after" blocking forever.

It hasn't been officially announced yet but DallasDBAs gave up their 6PM sponsor slot on GROUP BY Americas on May 25th 2021 so that I could do my "Black Arts" Index Maintenance "introduction" (cut down from 2.5 hours to 75 minutes... bring your water cooled helmet... your going to need it) on a ton of subjects including how Random GUID fragmentation is actually a myth perpetuated by misinformation and how the current world-wide accepted "Best Practices" for index maintenance are actually a "Worst Practice" and they were never intended to be adopted as a supposed "Best Practice".

great content!!!

You have an awesome content! Muchas gracias Brent!

Thnx for sharing! I would just add that setting MAX DOP in the DBCC CHECKDB or index maintenance (OLA) command is necessary only if your server MAX DOP setting is not good enough (like sharepoint) because OLA script takes global MAX DOP settings if you didn't explicitly specify differently in the command.

Hi Brent, We see in our Database, tables are getting fragmented frequently.. any lead and direction for such behavior ?

Where can I read / understand more about your comment on things change and having data / log files on separate drives doesn't make much sense anymore. I'd like to dig into that rabbit hole more if you can link me that specific video - Thanks