但是,每天有几次我突然在我的日志中出现数百个错误,超时,如下所示：
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?
