问题描述
在内部SQL数据库中,通常有一个维护计划,用于在索引使用不多的情况下偶尔重建索引。
PS:我之前曾尝试过,但由于找不到任何选择,所以我想也许是直到我阅读
解决方案在内部SQL数据库中,通常有一个维护计划,用于在索引使用不多的情况下偶尔重建索引。
PS:我之前曾尝试过,但由于找不到任何选择,所以我想也许是直到我阅读
解决方案我将指出,大多数人根本不需要考虑在SQL Azure中重建索引。是的,B +树索引可能会碎片化,是的,与具有经过优化的索引相比,这可能会导致一些空间开销和一些CPU开销。因此,在某些情况下,我们与客户合作来重建索引。 (主要方案是客户当前可能会用完空间,因为由于当前体系结构,SQL Azure中的磁盘空间有所限制)。因此,我鼓励您退后一步,认为使用SQL Server模型管理数据库并不是错误的,但是它可能值得也可能不值得您付出。
(如果最终确实需要重建索引,欢迎使用其他张贴者在此处发布的模型-它们通常是脚本任务的良好模型。请注意,SQL Azure托管实例还支持SQL Agent,您也可以
以下一些详细信息可以帮助您确定是否适合进行索引重建:
p>我给客户的官方建议是不要打扰索引重建,除非他们已经展示了第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 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 CunninghamArchitect, SQL
这篇关于如何设置Azure SQL自动重建索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!