sql-server – Azure SQL频繁连接超时

但是,每天有几次我突然在我的日志中出现数百个错误,超时,如下所示：

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

The wait operation timed out.

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进行默认命令超时的查询.我已经配置了这个执行策略：

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

数据库(总共大约15GB)被大量索引.这些错误遍布整个地方,通常在1-2分钟内发生几十到几百个.

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

解决方法

如果在这些时间期间您的DTU为20％不是CPU问题,但您始终可以通过在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

即使数据库被大量索引,索引也会碎片化,我建议运行它来检查当前的碎片：

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,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,'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自动化计划自动重建碎片索引,请参阅以下答案：Why my Azure SQL Database indexes are still fragmented?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!