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

 

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

 Признаки нехватки ресурсов ЦП

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

 Признаки нехватки ресурсов в подсистеме памяти

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

 Признаки наличие узких мест в дисковой подсистеме ввода-вывода

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

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

 

• Соперничество индексов. Проверьте значения ожидания блокировок и кратковременных блокировок в представлении 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 в событии «Блокировки», чтобы определить, какие инструкции участвуют во взаимоблокировке.

 Признаки нехватки ресурсов сети

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