1

I've read in the "About Service Pack 2 for SharePoint Products and Technologies" paper that SP2 includes an update for the Update Statistics Timer Job that causes SharePoint to run SQL Server's online index rebuild feature (p.4).

I'm uncertain of the terminology here but is this the rebuild that SQL Server uses for minor fragmentation (up to around 40%) and leaves the DB online? I'm also guessing that this will therefore not rebuild severely fragmented indexes as I think this requires the DB to come offline. Can someone please confirm my belief here?

Improve this question

1 Answer 1

Reset to default
2

The stored procedure you are referring to is (proc_DefragmentIndices) and is discussed in this KB article.

The stored procedure works within the limitations of the online index build feature, one of which being that you cannot perform an online index on an index that contains a LOB (large object) column. The bad news? SharePoint stores a lot of BLOB data (images, office docs etc.) in content databases.

Looking at the stored procedure itself, you can see that it attempts to perform an online defragmentation first then switches to offline defragmentation where required - i.e. for image-like columns. From what I can see there is no logic to determine the level of fragmentation - which could be ascertained using avg_fragmentation_in_percent.

My understanding of "online" in this context is an "online index build". This is not the same as the SQL database itself being online - an online index build works by maintaining two copies of the index meaning that data is fully available at all times.

Offline index rebuilds don't take the database itself offline - they place exclusive "locks" on your SQL table indexes during which time queries cannot be run on the table(s) in question. That's very different from the DB itself being in an offline state.

So to answer your question, the timer job added in MOSS 2007 SP2 will almost certainly result in offline index building given that you will have BLOB data in your content DB. The stored procedure will, however perform online index building wherever possible and will not take the database itself offline - only the table(s) in question.

Improve this answer
4
  • I won't add another answer as I think Ben's would suffice. But if more lean index hadling is needed, see Patrick Heyde's post: blogs.technet.com/b/patrick_heyde/archive/2010/05/27/…
    – Alexey Krasheninnikov
    Jan 17, 2011 at 0:37
  • Thanks all for your comments, and thanks for digging up this old question. By my understanding, an "online" rebuild means that the DB is still fully available for reading and writing, while an "offline" rebuild means... what exactly? Is the DB / table being reindexed available for writing and reading or reading only, or not available?
    – Mike H
    Feb 1, 2011 at 6:33
  • 1
    @Ben - I'm not sure if I'm just reading this response correctly this time or you've added something. My remaining question from the comment above is answered. Thanks.
    – Mike H
    Jul 12, 2011 at 3:31
  • Glad it helped.
    – Benjamin J Athawes
    Jul 12, 2011 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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