如何设置Azure SQL自动重建索引？

Question

如何设置Azure SQL自动重建索引？

sql-serverazureazure-sql-databaseazure-sql-server

26

在本地 SQL 数据库中，经常会有一个维护计划，定期重建索引，尤其是在它没有太多使用时。如何在 Azure SQL DB 中设置呢？附注：我之前试过了，但由于找不到任何选项，我想也许他们会自动执行直到我读到这篇文章并进行了尝试：this post。

SELECT
 DB_NAME() AS DBName
 ,OBJECT_NAME(ps.object_id) AS TableName
 ,i.name AS IndexName
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 FROM sys.dm_db_partition_stats ps
 INNER JOIN sys.indexes i
 ON ps.object_id = i.object_id
 AND ps.index_id = i.index_id
 CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
 ORDER BY ps.object_id, ps.index_id

我发现我有需要维护的索引

- Ashkan S

4个回答

17

你可以使用Azure自动化来安排索引维护任务，如此处所述：使用Azure自动化重建SQL数据库索引 以下是步骤： 1）如果没有Automation Account，请通过访问https://portal.azure.com并选择New>Management>Automation Account来创建。

2) 创建完自动化帐户后，打开详细信息，现在单击“Runbooks > 浏览库”

在搜索框中键入“索引”，然后会出现运行簿“如果Azure数据库中的表高度碎片化，则对其进行索引”：

4) 请注意，这个运行簿的作者是微软的SC自动化产品团队。点击导入：

5) 导入运行簿后，现在让我们将数据库凭据添加到资产中。单击“资产”>“凭据”，然后单击“添加凭据...”按钮。

6) 设置凭据名称（稍后将在运行簿中使用）、数据库用户名和密码：

7) 现在再次单击Runbooks，然后从列表中选择“Update-SQLIndexRunbook”，并单击“编辑…”按钮。您将能够看到将要执行的PowerShell脚本：

8) 如果您想测试脚本，请点击“测试窗格”按钮，测试窗口将打开。输入所需参数，然后点击“开始”执行索引重建。如果出现任何错误，则在结果窗口中记录该错误。请注意，根据数据库和其他参数的不同，此过程可能需要很长时间才能完成:

9) 现在回到编辑器，点击“发布”按钮以启用 runbook。如果我们点击“开始”，则会出现一个窗口要求输入参数。但由于我们想要安排这个任务，所以我们将点击“计划”按钮：

10) 点击“计划”链接创建运行簿的新计划。我已经指定每周一次，但这将取决于您的工作量以及索引随时间增加其碎片化程度的情况。您需要根据自己的需求和在执行之间执行初始查询来微调计划：

11) 现在介绍参数和运行设置：

注意：您可以通过不同的设置使用不同的计划表来进行试验，例如为特定的表添加一个特定的计划表。

完成后，请根据需要更改日志记录设置：

- Alberto Morillo

你从中得到什么样的报告？它有用吗？你能手动运行这个脚本吗？我想运行一次并查看它的作用。我发现有很多索引重建脚本，但我不想完全黑盒化这个过程（至少在开始时不想）。 - Simon_Weaver

这个运行手册上写着“最后更新时间：2014年10月12日”。是经过尝试和测试的吗？还是已经非常过时了 :-/ 我希望是前者！ - Simon_Weaver

您可以将执行结果记录到Azure SQL数据库中。http://www.systemcentercentral.com/azure-automation-runbook-operation-logging/ - Alberto Morillo

1

Azure自动化很好，而且价格也可以忽略不计。

您可以选择以下几个选项：

1.创建一个执行SQL任务并通过SQL代理进行调度。该执行SQL任务应包含索引重建代码以及统计信息重建。

2.您还可以创建到SQLAZURE的链接服务器并创建SQL代理作业。要创建到Azure的链接服务器，您可以参考这个SO链接：我需要向MS Azure SQL Server添加链接服务器

- TheGameiswar

0

如@TheGamiswar建议，添加一个链接服务器，然后创建如下存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [LinkedServerName].[RemoteDB].[dbo].[sp_RebuildReorganizIndexes]   
AS  
BEGIN  
    ALTER INDEX PK_MyTable ON MyTable REBUILD WITH (STATISTICS_NORECOMPUTE = ON, ONLINE=ON);
    ALTER INDEX IX_MyTable ON MyTable REBUILD WITH (STATISTICS_NORECOMPUTE = ON, ONLINE=ON); --Nonclustered index

    ALTER INDEX PK_MyTable ON MyTable REORGANIZE;
    ALTER INDEX IX_MyTable ON MyTable REORGANIZE;
END

然后在您的链接服务器上使用“SQL Server代理”创建一个新作业和计划：

