如何确定需要创建哪些索引？有什么建议吗？涉及到IT技术。

Question

我现在的情况是需要提高大约75个用于报告的存储过程（由他人创建）的性能。 我解决问题的第一步是创建了大约6个非规范化表，这些表将用于大部分报告。 现在我面临着一个相当艰巨的任务，即确定我应该创建哪些索引以最大程度地提高这些存储过程的性能。

我很好奇是否有人有任何建议，可以找出哪些列应包含在索引中？ 我考虑使用Profiler / DTA，或者可能像下面的查询一样设计某种查询来查找流行的列。

SELECT name, Count(so.name) as hits, so.xtype
from syscomments as sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE   sc.text like '%ColumnNamme%'
AND xtype = 'P'
Group by name,so.xtype
ORDER BY hits desc

如果你有任何想法可以帮助我避免手动查找这75个过程，请告诉我。

另外，对于这个数据库来说，插入操作每天只执行一次，因此插入性能对我来说并不是一个很大的问题。

- Abe Miessler

Answer 1

任何关于如何确定需要创建哪些索引的建议吗？

是的！请询问Sql Server告诉您。

Sql Server自动为可以用于提高性能的索引保留统计信息。这已经在后台为您进行了。请参见此链接：
http://msdn.microsoft.com/en-us/library/ms345417.aspx 尝试运行像这样的查询（直接从msdn中取出）：

SELECT mig.*, statement AS table_name,
    column_id, column_name, column_usage
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;

请注意。我见过人们把缺失索引视为圣经，并使用它们来推出他们实际上并不需要的一堆索引。索引具有成本，包括插入、更新和删除时的维护成本，以及磁盘空间和内存使用。要真正准确地利用这些信息，您需要在任何更改之前和之后分析关键过程的实际执行时间，以确保索引（单个或累积）的好处不会被成本所超过。

- Joel Coehoorn

Answer 2

如果你知道所有的活动都来自这75个存储过程，那么我建议使用分析器来跟踪哪些存储过程执行时间最长且调用次数最多。一旦你知道了它们是哪些过程，就要查看这些过程并确定在Where子句和JOIN ON部分中使用最频繁的列是哪些。很可能，这些列就是你想要放置非聚集索引的列。如果一组列经常一起使用，那么你很有可能会为该组制作一个非聚集索引。表可以有许多非聚集索引（250），但你可能不想在上面放置太多。我认为你会发现数据一遍又一遍地在同一列上搜索和连接。请记住80/20法则。你可能会在前20%的工作中获得80%的速度提升。当你添加了索引但几乎没有提高速度时，就应该停止了。

- Ben Hoffman

Answer 3

我同意bechbd的观点-使用你数据库流量的良好样本（通过在生产系统上运行服务器跟踪以获得最佳快照），并让数据库调整顾问分析该样本。

我同意你的观点-不要“盲目”依赖数据库调整顾问告诉你要做什么-这只是一个建议，但DTA不能考虑到所有情况。当然，通过添加索引，您可以加速查询-但同时会减慢插入和更新。

另外-为了真正找出某些东西是否有帮助，您需要实施它，再次测量并比较-这确实是唯一可靠的方法。涉及太多变量和未知因素。

当然，你可以使用DTA来微调单个查询以表现出色-但这可能会忽略这个查询每周只需调用一次，或者通过调整此一个查询并添加索引，你会影响其他查询。

索引优化总是一个平衡、权衡和试错的过程-它不是一个精确的科学，没有公式和食谱书来严格确定你所需要的。

- marc_s

---

你提供了很好的建议。我只想指出，我从未见过一个数据库是写入操作如此频繁，以至于添加索引的读取优势会明显影响写入操作。因此，应该添加所有的索引，然后定期检查哪些索引没有被使用。 - Spivonious

Answer 4

您可以在SSMS中使用SQL Server Profiler来查看表格的调用情况，然后使用Profiler中的Database Tuning Tool来至少让您朝着正确的方向前进。我知道大多数DBA可能会因为我推荐这个而尖叫，但对于像我这样的非DBA类型，它至少给了我们一个起点。

- bechbd

---

是的，那是我正在考虑的选项之一。我听很多人说过，不能依靠这种方法来生成索引。 - Abe Miessler

我已经进行了相当多的调整，这是我使用过并取得巨大成功的方法。从分析器中，您可以获得各种有用的信息，特别是CPU和磁盘IO使用情况。这将向您展示哪些存储过程最慢（或者至少是那些可能从调整中受益最多的存储过程）。然后，您可以打开这些存储过程并查看查询计划 - 尽管是否真的有人理解Sql查询计划呢？ - MrTelly

大多数人认为使用分析器/数据来了解我需要什么索引是一个好主意。如果在我的生产服务器上运行分析器数小时，可能会遇到什么问题？ - Abe Miessler

在您的实时系统中，要注意跟踪的优先级。如果太高，它们将干扰实时站点的性能。我相信，如果您从分析器 UI 运行跟踪，它会自动以高优先级运行，因此我建议不要使用 UI 在实时系统上运行跟踪（但命令行版本可以）。 - adrianbanks

Answer 5

如果这只是一个报告数据库，并且您需要性能，请考虑转移到数据仓库设计。当涉及到报告时，星型或雪花模式将比非规范化的关系设计表现更好。

- Jeff Hornby