在今天的文章里,我想谈下在线索引重建操作( Online Index Rebuild operations),它们在SQL Server 2014里有怎样的提升。我们都知道,自SQL Server 2005开始引入了在线索引重建操作。但这些在线操作并非真正的在线操作,因为在操作开始时,SQL Server需要获得共享表锁(Shared Table Lock (S) ),在操作结束时需要在对应表上获得架构修改锁(Schema Modification Lock (Sch-M) )。因此这些操作是真正的在线操作,只是营销技巧(marketing trick)。但是,亲,“在线”肯定比“部分在线”好听多了。
尽管如此,SQL Server 2014还是在在线索引重建的开始和结束发生的阻塞做了一些改进。因此,在你执行在线索引重建时,你可以定义所谓的锁优先级(Lock Priority)。来看看下面的代码,你会看到起作用的新语法:
1 ALTER INDEX idx_Col1 ON Foo REBUILD
2 WITH
3 (
4 ONLINE = ON
5 (
6 WAIT_AT_LOW_PRIORITY
7 (
8 MAX_DURATION = 1,
9 ABORT_AFTER_WAIT = SELF
10 )
11 )
12 )
13 GO
当阻塞情况发生时,你可以用WAIT_AT_LOW_PRIORITY关键字定义如何处理。使用第1个属性MAX_DURATION指定你想要等待的时间——这里是分钟,不是秒!用ABORT_AFTER_WAIT属性你指定哪个会话需要被SQL Server回滚。SELF意味着那个ALTER INDEX REBUILD语句会回滚,当你指定BLOCKERS时,阻塞的会话会回滚。当然,当没有阻塞发生时,在线索引重建操作会立即执行。因此这里你只能配置当阻塞情况发生时要怎么处理。
好了,我们来实操下。我们新建一个数据库,一个简单的表和一个聚集索引。
1 -- Creates a new database 2 CREATE DATABASE Test 3 GO 4 5 -- Use the database 6 USE Test 7 GO 8 9 -- Create a simple table 10 CREATE TABLE Foo 11 ( 12 Col1 INT IDENTITY(1, 1) NOT NULL, 13 Col2 INT NOT NULL, 14 Col3 INT NOT NULL 15 ) 16 GO 17 18 -- Create a unique Clustered Index on the table 19 CREATE UNIQUE CLUSTERED INDEX idx_Col1 ON Foo(Col1) 20 GO 21 22 -- Insert a few test records 23 INSERT INTO Foo VALUES (1, 1), (2, 2), (3, 3) 24 GO |
为了触发阻塞,我在不同的会话开始一个新的事务,但不提交:
1 BEGIN TRANSACTION
2
3 UPDATE Foo SET Col2 = 2
4 WHERE Col1 = 1
这意味着我们在需要修改的记录上获得排它锁(Exclusive Lock (X)),在对应的页上获得意向排它锁(Intent-Exclusive Lock (IX)),在表本身获得意向排它锁(Intent-Exclusive Lock (IX))。我们刚刚在SQL Server里创建了典型的锁定层次(locking hierarchy):表=>页=>记录。在表级别的意向排它锁(IX Lock)和在线索引重建操作需要的共享锁(Shared Lock)是不兼容的——典型的锁/阻塞情形发生了。当你现在执行在线索引重建操作时,会发生阻塞:
1 ALTER INDEX idx_Col1 ON Foo REBUILD
2 WITH
3 (
4 ONLINE = ON
5 )
6 GO