Устранение неполадок с прерывистым таймаутом SQL

У нас было несколько экземпляров в день, где мы получаем множество ошибок таймаута SQL из нескольких приложений (System.Data.SqlClient.SqlException: время ожидания истекло. Период ожидания истекает до завершения операции или сервера не отвечает.) У нас более 100 различных приложений в нашей сети, как для веб-приложений, так и для настольных приложений. Все от VB6 и классического ASP до .NET 4. Я могу найти все виды данных, которые показывают побочные эффекты, но не может точно определить, что вызывает это. Наш администратор базы данных говорит, что с сервером SQL ничего не происходит, и ИТ говорит, что нет ничего плохого в веб-серверах или сети, поэтому, конечно, я остался посередине, пытаясь устранить эту проблему.

Я действительно ищу предложения о том, какие другие способы устранения неполадок я могу сделать, чтобы попытаться отследить это.

Мы запускаем SQL Server 2008 R2 в кластере. Там есть несколько различных серверов, которые подключаются к нему, начиная от Windows Server 2003 до 2008 различных вариантов.

Вот что я сделал до сих пор:

Запустите SQL-трассировку длинных запросов и тупиков. Это не показывает взаимоблокировки во время проблем, а длинные запросы совпадают с нашими ошибками таймаута, но выглядят побочным эффектом, а не причина. Запросы, которые являются очень базовыми, которые обычно возвращаются, мгновенно заканчиваются тем, что время от времени запускается 30, 60 или 120 секунд. Это происходит в течение нескольких минут, после чего все подбирается и прекрасно работает после этого.
Использовать монитор производительности для отслеживания соединений пула соединений. Это иногда показывает некоторые всплески количества подключений в период времени, равный таймаутам, но все же даже не на полпути к пределу связи 100 по умолчанию. Опять же, ничего здесь, кажется, не указывает на причину.
Разделяйте веб-приложения в разных пулах приложений. Мы попытались сузить приложения, которые, по нашему мнению, могут быть основной проблемой (большинство болтовней и т.д.) и поместить их в отдельные пулы приложений, но это не похоже, влияют на что-либо или помогают нам сузить что угодно.
Мониторинг использования диска на SQL Server. Мы провели некоторый мониторинг на сервере SQL и не видим всплесков или каких-либо признаков проблем при возникновении этих тайм-аутов.
Verified TempDB не был причиной проблемы.

Я вернусь и добавлю больше, если я подумаю о том, что еще мы пробовали. Пожалуйста, дайте мне знать некоторые идеи о том, что следует устранить после.

Ответ 1

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

Похоже, что некоторые запросы/транзакции блокируют вашу базу данных, пока они не будут выполнены. Вы должны выяснить, какие запросы блокируют и переписывают их/запускают их в другое время, чтобы избежать блокировки других процессов. В этот момент ожидания требуют только времени ожидания.

Дополнительной точкой для поиска является размер автоматического увеличения вашего журнала транзакций и базы данных. Установите их на фиксированный размер, а не процент от текущих файлов. Если файлы становятся все выше, время, затрачиваемое на выделение достаточного пространства, в конечном итоге будет больше, чем ваш тайм-аут транзакции. И ваш db останавливается.

Ответ 2

Проблемы с производительностью сводятся к конкуренции CPU, IO или Lock. Похоже, вы исключили ИО. Я бы предположил, что CPU не проблема, так как это база данных, а не число cruncher. Таким образом, это приводит к конфликту блокировки.

Если вы можете выполнить sp_who2, в то время как запросы выходят из строя, вы можете использовать столбец BlkBy, чтобы вернуться к удерживанию блокировки, ожидающей всех остальных. Поскольку это происходит только несколько раз в день, у вас могут возникнуть проблемы с поиском достаточного количества данных, если вы запускаете это вручную, поэтому я предлагаю вам настроить автоматическую систему для выгрузки этого вывода на регулярной основе или, возможно, для запуска исключения тайм-аута приложения. Вы также можете использовать Монитор активности для просмотра ухудшения реакции на запросы в режиме реального времени, как это было предложено экспертом.

После того, как вы найдете долгосрочный запрос и приложение, которое его выполняет, вы можете немедленно разрешить домино таймаутов, уменьшив тайм-аут для этого единственного приложения ниже всех остальных (сейчас он должен быть длиннее). Затем вы должны проверить код, чтобы определить лучшее решение. Вы можете сократить время блокировки, совершив транзакцию раньше в sproc, или уменьшить блокировку, требуемую чтением, с помощью таких советов, как NOLOCK или UPDLOCK.

Здесь еще некоторое чтение на sp_who2: http://sqlserverplanet.com/dba/using-sp_who2/

И подсказки подсказок: http://msdn.microsoft.com/en-us/library/ms181714.aspx http://msdn.microsoft.com/en-us/library/ms187373.aspx

Ответ 3

Немного длинный снимок, но в лаборатории некоторое время назад мы столкнулись с ситуацией, когда SQL Server оказался невосприимчивым, а не потому, что мы использовали процессор или что-то, что можно было бы отслеживать в SQL Server, он оказался работоспособным для всех тестов но соединения не выполнялись при некоторой нагрузке.

