SQL Server 2014如何提升非在线的在线操作-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

SQL Server 2014如何提升非在线的在线操作

简介: 原文:SQL Server 2014如何提升非在线的在线操作 在今天的文章里,我想谈下在线索引重建操作( Online Index Rebuild operations),它们在SQL Server 2014里有怎样的提升。
原文:SQL Server 2014如何提升非在线的在线操作

在今天的文章里,我想谈下在线索引重建操作( 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

 

当你查看DMV sys.dm_tran_locks时,你会看到那个需要共享锁(Shared Lock(S))的会话需要等待。这个会话会永远等待。我刚才就说过:“部分在线”……

1 SELECT * FROM    sys.dm_tran_locks

当我们执行带有锁优先级(Lock Priority)的在线索引重建时,有趣的事情发生了: 

 1 -- Perform an Online Index Rebuild
 2 ALTER INDEX idx_Col1 ON Foo REBUILD
 3 WITH
 4 (
 5    ONLINE = ON
 6    (
 7       WAIT_AT_LOW_PRIORITY 
 8       (
 9          MAX_DURATION = 1, 
10          ABORT_AFTER_WAIT = SELF
11       )
12    )
13 ) 
14 GO

在这个情况下,我们的ALTER INDEX语句会等待1分钟(MAX_DURATION),然后语句本身取消了(ABORT_AFTER_WAIT)。

如果你在这里指定了BLOCKERS选项,那么阻塞的会话就会回滚。当我们同时(在1分钟期间)查看DMV sys.dm_tran_locks,我们看到了有趣的东西:

 

从图中可以看到,SQL Server这里请求一个LOW_PRIORITY_WAIT的状态。因此3个请求状态(GRANT,WAIT,CONVERT)有了第4个选项:LOW_PRIORITY_WAIT。当我们查看DMV sys.dm_os_waiting_tasks时,事情变得有意思(59是执行语句的会话ID):

1 SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id='59'

在线索引重建操作的等待会话报告了一个新的等待类型LCK_M_S_LOW_PRIORITY。这意味着当在线索引重建操作被阻塞时,我们可以从服务器级别(sys.dm_os_wait_stats)的等待统计信息里获得——不错!

但是LCK_M_S_LOW_PRIORITY并不是新的等待类型。在SQL Server 2014里,当你查看DMV sys.dm_os_wait_stats时,会看到21个新的等待类型:

1 SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%LOW_PRIORITY%'
  • LCK_M_SCH_S_LOW_PRIORITY
  • LCK_M_SCH_M_LOW_PRIORITY
  • LCK_M_S_LOW_PRIORITY
  • LCK_M_U_LOW_PRIORITY
  • LCK_M_X_LOW_PRIORITY
  • LCK_M_IS_LOW_PRIORITY
  • LCK_M_IU_LOW_PRIORITY
  • LCK_M_IX_LOW_PRIORITY
  • LCK_M_SIU_LOW_PRIORITY
  • LCK_M_SIX_LOW_PRIORITY
  • LCK_M_UIX_LOW_PRIORITY
  • LCK_M_BU_LOW_PRIORITY
  • LCK_M_RS_S_LOW_PRIORITY
  • LCK_M_RS_U_LOW_PRIORITY
  • LCK_M_RIn_NL_LOW_PRIORITY
  • LCK_M_RIn_S_LOW_PRIORITY
  • LCK_M_RIn_U_LOW_PRIORITY
  • LCK_M_RIn_X_LOW_PRIORITY
  • LCK_M_RX_S_LOW_PRIORITY
  • LCK_M_RX_U_LOW_PRIORITY
  • LCK_M_RX_X_LOW_PRIORITY

所有主要的等待类型(LCK_M_*)都有额外的锁优先级等待类型。这个非常酷,也非常强大,因为你很容易从中可以跟踪到为什么在线重建索引操作被阻塞。另外,对于分区切换(Partition Switching)也适用同样的技术(锁优先级(Lock Priorities)),因为在切换期间,操作也要在2个表(原表,目标表)上获取架构修改锁(Schema Modification Lock (Sch-M))。

我希望这篇文章可以让你理解SQL Server 2014里的锁优先级(Lock Priorities),还有为什么SQL Server里的“在线”操作实际上只是“部分在线”。

感谢关注!

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章