Here I continue a series of articles devoted to new options in SQL Server 2014. Today let’s review another useful operation – ONLINE rebuilding some partitions of partitioned objects (tables or indexes).
First let me remind you that some versions of SQL Server (Enterprise, Evaluation, Developer) allow you to perform a number of operations in ONLINE mode. The main advantage of this mode is that we can create / change index and keep on working with the data (including changing these data) at the same time.This happens due to the fact that schema lock (Sch-M) is not retained throughout creation / changing process. Here you can find a more complete list of options supported by different versions: https://msdn.microsoft.com/ru-ru/library/cc645993.aspx
Actually, the mentioned above option is highly useful, especially supposing that you have access to the data which is one of the key requirements for the system. However, up to release of SQL Server 2014 we have faced a number of limitations while working with partitioned objects. The main limitation was that we can not carry out ONLINE operations for a certain partition, but only for the whole object.
If we try to run the following query:
alter table [MyTable] rebuild partition = 1 with (online = on)
we will get exception:
‘online’ is not a recognized ALTER INDEX REBUILD PARTITION option
In case ONLINE mode is not set for the partition we will get Sch-M lock on the entire table, which is not convenient at all. With the hope to avoid table-level lock escalation we can change the escalation mode (LOCK_ESCALATION), as BOL states that locks are escalated to the table level by default ( = TABLE), and we are able to specify AUTO mode for a partitioned table:
In partitioned tables partition-level lock escalation is available. In this case escalation to a larger granularity of TABLE will not be proceeded.
However, this rule doesn’t work for REBUILD operations and we still get a table-level locking. And BOL mentions this fact as well:
Rebuilding a fragmented index can not be performed in ONLINE mode. During this operation the entire table is locked.
And now imagine that we need to perform data compression (data_compression) only in one partition and while doing this we will lock the entire table. The feedback on this subject is even published on the official website: http://connect.microsoft.com/SQLServer/feedback/details/709976/table-lock-during-partition-compression
And here is an extract from the answer:
We are currently working on supporting Online Index Rebuild for a single partition and hopefully this will be available in our next release.
And finally this happened! SQL Server 2014 supports operations for a single partition in ONLINE mode.
Below is the demonstration script:
create partition function pf_dt ( datetime )
as range right for values ( '20130701' );
go
create partition scheme ps_dt
as partition pf_dt all to ( [primary] );
go
create table dbo.test_table ( dt datetime, val varchar(50) ) on ps_dt (dt);
go
declare @start_dt datetime = '20130614';
with
cte1 as ( select t1.* from ( values(1),(1) ) t1(i) )
, cte2 as ( select t2.* from cte1 t1 cross join cte1 t2 )
, cte3 as ( select t3.* from cte2 t2 cross join cte2 t3 )
, cte4 as ( select t4.* from cte3 t3 cross join cte3 t4 )
, cte5 as ( select t5.* from cte4 t4 cross join cte4 t5 )
insert into dbo.test_table
select dateadd( mi, row_number() over ( order by (select null) ), @start_dt ), replicate( 'A', 50 ) from cte5;
go
alter table dbo.test_table
rebuild partition = 2 with ( online = on );
go
But the developers went further and added a number of useful parameters for ONLINE operations. One of them is low_priority_lock_wait, which allows you to specify the wait interval in case the resource you work on is blocked
MAX_DURATION = time [MINUTES]
and which action should be performed when the wait interval expires:
ABOUT_AFTER_WAIT = [NONE | SELF | BLOCKERS]
•NONE – no action (the same behavior in SQL Server 2012)
•SELF – to abort current operation
•BLOCKERS – to stop all processes «disturbing» us
For demonstration let’s execute this query in one session:
begin tran
select * from dbo.test_table with (holdlock)
and the one below in another session:
alter table dbo.test_table
rebuild partition = 1
with (online = on ( wait_at_low_priority ( max_duration = 1 minutes, abort_after_wait = self ) ) );
go
After a minute we see the exception:
Lock request time out period exceeded.
Now do not stop the first transaction and run this query:
alter table dbo.test_table
rebuild partition = 1
with (online = on ( wait_at_low_priority ( max_duration = 1 minutes, abort_after_wait = blockers ) ) );
go
One minute later we will see that the operation was completed successfully. Then let’s go to the window of 1st transaction and try to perform any operator. We will get an exception:
Error at the transport level when sending a request to the server (provider: Shared Memory Provider, error: 0 – There are no processes on the other end of the pipe.)
It means our transaction was successfully aborted
In conclusion, I’d like to notice that we can choose behavior for locks not only for ONLINE operations, but also for SWITCH operations. Below is a script for independent testing:
--Table for switching the data from one partition to another
create table dbo.test_table_demo ( dt datetime, val varchar(50) );
go
--Switching using wait_at_low_priority
alter table dbo.test_table
switch partition 1 to dbo.test_table_demo
with ( wait_at_low_priority ( max_duration = 1 minutes, abort_after_wait = self ) );
go