Проблема оказалась связана с объемом трафика с сервером, потому что мы запускали встроенные окна Syn Attack Flood Protection в Windows. Раздражающе, когда вы нажимаете на это, на сервере Windows не зарегистрировано сообщение или внутри SQL - вы видите только symtpoms, которые не удается выполнить, потому что окна замедляются при принятии сообщений и позволяют строить очередь. С точки зрения подключения сервер, похоже, не отвечает, когда он должен (он даже не подтвердил, что сообщение получено)

http://msdn.microsoft.com/en-us/library/ee377084(v=bts.10).aspx

Прокрутите вниз до SynAttackProtect, и вы увидите, что по умолчанию в Windows Server 2003 sp1 включено включить эту функцию по умолчанию. Это действующий механизм защиты DDOS, и отсутствие регистрации, которую он запускает, затрудняет обнаружение, когда ваш сервер делает это.

Прошло 3 дня в лаборатории MS, прежде чем выяснилось.

Вы упомянули 100 коней, у нас было приложение, которое постоянно подключалось, запускало запросы, а затем отключилось, и не открывало подключения. Это означало, что у нас было несколько потоков на каждом машинном соединении, используя это, 10 машин, несколько потоков на машину, и считалось, что достаточно много разных подключений, которые последовательно создаются/удаляются, чтобы вызвать защиту.

Если вы находитесь на этом уровне (поскольку он не является четко определенным порогом MS), трудно сказать.

Ответ 4

Как и другие плакаты, это звучит так, будто у вас проблема с блокировкой. Мы столкнулись с аналогичной проблемой несколько недель назад; тем не менее, наш был намного более прерывистым и часто прояснялся, прежде чем мы могли получить DBA на сервер, чтобы запустить sp_who2, чтобы проследить проблему.

В результате мы создали уведомление по электронной почте, если блокировка превысила определенный порог. Как только мы разместим это на месте, нам удалось идентифицировать процессы, которые были блокированы, и изменить уровень изоляции, чтобы читать нефиксированные, где это необходимо, чтобы исправить проблему.

Здесь приведена статья, в которой содержится обзор того, как настроить этот тип уведомления.

Если блокировка оказывается проблемой, и если вы этого еще не сделали, я бы предложил изучить настройку уровней изоляции на основе версий на основе версий.

Ответ 5

Вы находитесь на правильном пути со своей трассировкой и профилированием. что вам нужно сделать, это искать то, что у запросов, которые тайм-аут имеют вместе - вероятно, они все будут бить небольшое подмножество таблиц или индексов. Я подозреваю, что какое-то приложение имеет длительное обновление/вставку, которое влияет на запросы в таблицах, в которых используются индексы, затронутые обновлениями/вставками.

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

Тогда у вас есть некоторые решения. Один из вариантов заключается в изменении подсказок блокировки на запросах, которые выходят из строя. Но это нечестная практика, потому что она замаскирует настоящую проблему на некоторое время. В то время как вы видите, что тайм-ауты уходят на некоторое время, в зависимости от намека, который вы выберете, вы можете получить грязные чтения, а затем фальшивые данные, возвращающиеся с этих запросов. Это может оказаться хуже, чем таймауты - трудно сказать.

Лучше всего выяснить, какие из ваших приложений отправляют обновленные/вложенные вами вставки и выкапывают, чтобы выяснить, почему они занимают так много времени.

Ответ 6

Я предлагаю вам взглянуть на супер классный SQL Server Динамический вид управления:

Динамические виды управления и функции возвращают информацию о состоянии сервера, которые могут использоваться для мониторинга работоспособности экземпляра сервера, диагностики проблем и настройки производительности.

Эта статья является хорошим началом с DMV, хотя она была написана для SQL 2005 (первое появление DMVs): Устранение проблем производительности в SQL Server 2005, особенно "блокирующие" главы.

Ответ 7

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

Удачи в продолжении устранения неполадок!

Ответ 8

Я видел похожие проблемы, если на сервере SQL был установлен антивирус. Функции автоматического обновления AV синхронизировали сервер и не позволяли достаточно процессора для SQL Server.

Кроме того, вы помещаете небольшое приложение на сам SQL-сервер, который проверяет, могут ли быть созданы соединения или работает очень простой SQL, например "SELECT GETDATE();"? Это позволит устранить сетевые возможности.

Ответ 9

Поскольку я каждый день выполняю поиск и устранение неполадок, как часть моей работы, вот что я хотел бы сделать:

Так как это SQL Server 2008 R2, вы можете запустить SQLDiag, который входит в состав продукта. Вы можете перечислить книги онлайн для получения более подробной информации. Вкратце, трассировка и блокиратор на стороне сервера script.
Как только трассировка захвачена, найдите событие "Внимание". Это будет спад, который получил ошибку. Если вы фильтруете по SPID, вы увидите сообщение RPC: Completed перед "Attention". Проверьте время. Это время 30 секунд? Если да, то клиент ждал 30 секунд, чтобы получить ответ от SQL и получил "тайм-аут" [Это параметр клиента, поскольку SQL никогда не останавливается и соединение)
Теперь проверьте, действительно ли выполняемый запрос должен занимать 30 секунд?
Если да, настройте запрос или увеличьте время ожидания от клиента.
Если нет, этот запрос должен ждать некоторых ресурсов (заблокирован)
В этот момент вернитесь к Blocker Script и проверьте временной интервал, когда "Внимание" пришло

