主页 / dba / 问题 / 131444
Accepted
Craig
Asked: 2016-03-07 14:36:51 +0800 CST

如何开始索引新的数据仓库

  • 772

我正在构建一个数据仓库（SQL Server 2012，Kimball Dimensional modeling，750GB），它将向报告（SSRS）、多维数据集（SSAS）和各种数据馈送提供数据。

我打算使用索引调整工具（可能是sp_BlitzIndex），但我该如何开始呢？我是否从没有索引开始，然后让 sp_BlitzIndex 告诉我一段时间后要使用哪些索引？还是从明显的索引开始，然后使用 sp_BlitzIndex 构建？

我计划在每个主键上添加一个聚集索引，在每个外键上添加一个非聚集索引——然后让 sp_BlitzIndex 建议不太明显的选择。但也许我应该把它全部交给 sp_BlitzIndex。

index index-tuning

1 个回答

  1. Best Answer

    您从主键上的聚集索引开始的计划是一个很好的开始。

    理想情况下，您的聚簇索引（开始）应该是INTEGER IDENTITYDW 的代理键：

    1. 保持聚集索引尽可能窄，
    2. 这样维度表和事实表就不是堆了。

    当且仅当您期望一个表超过 2,147,483,647 行（最大 INT 值）时，使用BIGINT IDENTITY代替INT. （请参阅数值数据类型）。

    仔细考虑每个外键上的非聚集索引。将 NCI 放在每个外键上可能没有必要，而且可能弊大于利。相反，只专注于在查询中的 JOIN 或 WHERE 子句中使用它们的外键上创建非聚集索引。但是，不要只为每个 FK 创建一个非聚集索引——如果一个查询在连接中使用多个 FK，请考虑创建一个 NCI 来支持这些连接。使用索引键列的顺序来查看哪个效果最好。测试测试测试。

    编辑 9/26/2016 这是 Kendra Little 讨论外键索引的博客文章： http ://www.littlekendra.com/2016/09/22/indexing-foreign-keys-guidelines/

    堆不利于 SELECT / UPDATE / DELETE 性能（即 SSRS 和 SSAS 查询），尤其是查询中的任何类型的 JOIN 或 WHERE 子句，因为堆上的所有查询都是表扫描——没有堆搜索之类的东西。碎片通常是 HEAP 表的问题。

    堆非常适合 INSERT 性能。堆通常被认为是插入新数据的最快速度，因为没有索引更改（即更新索引的 b 树）或对 SQL Server 需要管理的数据进行排序（如果有索引）。这对于 STAGING 表最有用。（请参阅堆表的有效使用场景是什么）。在插入新数据时，HEAPS 优于 CLUSTERED INDEXES 的程度会有所不同。

    编辑：2016 年 9 月 7 日这是 Daniel Hutmacher 的博客文章，讨论了堆的其他用例： https ://sqlsunday.com/2016/09/01/compelling-case-for-heaps/

    请注意，聚集索引（和非聚集索引）会降低（假设的）每日夜间负载的性能，因为任何数据更改（INSERT、UPDATE、DELETES、MERGE）都会导致使用这些列的索引具有更改的数据（I' ll 将 INSERTed、UPDATEd、DELETEd、MERGEd 组合为“已更改”数据）也将被更新，这会导致 I/O 写入索引。这可能对性能造成很小的影响；这可能会对性能造成很大影响——这取决于表的大小和正在更改的数据量 (I/U/D/M) 以及其他因素。一个“小”维度表可能会很好地为 ETL 保留聚集索引和非聚集索引。“大”维度表可能会受益于禁用/重新构建方法。测试测试测试。）。

    一种方法（不一定是推荐，而是一种选择）来对抗具有现有索引的（大）表的数据更改的性能降低是：

    1. 在加载数据之前禁用或删除表的索引作为 SSIS ETL 作业的一部分，以优化 INSERT 性能，
    2. 然后在数据加载完成后重建或重新创建索引。

    如果您尝试这条路线，请测试并跟踪性能，以确保此方法确实比将数据加载到带有索引的表中更快——它可能不会更快。测试测试测试。

    在性能调优查询时， SET STATISTICS IO, TIME ON;在您的 SELECT 查询之前进行测量。专注于使用非聚集索引减少“逻辑读取”。记录和跟踪索引之前和索引之后的逻辑读取；保留历史。如果你成功了，你可以向你的团队和/或老板展示这些数字。

    您的数据仓库将提供 SSRS 报告和 SSAS 多维数据集处理运行。创建支持/调整这些查询的非聚集索引（也许 SSRS 和 SSAS 使用的查询是视图或存储过程？）。换句话说，调整您的 SSRS/SSAS 查询（通过非聚集索引）。您需要让您的 SSRS 报告视图或过程查询代码在计划缓存的一段时间内（例如，稳定执行 1 周）保持静态/稳定（即没有代码更改），然后继续进行调整。您可以更早地做到这一点，但您可能不会得到相同的结果。

    为了优化 SSAS，在 SSAS 多维数据集处理运行期间使用 Profiler（或扩展事件，如果您愿意）来捕获多维数据集在从 DW 提取数据时使用的 SELECT 查询——这可能不是您所期望的。这些是您为改进 SSAS 多维数据集处理时间而调整的查询。完成后不要忘记关闭分析器，因为它确实会增加一些开销。

    索引调整 DW 的好处是，您还可以同时调整 SSRS 报告和 SSAS 多维数据集处理运行。

    索引性能调优是一种持续的实践；你永远不会完成。如果出现以下情况，您可能需要更改索引策略：

    • 新表被创建/加入
    • 视图/存储过程的新 where 子句或新联接
    • 新列被添加到现有表中
    • 数据更改（即现在比您第一次调整索引时多了 100 万、1000 万或 1 亿行）

    索引性能调优可能具有挑战性和技巧性，但当您获得巨大的性能胜利时会非常有回报。我鼓励您搜索专门讨论索引策略的免费网络研讨会。了解不同类型的非聚集索引（即“覆盖”索引、过滤索引、列存储索引等）以及它们如何以及何时有用，以及何时它们可能弊大于利。

    当您准备好超越免费网络研讨会时，请考虑寻找要购买的书籍和/或付费培训以进一步提高您的索引技能——也许您的雇主会为培训和/或书籍付费。由于您提到了 Brent Ozar 团队的 sp_BlitzIndex，Kendra Little 完成了很棒的面对面付费课程（我参加过）并录制了有关索引性能调整的网络研讨会。还有很多其他优秀的 MVP 和优秀的 SQL 专业人士也提供索引培训。

    祝你好运，我希望这是有帮助和有见地的。

    • 2

相关问题