本文将探讨Azure SQL数据库的自动索引顾问（CREATE_INDEX、DROP_INDEX）。
简介
Azure SQL数据库使其用户能够专注于数据库对象，调整查询，而无需管理底层基础设施资源。数据库专业人员总是关注索引，以提高企业内部和Azure云数据库的查询性能。这些索引帮助查询优化器引擎快速检索数据并为客户请求提供服务。你可能在实际执行计划中或使用DMVsys.dm_db_missing_index_details看到过对企业内部的SQL Server的缺失索引建议。
DBA绝对不能遵循这些缺失索引的建议，因为如果索引设计不当，它可能会妨碍其他查询。这些未使用的索引会增加WRITELOG等待统计，因为索引需要额外的日志记录。同样的，一个新的索引可以减少数据更新，而不给读取查询提供任何好处。
这篇文章讨论了Azure SQL数据库的自动索引顾问或索引创建。
Azure数据库的自动数据库调优功能
Azure SQL数据库有一个内置的服务，可以连续监测查询，并根据查询的工作量为索引提供建议。用户可以审查这些索引并手动应用，或者启用自动调优选项，以便Azure自动应用这些索引。Azure数据库不断地从工作负载行为中学习，并确保数据库有最佳的索引。它使用内置的智能和高级配置的规则来预测最佳索引。利用这一功能，Azure SQL数据库确保优化大多数工作负载（查询），用于读取数据，对更新数据影响最小。
查询存储默认为Azure SQL数据库所启用。它为CREATE INDEX、DROP INDEX提供建议，可以提高查询性能。Azure SQL数据库在使用自动调整创建索引后监控查询性能。自动索引使用来自缺失索引DMV的数据，它使用查询存储监控建议的时间。如果它没有检测到任何性能改善，它就会自动恢复建议。
默认情况下，自动调整索引的Azure默认值如下。
- CREATE_INDEX = 禁用
- DROP_INDEX = 禁用
你可以在服务器级别或数据库级别启用这些自动调整选项。如果你在服务器级别配置了设置，所有数据库将继承这些设置。不过，你可以选择使用Azure门户或Azure CLI在单个数据库上禁用它们。
要查看默认的自动索引调整，请在Azure门户中导航到智能性能->Azure SQL Server的自动调整。如下图所示，默认配置是继承Azure的默认值。
你也可以为单个数据库定制这些自动调整。在Azure门户中，导航到性能建议->自动。
在这里，你可以得到以下继承选项供你选择。
- 服务器：在这个选项中，Azure SQL数据库从Azure服务器获得自动调整配置
- Azure默认：它使用Azure默认配置（创建索引=关闭，删除索引=关闭）
- 不继承：你可以为特定的数据库指定调整配置
Azure根据其潜在的性能影响来组合建议。
- 高影响：这些建议提供了最显著的性能改进
- 中等影响：这些建议明显地提高了性能
- 低影响：这些建议提供了相当大的性能影响
下面的图片显示了一些高影响的创建和删除索引建议。
你可以打开创建索引建议，它给出的信息有：表名、包含的列、影响、需要的磁盘空间、状态。点击查看脚本，检查索引脚本进行审查，或者手动部署。
图片参考。微软文档
Azure可能不会立即自动应用该建议。它在使用这些建议之前会监控工作负载和资源消耗。你可以观察有以下数值的状态栏。
状态 | 说明 |
成功 | Azure数据库成功应用了该建议。它在实施后也验证了性能。 |
待定 | 该建议已安排执行。 |
正在执行 | 索引建议的应用过程正在运行。 |
验证中 | Azure 已经应用了索引建议。但是，其验证正在进行中。 |
错误 | 在实施索引建议时出现了错误。这可能是由于瞬时问题或模式变化造成的。 |
正在恢复 | Azure应用了索引建议，但在验证过程中，它没有观察到性能的改善。因此，它正在恢复（回滚）这些更改。 |
已恢复 | 回滚过程已经完成。Azure数据库在为特定对象编制索引方面已经恢复到了原来的状态。 |
查出Azure索引建议创建的索引
可以查询索引视图sys.indexes，找出由Azure索引建议自动创建的索引。查询此视图并查看auto_created 列。
- Auto_created 1: 由自动调整创建的索引
- Auto_created 0：手动创建的索引
下面的截图引用了Azure数据库中的sys.indexes视图。
SELECT auto_created,object_name(object_id) AS ObjectName, type_desc ,*
FROM sys.indexes
WHERE object_name(object_id) LIKE 'Sales%'
- 注意：性能调优建议或自动调优是一个在线过程。Azure不会为应用这些建议而使数据库脱机。
为Azure SQL数据库启用或禁用自动调整配置
可以查询系统视图sys.database_automatic_tuning_options来检查自动调整选项配置。
SELECT name, desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options
下面的截图显示，默认情况下，CREATE_INDEX和DROP_INDEX选项是禁用的，其原因是这个配置是从Azure SQL Server中继承的。
- 注意：在这里，我们确实看到一个额外的选项MAINTAIN_INDEX。但是，到现在为止，还没有围绕它的官方文档。它可能与Azure SQL数据库未来的改进有关。
但是，如果你把单个数据库的继承属性修改为Azure默认值，那么reason_desc的值就会变成Auto_Configured。
- 注意：如果Azure由于资源利用率高而禁用了自动调整，或者查询存储状态变为只读，你可能会在reason_desc列看到系统禁用 的值。这表明Azure已经暂时禁用了Azure SQL数据库的自动调整功能。
ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO /* Allowed values : Auto, INHERIT, CUSTOM */
索引建议DMV - sys.dm_db_tuning_recommendations
你也可以利用动态管理视图sys.dm_db_tuning_recommendations来识别查询性能的退步和对CREATE_INDEX和DROP_INDEX的建议。即使你没有启用数据库的自动调整功能，你也可以查询这个DMV以获得建议。
下面的查询可以帮助你识别查询和它们的建议。
WITH DbTuneRec
AS (SELECT ddtr.reason,
ddtr.score,
pfd.query_id,
pfd.regressedPlanId,
pfd.recommendedPlanId,
JSON_VALUE(ddtr.state,
'$.currentValue') AS CurrentState,
JSON_VALUE(ddtr.state,
'$.reason') AS CurrentStateReason,
JSON_VALUE(ddtr.details,
'$.implementationDetails.script') AS ImplementationScript
FROM sys.dm_db_tuning_recommendations AS ddtr
CROSS APPLY
OPENJSON(ddtr.details,
'$.planForceDetails')
WITH (query_id INT '$.queryId',
regressedPlanId INT '$.regressedPlanId',
recommendedPlanId INT '$.recommendedPlanId') AS pfd)
SELECT qsq.query_id,
dtr.reason,
dtr.score,
dtr.CurrentState,
dtr.CurrentStateReason,
qsqt.query_sql_text,
CAST(rp.query_plan AS XML) AS RegressedPlan,
CAST(sp.query_plan AS XML) AS SuggestedPlan,
dtr.ImplementationScript
FROM DbTuneRec AS dtr
JOIN sys.query_store_plan AS rp
ON rp.query_id = dtr.query_id
AND rp.plan_id = dtr.regressedPlanId
JOIN sys.query_store_plan AS sp
ON sp.query_id = dtr.query_id
AND sp.plan_id = dtr.recommendedPlanId
JOIN sys.query_store_query AS qsq
ON qsq.query_id = rp.query_id
JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id;
重要性点
CREATE_INDEX
Azure使用了一个备份策略，防止它在高资源利用率期间应用索引建议。 它考虑了CPU、日志IO、数据IO和存储。
- 如果CPU、数据和日志IO的利用率在前30分钟内大于80%，Azure就会推迟创建索引建议
- 假设创建索引后，可用的存储空间小于10%。在这种情况下，索引推荐状态会变为错误。一旦有了空间，它可能会根据未来的工作负载和利用率再次处理该索引
DROP_INDEX
除了新的索引外，Azure也会分析现有的索引性能。如果该索引没有被使用，它就会提出放弃该索引的建议。在以下情况下，它会考虑丢弃索引。
- 该索引是重复的（相同的索引和包含的列）
- 该索引长期不使用，长达93天之久。
Azure SQL数据库也会在丢弃索引后自动监控工作负载性能。如果性能下降，它会恢复建议（重新创建索引）。
总结
Azure SQL数据库的智能自动调整机制会自动推荐合适的索引，以根据工作负载和性能审查来创建或删除。如果你不是专家，Azure会负责索引管理并运行优化的查询，以提高资源利用率和吞吐量。
