Статистическая коллекция MS 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).

Статистическая коллекция MS SQL Server

Оптимизатор СУБД семейства MS SQL Server собирает следующую коллекцию статистической информации уровня таблиц, которая является частью объекта статистики, и использует ее и для оценки стоимости запроса:

• число строк в таблице или индексе (поле rows в sys.sysindexes );
• число страниц, занятых таблицей или индексом (поле dpages в sys.sysindexes ).

Оптимизатор СУБД семейства MS SQL Server собирает следующую статистику по столбцам таблицы и сохраняет ее в объекте статистики (statblob):

• время, когда были собраны статистические данные;
• число строк, используемое для создания гистограммы, и информация о плотности (описано ниже);
• средняя длина ключа;
• гистограмма отдельного столбца, включая номера шагов;
• резюме по строке, если поле содержит символьные данные. Результат, выводимый DBCC SHOW_STATISTICS, содержит столбец String Index, который принимает значение YES, если объект статистики содержит резюме для строки.

Гистограмма — это набор значений данного поля, ограниченный до 200 значений. Все значения поля, или выборка из них, отсортированы, и эта упорядоченная последовательность может быть разделена не более чем на 199 интервалов так, чтобы фиксировалась наиболее статистически важная информация. Как правило, эти интервалы имеют разные размеры. Ниже представлены значения или информация, достаточная для получения такой информации и сохраняемая для каждого шага в гистограмме.

• RANGE_HI_KEY — значение ключа, показывающее верхнюю границу шага гистограммы.
• RANGE_ROWS — определяет, сколько строк внутри диапазона (они должны иметь значения ключа меньше, чем у своего RANGE_HI_KEY, но больше, чем меньшее значение RANGE_HI_KEY у предыдущего диапазона).
• EQ_ROWS — определяет, какое число строк в точности равно RANGE_HI_KEY.
• AVG_RANGE_ROWS — среднее число строк с разными значениями в диапазоне.
• DISTINCT_RANGE_ROWS — определяет число разных значений ключа внутри этого диапазона (не включая значения ключа предыдущего диапазона своего RANGE_HI_KEY ).

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

• Histogram initialization: инициализация гистограммы является первым шагом, на котором идет работа по сбору последовательности значений, начинающихся с начала отсортированного набора и до 200 значений RANGE_HI_KEY, EQ_ROWS, RANGE_ROWS и DISTINCT_RANGE_ROWS ( RANGE_ROWS и DISTINCT_RANGE_ROWS на этом шаге всегда равны нулю). Первый шаг заканчивается, если были пройдены все полученные на входе значения или если были найдены первые 200 значений.
• Scan with bucket merge: сканирование со слиянием в диапазоны является вторым шагом, на котором, в порядке сортировки, обрабатывается каждое дополнительное значение первого столбца ключа статистики. Каждое значение в последовательности может быть добавлено к последнему диапазону или в новый диапазон, создаваемый в конце существующих диапазонов (это возможно потому, что входные значения отсортированы). Если был создан новый диапазон, то одна пара из существующих соседних диапазонов будет объединена в единый диапазон. Эта пара диапазонов выбирается из соображений предотвращения потери информации. Число шагов после слияния диапазонов остается в пределах 200. Этот метод основан на вариации maxdiff гистограммы.
• Histogram consolidation: консолидация гистограммы составляет третий шаг, на котором может быть подвержено слиянию еще больше число диапазонов, если при этом не будет потерян существенный объем информации. Поэтому, даже если столбец имеет более 200 уникальных значений, число шагов гистограммы может быть меньше 200.

Если гистограмма была сформирована с использованием выборки, то значения RANGE_ROWS, EQ_ROWS, DISTINCT_RANGE_ROWS и AVG_RANGE_ROWS будут иметь оценки и поэтому не будут целыми числами.

Плотность — это информация о числе дубликатов в анализируемом столбце или комбинации столбцов, и она вычисляется так: 1 / (число различающихся значений). Когда столбец используется в предикате равенства, тогда число квалифицированных строк будет оценено с применением значения плотности, полученного из гистограммы. Гистограммы также нужны для оценки селективности предикатов в выборках с неравенствами, объединениями и другими операторами.

В дополнение к timestamp (показывающему время, когда были собраны статистические данные), числу строк в таблице, числу отобранных для создания гистограммы строк, плотности, информационной и средней длине ключа и непосредственно к самой гистограмме, статистическая информация по одному столбцу включает еще значение All density, формируемое для каждого набора столбцов и определяющее префикс набора статистики столбца. Это значение можно увидеть во втором блоке строк, выводимом командой DBCC SHOW_STATISTICS. All density представляет из себя оценку: 1 / (число различающихся значений в префиксном наборе столбца). В следующем абзаце поясняется смысл этого значения.

Каждый набор статистики по нескольким столбцам (гистограмма и два или более значения плотности) хранится в одном statblob вместе с timestamp последнего обновления статистики, числом строк в типичной для сбора статистике выборке, числом шагов в гистограмме и средней длиной ключа. Резюме по строке создается только для первого столбца, если он содержит символьные данные.

sp_helpindex и sp_helpstats показывают списки статистик, доступные для анализируемой таблицы. sp_helpindex показывает все индексы таблицы, а sp_helpstats — список всех статистик по таблице. Каждый индекс также имеет статистическую информацию для ее столбцов. Создаваемая с использованием команды CREATE STATISTICS статистическая информация эквивалентна статистике, сформированной командой CREATE INDEX, если индекс создается на тех же столбцах. Единственная разница — при использовании команды CREATE STATISTICS будет задействована используемая по умолчанию выборка, в то время как для команды CREATE INDEX сбор статистики будет сопровождаться полным сканированием таблицы, так как в любом случае для построения индекса будут обработаны все строки таблицы.

