如何设置Azure SQL自动重建索引？ [英] How to set Azure SQL to rebuild indexes automatically?

问题描述

在内部SQL数据库中，通常有一个维护计划，用于在索引使用不多的情况下偶尔重建索引。

PS：我之前曾尝试过，但由于找不到任何选择，所以我想也许是直到我阅读

解决方案

我将指出，大多数人根本不需要考虑在SQL Azure中重建索引。是的，B +树索引可能会碎片化，是的，与具有经过优化的索引相比，这可能会导致一些空间开销和一些CPU开销。因此，在某些情况下，我们与客户合作来重建索引。 （主要方案是客户当前可能会用完空间，因为由于当前体系结构，SQL Azure中的磁盘空间有所限制）。因此，我鼓励您退后一步，认为使用SQL Server模型管理数据库并不是错误的，但是它可能值得也可能不值得您付出。

（如果最终确实需要重建索引，欢迎使用其他张贴者在此处发布的模型-它们通常是脚本任务的良好模型。请注意，SQL Azure托管实例还支持SQL Agent，您也可以

以下一些详细信息可以帮助您确定是否适合进行索引重建： p>

• 您引用的链接来自2013年的一篇文章。该文章之后，SQL Azure的体系结构已完全重做。具体来说，硬件体系结构从基于本地旋转磁盘的模型转变为基于本地SSD的模型（在大多数情况下）。因此，原始文章中的指导已过时。


• 在当前体系结构中，您可能会遇到一些情况，即碎片索引可能会耗尽空间。您可以选择重建索引或暂时移到较大的保留大小（这将花费更多的钱），以支持更大的磁盘空间分配。 [由于机器上的本地SSD空间有限，因此保留大小大致与机器的比例相关。随着我们获得具有更大/更多驱动器的较新硬件，您将拥有更多的扩展选择。]


• 与旋转磁盘相比，SSD碎片影响相对较低，因为随机IO的成本并不高确实比顺序的更高。多走一些B +树中间页的CPU开销是适度的。通常情况下，我通常会看到最多5-20％的开销（这可能会或可能无法证明定期重建的合理性，因为重建时对工作量的影响更大）


• 如果使用查询存储（在SQL Azure中默认情况下处于启用状态），您可以评估特定的索引重建是否明显地有助于提高性能。您可以进行此测试，以检查您的工作负载是否有所改善，然后再花时间自行构建和管理索引重建操作。


• 请注意，当前没有数据库内资源SQL Azure内部用于用户工作负载的治理。因此，如果开始重建索引，最终可能会消耗大量资源并影响您的主要工作量。当然，您可以尝试将时间安排在下班时间进行，但是对于在全球拥有大量客户的应用程序来说，这可能是不可能的。


• 此外，我会注意到很多客户拥有索引重建作业，因为他们希望更新统计信息。不必为了重建统计信息而重建索引。在最近的SQL Server和SQL Azure中，用于统计信息更新的算法在较大的表上变得更加激进，并且在客户查询最近插入的数据（自上次统计信息更新以来）的情况下，我们如何估算基数的模型已更改，以实现更高的兼容性。水平。因此，通常情况下，客户甚至根本不需要进行任何手动统计信息更新。


• 最后，我将注意到统计信息过时的影响是从历史上看，您会获得计划选择的回归。对于重复查询，通过在查询存储区中引入自动调整功能可以减轻很多影响（如果自动调整功能与以前的计划相比，如果发现查询性能出现较大的下降，则可以强制执行先前的计划）。

我给客户的官方建议是不要打扰索引重建，除非他们已经展示了第1层应用程序实际需求（收益大于成本），或者它们是SaaS ISV，他们试图在弹性池或多租户数据库设计中的许多数据库/客户上调整工作负载，以便减少COGS或避免用完很大的数据库上的磁盘空间（如前所述）。在平台上拥有的最大客户中，有时 可以看到与客户手动进行索引操作的价值，但我们通常不需要做常规的工作，而仅需如果。 SQL团队的意图是，您根本不需要理会这些，而只需专注于您的应用即可。当然，总有一些事情可以添加或改进到我们的自动机制中，因此，我完全允许单个客户数据库可能需要执行此类操作。除了我提到的案例，我还没有见过任何我自己的东西，即使这些问题也很少出现。

我希望这可以为您提供一些背景信息，以了解为什么不是这样尚未在平台上完成-与其他紧迫的需求相比，对于今天我们服务中的绝大多数客户数据库而言，这并不是一个问题。当然，我们会重新审查每个计划周期所需的工作清单，并且会定期检查类似的机会。

