patternsqlMinor
SQL Server 2008 Reindexing/Rebuilding
Viewed 0 times
2008sqlreindexingserverrebuilding
Problem
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.
Solution
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/
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/
Context
StackExchange Database Administrators Q#155219, answer score: 5
Revisions (0)
No revisions yet.