patternMajor
Compact Large Objects in Reorganize Index task
Viewed 0 times
objectslargeindexreorganizetaskcompact
Problem
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?
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?
Solution
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 to think 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.
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 to think 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.
Context
StackExchange Database Administrators Q#8132, answer score: 20
Revisions (0)
No revisions yet.