Настройка производительности запросов к хранилищу данных

Оптимизатор СУБД семейства MS SQL Server

Оптимизатор СУБД семейства MS SQL Server на основе оценки стоимости динамически определяет стратегию обработки запроса, основанную на текущей структуре таблицы/индекса и данных. Такое динамическое поведение может быть преодолено с помощью хинтов оптимизатора, забирая некоторые решения из рук оптимизатора и инструктируя его использовать определенную стратегию обработки. Это делает поведение оптимизатора статическим и не позволяет ему динамически обновлять стратегию обработки при изменении структуры таблицы или индекса, а также изменении данных.

В СУБД семейства MS SQL Server предусмотрены инструменты мониторинга производительности работы сервера, для понимания работы которых необходимо познакомиться с используемой терминологией и принципами мониторинга производительности.

Мониторинг производительности начинается с определения эталонного графика производительности. Эталонный график производительности – это набор определенных показателей производительности, собранных в начале эксплуатации БД.

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

Эти действия являются важными, поскольку оптимизатор СУБД MS SQL Server опирается на собранную статистику при построении планов выполнения запросов.

По собранным данным определяются ресурсы, которые тормозят работу сервера.

В MS SQL Server предусмотрено достаточное количество счетчиков, которые позволяют определить ресурсы и запросы, оказывающие влияние на производительность сервера.

Оптимизатор СУБД MS SQL Server использует два основных показателя: время отклика системы на запросы пользователей и пропускную способность (throughput). Время отклика является субъективным показателем, а пропускная способность — объективным показателем работы сервера, числом транзакций в секунду.

Наиболее универсальным средством мониторинга и анализа производительности MS SQL Server является системный монитор.

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

Вторым важным инструментом для анализа производительности является профилировщик, который позволяет найти и запротоколировать команды, требующие много времени на выполнение.

Существует еще несколько инструментов, предназначенных для мониторинга и анализа производительности в СУБД семейства MS SQL Server.

Отметим, что оптимизация запросов является задачей администратора ХД. Проектировщик ХД должен представлять в целом, как выполняется настройка производительности, чтобы использовать эти знания при проектировании схем ХД.

Статистики, используемые оптимизатором запросов СУБД MS SQL Server

СУБД семейства MS SQL Server имеют возможность автоматически создавать и обновлять статистику. Этот механизм задействуется по умолчанию. В большинстве приложений БД разработчики и администраторы могут пользоваться автоматическим созданием и обновлением статистики, обеспечивающим в достаточной мере всестороннюю и точную статистику данных, по которой оптимизатор запросов SQL Server выбирает хорошие планы исполнения. Имеется также возможность управлять созданием и обновлением статистики вручную.

Важным моментом с точки зрения обеспечения высокой производительности приложений ХД и БД является возможность асинхронного обновления статистики в автоматическом режиме. Это помогает повысить предсказуемость времени отклика на запрос в высокопроизводительных системах.

В СУБД семейства MS SQL Server имеются следующие возможности работы со статистикой:

• implicitly create and update statistics — фоновое создание и обновление статистики с заданной по умолчанию частотой обновления (в командах SELECT, INSERT, DELETE и UPDATE использование столбца в условии WHERE или в JOIN приводит к созданию или обновлению статистики, если это необходимо, и при условии, что включено автоматическое обновление);
• manually create and update statistics — ручное управление статистикой, с заданной частотой обновления и удаления ( CREATE STATISTICS, UPDATE STATISTICS, DROP STATISTICS, CREATE INDEX, DROP INDEX );
• manually create statistics in bulk — ручное создание статистики для всех столбцов во всех таблицах БД ( sp_createstats );
• manually update all existing statistics — ручное обновление статистики во всей БД ( sp_updatestats );
• list statistics objects — просмотр существующих объектов статистики таблицы или БД ( sp_helpstats, представления каталога sys.stats, sys.stats_columns );
• display descriptive information about statistics objects — просмотр описаний объектов статистики ( DBCC SHOW_STATISTICS );
• enable and disable automatic creation and update of statistics — включение/выключение автоматического создания и обновления статистики для всей БД либо для определенной таблицы или объекта статистики (опции ALTER DATABASE: AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS; sp_autostats; и опции NORECOMPUTE: CREATE STATISTICS и UPDATE STATISTICS );
• enable and disable asynchronous automatic update of statistics — включение/выключение автоматического, асинхронного обновления статистики ( ALTER DATABASE, опция AUTO_UPDATE_STATISTICS_ASYNC ).

