I have a question about index maintenance. I have a weekly process that does all of my database checks, etc. and a weekly rebuilding of indexes. Those rebuilds have been causing some locking pressure on one of our apps and I am looking at the possibility of scaling the rebuilds back, making them conditional under the right circumstances.
Now, I understand that every case and every application is different, imposing different kinds of tuning if I wanted it 100% optimized. But what I am looking for is a general "good enough" algorithm.
As we are running SQL 2016 Enterprise, it looks like I can do index rebuilds online, which will probably help with the locking pressure, but it would also be nice to be able to look at something in the database and say, hey, "this one can be skipped entirely this week.", either because it's one of my reference tables (no table content changes since last week), or some other slowly changing table…
For various reasons, I gave up on Microsoft's SQL Maintenance Plans a while back and I now use an in house c# program to do all of my SQL maintenance (transaction logs, weekly full backups, weekly checkdbs, weekly index maintenance, etc.) all while sending me an email when it's done letting me know if things went smoothly, or went south somewhere along the way. So, I don't mind adding some queries to my code to see about making index maintenance conditional or not.
Best Answer
It's totally an opinion and observation based in my current project where we handle databases over 20 TBs . We do not do any rebuild or re-organize of indexes as that is very expensive operation. So please check if that is actually helping you out in performance. Rather we focus more on update stats daily with change or modified stats.
If needed, you can use ola hallegren which has excellent parameters to set that up else use the one available from Microsoft. The SQL tiger team has been doing an excellent job for such activities in their git hub page for adaptive index defrag proc here