请参阅详细信息https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-job?view=sql-server-2017。

- user8128167

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Conor Cunningham MSFT · Accepted Answer

更新：请注意，工程团队已发布了更新的指南，以更好地规范本答案中的某些建议，并以更“官方”的方式呈现，一些客户要求如此。 SQL Server/DB索引指导。谢谢，Conor。

原回答：

我想指出的是，在 SQL Azure 中，大多数人根本不需要考虑重建索引。是的，B+ 树索引可能会变得碎片化，这可能会导致一些空间开销和与完美调整的索引相比的一些 CPU 开销。因此，有一些情况我们需要与客户合作来重建索引。（主要情况是当客户可能会因为当前架构中 SQL Azure 的磁盘空间受限而耗尽空间）。所以，我鼓励您退后一步，考虑使用 SQL Server 模型来管理数据库并没有“错”，但这可能或可能不值得您的努力。

（如果您最终确实需要重建索引，则可以使用其他帖子中发布的模型-它们通常是编写任务的良好模型。请注意，SQL Azure 托管实例还支持 SQL Agent，您也可以使用它来创建作业以编写维护操作，如果您选择这样做）。

以下细节可能有助于您决定是否适合索引重建：

您所引用的链接来自2013年的帖子。在该帖子之后，SQL Azure的架构完全重新设计。具体而言，硬件架构从基于本地旋转磁盘的模型移动到基于本地固态硬盘（在大多数情况下）的模型。因此，原始帖子中的建议已经过时。
在当前的架构中，您可能会遇到使用碎片化索引时空间不足的情况。您可以选择重建索引或暂时增加预留大小（这将花更多的钱），以支持更大的磁盘空间分配。[由于机器上的本地SSD空间有限，预留大小与机器比例大致相关。随着我们获得更大/更多驱动器的新硬件，您将拥有更多的规模扩展选项]。
与旋转磁盘相比，SSD碎片化的影响相对较小，因为随机IO的成本实际上并没有比顺序IO更高。遍历几个更多的B+树中间页面的CPU开销是适度的。我通常在平均情况下看到最多5-20％的开销（这可能或可能不需要定期重建，因为重建时的工作量影响要大得多）
如果您正在使用查询存储（在SQL Azure中默认启用），您可以评估特定索引重建是否真正提高了性能。在为构建和管理索引重建操作耗费时间之前，您可以通过此测试来查看工作负载是否得到了改进。
请注意，当前在SQL Azure中没有针对用户工作负载的数据库内资源治理。因此，如果您开始重建索引，则可能会消耗大量资源并影响主要工作负载。当然，您可以尝试将做事情安排在非工作时间进行，但对于全球客户众多的应用程序来说，这可能是不可能的。
此外，我要指出许多客户之所以进行索引重建作业是“因为他们想更新统计信息”。重新构建统计信息不是必要的。在最近的SQL Server和SQL Azure中，针对较大的表的统计信息更新算法更加积极，并且在查询最近插入的数据（自上次统计信息更新以来）的情况下，我们在估计基数的模型也已在后续兼容性级别中发生了变化。因此，通常情况下，客户甚至不需要进行任何手动统计信息更新。
最后，我要指出的是，过时的统计数据对计划选择回归的影响在历史上是显著的。对于重复查询，自动调整功能通过查询存储（query store）的引入，缓解了很多这种影响（如果它注意到与先前计划相比查询性能有大幅度下降，则强制使用先前计划）。

我向客户提供的官方建议是，除非他们已经展示了真正的需求（收益大于成本）或者他们是一个SaaS ISV，在弹性池中的多个数据库/客户或者在多租户数据库设计中调整工作负载，以便减少COGS或避免在非常大的数据库上用尽磁盘空间（如前所述），否则不必费心进行索引重建。在我们平台上最大的客户中，我们有时能够手动进行索引操作，并与客户一起完成，但我们通常不需要定期进行此类操作“以防万一”。 SQL团队的意图是让您根本不需要担心这个问题，只需关注您的应用程序即可。当然，我们总是可以添加或改进我们的自动机制，因此我完全允许某个单独的客户数据库可能需要这样的操作。除了我提到的情况之外，我自己没有看到任何问题，甚至那些情况也很少出现。

希望这为您提供了一些背景，以理解为什么平台尚未进行此操作-与其他紧迫需求相比，今天我们服务中绝大多数客户数据库都没有出现此问题。当然，我们每个规划周期都会重新审视需要构建的事物清单，并经常查看这样的机会。

祝你好运 - 无论结果如何，我希望这可以帮助你做出正确的选择。

诚挚地， Conor Cunningham 架构师，SQL