Анализ запросов с целью повышения скорости их выполнения

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

Шаг 1. Обновить статистику. До того, как добавить индексы, необходимо убедиться, что статистика БД в системном каталоге корректна. Если вы выполняете запрос без учета действительной производительности БД, вам следовало бы обновить статистику для всех таблиц, указанных в предложении FROM, используя команду UPDATE STSTISTICS или другую специальную команду СУБД. С другой стороны, если вы используете небольшую тестовую БД, то можно вручную вычислить необходимые статистические показатели и внести их в системный каталог.

Когда вы обновляете статистику, вам следовало бы скомпилировать команду SQL. Сравните новый план запроса со старым до обновления статистики, чтобы определить изменения в нем (иногда требуется довольно длительное время для построения плана.) Сравнивая планы, можно избежать повторного выполнения запроса только для того, чтобы убедиться, что производительность его выполнения идентична предыдущему выполнению этого запроса. Если статистика изменилась – выполните запрос, чтобы определить, увеличилась ли производительность и насколько.

Шаг 2. Упростить команду SELECT. Перед добавлением индексов или переписыванием плана выполнения следует попытаться упростить запрос. Задача состоит в том, чтобы сделать выражение SELECT как можно проще, сократив по мере возможностей число переменных в нем. Упростив запрос, скомпилируйте команду, чтобы посмотреть план запроса. Сравните новый план запроса со старым. Определите, увеличилась ли производительность запроса, выполнив его.

Для того чтобы упростить SELECT, необходимо:

• исключить ненужные предикаты и предложения;
• расставить скобки в арифметических и логических выражениях;
• преобразовать связанные переменные в константы.

Исключение ненужных предикатов и предложений. Обычно в команду SELECT включают предложений больше, чем это необходимо на самом деле. Так делается для того, чтобы гарантировать корректность ответа, или из-за плохого понимания синтаксиса SQL. До попытки настроить запрос проектировщик базы данных должен удалить все ненужные предложения и предикаты.

Примерами предложений, которые обычно включаются в запрос, но не являются необходимыми, являются:

• предложение ORDER BY. Часто это предложение включается, даже если определенный порядок в результирующем множестве не требуется приложением или конечным пользователем;
• предикаты предложения WHERE. Часто это предложение содержит избыточное множество предикатов ограничения. Например, предикаты в следующем предложении WHERE являются избыточными, так как DEPT_NO есть первичный ключ, и, следовательно, будет уникально идентифицировать только одну строку:

  WHERE DEPT_NO = 10 AND DEPT_NAME = 'PERATIONS'.

Расстановка скобок в арифметических и логических выражениях. Синтаксис SQL обычно включает специальные правила предшествования для оценки арифметических и логических выражений. Однако достаточно просто сделать ошибку при применении правил предшествования. Следовательно, мы рекомендуем вам расставлять скобки в этих выражениях для явного определения предшествования в выполнении операций. Вы можете подумать, что оптимизатор оценивает выражения одним способом, а в действительности он поступает по-другому.

Преобразование связанных переменных в константы. Связанные переменные обычно применяются, когда компилируется команда SQL. Часто желательно использовать хранимые команды для того, чтобы исключить перекомпиляцию утверждения SQL, которое будет выполняться много раз. Эти хранимые команды являются скомпилированными, и их планы выполнения сохраняются в системном каталоге вместе с исходным текстом. Приложения тоже выполняют предкомпиляцию для многократно применяемых команд выборки. В этих ситуациях связанные переменные используются для передачи некоторых значений в команду во время ее выполнения. Однако когда связанные переменные задействуются, оптимизатор не знает, какие границы переменных положены при выполнении команды. Это вынуждает оптимизатор запросов делать грубые предположения при вычислении фактора селективности. Такие предположения могут привести оптимизатор к вычислению большого фактора селективности и применению специфических индексов.

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

При использовании связанных переменных в предикате селекции оптимизатор запросов вынужден использовать умалчиваемый фактор селективности 1/3.

Рассмотрим следующий пример.

Пример 24.2. Предикаты: Amount > :BindVar и Amount > 1000. Преимущество применения первого предиката состоит в том, что команда может быть откомпилирована один раз и затем много раз использована с различными значениями. Недостаток состоит в том, что оптимизатор запросов имеет меньше информации для его оценки во время компиляции. Он не знает, будет ли 10 или 10000000 стоять вместо связанной переменной. Следовательно, он не способен вычислить корректно фактор селективности. В этой ситуации будет установлено значение по умолчанию, которое не обязательно будет отражать истинную ситуацию в данных. Поскольку значение фактора селективности, равное 1/3, относительно высокое (при отсутствии других предикатов), оптимизатор не может использовать какой-либо индекс, связанный с колонкой в этом предикате.

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

Связанные переменные также приводят к проблеме при использовании предиката оператора LIKE. Этот оператор может включать символ подстановки.

Пример 24.3. Рассмотрим запрос на поиск всех продавцов, чье имя начинается с буквы "А":

SELECT * FROM CUSTOMER WHERE NAME LIKE 'A%';

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

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

Шаг 3. Пересмотреть план запроса. Выполните запрос так, чтобы посмотреть его план. Вы должны хорошо понимать план запроса, чтобы использовать его. Несколько элементов этого плана требуют особого внимания.

• Преобразование подзапроса в соединение. Оптимизатор преобразует большинство подзапросов в соединения. Нужно знать, на каких этапах выполнения запроса это преобразование

продолжение следует...

Продолжение:

Часть 1 Настройка производительности запросов к хранилищу данных
Часть 2 Статистическая коллекция MS SQL Server - Настройка производительности запросов к
Часть 3 Оптимизация запросов для схем типа "звезда" - Настройка производительности запросов