为什么我的Azure SQL（SQL Server）数据库会在一段时间内过载数据IO？

Question

为什么我的Azure SQL（SQL Server）数据库会在一段时间内过载数据IO？

10

我正在运行一个Azure SQL数据库，使用的是S2版（50 DTUs）。服务器的正常使用情况通常在10%的DTU左右。然而，这个服务器经常会出现一种状态，即数据库的DTU使用率会在几个小时内达到85-90%。然后突然又回到正常的10%使用率。

在应用程序对服务器进行查询时，在这种过载状态下似乎仍然能够快速运行。

我可以将服务器从S2扩展到任何规模（例如S3），然后再缩小回S2，它似乎可以清除掉它所卡住的状态。但是几个小时后，它又会重复相同的过载状态循环。另一个奇怪的事情是，我注意到如果我将这个服务器一直运行在S3计划（100 DTU）上，我就没有观察到这种行为。它似乎只在我将数据库缩小到S2计划（50 DTU）时发生。在S3计划中，我的DTU使用率始终保持在5-10%。显然是被低效利用了。

我已经查看了Azure SQL查询报告，寻找异常查询，但实际上并没有看到什么异常，并且显示我的查询使用了我预期的资源。

从这里我们可以看到，所有的使用情况都来自于Data IO。如果我将这里的性能报告更改为按最大值显示顶部的Data IO查询，我们会看到这样的结果：

看这些长时间运行的查询似乎指向统计更新。并没有什么来自我的应用程序的运行。例如，查询16302显示：

SELECT StatMan([SC0], [SC1], [SC2], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM (SELECT [UserId] AS [SC0], [OrganizationId] AS [SC1], [Id] AS [SC2] FROM [dbo].[Cipher] TABLESAMPLE SYSTEM (1.250395e+000 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SC2], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

但另一方面，报告还显示这些查询仅占服务器数据IO使用的很小比例（<4%）。作为常规维护的一部分，我每周还会对整个数据库进行统计更新（和索引重建）。这里还有另一个报告，显示了在高资源使用事件期间仅覆盖几个小时的时间段内的最大数据IO查询。

正如我们所看到的，没有任何查询报告重要的数据IO使用情况。

我还运行了数据库上的sp_who2和sp_whoisacive，并没有真正发现什么突出的问题（尽管我承认我对这些工具不是很熟悉）。

我该怎么弄清楚这里发生了什么？我不认为我的应用程序查询有责任造成这种资源使用，并且我感觉服务器上有一些内部进程正在后台运行并将其耗尽。

- kspearrin

所以你看到有更新统计正在运行，这自然会带来一些相当可观的I/O成本，对吧？如果这个查询在24小时内占总IO的4%，你认为它仍然可能是你在图表中看到的峰值的一个因素吗？当你没有达到DTU的最大限制且查询性能仍然可接受时，我会犹豫地使用"过载"这个词。为什么服务器随时间不同利用其资源会成为一个问题呢？ - LowlyDBA - John M

@LowlyDBA 我不确定如何验证查询是否是导致这个问题的原因。当它只显示4%的使用率时，我不认为这会导致整体DTU阈值接近100%的使用率。这里有很多未解释的使用情况。基本上，我正在努力弄清楚为什么会发生这种情况。持续几个小时的高峰使服务器接近100%，而且正如我之前提到的，当我将可用的DTU资源加倍（S3计划）时，似乎根本不会发生这种情况。 - kspearrin

记住，DTU不仅仅是输入/输出（I/O），它还包括CPU和内存。因此，比较这两者可能并不是一个有用的指标。对于在较小窗口中（仅显示峰值时段）资源的可视化分解，查询性能洞察工具会提供给您什么信息？ - LowlyDBA - John M

@LowlyDBA 我上面发布的报告截图似乎清楚地表明资源都来自Data IO。CPU和Log IO并不是真正的主要因素。例如，通过查看最大CPU%的查询，只有一个罪魁祸首在问题发生期间仅使用了2%的资源。截图链接：https://imgur.com/rxyMLc9 - kspearrin

我的意思是，比较具有不同I/O、CPU和内存的两个系统是没有任何用处的，即使其中一个恰好是另一个的两倍。自然而然地，它会表现得更好，甚至可能使用完全不同的查询计划。 - LowlyDBA - John M

顾问有没有任何指数推荐？另外，为什么要使用 OPTION (MAXDOP 16)？ - Kin Shah

@Kin 不，所有的索引看起来都足够了。统计更新查询不是由我运行的。你在查询中看到的带有 OPTION (MAXDOP 16) 的统计更新查询是由服务器自己运行的。 - kspearrin

在Azure门户上，您不能依赖DTU图表。相反，请使用sys.dm_db_resource_stats。正如论坛帖子所示，DTU图表存在错误，例如：https://social.msdn.microsoft.com/Forums/azure/en-US/d6497224-f46f-41f4-9180-1e307acab08a/dtu-graph-not-working-right?forum=ssdsgetstarted - Alberto Morillo

@AlbertoMorillo sys.dm_db_resource_stats似乎表明Azure门户中的DTU图是正确的。请参见https://i.imgur.com/bK3asoe.png - kspearrin

我建议您运行一个扩展事件会话，并筛选出涉及大量IOPS的查询（否则结果将非常繁忙）。这是一个很尴尬的位置，因为在Azure上的扩展事件甚至比本地环境更不愉快，但它将显示实际执行所有这些IO操作的查询列表。 - Marian

@kspearrin你解决了这个问题吗？我现在的应用程序也遇到了同样的情况，导致整个网站都无法访问。 - Dirk Boer

1@DirkBoer 在我们的情况下，这似乎与服务器上运行的统计聚合查询有关。我们关闭了某些表的自动统计功能，以帮助解决这个问题。 - kspearrin

1个回答

相关问题
10 为什么我的Azure SQL Server运行如此缓慢？

- pim · Accepted Answer

考虑到在高峰期间您的日志活动很少，我们可以假设没有或很少发生DUI。

您曾经提到过高峰期不会影响性能，但又说会有影响。究竟是哪种情况呢？

您还提到，在扩展操作之后这个问题就会消失。这是有道理的，因为这类似于重新启动本地服务器，可以有效地终止所有进程等。

我猜测这个数据库是从应用层进行访问的，我的猜测正确吗？如果是的话，我怀疑您的连接没有正确关闭。垃圾收集器应该最终会处理这些问题（但不能依赖它），但我曾经见过因为应用层未关闭连接而导致类似的情况出现。在我们的情况下，应用程序非常繁忙，最终出现了并发连接错误，这正是我们发现问题的原因。

在高峰期尝试以下查询：

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    s.status,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
ORDER BY c.connect_time ASC

如果我没记错的话，你会发现返回了一大堆状态为“Sleeping”或更糟糕的“Running”的记录。如果是这种情况，那么应用层面就存在更大的问题。我们可以通过复制数据库并使用以下查询（使用基本层以避免过高的成本），来进一步调试并监控这种行为。

CREATE DATABASE Database1_copy AS COPY OF Database1 ( EDITION = 'basic' );