3

Should I reindex/rebuild while my DB is up and running or should I take it offline? Also after doing some digging around I came across this article that recommends Rebuilding after 40% fragmentation and Reindexing between 10% to 40%. Can anyone verify this? My DB is a quite large and is slowing down more and more.

3
  • What you mean by saying Can anyone verify this ? Do you want to confirm it or what ? – Pரதீப் Nov 14 '16 at 15:59
  • Well another issue I am coming across is that when I do Reindex or Rebuild a DB the percentage of fragmentation stays the same. – EightyTwo Nov 14 '16 at 16:08
  • Are you sure that fragmentation is your problem here? What statistics have you checked that says this is a problem? – Rich Benner Nov 14 '16 at 16:15
4

Should I reindex/rebuild while my DB is up and running or should I take it offline?

You cannot rebuild index when database is offline, the database has to be online to rebuild the index.

The widely accepted parameter for rebuilding is if fragmentation >30 % rebuild it and if fragmentation lies between 10 and 30 % reorganize it. You can use your own script, maintenance plan or Ola Hallengren script or My script. If you have time I would suggest you to create your own.

0
5

I'd highly recommend using Ola Hallengren's index maintenance scripts, they're very widely used.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

I personally set the script to Reorganise at 50% and Rebuild at 80% (and only check indexes over 1000 pages). It's recommended to have this as an overnight run if you have the maintenance window.

Beware that the first time you run this it may have to do quite a lot of work.

As a side note, do you know that fragmentation is actually causing your performance slow down? It could be any number of things unless you've already narrowed this down. I'd recommend using Brent Ozar's scripts for a health check if you haven't already. Start with sp_Blitz and go from there.

https://www.brentozar.com/blitz/

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.