Кроме того, SQL Server Management Studio позволяет в графическом интерфейсе просматривать и управлять объектами статистики, которые можно просматривать в проводнике объектов в специальной папке под каждым объектом таблицы.

В MS SQL Server применено множество влияющих на статистику показателей и механизмов, которые позволяют оптимизатор запросов улучшить выбор плана исполнения запроса за счет анализа более широкого диапазона запросов или же предоставить возможность более тонко управлять сбором статистики. Можно выделить следующие показатели и механизмы:

• String summary statistics – частота распределения подстрок при анализе символьных полей. Помогает оптимизатору лучше оценивать селективность условий с оператором LIKE ;
• Asynchronous auto update statistics – асинхронное, автоматическое обновление статистики, в операторе ALTER DATABASE опция AUTO_UPDATE_STATISTICS_ASYNC. Когда опция задействуется, MS SQL Server автоматически обновляет статистику в фоновом режиме. При этом запрос, который привел к обновлению статистики, ничего не блокирует и используется уже накопленная статистика. Все это позволяет обеспечить большую предсказуемость времени отклика запроса для некоторых типов рабочей нагрузки;
• Computed column statistics – статистика по вычисляемым полям может собираться вручную или автоматически;
• Large object support – поддержка больших объектов, таких как столбцы типов: ntext, text и image, а также новых типов данных: nvarchar(max), varchar(max) и varbinary(max) — они здесь также могут быть определены как столбцы, по которым собирается статистика;
• Improved statistics loading framework – улучшенная статистика загруженных структур позволяет оптимизатору получать статистику внутренних механизмов, позволяя охватить все относящиеся к статистике аспекты, за счет чего повышается качество результата и, соответственно, оптимизация и производительность;
• Increased ability to automatically create statistics on computed columns – возможность автоматического создания статистики по вычисляемым полям;
• Minimum sample size – минимальный размер выборки установлен в 8 Мб при исчислении данных, или он приравнивается к размеру таблицы, если она меньше этого размера;
• Increased limit on number of statistics – увеличено предельное число статистик, т.е. число объектов статистики, колонок для одной таблицы, теперь оно равно 2000, и еще 249 индексных статистик могут быть добавлены, делая общее число объектов статистических данных на таблицу равным 2249;
• Enhanced DBCC SHOW_STATISTICS output – расширение возможностей DBCC SHOW_STATISTICS позволяет теперь отображать имена объектов статистики, что позволяет избегать двусмысленности;
• Statistics auto update is now based on column modification counters – автоматическое обновление статистики теперь основано на счетчиках модификации колонки, изменения отслеживаются на уровне колонки, и автоматическое обновление статистики можно предотвратить для тех колонок, для которых не было зафиксировано достаточно изменений;
• Statistics on internal tables – статистика по внутренним таблицам собирается для таблиц, перечисленных в sys.internal_tables, включая XML и полнотекстовые индексы, очереди брокера сервисов и запросы к таблицам оповещений;
• Single rowset output for DBCC SHOW_STATISTICS – единый отчет по набору строк для DBCC SHOW_STATISTICS предоставляет возможность вывести единый заголовок, вектор плотности и гистограмму для набора строк. Это позволяет упростить разработку автоматов обработки результатов исполнения DBCC SHOW_STATISTICS ;
• Statistics on up-to 32 columns – с 16 до 32 было увеличено число колонок в объекте статистики;
• Statistics on partitioned tables – статистика по секциям таблиц поддерживается для секционированных таблиц. Гистограммы поддерживаются для таблиц, но не для секций таблицы;
• Parallel statistics gathering for fullscan – для статистики, собранной во время полного сканирования, создание одного объекта статистики может распараллеливаться и в секционированных, и в обычных таблицах;
• Improved recompiles and statistics creation in case of missing statistics – стали лучше учитываться такие моменты, как перекомпиляция и создание статистики в случае ее отсутствия, в режиме автоматического создания или при неудачах сбора статистики. При последующем применении плана исполнения, созданного без статистики, статистика генерируется автоматически, запрос исполняется и план перекомпилируется. Состояние отсутствия статистики не хранится. Для получения дополнительной информации, обратитесь к технической документации MS SQL Server надлежащей версии;
• Improved recompilation logic and statistics update for empty tables – улучшена логика рекомпиляции и обновления статистики для пустых таблиц. Изменение от 0 до > 0 строк в таблице приводит к рекомпиляции запроса и обновлению статистики;
• Clearer and more consistent display of histograms – стали более понятными и менее противоречивыми показания гистограмм. Внесены улучшения в DBCC SHOW_STATISTICS, из-за которых гистограммы теперь всегда предварительно масштабируются, а уже потом сохраняются в каталогах;
• Inferred date correlation constraints – добавлены ограничения дедуктивной корреляции дат, с которыми, через опцию БД DATE_CORRELATION_OPTIMIZATION, можно заставить SQL Server учитывать информацию о корреляции полей типа datetime между парами таблиц, связанных внешним ключом. Эта информация используется для того, чтобы иметь возможность определять для небольшого числа запросов подразумеваемые для них предикаты, и не используется непосредственно для оценки селективности или оценочной стоимости для оптимизатора, так что она не является статистикой в строгом смысле, но очень близка к статистике, являясь вспомогательной информацией, обычно помогающей получать лучший план запроса;
• sp_updatestats – эта процедура обновляет только те статистические данные, которые требуют обновления, основываясь при этом на информации из rowmodctr в системном представлении sys.sysindexes, устраняя таким образом ненужные обновления для неизменяемых элементов. Для БД, у которых уровень совместимости установлен в 90 и выше, sp_updatestats использует для UPDATE STATISTICS установки, соответствующие автоматическому режиму для любых индексов или статистик.