祝您好运-无论您在这里取得什么成果，我希望这能帮助您做出正确的选择。

此致，
Conor Cunningham
建筑师，SQL

In on premise SQL databases, it is normal to have a maintenance plan for rebuilding the indexes once in a while, when it is not being used that much.

How can I set it up in Azure SQL DB?

P.S: I tried it before, but since I couldn't find any options for that, I thought maybe they are doing it automatically until I've read this post and tried:

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

And found out that I have indexes that need maintaining

解决方案

I'll point out that most people don't need to consider rebuilding indexes in SQL Azure at all. Yes, B+ Tree indexes can become fragmented, and yes this can cause some space overhead and some CPU overhead compared to having perfectly tuned indexes. So, there are some scenarios where we do work with customers to rebuild indexes. (The primary scenario is when the customer may run out of space, currently, as disk space is somewhat limited in SQL Azure due to the current architecture). So, I will encourage you to step back and consider that using the SQL Server model for managing databases is not "wrong" but it may or may not be worth your effort.

(If you do end up needing to rebuild an index, you are welcome to use the models posted here by the other posters - they are generally fine models to script tasks. Note that SQL Azure Managed Instance also supports SQL Agent which you can also use to create jobs to script maintenance operations if you so choose).

Here are some details that may help you decide if you may be a candidate for index rebuilds:

• The link you referenced is from a post in 2013. The architecture for SQL Azure was completely redone after that post. Specifically, the hardware architecture moved from a model that was based on local spinning disks to one based on local SSDs (in most cases). So, the guidance in the original post is out of date.
• You can have cases in the current architecture where you can run out of space with a fragmented index. You have options to rebuild the index or to move to a larger reservation size for awhile (which will cost more money) that supports a larger disk space allocation. [Since the local SSD space on the machines is limited, reservation sizes are roughly linked to proportions of the machine. As we get newer hardware with larger/more drives, you have more scale-up options].
• SSD fragmentation impact is relatively low compared to rotating disks since the cost of a random IO is not really any higher than a sequential one. The CPU overhead of walking a few more B+ Tree intermediate pages is modest. I've usually seen an overhead of perhaps 5-20% max in the average case (which may or may not justify regular rebuilds which have a much bigger workload impact when rebuilding)
• If you are using query store (which is on by default in SQL Azure), you can evaluate whether a specific index rebuild helps your performance visibly or not. You can do this as a test to see if your workload improves before bothering to take the time to build and manage index rebuild operations yourself.
• Please note that there is currently no intra-database resource governance within SQL Azure for user workloads. So, if you start an index rebuild, you may end up consuming lots of resources and impacting your main workload. You can try to time things to be done off-hours, of course, but for applications with lots of customers around the world this may not be possible.
• Additionally, I will note that many customers have index rebuild jobs "because they want stats to be updated". It is not necessary to rebuild an index just to rebuild the stats. In recent SQL Server and SQL Azure, the algorithm for stats update was made more aggressive on larger tables and the model for how we estimate cardinality in cases where customers are querying recently inserted data (since the last stats update) have been changed in later compatibility levels. So, it is often the case that the customer doesn't even need to do any manual stats update at all.
• Finally, I will note that the impact of stats being out of date was historically that you'd get plan choice regressions. For repeated queries, a lot of the impact of this was mitigated by the introduction of the automatic tuning feature over query store (which forces prior plans if it notices a large regression in query performance compared to the prior plan).

The official recommendation that I give customers is to not bother with index rebuilds unless they have a tier-1 app where they've demonstrated real need (benefits outweigh the costs) or where they are a SaaS ISV where they are trying to tune a workload over many databases/customers in elastic pools or in a multi-tenant database design so they can reduce their COGS or avoid running out of disk space (as mentioned earlier) on a very big database. In the largest customers we have on the platform, we sometimes see value in doing index operations manually with the customer, but we often do not need to have a regular job where we do this kind of operation "just in case". The intent from the SQL team is that you don't need to bother with this at all and you can just focus on your app instead. There are always things that we can add or improve into our automatic mechanisms, of course, so I completely allow for the possibility that an individual customer database may have a need for such actions. I've not seen any myself beyond the cases I mentioned, and even those are rarely an issue.

I hope this gives you some context to understand why this isn't being done in the platform yet - it just hasn't been an issue for the vast majority of customer databases we have today in our service compared to other pressing needs. We revisit the list of things we need to build each planning cycle, of course, and we do look at opportunities like this regularly.

Good luck - whatever your outcome here, I hope this helps you make the right choice.

Sincerely, Conor Cunningham Architect, SQL

这篇关于如何设置Azure SQL自动重建索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！