-1

We have a database named WSS_Us_App in our Production SQL Server 2014. In this database all of the indexes need to be rebuilt as most have a fragmentation level greater than 50%. I plan to rebuild these indexes during business hours as I was told that there will not be a problem and would not cause any major issues.

I plan to start rebuilding the indexes manually. However for the future, I would like to set up a SQL job that will take care of this task for me - perhaps every weekend have this job run.

My questions:

  1. Is it better to rebuild these indexes with the ONLINE option? (I am having a hard time to understand the difference between with ONLINE option vs not)

  2. I have some indexes that have high percent of fragmentation level but the page count is low. For example, an index called IX_LogTime has a fragmented percent of 50% and a page count of 2. Is this necessary to rebuild?

sqllover2020
  • 83
  • 2
  • 10

2 Answers2

0
  1. You will need enterprise edition to do rebuilds online but advantage is you only need a schema lock, non-online rebuilds will cause blocking so better to do it out of hours
  2. Rebuilding indexes with very few pages is indeed a waste of time
  3. If this is Sharepoint please double-check the Sharepoint documentation and use the supported methods
Stephen Morris - Mo64
  • 4,056
  • 1
  • 9
  • 17
0

  1. https://stackoverflow.com/questions/6309614/what-is-the-difference-between-offline-and-online-index-rebuild-in-sql-server

  2. MS recommends to go for rebuild\reorganize if the page count is more than 1k by default. Lower page counts leads to more fragmentation at very minimal changes, which is the reason it's of no use for doing defragmentation on those. However, at the end performance only matters - if you are seeing good performance after doing defragmentation on those, then go ahead with that. If not, then skip those.

Mahesh Giri
  • 25
  • 4