Azure SQL频繁连接超时 [英] Azure SQL frequent connection timeouts

问题描述

我们正在一个由SQL Azure数据库支持的Azure上运行一个Web应用程序（2个实例）。在任何给定时间，都有50-150个用户使用该网站。数据库以S2性能级别运行。 DTU平均约为20％。

We're running a web app (2 instances) on Azure, backed by a SQL Azure database. At any given time there are 50-150 users using the website. The database runs at S2 performance level. The DTU is around 20% on average.

但是，每天有几次我突然在日志中发现数百个带有超时的错误，例如：

However, a few times every day I suddenly get hundreds of errors in my logs with timeouts, like this:

执行命令定义时发生错误。有关详细信息，请参见内部异常。

An error occurred while executing the command definition. See the inner exception for details.

等待操作超时。

超时已过期。在操作完成之前超时或服务器没有响应。尝试连接到路由目标时发生此故障。尝试连接到原始服务器所花费的时间为-[登录前]初始化= 1；握手= 21; [登录]初始化= 0;认证= 0; [登录后] complete = 1;

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=1; handshake=21; [Login] initialization=0; authentication=0; [Post-Login] complete=1;

我们正在使用EF6进行默认命令超时的查询。我已经配置了以下执行策略：

We're using EF6 for queries with the default command timeout. I've configured this execution strategy:

SetExecutionStrategy("System.Data.SqlClient", 
            () => new SqlAzureExecutionStrategy(10, TimeSpan.FromSeconds(15)));

数据库（总共约15GB）已建立索引。这些错误会在各处发生，通常在1-2分钟内会发生数十到数百个。

The database (about 15GB total) is heavily indexed. These errors occur all over the place, usually dozens to hundreds within 1-2 minutes.

我可以采取哪些措施来防止这种情况的发生？

What steps can I take to prevent this from happening?

推荐答案

它在1-2分钟内发生的事实可能意味着活动突然爆发或某些进程可能锁定了表。

The fact that it happens in 1-2 minutes might mean a burst in activity or some process that might be locking up tables.

如果您的DTU在那段时间不是20％，这不是CPU问题，但是您始终可以通过在数据库上运行以下查询来查找瓶颈：

If your DTU during those times is at 20% is not a CPU issue, but you can always find which are the bottlenecks by running this query on the DB:

SELECT TOP 10 
total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                                                           WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                                                           ELSE statement_end_offset 
                                                       END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 
ORDER BY Avg_CPU_Time DESC

即使数据库已建立大量索引，索引也会碎片化，我建议运行此命令以检查当前碎片：

Even if the DB is heavily indexed, indexes get fragmented, I'd advice running this to check the current fragmentation:

select a.*,b.AverageFragmentation from 
(               SELECT tbl.name AS [Table_Name], tbl.object_id, i.name AS [Name], i.index_id, CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered], 
CAST(case when i.type=3 then 1 else 0 end AS bit) AS [IsXmlIndex], CAST(case when i.type=4 then 1 else 0 end AS bit) AS [IsSpatialIndex]
               FROM
               sys.tables AS tbl
               INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id))a
inner join
(               SELECT tbl.object_id, i.index_id, fi.avg_fragmentation_in_percent AS [AverageFragmentation]
               FROM
               sys.tables AS tbl
               INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
               INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
)b
on a.object_id=b.object_id and a.index_id=b.index_id
order by AverageFragmentation desc

您还可以使用Azure自动化计划碎片索引的自动重建，请参见以下答案：为什么我的Azure SQL数据库索引仍然碎片化d？

You can also use Azure Automation to schedule an automatic rebuilding of fragmented indexes, see answer at: Why my Azure SQL Database indexes are still fragmented?

这篇关于Azure SQL频繁连接超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！