Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm setting up a maintenance plan in SQL Server 2008 R2.

On the Reorganize Index Task it has an option for "Compact Large Objects". What does that mean and do I want to turn it on?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

Large objects (LOBs) are stored in different "pages". A page is a small section of a database, typically an 8kb chunk. For rows that contain LOB columns, the normal page will contain a reference to the LOB page.

From the alter index manual page:

WITH ( LOB_COMPACTION = { ON | OFF } )
Specifies that all pages that contain large object (LOB) data are compacted. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. The default is ON.

The default is "on" and it's hard of a case where you'd want to turn it off. Perhaps if you had a strict time window for reindexing, and turning LOB compaction off would allow you to meet the window.

share|improve this answer
    
Thanks. This made perfect sense. –  Chris Lively Nov 21 '11 at 21:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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