sql azure时间消耗查询

Question

我在Sql中有一个表，包含大约6M行。我想为它创建一个新的索引。cmd就像：

CREATE NONCLUSTERED INDEX [INDEX1] ON [dbo].Table1
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC,
    [Column4] ASC
)
INCLUDE ( [Column5],[Column6]) 

大约15分钟后，就会出现一个错误。

"Msg 10054，20级，状态0，第0行 从服务器接收结果时发生了传输级错误.(provider: TCP Provider，error: 0-现有连接被远程主机强制关闭)。

我试了好几次，都犯了同样的错误。但是，我执行了其他耗时的查询，如：

插入table1(Col1，Col2，Col3)，从table2中选择Col1、Col2、Col3

花了20分钟才成功返回。

查询在相同的Sql中执行。我不知道这里发生了什么。有人能帮忙吗？谢谢!

Answer 1

对于包含1亿行的表，我也遇到了同样的问题，并联系了Microsoft的支持部门。这是我得到的答复：

不能在表上创建索引的原因是，您在平台上面临一个限制，即不能将大于2GB的事务处理。 创建索引是依赖事务日志执行表页移动的事务操作。表中更多的行意味着要在to中放置更多的页面。因为您的表包含了1亿条记录(这是一个相当大的数字)，所以很容易达到这个限制。 为了创建索引，我们需要更改方法。基本上，我们将使用一个临时(暂存)表来存储数据，同时在源表上创建索引，这是以前从数据中清除的。行动计划：

1. 创建一个与原始表相同但没有任何索引的暂存表(这使暂存表成为堆)
2. 将数据从原始表移动到暂存表(插入速度更快，因为暂存表是堆)
3. 清空原表
4. 在原始表上创建索引(这次事务应该是几乎为空的)
5. 将数据从暂存表移回原始表(这需要一些时间，因为该表包含索引)
6. 删除暂存表

他们建议使用BCP在暂存表和原始表之间移动数据。

当查看event_log表时..。

select * from sys.event_log 
where database_name ='<DBName>'
and event_type <> 'connection_successful'
order by start_time desc

。。我发现了这个错误消息：

由于过多使用事务日志空间，会话已被终止。尝试在单个事务中修改较少的行。

Answer 2

谢谢你的回答！事实上，我也找到了根本原因。

有一个解决方案，设置ONLINE=ON，在联机模式下，索引创建任务将被分解为多个小任务，这样T-日志不会超过2GB。

但是有一个限制，索引创建命令的'include列‘不能是无限制大小的对象，比如nvarchar(max)，如果是这样的话，命令将立即失败。

因此，在Sql中，对于创建如下操作的索引：

CREATE NONCLUSTERED INDEX [INDEX1] ON [dbo].Table1
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC,
    [Column4] ASC
)
INCLUDE ( [Column5],[Column6]) 

如果前面的操作失败，请执行以下操作。

1.使用“online=on”创建索引

2.如果#1失败，意味着column5或column6为nvarchar(max)，查询表大小(如果< 2GB )，直接使用online=off创建索引。

3.如果2号失败，意味着表大小> 2GB，那么在不涉及临时表的情况下创建索引没有简单的方法，必须采取行动，ahkvk回答。