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.