Приведем несколько определений терминов, которые будут использованы в дальнейшем.

Объект statblob: статистический Binary Large Object (BLOB), т.е. большой, бинарный статистический объект. Этот объект хранится во внутреннем представлении каталога sys.sysobjvalues.

Статистика String Summary: резюме строки — это такая форма статистики, которая описывает частоту распределения подстрок в поле записи. Используется для оценки селективности предикатов LIKE. Хранится в statblob для поля записи.

Объект sysindexes: системное представление каталога sys.sysindexes, которое содержит информацию о таблицах и индексах.

Объект Predicate: предикат — это условие, которое оценивается как истина или ложь. Предикаты используются в предложении WHERE или в JOIN запросов к базе данных.

Selectivity: селективность — это доля строк в получаемом предикатом наборе данных, которые удовлетворяют условию этого предиката. Также встречаются более сложные определения селективности, которые необходимы для оценки числа строк, вовлеченных в объединения, DISTINCT и другие операторы. Например, SQL Server 2005 оценивает селективность предиката "Sales.SalesOrderHeader.OrderID = 43659" в базе данных AdventureWorks как 1/31465 = 0.00003178.

Cardinality estimate: оценка числа элементов, позволяет определить объем результирующего набора. Например, если таблица T имеет 100000 строк, а запрос содержит предикат отбора: T.a = 10, и гистограмма показывает селективность T.a = 10 – 10 %, то оценка количества элементов в той доле строк T, которую нужно обработать запросом, будет: 10 % * 100000, и равна 10000 строк.

Объект LOB: большой объект, обычно имеет типы: image, text, ntext, varchar(max), nvarchar(max), varbinary(max).