2

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.

  • 2
    It may be worth to take a look to Ola Hallegren ola.hallengren.com His solution implements all what you are mention and are very simple to implement, and gives you control on many of the stuff you need. It has been used extensively across many shops with excellent results. In my case for some policies I didn't implement the backup solution, but I'm still using reindex and check db procedures. – dbamex May 20 at 21:52
  • This podcast by Kendra Little is very helpful: youtube.com/watch?v=fu6DGsDYnKI – Priyanka May 21 at 4:44
3

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

0

Also take a look at http://www.minionware.net/products/reindex/ its configurable thru table defined params.

-1

Usually, a good rule of thumb starting point is that indexes should be rebuilt when they are fragmented by more than 30% and should be reorganized when they are between 10-30% fragmented.

You could write a script which queries sys.dm_db_index_physical_stats (avg_fragmentation_in_precent column) to work out the percentage fragmentation and then take the appropriate action (rebuild or reorganize)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.