Заранее: извините за длину вопроса ... не удалось найти правильный баланс между деталями и краткостью.
У нас возникают проблемы с сервером БД для нашего веб-приложения, когда запросы, которые должны (и обычно выполняются) выполняться за очень короткое время (<10 мс), в случайных случаях занимают от 1 до 30 секунд для выполнения - без очевидных шаблон. Согласно нашим трассировкам профилировщика, некоторые из них даже являются запросами "ничего не делать", например "exec sp_reset_connection" (обычно выполняется за 0 мс; наблюдаемые пики от 3 до 6 с), и "SET NO_BROWSETABLE ON"и т. д. Вот несколько примеров:
SELECT * FROM [Localisation].[TimeZoneRule] WHERE [Name] = 'EU'
куда TimeZoneRule имеет около 500 000 строк в 5 столбцах. Имеет суррогатный первичный ключ и индекс на Name. Обычно занимает 0,97 мс, максимум - 11 с. Таблица НИКОГДА не записывается (была предварительно заполнена перед запуском). Профилировщик записывает, что это занимает 0-15 ЦП, 18-25 чтений, 0-1 записей (не знаю, зачем писать).
UPDATE [Core].[User] SET [LastUsed] = GETUTCDATE() WHERE Id = '<uid>'
куда User имеет около 30 000 строк в 10 столбцах (один из которых является столбцом Xml). Id - кластерный первичный ключ. Таблица регулярно записывается и читается. Обычно занимает 10 ~ 20 мс, максимум - 26 с. Профилировщик записывает, что это занимает 0 CPU, 15-36 чтений, 0-1 записей.
INSERT INTO [Log].[Session] (ASPSessionId, Start, ClientAddress, ClientSoftware, ProxyAddress, ProxySoftware)
VALUES(<number>, GETUTCDATE(), '<ipv4address>', '<User agent string>', '<ipv4address>', '<proxy software name (if present)>')
куда Session содержит около 1 000 000 строк в 8 столбцах. Имеет суррогатный первичный ключ (идентификатор) и индекс на ASPSessionId. Таблица регулярно записывается, но редко читается (только нами непосредственно из SSMS). Обычно занимает 15–150 мс, максимум - 5 с. У меня нет записи профиля для него, но из памяти ЦП около 0, чтение и запись были между 0 и 100 каждое.
Используемая нами установка представляет собой зеркальную установку с Dell 2950 в качестве принципа (2 4-ядерных процессора xeon 2.6, 16 ГБ ОЗУ) и Dell 6850 в качестве зеркала (4 HT Xeon 3.2, 8 ГБ ОЗУ). Оба работают под управлением 64-разрядной версии SQL 2005 SP4. Рассматриваемая база данных не очень большая, около 16 ГБ. Основной имеет 6 дисков SAS, разделенных на 3 тома RAID-1; один для System + Page + TempDB, один для MDF базы данных и один для журнала транзакций + ежечасное резервное копирование журнала + ежедневное резервное копирование БД. Я знаю, что ситуация с журналами далеко не лучшая - с точки зрения дискового ввода-вывода (см. Ниже) и безопасности данных.
Пока мы считать мы исключили:
TimeZoneRule никогда не записывается и, по моему мнению, никогда не должен иметь эксклюзивную блокировку. Кроме того, мы проверили трассировки, и во многих случаях выполнялся только «проблемный запрос» - единственное другое действие - отключение других подключений.(*) Мы попытались заставить профилировщик захватывать события, связанные с получением блокировки, но трассировка раздувается до нечитаемых размеров и, что еще хуже, веб-приложение останавливается.
Не будучи администраторами баз данных, у нас быстро заканчиваются идеи. Может ли кто-нибудь придумать что-нибудь, что я должен рассмотреть в следующий раз, или что-нибудь, что я по глупости пропустил?
Мало вещей, которые могут оказаться бесполезными или полезными;
Если это происходит с хранимыми процедурами, это может быть анализ параметров -> http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
Вы используете ASP для веб-приложения? У нас была проблема, похожая на ASP + IIS и SQL с использованием хранимых процедур. Кажется, я помню, что причиной этого были тайм-ауты семафоров. На выполнение запроса уходит почти 30+ секунд, но потом какое-то время все в порядке. Я не могу найти на нем свою информацию, но, кажется, помню, что это было связано с тайм-аутом IIS, это было на стороне IIS.
Этот инструмент тоже может быть полезен -> http://blog.brianhartsock.com/2008/12/16/quick-and-dirty-sql-server-slow-query-log/
Когда вы используете SQL 2005, вы можете взять данные SQL Profiler и сравнить их с данными Perfmon, чтобы увидеть, можете ли вы увидеть корреляцию. Это делается путем сохранения данных трассировки и данных perfmon в файлы с использованием обычных методов. Затем откройте трассировку SQL Profiler в профилировщике, и тогда одним из параметров в меню файла будет Импорт данных производительности. Это позволит вам выбрать запрос и посмотреть, что счетчики делали в то время (или близко к нему, в зависимости от вашего интервала сбора данных perfmon).
Пики в очереди на диск никогда не бывают хорошими. Особенно такой высокий. Какой ввод-вывод вы отправляете на диск, когда очередь становится настолько высокой? По сути, вам не нужно, чтобы очередь на дисках превышала (2 * n), где n - количество дисков в массиве. Поскольку в вашем случае вы используете 2-дисковый RAID 1 n = 1 (поскольку вы получаете скорость только одного диска).
В perfmon есть счетчик, который показывает секунды на чтение и секунды на запись. Как выглядят эти счетчики, когда выполнение запросов начинает занимать много времени. А как насчет нормально? (Все, что превышает 0,02 секунды, - это плохо.) Какова предполагаемая продолжительность жизни страницы? (Все, что меньше 300 секунд, обычно плохо, но это может варьироваться.) Каков коэффициент попадания в кэш SQL Server? (Все, что ниже ~ 97%, обычно плохо. Мне нравится, что выше 99,9%.)
Вы видите базу данных и / или рост журнала События? Такие события будут отображаться в ERRORLOG и в счетчиках производительности.
Есть пара вещей, которые стоит попробовать, наиболее полезными из которых являются отображение предполагаемого плана выполнения и включение фактического плана выполнения в SMSS.
Если вы отметите эту кнопку «Включить фактический план выполнения» перед запуском запроса, после выполнения запроса вы увидите, где были затраты на запрос. Исходя из затрат, обычно довольно легко определить, где что-то пошло не так. Если это SORT, значит, это плохой индекс. Если он создает хеш-таблицу, то это плохой индекс / плохое соединение, есть множество вещей, которые могут пойти не так, о которых вы можете даже не знать, во время простого запроса SELECT *.
Второе, что нужно попробовать, - это запустить профилировщик SQL-запросов (выделите запрос, щелкните правой кнопкой мыши, проследите запрос в профилировщике SQL). Он также обнаружит недостатки, которые можно устранить.
Однако все ваши запросы довольно просты и не указывают на недостатки дизайна базы данных, однако они могут, по крайней мере, дать вам представление о том, куда идти дальше (очевидно, вы будете проверять его в то время, когда выполнение занимает больше времени, чем ожидалось) .
Еще одно место для поиска, и иногда это может быть информационная перегрузка, - это SQL Server Profiler (который, как вы упомянули, вы уже использовали, но поймали ли вы его во время выполнения 26 с?). Вы можете наблюдать за всем, что делает SQL-сервер, в (почти) реальном времени. В зависимости от того, как долго длится ваше окно такого рода активности, если оно у вас готово к запуску трассировки, как только оно начинает становиться пухлым, включите трассировку, посмотрите, есть ли на сервере SQL что-то, чего он ждет.
Регулярно ли вы вручную перестраиваете статистику таблиц ваших баз данных? Если они устарели и установлен параметр автоматического обновления статистики, запросы могут приостанавливаться на время восстановления статистики.
Помимо обновления статистики вручную, вы также можете рассмотреть возможность включения асинхронной статистики.
Вот T-SQL:
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON
Дальнейшее чтение:
http://msdn.microsoft.com/en-us/library/ms190397.aspx
Я не уверен, что это основная причина вашей проблемы, но, возможно, стоит ее исключить.