Тайм-аут SQL Azure после неактивности запроса

Question

Я испытываю частые таймауты в управляемом экземпляре SQL Azure. Если в течение некоторого времени не выполнялось никаких запросов, первый запрос, который, как ожидается, будет возвращать от 500 до 2000 строк, будет использовать все доступные DTU в моем ценовом уровне (план S2 50 DTU) и всегда приведет к следующему исключению:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (258): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
ClientConnectionId:903324e4-4eba-4522-bae8-228a23c0e51c
Error Number:-2,State:0,Class:11
ClientConnectionId before routing:24ca7fb2-4c3b-44ad-b393-d8cda9dda172

При выполнении одного и того же запроса сразу после этого сервер отвечает мгновенно. Возможно, потому что последний запрос был загружен в память. SQL Server используется в настройке IoT, поэтому потоковая передача данных (массовые вставки) с устройств происходит постоянно в течение дня. Я попытался решить эту проблему, позволив функции Azure, выполняющей запрос каждый час, загружать часто используемые данные в память, но это решит проблему только для конкретного объекта, к которому я обращаюсь, в функции Azure. Согласно плану выполнения, все мои запросы используют правильные индексы.

Я не думаю, что реализация политики повторных попыток является приемлемым решением. С моей точки зрения, это только уменьшит пользовательский опыт еще больше. У кого-нибудь есть опыт решения этой проблемы?

РЕДАКТИРОВАТЬ: план исключений здесь: https://www.brentozar.com/pastetheplan/?id=rJQvb7tRm

Answer 1

Фактические планы мероприятий показывают, что 1187 строк были прочитаны поиском кластерного индекса на DeviceUniqueIdentifier ключ кластеризованного индекса, хотя ни одна строка не удовлетворяет другим критериям. Добавление InstanceId а также NVEControllerTimestamp ключ кластеризованного индекса позволит избежать ненужного прикосновения к этим строкам.

Как правило, лучшим индексом для поддержки тривиального запроса, который возвращает все столбцы, является кластеризованный индекс с первыми столбцами ключа предиката равенства (DeviceUniqueIdentifier а также InstanceId), а затем столбцы неравенства (поиск диапазона по NVEControllerTimestamp).

Ты можешь использовать CREATE INDEX...WITH (DROP_EXISTING = ON) добавить дополнительные столбцы к существующему кластерному индексу.