Если замучила ошибка SQL «Истекло время ожидания»

Ваш отзыв

ошибка истекло время ожидания

Добрый день, уважаемый читатель блога! 16 мая этому ресурсу исполнилось ровно 3 года, если считать с даты первого поста! Пройден весьма не малый путь, но мы двигаемся дальше. С ошибкой «Истекло время ожидания» приходится сталкиваться пусть не каждый день, но часто. Это может означать, что не открылся наш любимый сайт или что какой-то сервер теперь недоступен. Какое же несчастье увидеть данную ошибку при подключении к MS SQL серверу! После основной фразы как правило идет расшифровка, из-за чего именно возникло подобное сообщение. Попробуем разобраться.

Ошибка «истекло время ожидания» может иметь следующие вариации:

  • Это время ожидания истекло до завершения операции или сервер не отвечает
  • Время ожидания истекло раньше, чем удалось получить подключение из пула. Возможно, все подключения в пуле уже используются и достигнут максимальный размер пула

Если сервер не отвечает

При появлении ошибки, связанной с таймаутом ответа сервера SQL, первое что приходит в голову — это слишком долгий запрос. Здесь два пути — или оптимизировать его, проинспектировать имеющиеся индексы и т.п., или увеличивать таймаут соединения.

Проследить тяжелые запросы можно при помощи Microsoft SQL Management Studio. Для этого надо кликнуть правой кнопкой мыши по серверу SQL в интерфейсе данной программы и в открывшемся контекстном меню щелкнуть по пункту меню «Activity Monitor» или «Монитор активности» в русском варианте. Раскройте вкладку (хотя нет, по-умному это все же аккордеон называется! ) «Recent Expensive Queries», отобразится таблица с запросами, которые оказали заметное воздействие на работу сервера SQL. Обратите внимание на число выполений запроса в минуту, а также на число чтений (если select) и записей в секунду.


Второе, что возможно — это попытка что-то прочитать в рамках еще не закрытой транзакции. Напишем некий код, который что-то там обновляет в таблице TestTypes. Открыта транзакция, идет выполнение обновления данных, и следующей строкой вызывается метод с сигнатурой void LoggingUpdate(string connectString).

Смотрим, что в нем. Зачем-то с самого начала нам понадобились данные из той самой таблицы TestTypes, которую мы обновляем.

На строке command.ExecuteScalar() выполнение кода может «зависнуть», и со временем мы получим ошибку о возникшем таймауте.

Однако, если нам нечего делать, мы поменяем две строки местами в методе UpdateSmth и запишем не

а так

, то все пройдет нормально, так как выполнение select будет происходить до команды update. Стоит ли на это полагаться? Думаю, нет .

Если все подключения в пуле уже используются

Для начала два слова о том, что такое пул подключений MS SQL сервера. Если бы физические соединения до БД (сокеты, именованные каналы) действительно создавались каждый раз при вызове команды connection.Open(), это приводило бы к дополнительной нагрузке на сервер. Чтобы этого не делать, был создан пул подключений, в рамках которого существует набор соединений, потенциально готовых для открытия. В самом простом случае

одна строка подключения — это один пул

Проверка идет по точному совпадению строк, если не используется встроенная виндовая безопасность. При использовании разных удостоверений Windows, которыми пользователь подключается к БД, создаются различные пулы, хотя строки подключения могут и совпадать.

Пока соединение никому не нужно, оно «спит». Как только программа потребовала (connection.Open()) себе новое подключение, организатор пулов смотрит, есть ли такие соединения, которые можно задействовать или нет. Если брать нечего, все занято, запрос помещается в очередь, в которой он «пасется» по умолчанию в течение 15 секунд. Если за это время вакантное соединение найдено не было, выкидывается исключение, которое на английском языке будет звучать так: «Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.».

По умолчанию в пуле может одновременно находиться до 100 открытых соединений. Их количество можно подкорректировать в ConnectionString, изменив значение параметра Max Pool Size.

Т.е. строка подключения к БД может выглядеть примерно так:

Но это экстенсивный путь. Первое, что надо проверить — это реальное количество соединений к базе данных. Посмотреть и проанализировать имеющиеся подключения к БД MS SQL можно при помощи запроса, например, такого вида

, где вместо ‘DB’ надо написать имя своей базы данных.

Необходимо проверить, правильно ли закрываются соединения к серверу MS SQL в коде, этому могут помочь возвращаемые тексты запросов, см. картинку ниже:

количество соединений к БД MS SQL

Допустим, у нас есть такой код на C#:

Не стоит так делать! Используя такой подход, мы позволяем подключениям бесконтрольно плодиться, что и приводит к разбираемой здесь ошибке.

Достаточно использовать using для объекта SqlConnection. Т.е. приведенный выше код необходимо переписать так:

Однако это еще далеко не панацея! Даже в случае использования using’ов нас могут ждать подводные камни.

Сейчас приведу безобидный с виду код:

Пусть метод NestedConnection выглядит так:

Такой подход тоже приводит к этой ошибке рано или поздно. Правда, здесь будет всего на одно соединение больше, чем надо, а не на N, где N — это число вызовов метода NestedConnection().

Есть два варианта исправления:

  • вызывать участки кода, в которых идет открытие соединения, последовательно друг за другом, а не один внутри другого
  • передавать объект SqlConnection по ссылке. Есть противопоказания, необходимо проконсультироваться с SQL-сервером!

В последнем случае «противопоказания» связаны с тем, что можно нарваться, например, на ошибку,  описанную выше в статье, связанную с ожидающей незакрытой транзакцией. Т.е. все необходимые изменения нужно проводить максимально аккуратно.


Оставьте комментарий

XHTML: Вы можете использовать следующие теги: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url=""> <img src="" alt="" class="" width="" height="">

Нажимая кнопку "Отправить комментарий", Вы автоматически соглашаетесь с политикой конфиденциальности и даете свое согласие на обработку персональных данных. Ваши данные не будут переданы третьим лицам.

http://noservice.ru/wp-content/plugins/wp-monalisa/icons/ab.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/ac.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/ag.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/ah.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/ai.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/ak.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/am.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/an.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/ao.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/aq.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/ar.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/at.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/av.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/aw.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/ay.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/az.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/bb.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/bc.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/bd.gif 
http://noservice.ru/wp-content/plugins/wp-monalisa/icons/be.gif 
больше...