主页 / dba / 问题 / 213980
Accepted
Russ960
Asked: 2018-08-03 22:49:32 +0800 CST

非聚集索引的创建时间比聚集索引长得多

  • 772

我有一个在表上创建聚集索引和非聚集索引以进行分区交换的过程。我遇到的问题是创建聚集索引大约需要 2 分钟：

CREATE UNIQUE CLUSTERED INDEX UCX_Idx ON myTable (
    IdCol1 ASC
    ,Col2 ASC
    ,IdCol4 ASC
    ,IdCol3 ASC
    ,Col1 ASC
    ,Col6 ASC
    ) ON PS_1 (IdCol1) END

但是非聚集索引的创建大约需要 1.5 小时：

CREATE NONCLUSTERED INDEX IX_Idx1 ON myTable (
    IdCol1 ASC
    ,IdCol2 ASC
    ,IdCol3 ASC
    ,IdCol4 ASC
    ,Col1 ASC
    ) INCLUDE (
    Col2
    ,DateCol1
    ,Col2
    ,Col3
    ,Col4
    ,Col5
    ,Col6
    )
    WITH (SORT_IN_TEMPDB = ON) ON PS_1(IdCol1) END

我在 SQL Server 2014 中没有看到这种行为，但我在 SQL Server 2016 中看到了。相同数量的 RAM 和 CPU。我已经在没有 SORT_IN_TEMPDB = ON 的情况下尝试过，但它也有类似的问题。实际上，我已经在我的环境中的不同地方看到了这一点，并且都安装了 SQL Server 2016（标准版）。

sql-server-2016 clustered-index

1 个回答

  1. Best Answer

    开放式问题

    • 表的 DD 是什么样的？
    • 列的顺序是什么？

    聚集索引创建

    当您创建聚集索引时，实际上是按照您正在创建的聚集索引的顺序对表中的数据进行排序。

    它们不是表格的~副本~，它们是表格，按您选择作为键的列排序。它可能是一个。可能是几个。它可能是一个 GUID！但那是另一次了。从现在开始很久了。当我集结军队时，按照古老的预言。

    参考：非聚集索引中的聚集索引键列(Brentozar.com)

    非聚集索引创建

    聚簇索引的列顺序与非聚簇索引中的列顺序完全不同，直接影响索引的创建。数据库引擎必须多次搜索您的聚集索引，直到它以正确的顺序将所有信息组合在一起以创建非聚集索引。这就是为什么创建非聚集索引需要这么长时间的原因。

    然后是聚集索引始终是非聚集索引的一部分的开销。

    SQL Server 2014 和 SQL Server 2016 之间的差异

    至于各个版本在索引创建上的差异：您真的将苹果与苹果进行比较，而不是与梨进行比较吗？或者换一种说法：SQL Server 2014 服务器是否运行在具有相同 CPU 核心频率、相同数量的 RAM 和相同 MAX_DOP 设置的相同硬件上？

    使用标准版或企业版时，索引创建存在一些限制/限制...

    企业版与标准版

    并非每个 SQL Server 版本都提供并行索引操作。有关详细信息，请参阅 SQL Server 2016 版本支持的功能

    [...]

    • 并行索引执行和 MAXDOP 索引选项适用于以下 Transact-SQL 语句：
      • 创建索引
      • 改变索引重建
      • DROP INDEX（这仅适用于聚集索引。）
      • ALTER TABLE ADD（索引）约束
      • ALTER TABLE DROP（聚集索引）约束

    参考：配置并行索引操作(Microsoft Docs)

    如果您随后快速浏览了 SQL Server 2014 和 2016 的功能，那么这两个版本都限制了标准版的 MAXDOP：

    SQL Server 2014 - SQL Server 2016 的版本和支持的功能 - RDBMS 可管理性

                                Enterprise    Standard
Parallel indexed operations     Yes          -

    参考：SQL Server 2014 版本支持的功能(Microsoft Docs)

    SQL Server 2016 - SQL Server 2016 的版本和支持的功能 - RDBMS 可管理性

                                Enterprise    Standard
Parallel indexed operations     Yes          No

    参考：SQL Server 2014 版本支持的功能(Microsoft Docs)

    此选项对索引创建有直接影响，这可能是您看到索引创建时间差异很大的原因。

    想法

    您可能需要考虑重新设计索引。我建议阅读以下一些文章：

    附加阅读

    • 4

相关问题