SQLServer 在线添加索引

支持的版本

首先SQLServer只在Enterprise, Developer, Evaluation 版本支持在线添加索引

过程

假设对一个堆表做在线聚集索引的创建：

三个阶段：准备阶段、构建阶段、完成阶段；

准备阶段：此阶段非常短，会创建操作表的行版本，更新元数据创建一个索引，对表加S锁和IS锁；

构建阶段：此阶段是主要阶段，会对原表的数据做扫描、排序、合并 并通过bulk load插入目标索引，对表加IS锁；这个阶段用户的增、删、改、查操作会作用于原索引和新构建的索引，这也是理解为什么在线添加索引会增加开销的根本；

Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double. This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation.

完成阶段：此阶段非常短，会对元数据做更新并且可能会删除原表，但加的锁比较重 SCH-M，并且在此阶段开始前这个表上老的未提交的更新操作都必须完成（如果未完成则会一直阻塞在这个阶段）
新的DML操作是否会阻塞取决于 操作需要获取的锁是否和SCH-M锁冲突（除了nolock都冲突）

注意点

1. 在线添加索引 如果是修改已经存在的索引或者对已经存在的索引的有影响（比如修改clustered index会对nonclustered index重建），那么现在的索引还能否使用？

   答：可以。

2. 哪些情况不支持online

   • 当创建、重建的索引是聚集索引并且操作的表含有大字段时 包括： image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
   • 当创建、重建的是非聚集索引 但涉及的key或者nonkey（include）列含有大字段时
   • 当创建、重建的索引是作用于本地临时表时
3. 在线添加索引可能会引发死锁，如果引发那么牺牲者是其它事务

测试

1. test_1 是堆表 不含任何索引，只有少量数据

    session 111执行如下SQL

    begin tran
    create clustered index CIX_test_1_c1
    on test_1 (c1)
    with(online=on)
    --rollback tran

1. 查看session 111 获取的锁

   select * from sys.dm_tran_locks where request_session_id=111
   

2. 其它DML操作阻塞
   

此次测试延长了第三阶段的架构修改锁，阻塞了DML操作，实际生产环境中大部分时间应该集中在第二阶段，所以我们通常说的不影响DML也不是完全不影响

所以，一般我们说的不影响也不是完全不影响，只是大部分的时间是不影响的

在线添加索引带来的问题

在线添加索引少锁表的同时DBA要清楚可能出现的问题，比如这个CASE

I have a scenario where I need to create a Clustered Index (CI) on a very large SQL Server 2012 database table. This table has about approximately 10 billion rows, 500 GB in size. The job ran for about 20 hours into it and then fails with error: "Out of disk space in tempdb". My tempDB size is 1.8TB, but yet it's still not enough.

I have exhausted Google search and decided to post it here to get some extra help.


 

CREATE CLUSTERED INDEX CI_IndexName

ON TableName(Column1,Column2)

WITH (MAXDOP= 4, ONLINE=ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION=PAGE)

ON sh_WeekDT(Day_DT)

GO

    

100亿条记录、500GB大表，添加索引20小时报错，tempdb空间不够，如何解决？

理解了在线添加索引可以给出一条建议：放弃online改成offline，作者修改后 跑了7.45小时成功了。