Database Reference
In-Depth Information
■
online index rebuild acquires schema modification (SCh-M) lock during the final phase of execution. even
though this lock is held for a very short time, it can increase locking and blocking in very active oLtp systems. SQL
Server 2014 introduces the concept of low-priority locks, which can be used to improve system concurrency during
online index rebuild operations. We will discuss low-priority locks in detail in Chapter 23, “Schema Locks.”
Note
Unfortunately, there are two caveats associated with online index rebuild. First, even with the Enterprise Edition,
SQL Server 2005-2008R2 does not support
online
index rebuild if an index has large-object (LOB) columns defined,
such as
(n)text
,
image
,
(n)varchar(max)
,
varbinary(max)
,
xml
, and others.
The second issue is more complicated. Index rebuild does not move
LOB_DATA
allocation units to the new filegroup.
Let's look at an example and create a table that has an LOB column on the
FG1
filegroup. Listing 15-11 shows the code for this.
Listing 15-11.
Moving a table with an LOB column to a different filegroup: Table creation
create table dbo.RegularTable
(
OrderDate date not null,
OrderId int not null identity(1,1),
OrderNum varchar(32) not null,
LobColumn varchar(max) null,
Placeholder char(50) null,
) textimage_on [FG1];
create unique clustered index IDX_RegularTable_OrderDate_OrderId
on dbo.RegularTable(OrderDate, OrderId)
on [FG1];
As a next step, let's check that all allocation units are residing in the
FG1
filegroup. The code for this is shown in
Listing 15-12. You can see the result of the query in Figure
15-8
.
Listing 15-12.
Moving a table with an LOB column to a different filegroup: Checking allocation units placement
select
p.partition_number as [Partition]
,object_name(p.object_id) as [Table]
,filegroup_name(a.data_space_id) as [FileGroup]
,a.type_desc as [Allocation Unit]
from
sys.partitions p join sys.allocation_units a on
p.partition_id = a.container_id
where
p.object_id = object_id('dbo.RegularTable')
order by
p.partition_number
Figure 15-8.
Allocation units placement after table creation
