Глобальное замедление SQL Server 2005

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

Наша установка

Пара серверов содержит эту ветвь нашего приложения SAS. Один из них является сервером приложений, на котором запущено несколько приложений в IIS, а другой, который испытал замедление, представляет собой Windows Server 2008, работающий под управлением SQL Server 2005. В SQL размещается от 100 до 200 баз данных.

Проблема / расследование

Сервис в значительной степени останавливается. Некоторые запросы проходят, но большинство страдают тайм-аутами SQL. Процессор и оперативная память SQL-машины выглядят нормально, в среднем около 25% рабочей нагрузки процессора и 85% оперативной памяти. В то время я не думал проверять активность диска, так как пошел прямо к "EXEC sp_who2"

В результате были показаны сотни задач, заблокированных с помощью идентификатора 123, который был сам по себе и с сотней других, заблокированных с помощью идентификатора 456. Обычное выполнение обычно вообще не блокирует задачи. Когда я перезапустил sp_who2 через 15-20 секунд, появились разные идентификаторы блокировки, но количество заблокированных / блокирующих задач, похоже, осталось прежним. (не считал группы из-за аварийного режима)

Большинство задач блокировались с помощью таких операторов, как "SELECT INTO" или "CREATE INDEX on temptable".

Обходной путь

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

Мой вопрос

Что я могу сделать, чтобы исправить эту проблему, желательно до того, как она возникнет снова?

Суб-вопросы:

• Есть ли другой путь, который я могу исследовать во время нормальной деятельности?
• Если / когда проблема повторяется, какую информацию я должен собрать? (Нужно быть быстрым, чтобы получить, поскольку это означает, что у нас снова будет перерыв в обслуживании)

Что я сделал до сих пор

Судя по симптомам, мы подозревали, что проблема была в некотором роде спора о базе данных tempdb. (Другим симптомом было то, что щелчок правой кнопкой мыши на базе данных tempdb для просмотра свойств во время проблемы через некоторое время вызвал ошибку)

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

С тех пор я прочитал много разных источников информации о разногласиях с tempdb, но не ограничиваясь ими:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/

Исходя из того, что я понял, лучше всего иметь файлы tempdb с заданным начальным размером и по одному на ядро, до 8 файлов. Мы планируем внедрить это (8 ядер, 8 файлов) как можно скорее, так как это лучшая практика. Все они будут находиться на одном жестком диске (на данный момент), но мы считаем, что в худшем случае нет улучшения, а в лучшем случае мы получаем разницу между узким местом логического конфликта и узким местом дискового ввода-вывода.

Однако мы не можем быть уверены в корреляции с проблемой, которая у нас была. Из того, что я понимаю, разбиение на несколько временных файлов помогло бы при ожидании типа "PAGELATCH_XX", но при выполнении запроса Пола С. Рэндала (см. 1-я опубликованная ссылка) во время обычной деятельности этот тип ожидания отсутствует. Лучшие 3, которые я вижу во время нормальной деятельности:

CXPACKET 68,63%
LATCH_EX 18,46%
PAGEIOLATCH_SH 4,35%

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