SQLServer 在线添加索引

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: SQLServer 在线添加索引 支持的版本 首先SQLServer只在Enterprise, Developer, Evaluation 版本支持在线添加索引 过程 假设对一个堆表做在线聚集索引的创建: 三个阶段:准备阶段、构建阶段、完成阶段; 准备阶段:此阶段非常短,会创建操作表的行

SQLServer 在线添加索引

支持的版本

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

过程

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

三个阶段:准备阶段、构建阶段、完成阶段;

准备阶段:此阶段非常短,会创建操作表的行版本,更新元数据创建一个索引,对表加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

  2. 其它DML操作阻塞
    3

此次测试延长了第三阶段的架构修改锁,阻塞了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小时成功了。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
261 0
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
90 13
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
4月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
57 6
|
4月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
271 1
|
3月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
391 0
|
4月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
253 3
|
4月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。