Выше предполагается, что проблема связана с тем, что SQL Server не связан с сетью!

Ответ 10

Мой опыт работы с этими проблемами (но не на SQL Server) заключается в том, что причиной многозадачности часто является чрезмерная многозадачность. Если есть одинаковые/связанные данные/таблицы, которые запрашиваются (почти) в одно и то же время по многим соединениям, СУБД может иметь проблемы с сохранением всей изоляции при проверке. Это не так много проблем с использованием диска, так как некоторые подключения ждут от других вещей. Синхронизация очень дорогая с точки зрения использования ЦП.

100 соединений слишком много, на мой взгляд. (По моему опыту снова) даже 20 подключений, которые требуется выполнить одной машиной, могут быть чрезмерно оптимистичными.

Ответ 11

Проблема заключается в том, что из-за плохого запроса время выполнения запроса занимает более 60 секунд или блокировка в таблице

Проблема похожа на тупик; у нас есть запросы, которые блокируют запросы во времени. Тайм-аут по умолчанию для запроса составляет 60 секунд, а за ним у нас будет SQLException для тайм-аута.

Пожалуйста, проверьте журналы SQL Server для взаимоблокировок. Другой способ решить проблему увеличения таймаута для объекта Command (Temp Solution).

Ответ 12

Виртуализированы ли эти серверы? В другом посте я читал о SQL-сервере, работающем иногда очень медленно из-за нехватки памяти. Это, в свою очередь, было вызвано так называемым всплеском памяти, который виртуализатор использовал для ограничения объема памяти, используемой этим виртуальным сервером. Это было трудно найти, потому что давление на физическую память не имело никакого отношения к самому серверу SQL.

Другой распространенной причиной временного ухудшения производительности может быть антивирус. Когда будет установлено новое определение вируса, все другие процессы будут страдать и работать очень медленно. Просмотрите любой другой процесс автоматического обновления, это может также потребовать много ресурсов совершенно неожиданно. Удачи вам!

Ответ 13

Мы столкнулись с этим с SQL Server 2012/SP3 при запуске запроса через объект SqlCommand из приложения С#. Команда была простым вызовом хранимой процедуры, имеющей один параметр таблицы; мы передавали список из 300 целых чисел. Процедура, в свою очередь, называлась тремя определяемыми пользователем функциями и передавала таблицу в качестве параметра каждому из них. CommandTimeout был установлен на 90 секунд.

При выполнении точно такой же хранимой процедуры с тем же аргументом из SQL Server Management Studio запрос выполнялся через 15 секунд. Но при запуске из нашего приложения, используя указанную выше настройку, SqlCommand тайм-аут. Тот же SqlCommand (с разными, но сопоставимыми данными) успешно работал в течение нескольких недель, но теперь он не прошел с любым аргументом таблицы, содержащим более 20 или около того целых чисел. Мы выполнили трассировку и обнаружили, что при запуске из объекта SqlCommand база данных провела все блокировки на 90 секунд и вызовет процедуру только примерно в момент таймаута. Мы изменили время CommandTimeout, и независимо от того, что мы выбрали, сохраненный proc будет вызываться только в самом конце этого периода. Таким образом, мы предполагаем, что SQL Server бесконечно приобретал одни и те же блокировки снова и снова, и что только тайм-аут объекта Command заставлял SQL Server останавливать свой бесконечный цикл и начинать выполнение запроса, и к этому времени было слишком поздно для успеха. Моделирование этого же процесса на аналогичном сервере с использованием аналогичных данных не показало такой проблемы. Наше решение состояло в том, чтобы перезагрузить весь сервер базы данных, после чего проблема исчезла.

Итак, похоже, что в SQL Server существует некоторая проблема, при которой некоторый ресурс кумулятивно потребляется и никогда не выпускается. В конце концов, при подключении через SqlConnection и запуске SqlCommand с использованием параметра таблицы SQL Server переходит в бесконечный цикл, фиксирующий блокировки. Цикл завершается таймаутом объекта SqlCommand. Решением является перезагрузка, по-видимому, восстановление (временное?) Здравомыслия SQL Server.

Ответ 14

У меня была проблема, подобная этому, и выяснилось, что это связано с установкой среды .NET.

Sqlcommand.Timeout

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=VS.100).aspx

Значение по умолчанию - 30 секунд, указанное в вышеуказанном URL-адресе Microsoft, попробуйте установить это на большее количество секунд или, возможно, -1, прежде чем открывать соединение, чтобы узнать, разрешает ли это проблему.

Возможно, это параметр в файлах web.config или app.config или в файлах конфигурации applicaiton/web server.