Десять важнейших проблем производительности SQL Server 2005 для приложений хранилищ данных и отчетности

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

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

Поиск ключа по ключу кластеризации похож на объединение, однако в XML Showplan это обозначено как «поиск». Этого можно избежать с помощью включенных в объединение некластеризованных индексов.

Отсутствует потенциально выгодный индекс по столбцам, участвующим в объединении, что приводит к объединению хешированием. Избежать объединения HASH JOIN можно, создав индексы по столбцам объединения.

Если показателей signal waits > 25% от общего числа показателей ожидания, значит, процессор представляет собой «узкое место». Смотрите показатели ожидания signal waits и total waits в динамическом представлении sys.dm_os_wait_stats. Показатели signal waits означают время, проведенное в очереди выполнения в ожидании процессора. Высокое значение signal waits указывает на узкое место при обработке процессором.

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

Резкое значительное снижение ожидаемого срока жизни страницы. Приложения хранилищ данных (например, большие транзакции) могут испытывать значительное снижение ожидаемого срока жизни страницы. Это происходит из-за сброса кэша большой операции чтения. См. объект системного монитора SQL Server Buffer Manager.

Ожидающие запросы на предоставление памяти. См. счетчик запросов, ожидающих предоставления памяти объекта SQL Server Memory Manager системного монитора. Выделение больших объемов памяти типично для приложений хранилищ данных. Добавление памяти может улучшить ситуацию, в противном случае выполнение невозможно до того, как произойдет выделение памяти.

Неожиданные падения или постоянно низкий уровень эффективности работы кэш-памяти SQL. Падения или постоянно низкий уровень эффективности работы кэш-памяти может указывать на нехватку памяти или отсутствующие индексы.

Лучшим показателем производительности записи диска являются время в секундах на чтение и на запись. Когда нагрузка на систему ввода-вывода незначительна, очередь диска не используется, поэтому время на запись или чтение будет минимальным. Обычно завершение операции чтения требует 4-8 миллисекунд в условиях нормальной загрузки. Факторами, влияющими на производительности системы ввода-вывода, является количество шпинделей и такие показатели производительности диска, как количество последовательных и случайных операций (по данным производителя). С ростом количества операций ввода-вывода может возникать очередь дисковых операций. Результатом очереди диска является рост временных затрат на операции чтения и записи. Периодическое возрастание времени на чтение-запись может быть приемлемо для многих приложений. Для нужд высокопроизводительных приложений OLTP используются сложные подсистемы сетевого хранения SAN, обеспечивающие большую масштабируемость ввода-вывода и надежную обработку пиков активности ввода-вывода. Постоянно высокие значения времени на операцию чтения (>15 мс) указывают на узкое место в дисковой подсистеме.

Высокие значения времени на операцию чтения. См. счетчики производительности логических или физических дисков системного монитора. Загрузка хранилищ данных может производиться либо с помощью операций вставки, обновления и удаления с ведением журнала, либо с помощью операций массового копирования без ведения журнала. Ведение журнала операций требует записи в журнал транзакций. Скорость записи в журнал транзакций может достигать 1мс и меньше в высокопроизводительных средах SAN. Для многих приложений периодический всплеск среднего времени на операцию записи приемлем, учитывая высокую стоимость сложных подсистем SAN. Однако постоянно высокие значения среднего времени на операцию записи являются точным показателем узкого места в дисковой подсистеме.

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

Соперничество индексов. Ищите в представлении sys.dm_db_index_operational_stats высокие значения ожидания блокировок и запираний. Сравните с запросами блокировок и запираний.

Высокие средние значения ожидания блокировок и ожиданий. Средние значения ожидания блокировок и запираний записей вычисляются делением времени ожидания блокировок в миллисекундах на ожидания. Среднее время ожидания блокировки в миллисекундах, рассчитанное по данным sys.dm_db_index_operational_stats, представляет собой среднее время каждой блокировки.

Отчет о процессе блокировки сообщает о длительных блокировках. См. команду sp_configure для установки порогового значения интервала ожидания заблокированных процессов отчет о заблокированных процессах профилировщика в событии «Ошибки и предупреждения».

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

Большие задержки сети, а также приложение, постоянно обращающееся к базе данных.

Пропускная способность сети недостаточна. См. счетчики пакетов в секунду и текущие счетчики пропускной способности в объекте сетевого интерфейса системного монитора. Для кадров TCP/IP реальная пропускная способность рассчитывается как количество пакетов в секунду * 1500 * 8 /1000000 Мбит/сек.

Поскольку нагрузки хранилищ данных и отчетов являются в основном операциями чтения, совместимыми с другими операциями чтения, обычно несовместимые монопольные блокировки возникают при пакетной загрузке данных или периодических загрузках. Если статистика ожиданий отображает LCK_x. или PAGELATCH_EX, см. объяснение sys.dm_os_wait_stats в руководстве “SQL Server 2005 Performance Tuning using Waits & Queues”.

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

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

Смотрите записи об отсутствующих индексах в sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups и sys.dm_db_missing_index_details

Чрезмерная фрагментация создает проблемы для больших операций ввода-вывода. Функция таблицы динамического управления sys.dm_db_index_physical_stats возвращает процент фрагментации в столбце avg_fragmentation_in_percent. Фрагментация не должна превышать 25%. От снижения фрагментации индексов могут выиграть операции сканирования больших диапазонов данных, обычные в приложениях хранилищ данных и отчетов.

В случае больших таблиц, которые часто встречаются в приложениях хранилищ данных, секционирование предлагает важные преимущества в производительности и управлении. Например, самым быстрым способом загрузки данных является массовое копирование без ведения журнала. При таком копировании индексы должны быть удалены. На таблице с миллиардами записей это может быть трудноосуществимо, если только не использовать секционирование таблиц. Таким образом можно создать промежуточную таблицу, идентичную большой таблице без индексов. Для загрузки данных используется быстрое массовое копирование без ведения журнала. Затем к промежуточной таблице добавляются индексы, а после них — ограничения. Затем операция метаданных SWITCH IN переключает адреса указателей промежуточной таблицы с данными и пустой целевой секции секционированной таблицы, в результате чего получается секция с данными и пустая промежуточная таблица. Кроме ускорения загрузки данных, секционированные таблицы позволяют устранить блокировки в больших секционированных таблицах во время загрузки. Более подробную информацию см. по адресу «Loading Bulk Data into Partitioned Tables » (на английском языке). Помимо быстрой загрузки секционированные таблицы позволяют выполнять быстрое удаление (с целью архивации или удаления скользящего окна), где вместо удаления больших объемов с ведением журнала применяется операция переключения метаданных SWITCH OUT, меняющая адреса указателей полной секции, подлежащей удалению, и пустой таблицы. После операции SWITCH OUT остается пустая секция и заполненная данными промежуточная таблица. Затем промежуточную монолитную таблицу можно либо удалить, либо добавить в секционированную архивную таблицу с помощью той же операции SWITCH IN. Секции также удобнее в управлении — сочетая их со специальным размещением файлов, можно реализовывать различные стратегии резервного копирования и восстановления.