数据库引擎优化顾问

2017-03-06

Microsoft数据库引擎优化顾问（DTA）分析数据库，并提出建议，可用于优化查询性能。可以使用数据库引擎优化顾问来选择和创建一组最佳的索引、索引视图或表分区，而无需对数据库结构或 SQL Server 的内部有专家了解。使用 DTA，可以执行以下任务。

排查特定问题查询的性能问题
调整一组大型查询以适应一个或多个数据库
执行对物理设计潜在更改的探索性假设分析
管理存储空间

数据库引擎优化顾问权益

如果不完全了解数据库结构和针对数据库运行的查询，优化查询性能可能很困难。数据库引擎优化顾问可以通过分析当前查询计划缓存或通过分析创建的 Transact-SQL 查询的工作负荷来简化此任务，并推荐适当的物理设计。对于更高级的数据库管理员，DTA 公开了一种强大的机制，用于对不同的物理设计替代项执行探索模拟分析。 DTA 可以提供以下信息。

建议使用查询优化器分析工作负荷中的查询，为数据库提供最佳索引组合。
建议对工作负荷中引用的数据库进行对齐或非对齐分区。
为负载中引用的数据库推荐索引视图。
分析建议的更改的影响，包括索引使用情况、表之间的查询分布以及工作负荷中的查询性能。
建议为一组问题查询优化数据库的方法。
允许通过指定高级选项（如磁盘空间约束）对建议进行自定义。
提供汇总实现给定工作负荷建议的效果的报告。

数据库引擎优化顾问旨在处理以下类型的查询工作负荷。

仅限联机事务处理（OLTP）查询
仅联机分析处理（OLAP）查询
混合 OLTP 和 OLAP 查询
查询密集型工作负荷（查询多于数据修改）
更新密集型工作负荷（数据修改比查询更多）

DTA 组件和概念

数据库引擎优化顾问图形用户界面
一个易于使用的接口，可在其中指定工作负荷并选择各种优化选项。

dta 效用
数据库引擎优化顾问的命令提示符版本。通过 dta 实用工具，您可以在应用程序和脚本中使用数据库引擎优化顾问功能。

工作量
用于优化数据库的 Transact-SQL 脚本文件、跟踪文件或跟踪表，其中包含具有代表性的工作负荷。从 SQL Server 2012 开始，可以将计划缓存指定为工作负荷。

XML 输入文件
数据库引擎优化顾问可用于优化工作负荷的 XML 格式文件。 XML 输入文件支持 GUI 或 dta 实用工具中不可用的高级优化选项。

局限性与限制

数据库引擎优化顾问具有以下限制和限制。

它不能添加或删除唯一索引，或者用于强制实施 PRIMARY KEY 或 UNIQUE 约束的索引。
它无法分析设置为单用户模式的数据库。
如果为超出实际可用空间的优化建议指定最大磁盘空间，数据库引擎优化顾问将使用指定的值。但是，执行建议脚本来实现该脚本时，如果未首先添加更多磁盘空间，脚本可能会失败。可以使用 dta 实用工具的 -B 选项指定最大磁盘空间，或者通过在“高级优化选项”对话框中输入值来指定。
出于安全原因，数据库引擎优化顾问无法优化驻留在远程服务器上的跟踪表中的工作负荷。若要解决此限制，可以使用跟踪文件而不是跟踪表，或将跟踪表复制到远程服务器。
当你施加约束（例如，当你指定优化建议的最大磁盘空间时（使用 -B 选项或 高级优化选项 对话框）时，数据库引擎优化顾问可能会被迫删除某些现有索引。在这种情况下，生成的数据库引擎优化顾问建议可能会产生负面的预期改进。
当您指定一个约束来限制优化时间（通过使用-A选项与dta实用工具，或者在优化选项选项卡上勾选限制优化时间），数据库引擎优化顾问可能会超过该时间限制，以生成准确的预期改进，并提供关于到目前为止已使用的工作负荷部分的分析报告。
在以下情况下，数据库引擎优化顾问可能不会提出建议：
1. 要优化的表包含的数据页少于 10 个。
2. 建议的索引在当前物理数据库设计中无法提供足够的查询性能改进。
3. 运行数据库引擎优化顾问的用户不是db_owner数据库角色或sysadmin固定服务器角色的成员。工作负荷中的查询在运行数据库引擎优化顾问的用户的安全上下文中进行分析。用户必须是db_owner数据库角色的成员。
数据库引擎优化顾问将优化会话数据和其他信息 msdb 存储在数据库中。如果对 msdb 数据库进行了更改，可能会丢失调优会话数据。若要消除此风险，请为 msdb 数据库实施适当的备份策略。

性能注意事项

数据库引擎优化顾问可以在分析期间消耗大量处理器和内存资源。若要避免降低生产服务器的速度，请遵循以下策略之一：

在服务器免费时优化数据库。数据库引擎优化顾问可能会影响维护任务性能。
使用测试服务器/生产服务器功能。有关详细信息，请参阅 “减少生产服务器优化负载”。
仅指定希望数据库引擎优化顾问分析的物理数据库设计结构。数据库引擎优化顾问提供了许多选项，但仅指定必要的选项。

依赖于xp_msver扩展存储过程

数据库引擎优化顾问依赖于 xp_msver 扩展存储过程来提供完整的功能。默认情况下，此扩展存储过程处于打开状态。数据库引擎优化顾问使用此扩展存储过程提取要优化的数据库所在的计算机上的处理器数和可用内存。如果 xp_msver 不可用，数据库引擎优化顾问假定运行数据库引擎优化顾问的计算机的硬件特征。如果运行数据库引擎优化顾问的计算机的硬件特征不可用，则假定有一个处理器和 1024 兆字节（MB）内存。

此依赖项会影响分区建议，因为建议的分区数取决于这两个值（处理器数和可用内存）。使用测试服务器优化生产服务器时，依赖项也会影响优化结果。在此方案中，数据库引擎优化顾问使用 xp_msver 从生产服务器提取硬件属性。优化测试服务器上的工作负荷后，数据库引擎优化顾问使用这些硬件属性生成建议。有关详细信息，请参阅 xp_msver (Transact-SQL)。