Распространенные проблемы производительности SQL Server в работе с приложениями OLTP

Рабочая нагрузка OLTP характеризуется высоким объемом схожих транзакций небольшого размера.

Важно учитывать эту особенность при изучении взаимосвязи структуры базы данных, использования ресурсов и производительности системы. Далее описаны наиболее распространенные узкие места в работе приложений OLTP.

1. Проблема в структуре базы данных, если…

• Выполняется слишком много соединений таблиц для частых запросов. Чрезмерное использование соединений в приложении OLTP увеличивает время выполнения запросов и непроизводительно расходует системные ресурсы. Обычно, если для частых операций необходимы 5 и более соединений таблиц, надо изменить структуру базы данных.
• Слишком большое число индексов на часто обновляемых (с учетом операций вставки, обновления и удаления) таблицах создает дополнительную нагрузку по сопровождению индексов. Обычно база данных OLTP должна поддерживать необходимый минимум индексов, что связано с уже упоминавшейся причиной большого объема схожих транзакций в сочетании с высокой стоимостью обслуживания индекса.
• Крупные операции ввода-вывода, такие как просмотр таблиц и диапазонов из-за отсутствия индексов. Транзакции OLTP по определению не должны требовать крупные операции ввода-вывода, и, если такие операции проводятся, следует изучить эту проблему.
• Неиспользуемые индексы вызывают необходимость затрачивать ресурсы на сопровождение операций вставки, обновления и удаления, но при этом не приносят пользы ни одному из пользователей. Неиспользуемые индексы следует устранить. Любой индекс, который был использован (операциями выборки, обновления или удаления), будет отображаться в представлении sys.dm_db_index_usage_stats. Поэтому любой определенный индекс, не включенный в это динамическое административное представление, не использовался с момента последнего перезапуска SQL Server.

2. Процессор является узким местом, если…

• Продолжительность ожидания сигнала превышает 25 % общего ожидания. Значения ожидания сигнала и общего ожидания см. в представлении sys.dm_os_wait_stats. Ожидание сигнала соответствует времени, которое готовый к запуску запрос провел в ожидании процессора. Высокое значение ожидания сигнала свидетельствует о нехватке ресурсов ЦП.
• Показатель повторного использования планов составляет менее 90 %. Для выполнения запроса используется план запроса. Для рабочей нагрузки OLTP желательно неоднократное использование планов, поскольку повторное создание того же плана (для схожих или идентичных транзакций) будет напрасной тратой ресурсов процессора. Сравните параметры статистики SQL Server: число пакетных запросов в секунду и число компиляций SQL-кода в секунду. Вычислите показатель повторного использования планов по следующей формуле: повторное использование планов = (число пакетных запросов — число компиляций SQL-кода) / число пакетных запросов. Из правила повторного использования планов есть исключение: планы с нулевой стоимостью не будут кэшироваться (и не будут повторно использоваться) в SQL 2005 с пакетом обновления 2 (SP2). Приложения, использующие планы с нулевой стоимостью, будут иметь более низкий показатель повторного использования планов, но это не вызовет проблем с производительностью.
• Продолжительность ожидания параллельного потока cxpacket превышает 10 % общего ожидания. Параллелизм позволяет ускорить выполнение запросов за счет дополнительных затрат процессорных ресурсов. С учетом большого в задачах OLTP количества транзакций параллельные запросы обычно сокращают пропускную способность OLTP, поэтому их использования следует избегать. Статистику ожидания см. в представлении sys.dm_os_wait_stats.

3. Память является узким местом, если…

• Стабильно низкое среднее значение ожидаемого срока жизни страницы. Проверьте значение счетчика «Средний ожидаемый срок жизни страницы», который расположен в объекте системного монитора «Диспетчер буферов SQL Server» (это значение соответствует среднему времени в секундах, в течение которого страница остается в кэше). Для задач OLTP средний ожидаемый срок жизни страниц составляет 300 (5 минут). Меньшие значения могут свидетельствовать о нехватке памяти, отсутствии индексов или записи кэша на диск.
• Внезапное резкое падение ожидаемого срока жизни страницы. В приложениях OLTP (с транзакциями малого размера) ожидаемый срок жизни страницы должен быть стабильным (или медленно возрастающим). См. объект системного монитора «Диспетчер буферов SQL Server».
• Наличие ожидающих операций выделения памяти. Проверьте значение счетчика «Ожидающие запросы на выделение памяти» в объекте системного монитора «Диспетчер памяти SQL Server». Для транзакций OLTP, имеющих малый размер, нет необходимости выделять большой объем памяти.
• Внезапное снижение или стабильно низкое значение коэффициента попадания в кэш. Для приложений OLTP (с малым размером транзакций) коэффициент попадания в кэш должен быть высоким. В силу малого размера транзакций OLTP не должны наблюдаться (1) резкие падения коэффициента попадания в кэш и (2) стабильно низкие значения коэффициента попадания в кэш (менее 90 %). Резкие падения или стабильно низкие значения этого коэффициента могут сигнализировать о нехватке памяти или отсутствии индексов.

4. Подсистема ввода-вывода является узким местом, если…

• Высокое значение средней продолжительности чтения с диска. Если в подсистеме ввода-вывода организуется очередь, продолжительность операции чтения возрастает. См. значение системного монитора для логического или физического диска (счетчик числа секунд на операцию чтения). Обычно, если ресурсов ввода-вывода достаточно, для завершения операции чтения требуется 4–8 мс. Если ресурсов ввода-вывода недостаточно из-за высокого объема запросов, среднее время операции чтения возрастает и в результате формируются дисковые очереди. Для многих приложений будет допустимым периодическое возрастание продолжительности чтения. Для высокопроизводительных приложений OLTP сложные подсистемы SAN обеспечивают повышенную масштабируемость ввода-вывода и устойчивость к резким всплескам нагрузки на подсистему ввода-вывода. Стабильно высокие значения продолжительности чтения с диска (>15 мс) свидетельствуют о нехватке ресурсов диска.
• Высокое значение средней продолжительности записи на диск. См. значение системного монитора для логического или физического диска. Пропускная способность для приложений OLTP с большим числом транзакций зависит от скорости последовательной записи в журнал транзакций. В высокопроизводительных средах SAN время записи в журнал транзакций может составлять 1 мс (и менее). Для многих приложений периодическое возрастание средней продолжительности записи на диск будет приемлемым с учетом высокой стоимости сложных подсистем SAN. Однако стабильно высокое значение средней продолжительности записи на диск является достоверным свидетельством нехватки дисковых ресурсов.
• Крупные операции ввода-вывода, такие как просмотр таблиц и диапазонов из-за отсутствия индексов.

Наиболее заметные статистические показатели ожидания в представлении sys.dm_os_wait_stats относятся к вводу-выводу, в том числе ASYNCH_IO_COMPLETION, IO_COMPLETION, LOGMGR, WRITELOG и PAGEIOLATCH_x.

5. Механизм блокировки является узким местом, если…

• Возникает конфликт индексов. Проверьте значения ожидания блокировок и кратковременных блокировок в представлении sys.dm_db_index_operational_stats. Сравните эти показатели с числом запросов на блокировку и кратковременную блокировку.
• Возникает высокая средняя продолжительность ожидания блокировки или кратковременной блокировки строк. Средняя продолжительность ожидания блокировки или кратковременной блокировки строк вычисляется путем деления времени ожидания блокировки и кратковременной блокировки (в мс) на число случаев ожидания блокировки и кратковременной блокировки. Средняя продолжительность ожидания блокировки в миллисекундах, вычисленная по данным sys.dm_db_index_operational_stats, представляет среднее время для каждого блока.
• В отчете по заблокированным процессам показаны длинные блоки. См. значение параметра blocked process threshold процедуры sp_configure и класс событий профайлера Blocked Process Report в событии «Ошибки и предупреждения».
• Наиболее ожидаемой статистикой является LCK_x. См. представление sys.dm_os_wait_stats.
• Возникает большое число взаимоблокировок. Чтобы определить, какие инструкции участвуют во взаимоблокировке, см. класс событий профайлера Graphical Deadlock в событии «Блокировки».

6. Сеть является узким местом, если…

• Высокое значение задержки сети в сочетании с использованием приложения, многократно передающего данные в базу данных и получающего данные из базы.
• Исчерпана пропускная способность сети. См. счетчик числа пакетов в секунду и счетчики текущей пропускной способности в объекте системного монитора «Сетевой интерфейс». Для кадров TCP/IP фактическая пропускная способность рассчитывается по следующей формуле: (число пакетов в секунду)*1 500*8/1 000 000 (Мбит/с).