SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Использование статистики оптимизатором запросов Microsoft SQL Server 2005

ПУБЛИКАЦИИ  

По материалам статьи Eric N. Hanso: Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
Перевод Александра Гладченко

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

СОДЕРЖАНИЕ

Обзор механизмов сбора статистики

SQL Server 2005 имеет много обслуживающих статистику механизмов. Самый важный из них - это возможность автоматически создавать и обновлять статистику. Этот механизм задействуется по умолчанию в SQL Server 2005 и SQL Server 2000. Приблизительно 98 % инсталляций SQL Server 2000 оставляют задействованным механизм автоматического обновления статистики, что принято считать хорошей практикой. В большинстве приложений баз данных, разработчики и администраторы могут положиться на автоматическое создание и обновление статистики, которое в достаточной мере обеспечивает всестороннюю и точную статистику данных, по которой оптимизатор запросов SQL Server 2005 выбирает хорошие планы исполнения, и в то же время снижаются затраты на разработку и администрирование. Если же Вам нужно в большей мере управлять созданием и обновлением статистики, чтобы получить боле соответствующие Вашим нуждам планы исполнения запросов, или более тонко управлять сбором статистики, Вы можете использовать ручное создание и обновление статистики.
Важным новшеством с точки зрения обеспечения высокой производительности приложений баз данных является возможность асинхронного обновления статистики в автоматическом режиме. Это помогает повысить предсказуемость времени отклика на запрос в высокопроизводительных системах.
В SQL Server 2005 имеются следующие возможности работы со статистикой:

  • 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 позволяет в графическом интерфейсе просматривать и управлять объектами статистики, которые можно просматривать в Проводнике Объектов в специальной папке под каждым объектом таблицы.

[В начало]

Новшества в статистике SQL Server 2005

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

  • String summary statistics: частота распределения подстрок при анализе символьных полей. Помогает оптимизатору лучше оценивать селективность условий с оператором LIKE.

  • Asynchronous auto update statistics: асинхронное, автоматическое обновление статистики, в операторе ALTER DATABASE опция AUTO_UPDATE_STATISTICS_ASYNC появилась в SQL Server 2005 и отключена по умолчанию. Когда опция задействуется, SQL Server 2005 автоматически обновляет статистику в фоновом режиме. При этом запрос, который привёл к обновлению статистики, ничего не блокирует, и используется уже накопленная статистика. Всё это позволяет обеспечить большую предсказуемость времени отклика запроса для некоторых типов рабочей нагрузки.

  • Computed column statistics: статистика по вычисляемым полям может собираться вручную или автоматически (это было частично реализовано в SQL Server 2000, но было не документировано).

  • Large object support: поддержка больших объектов, таких как столбцы типов: ntext, text и image, а так же новых типов данных: nvarchar(max), varchar(max) и varbinary(max), которые теперь также могут быть определены как столбцы, по которым собирается статистика.

  • Improved statistics loading framework: улучшенная статистика загруженных структур позволяет оптимизатору лучше, чем в SQL Server 2000, получать статистику внутренних механизмов, позволяя охватить все относящиеся к статистике аспекты, за счёт чего повышается качество результата и соответственно оптимизации и производительности.

  • Increased ability to automatically create statistics on computed columns: за счёт появления возможности автоматического создания статистики по вычисляемым полям, выполняемого в SQL Server 2005 при необходимости и при условии, что запрос содержит эквивалент выражения вычисляемого поля, также можно получить существенный выигрыш в качестве такой статистики.

  • 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: автоматическое обновление статистики теперь основано на счётчике column modification counters. В SQL Server 2000, обновление статистики определялось по номеру изменений строки. Теперь, изменения отслеживаются на уровне столбца, и автоматическое обновление статистики можно предотвратить для тех столбцов, для которых не было зафиксировано достаточно изменений.

  • 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: статистика по секциям таблиц теперь поддерживается и для секционированных таблиц, появившихся в SQL Server 2005. Гистограммы поддерживаются потаблично (не посекционно).

  • Parallel statistics gathering for fullscan: для статистики, собранной во время полного сканирования, создание одного объекта статистики может распараллеливаться как для секционированных, так и для обычных таблиц.

  • Improved recompiles and statistics creation in case of missing statistics: стали лучше учитываться такие моменты, как перекомпиляция и создание статистики в случае её отсутствия, в режиме автоматического создания или при неудачах сбора статистики. При последующем применении плана исполнения, созданного без статистики, статистика создаётся автоматически, запрос исполняется, и план перекомпилируется. Состояние отсутствия статистики не хранится. Для получения дополнительной информации, обратитесь к статье: Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005.

  • Improved recompilation logic and statistics update for empty tables: улучшена логика рекомпиляции и обновления статистики для пустых таблиц. Изменение от 0 до > 0 строк в таблице приводит к рекомпиляции запроса и обновлению статистики. Для получения дополнительной информации, обратитесь к статье: Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005.

  • Clearer and more consistent display of histograms: стали более понятными и менее противоречивыми показания гистограмм. Внесены улучшения в DBCC SHOW_STATISTICS, из-за которых гистограммы теперь всегда предварительно масштабируются, а уже потом сохраняются в каталогах.

  • Inferred date correlation constraints: добавлены ограничения дедуктивной корреляции дат, с которыми, через опцию базы данных DATE_CORRELATION_OPTIMIZATION, можно заставить SQL Server учитывать информацию о корреляции полей типа datetime между парами таблиц, связанных внешним ключом. Эта информация используется для того, чтобы иметь возможность определять для небольшого числа запросов подразумеваемые для них предикаты. Эта информация не используется непосредственно для оценки селективности или оценочной стоимости для оптимизатора, так что это не является статистикой в строгом смысле, но это она очень близка к статистике, являясь вспомогательной информацией, обычно помогающей получать лучший план запроса.

  • sp_updatestats: в SQL Server 2005 эта процедура обновляет только те статистические данные, которые требуют обновления, основываясь при этом на информации из rowmodctr в системном представлении sys.sysindexes, устраняя, таким образом, ненужные обновления для не изменяемых элементов. Для баз данных, у которых уровень совместимости установлен в 90 и выше, sp_updatestats использует для UPDATE STATISTICS установки соответствующие автоматическому режиму для любых индексов или статистик.

Также в новой версии есть и некоторые другие, менее значительные изменения в поведении механизмов сбора статистики. В частности, поле statblob в sys.sysindexes теперь всегда устанавливается в NULL, а сам statblob хранится в скрытой, внутренней таблице каталога.

[В начало]

Определения и терминология

В этой главе определяются термины, применяемые при описании статистики SQL Server 2005:

  • 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).

[В начало]

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

SQL Server 2005 собирает представленную ниже коллекцию статистической информации уровня таблиц, которая является частью объекта статистики, но SQL Server 2005 иногда использует её и для оценки стоимости запроса:

  • число строк в таблице или индексе (поле rows в sys.sysindexes).

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

SQL Server 2005 собирает следующую статистику по столбцам таблицы и сохраняет её в объекте статистики (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).

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

  • 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 / (число различающихся значений в префиксном наборе столбца). В следующей главе буде представлен демонстрирующий смысл этого значения пример.
Обратите внимание: наблюдаемое в первой строке возвращаемого dbcc show_statistics результата значение Density - является плотностью всех значений, и имеет другой смысл, чем значения RANGE_HI_KEY. Значения RANGE_HI_KEY обычно более часто встречаются в распределении. Следовательно, это Density предоставляет полезную информацию о плотности не часто встречающихся значений.
Статистика по нескольким столбцам одного набора состоит из: одной гистограммы для первого столбца (указанного в определении статистики), одного значения плотности для первого столбца, и значения All Density для каждой префиксной комбинации столбцов (включая один первый столбец). Каждый набор статистики по нескольким столбцам (гистограмма и два или более значений плотности) хранится в одном statblob вместе с timestamp последнего обновления статистики, числом строк в типичной для сбора статистике выборке, числом шагов в гистограмме, и средней длинной ключа. Резюме по строке создаётся только для первого столбца, если он содержит символьные данные.
Используйте sp_helpindex и sp_helpstats для отображения списка статик, доступных для анализируемой таблицы. sp_helpindex показывает все индексы таблицы, а sp_helpstats список всех статистик по таблице. Каждый индекс также имеет статистическую информацию для ее столбцов. Создаваемая с использованием команды CREATE STATISTICS статистическая информация эквивалентна статистике, сформированной командой CREATE INDEX, если индекс создаётся на тех же столбцах. Единственная разница это то, что при использовании команды CREATE STATISTICS будет задействована используемая по умолчанию выбора, в то время как для команды CREATE INDEX сбор статистики будет сопровождаться полным сканированием таблицы, так как в любом случае для построения индекса будут обработаны все строки таблицы.

[В начало]

Пример создания и демонстрации статистики

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

USE tempdb 
GO
-- Избавляемся от объектов, созданных при предыдущих попытках запуска примера.
IF object_id(N'Person.Contact','U') IS NOT NULL
DROP TABLE Person.Contact
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Person')
DROP SCHEMA Person
GO
- Создать типовую схему и таблицу.
CREATE SCHEMA Person
GO
CREATE TABLE Person.Contact(
FirstName nvarchar(60), 
LastName nvarchar(60), 
Phone nvarchar(15), 
Title nvarchar(15)
)
GO
-- Заполняем таблицу несколькими строками.
INSERT INTO Person.Contact 
   VALUES(N'James',N'Smith',N'425-555-1234',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'James',N'Andersen',N'425-555-1111',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'James',N'Andersen',N'425-555-3333',N'Mr')
INSERT INTO Person.Contact 
   VALUES(N'Christine',N'Williams',N'425-555-0000',N'Dr')
INSERT INTO Person.Contact 
   VALUES(N'Susan',N'Zhang',N'425-555-2222',N'Ms')
GO
-- Демонстрация того, что нет статистических данных для персон в таблице контактов.
sp_helpstats N'Person.Contact', 'ALL'
GO
-- Неявно создаём статистику по LastName.
SELECT * FROM Person.Contact WHERE LastName = N'Andersen'
GO
-- Демонстрация того, что статистика была автоматически созданы для LastName.
sp_helpstats N'Person.Contact', 'ALL'
GO

Результат:


statistics_name	          statistics_keys
-----------------------------------------------
_WA_Sys_00000002_1B29035F   LastName


-- Создание индекса, при чём также создастся и статистика.
CREATE NONCLUSTERED INDEX Phone on Person.Contact(Phone)
GO
-- Проверяем, что создание индекса создало связанный объект статистики.
sp_helpstats N'Person.Contact', 'ALL'
GO

Результат::


statistics_name	          statistics_keys
-----------------------------------------------
_WA_Sys_00000002_1B29035F   LastName
Phone	                   Phone


-- Создаём объект статистики для нескольких полей: имя и фамилия.
CREATE STATISTICS FirstLast ON Person.Contact(FirstName,LastName)
GO
-- Показываем, что теперь у таблицы есть три объекта статистики.
sp_helpstats N'Person.Contact', 'ALL'
GO

Результат::


statistics_name	          statistics_keys
-----------------------------------------------
_WA_Sys_00000002_1B29035F   LastName
FirstLast                   FirstName, LastName
Phone	                   Phone


-- Отображаем статистику для LastName.
DBCC SHOW_STATISTICS (N'Person.Contact', LastName)
GO

Результат::


Информация из заголовка статистик:
Name                        Updated	Rows        Rows Sampled Steps Density Average key length String Index
----------------------------------------------------------------------------------------------------------
_WA_Sys_00000002_1B29035F   Mar 25 2005 11:21AM  5   5       4     0       13.6                YES



Префикс набора полей и связанные с ним плотности и длина:
All Density Average Length Columns
-----------------------------------
0.25        13.6           LastName



Шаги гистограммы:
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
------------------------------------------------------------------
Andersen     0          2       0                   0
Smith        0          1       0                   1
Williams     0          1       0                   1
Zhang        0          1       0                   1


-- Если Вы берете имя объекта статистики, отображенного показанной выше командой, 
-- и его subsitute в качестве второго параметра для DBCC SHOW_STATISTICS, 
-- Вы можете сформировать подобную показанной ниже команду 
-- (точное имя автоматически созданного объекта статистики 
-- наверняка будет отличатся от Вашего).

DBCC SHOW_STATISTICS (N'Person.Contact', _WA_Sys_00000002_2D7CBDC4)

-- Исполнение показанной выше команды иллюстрирует, 
-- что Вы может посмотреть статистику по имени столбца или объекта статистики.
GO
-- Следующий выводимый результат демонстрирует статистику по нескольким полям.
-- Обратите внимание на две разных группы плотности для второго rowset.
DBCC SHOW_STATISTICS (N'Person.Contact', FirstLast)

Результат (только второй rowset)::


Префикс набора полей и связанные с ним плотность и длина:
All density Average Length Columns
----------------------------------------------
0.3333333   11.6           FirstName
0.25        25.2           FirstName, LastName

Чтобы увидеть полностью заполненную гистограмму для большой таблицы, выполните следующие команды:

USE AdventureWorks
-- Удаляем созданные ранее объекты.
IF EXISTS (SELECT * FROM sys.stats 
           WHERE object_id = object_id('Sales.SalesOrderHeader')
           AND name = 'TotalDue')
DROP STATISTICS Sales.SalesOrderHeader.TotalDue
GO
CREATE STATISTICS TotalDue ON Sales.SalesOrderHeader(TotalDue)
GO
DBCC SHOW_STATISTICS(N'Sales.SalesOrderHeader', TotalDue)

[В начало]

Создание статистики для SQL Server 2005

Вы можете создать статистику для SQL Server 2005 несколькими способами:

  • Оптимизатор автоматически создает статистику, как побочный эффект оптимизации инструкций SELECT, INSERT, UPDATE и DELETE, когда включена опция AUTO_CREATE_STATISTICS, которая задействована по умолчанию.

  • В SQL Server 2005 основными инструкциями, которые явно собирают статистическую информацию, являются: CREATE INDEX - создаёт объявленный индекс, а также создает для него набор статистики по его комбинации полей, составляющих индексный ключ (но не другие, входящие в индекс поля). CREATE STATISTICS - занимается только созданием статистики по заданному полю или комбинации полей.

  • Кроме предыдущего способа, есть несколько других способов создания статистики или индексов. В конечном счете, каждый из таких способов исполняет одну из указанных выше двух команд. Используйте sp_createstats для создания статистики по всем возможным полям (кроме полей с типом XML) во всех пользовательских таблицах текущей базы данных. При этом новые объекты статистики не будет создаваться для тех полей, которые уже имеют объекты статистики.

  • Используют dbcc dbreindex для восстановления одного или нескольких индексов у таблицы в указанной базе данных.

  • Можно воспользоваться Management Studio, развернув соответствующую папку под объектом Table, щёлкнуть правой кнопкой мыши по папке Statistics, и выбрав пункт New Statistics.

  • Можно использовать для создания индексов утилиту Database Tuning Advisor (DTA).

В качестве примера рассмотрите команду CREATE STATISTICS для таблицы AdventureWorks.Person.Contact:


CREATE STATISTICS FirstLast2 ON Person.Contact(FirstName,LastName)
WITH SAMPLE 50 PERCENT

Обычно, статистических данных, полученных по типовой выборке, достаточно, чтобы получился хороший план исполнения запроса. Однако, могут быть случаи, когда статистика по большой выборке может помочь лучше оптимизировать запрос, например, когда значения в выборке по данному столбцу не носят случайный характер. Не случайный характер выборки может быть, если данные отсортированы или кластеризованы. Сортировка или кластеризация может быть результатом создания индексов, или загрузки данных в хип, который уже отсортирован или кластеризован. Обычно, большая выборка с полным сканированием используется потому, что она дает самую точную статистику. Ценой этого становиться увеличение времени создания такой статистики.
Пример команды создает один статистический объект по двум полям. В этом случае, SAMPLE 50 PERCENT игнорируется, и будет просканирована вся таблица, потому что она слишком маленькая. Исполнение выборки нужно для предотвращения излишних сканирований данных и затрагивает только те таблицы и индексы, у которых более 1024 страниц (8 Мбайт).
В SQL Server 2005 статистические данные создаются для всех индексов во время их создания. При компиляции запросов SQL Server автоматически создает статистику по одному полю. Эта статистика создаётся по полям, для которых оптимизатор иначе должен был бы оценивать приблизительную плотность или распределение. Но есть и исключения из этого правила. Статистика не может быть создан автоматически, когда (1) база данных в режиме - read-only, (2) существует много не завершённых и исполняющихся компиляций, и (3) тип данных поля не поддерживается автоматической статистикой.
Функция автоматического создания статистики может быть заблокирована на уровне базы данных, для чего нужно выполнить:


ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS OFF

Точно так же можно разрешить на уровне базы данных автоматическое создание статистики:


ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS ON

Рекомендуется, чтобы Вы оставили значение ON. Отключать автоматическое создание статистики стоит, если Вы можете этим решить имеющиеся у Вас проблемы производительности, и нужно определить отличную от заданной по умолчанию частоту выборки для некоторых таблиц.
По умолчанию, статистические данные собираются по выборке из пробного набора данных, когда выполняется команда CREATE STATISTICS или когда статистика создаётся автоматически. CREATE INDEX, так или иначе, просматривает весь набор данных. Поэтому, первый набор статистических данных при создании индекса собирается не по пробному набору (эквивалентен сканированию всей таблицы). Команда CREATE STATISTICS позволяет задавать в предложении WITH размер пробной выборки, а также использовать сканирование всей таблицы или заданный процент от анализируемых данных или число сканируемых строк (которое интерпретируется приблизительно). Также можно унаследовать предыдущий размер пробного набора, если в команде UPDATE STATISTICS задать WITH RESAMPLE. Это особенно полезно, когда есть индексы по некоторым полям или наборам полей (с самого начала получивших статистику в результате полного сканирования), и если есть статистика по какому - нибудь другому полю или нескольким полям (изначально созданная по пробному набору). Тогда использование опции RESAMPLE в UPDATE STATISTICS соберёт статистику сканированием таблицы для индексов, а по пробной выборке для остальных полей.
Для маленьких таблиц пробная выборка составляет минимум 8 Мбайт данных. Если таблица в начале была маленькой, а выборка осуществлялась с заданной по умолчанию частотой, и после этого для обновления статистики используется опция RESAMPLE, Вы получите эквивалент полного сканирования таблицы, не смотря на то, что размер таблицы мог уже превысить 8 Мбайт. Поэтому, стоит избегать использования RESAMPLE, если Вы хотите наверняка использовать заданную по умолчанию частоту, и не зависеть от изменения размера таблицы.
Частота для RESAMPLE вычисляется, как функция от числа отобранных строк и общего числа строк в таблице, полученных во время предыдущего вычисления статистики. Так как реальное значение частоты выборки (sampling rate) может изменяться из-за случайного характера самой выборки, частота RESAMPLE является только приближением предшествующей частоты для выборок без полного сканирования. Для последовательно повторяющейся выборки лучше явно определить ту же самую частоту выборки, которая использовалась до этого в UPDATE STATISTICS, отказавшись, таким образом, от использования RESAMPLE.
Команда dbcc show_statistics показывает типичный размер, озаглавленный Rows Sampled. Автоматически созданная или обновлённая статистика (о которых мы поговорим в следующей главе) всегда генерируются с использованием заданной по умолчанию выборки. Заданная по умолчанию частота выборки является медленно возрастающей функцией от размера таблицы, что позволяет собирать статистику относительно быстро даже для очень больших таблиц.
Когда статистические данные созданы и обновлены, оптимизатор должен выбрать путь доступа для собора статистики. Путь доступа может включать хип, кластерный индекс или не кластерный индекс. Для статистической выборки, оптимизатор старается не выбирать те пути доступа, у которых данные физически отсортированы по первому ключевому полю статистики. Это делает выборку более случайной, а саму статистику более точной. Для тех путей доступа, которые не отсортированы по ключу статистики (если такой путь доступа существует), выбирается самый дешёвый по стоимости доступа путь. Это - самый короткий индекс или хип. Для статистики с полным сканированием, порядок сортировки присущий путям доступа не существенен, и не влияет на точность статистики, так что будет выбран путь доступа с самой маленькой стоимостью.
Автоматическое создание статистики можно контролировать с помощью SQL Server Profiler. Сообщение Auto Stats находится в группе событий трассировки: Performance. Также при определении трассы для сообщения Auto Stats стоит выбрать столбцы: IntegerData, Success и ObjectID. Когда будет получено сообщение AutoStats, в столбце Integer Data будет количество созданных или обновлённых статистик для данной таблицы, В ObjectID будет идентификатор таблицы, а в столбце TextData (включаемом в трассу по умолчанию) будут имена полей, для которых было выполнено создание или обновление статистики, дополненных соответствующими префиксами Created: или Updated:. В поле Success будет содержаться информация об успешности или отказе операции Auto Stats. Успешность имеет три возможных значения:

Наименование

Значение

Описание

FAILED

0

Неудачное создание или обновление Auto Stats, по каким - либо причинам, кроме THROTTLED (см. ниже).
Например, база данных была в режиме read-only.

SUCCESS

1

Успешное создание или обновление Auto Stats.

THROTTLED

2

Неудачное создание или обновление Auto Stats, потому что происходило слишком много оптимизаций.

В некоторых случаях можно наблюдать сообщения AutoStats, в которых не были созданы или обновлены никакие статистические данные. Такие сообщения получаются, когда выключена опция auto update statistics или когда было зафиксировано существенное количество изменений в таблице, к которой выполнялся запрос, и оптимизатор не смог удалить все ссылки к этой таблице из плана исполнения запроса из-за структуры этого запроса и воздействия ограничений внешнего ключа.
Команда DROP STATISTICS используется для удаления статистики, но не возможно удалить статистические данные, которые являются побочным продуктом создания индекса. Такие статистические данные удаляются только вместе с удалением индекса.

[В начало]

Поддержка статистики в SQL Server 2005

После череды операций INSERT, DELETE и/или UPDATE, выполненных с таблицей, статистика уже не будет отражать действительное распределение по затронутому полю или индексу. Если оптимизатору запросов SQL Server потребуется статистика для какого - нибудь поля таблицы, которая была перед этим подвержена значительным модификациям, причём, уже после того, как статистические данные были созданы или обновлены; в таком случае SQL Server автоматически обновит статистику по пробной выборке значений этого поля (как при auto update statistics). Автоматическое обновление статистики будет спровоцировано оптимизацией запроса или компиляцией плана исполнения, и затронет только участвующие в запросе поля. Статистика будет обновлена перед компиляцией запроса, если опция AUTO_UPDATE_STATISTCS_ASYNC принимает значение OFF, а если значение ON, то обновление статистики будет выполнено асинхронно.
Если запрос компилируется первый раз, и оптимизатору нужен соответствующий объект статистики, и при этом этот объект существует, тогда, в случае, если необходимый объект статистики устарел, он будет обновлён. После исполнения запроса и попадания его плана в кэш, его статистика проверяется, чтобы определить, не является ли она устаревшей. Если она устарела, план удаляется из кэша, и во время компиляции запроса статистика будет обновлена. План также удаляется из кэша, если изменяются какие - либо статистические данные, от которых он зависит.
SQL Server 2005 принимает решение по обновлению статистики, основываясь на изменениях в счётчике column modification counters (colmodctrs).
Объекты статистики считаются устаревшими в следующих случаях:

  • Когда статистика собирается для постоянной таблицы, она устаревает если:

    1. Размер таблицы в диапазоне от 0 до > 0 строк.

    2. Число строк в таблице после сбора статистических данных было не более 500, и colmodctr для первого поля объекта статистики изменился с тех пор более чем на 500.

    3. Таблица, в момент сбора статистических данных, имела более 500 строк, и colmodctr первого поля объекта статистики изменился более чем на 500 + 20 % от числа строк в таблице на момент сбора статистических данных.

  • Если объект статистики относится к временной таблице, он будет считаться устаревшим по описанным выше причинам, за исключением того, что вводиться более строгий порог для повторного обновления, равный 6 строкам, вместо 500, как это было указано в пункте 2 предыдущего варианта.

Временные таблицы вообще не имеют статистики.
Описанный выше механизм auto update statistics может быть отключен на различных уровнях.

  • На уровне базы данных, отключите использование auto update statistics командой:

    ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF

  • На уровне таблицы, отключить auto update statistics можно используя опцию NORECOMPUTE команды UPDATE STATISTICS или команды CREATE STATISTICS.

  • Используйте sp_autostats, которая показывает изменения установок auto update statistics для таблиц, индексов или объектов статистики.

Повторное включение механизма автоматического обновления статистики может быть сделано подобно тому, как это осуществляется в ALTER DATABASE, UPDATE STATISTICS или sp_autostats.
SQL Server 2005 может автоматически обновлять статистику по всем базам, потаблично, поиндексно или на уровне объектов статистики. Не смотря на то, что можно изменить эту установку с помощью всего одной команды sp_autostats для всей статистики у одной таблицы, поведение статистики будет изменено индивидуально для каждого объекта статистики и индекса этой таблицы. Не существует метаданных, которые бы явно хранили информацию о том, что auto update statistics принимает значение ON или OFF для отдельной таблицы.
В представленной ниже таблице показан суммарный эффект от разных установок для базы данных, таблицы и её индексов на возможности установок для объекта:

Установка для
базы данных

Установка для индекса
или объекта статистики

Auto Update Statistic
на уровне объекта

ON

ON

ON

ON

OFF

OFF

OFF

ON

OFF

OFF

OFF

OFF

Не возможно отменить установленное базе данных для auto update statistics значение OFF, устанавливая его в ON для статистики более низкого уровня объектов.
Автоматическое обновление статистики всегда выполняется по пробной выборке индекса или таблицы, с заданной по умолчанию частотой. Чтобы явно установить эту частоту, выполните команду CREATE или UPDATE STATISTICS.
Обновление статистики регистрируется с помощью тех же сообщений в SQL Profiler, как и при создании статистики.

[В начало]

Резюме статистики по строке

SQL Server 2005 использует патентованную технологию оценки селективности для условий с LIKE. Он формирует резюме статистики по частотности распределения подстроки в символьных полях (резюме по строке). Это относится к полям следующих типов: text, ntext, char, varchar и nvarchar. Использование резюме по строке помогает SQL Server точно оценить селективность условий с LIKE, где образец поиска может иметь любое количество знаков подстановки и в любой их комбинации. Например, SQL Server может оценивать селективность предикатов следующей формы:

Column LIKE 'string%' Column LIKE '%string' Column LIKE '%string%' Column LIKE 'string' Column LIKE 'str_ing' Column LIKE 'str[abc]ing' Column LIKE '%abc%xy'

Если в образце LIKE используется указанный пользователем символ замены (то есть, образец поиска имеет форму LIKE образец ESCAPE escape_character), то только в этом случае SQL Server 2005 угадывает селективность.
Всё это является развитием возможностей, присутствовавших в SQL Server 2000, который использовал угадывание для селективности по всем знакам подстановки, кроме оконечного в строке знака подстановки %, используемого в образце поиска LIKE, что ограничивало точность оценок. Если в первой строке возвращаемого DBCC SHOW_STATISTICS результата поле String Index имеет значение YES, это означает, что объект статистики включает в себя резюме по строке. Само содержание резюме по строке не показывается. Резюме по строке включает в себя дополнительную информацию, которая отличается от той, которая составляет гистограмму.
Для строк превышающих 80 символов, из строки извлекаются первые и последние 40 символов, происходит их конкатенация, после чего результат используется для создания резюме по строке. Следовательно, точная оценка частотности для тех подстрок, которые находятся в игнорируемой части строки, не возможна.

[В начало]

Статистика по вычисляемым полям

SQL Server 2005 поддерживает создание, обновление и использование статистики по вычисляемым полям, даже в тех случаях, когда сам запрос не содержит имени вычисляемого поля, а содержит его выражение (формулу). SQL Server 2000 мог создавать, обновлять и использовать статистику по вычисляемым полям только в автоматическом режиме, и только если вычисляемое поле указывалось в запросе явно.
Вы сможете пронаблюдать автоматическое создание статистики в SQL Server 2005 для вычисляемого поля в таблице Sales.SalesOrderHeader.TotalDue базы данных AdventureWorks, если выполните представленный ниже Transact-SQL скрипт:

USE AdventureWorks GO -- Удаляем всю статистику для Sales.SalesOrderHeader DECLARE c CURSOR FOR SELECT name FROM sys.stats WHERE object_id = object_id('Sales.SalesOrderHeader') AND auto_created <> 0 AND user_created <> 0 DECLARE @name NVARCHAR(255) OPEN c FETCH next FROM c INTO @name WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('drop statistics Sales.SalesOrderHeader.' + @name) FETCH NEXT FROM c INTO @name END CLOSE c DEALLOCATE c -- Исполняем запрос к Sales.SalesOrderHeader, включающий выражение, -- эквивалентное вычисляемому полю TotalDue: -- ((isnull(([SubTotal]+[TaxAmt])+[Freight],(0))). SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) > 200000.00 ORDER BY TotalDue DESC -- Смотрим созданную статистику. -- Заметьте, что статистика создаётся для поля TotalDue даже не смотря на то, -- что его имя явно не фигурирует в запросе. sp_helpstats 'Sales.SalesOrderHeader'

SQL Server 2005 не поддерживает статистику по не сохраняемым вычисляемым полям, которые используют выражения CLR (common language runtime), например, вызывающих в выражении определяемую пользователем CLR - функцию. Для того, что бы собирать статистику по использующему CLR вычисляемому полю, это поле должно быть отмечено, как: PERSISTED.

[В начало]

Статистика по полям, основанным на определяемых пользователем CLR - типах данных

SQL Server 2005 поддерживает создание, обновление и использование статистики по полям, основанным на определяемых пользователем CLR - типах, если такой пользовательский тип данных поддерживает бинарное упорядочивание, иначе, статистика поддерживаться не будет. Тип данных поддерживает бинарное упорядочивание, если у атрибута SqlUserDefinedType, который является частью спецификации определения типов данных, опция IsByteOrdered установлена в "true". Когда тип поддерживает бинарное упорядочивание, это означает, что стандартная бинарная сортировка упорядочивает данные в семантически правильном порядке для этого типа.

[В начало]

Статистика и индексированные представления

Обычно, статистика не требуется для индексированных представлений. Это происходит потому, что подстановка индексированных представлений в план исполнения запроса происходит только после того, как все статистические данные для используемых в запросе таблиц и индексов будут присоединены к плану запроса. Есть одно исключение: статистика будет использоваться в том случае, когда в предложениях FROM присутствует ссылка непосредственно на представление, и применяется подсказка оптимизатору NOEXPAND. Обратите внимание, что возникнет ошибка и план не будет создан, если NOEXPAND задан для представления, которое не имеет индекса.
Из-за ограниченности применения, статистика по индексированным представлениям не создаётся после исполнения sp_createstats и не обновляется с помощью sp_updatestats. Зато автоматическое обновление и создание статистики применимо и к индексированным представлениям. Но, как отмечалось выше, такая статистика может быть востребована оптимизатором и собрана только в случае использования в запросе хинта NOEXPAND, вкупе с включённой опцией автоматического обновления/создания статистики (ON). Кроме того, существует возможность ручного сбора статистики для полей индексированны представлений, для чего нужно использовать команды CREATE STATISTICS или UPDATE STATISTICS.

[В начало]

Хорошая практика в управлении статистикой

Целью использования статистики в SQL Server является обеспечение возможности получения хороших оценок числа элементов для оптимизатора запросов, что бы он смог построить хорошие планы исполнения запросов, и в то же время не потерял в производительности, собирая разумный объём статистики. Ниже мы рассмотрим наиболее полезные действия по управлению статистикой в SQL Server, являющимися очень важными с момента установки сервера.

[В начало]

Использование автоматического создания и обновления статистики

Для подавляющего большинства установок SQL Server, наиболее важным, и хорошей практикой, является использование режима автоматического создания и обновления статистики для всех баз данных. Этот режим включён по умолчанию. Если Вы вдруг наблюдаете наличие плохих планов исполнения запросов и подозреваете, что это происходит из-за отсутствия или устаревания статистических данных, в первую очередь проверьте, что включен режим автоматического сбора статистики.

[В начало]

Где это необходимо, используйте сбор статистики с полным сканирование таблицы

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

  • Оставьте включённым режим автоматического сбора статистики.

  • Для той статистики, точность которой Вас не удовлетворяет, используйте:

    CREATE STATISTICSWITH FULLSCAN, NORECOMPUTE

    Можно создать запускаемое по расписанию задание, которое будет периодически делать следующее:

    UPDATE STATISTICSWITH FULLSCAN, NORECOMPUTE

Частота обновления статистики зависит от особенностей вашего приложения и может быть определена эмпирически. Хорошей отправной точкой для экспериментов с обновлением методом полного сканирования, если эта таблица обновляется часто и интенсивно, выбрать разовое обновление по ночам. Если таблица обновляется слабо, выполнять обновление статистики с полным сканирование раз в неделю.

[В начало]

Избегите использования локальных переменных в запросах

Если Вы используете локальную переменную в предикате запроса вместо параметра или константы, оптимизатор будет использовать сокращённую качественную оценку или предположение по селективности предиката. Используйте вместо локальных переменных в запросе параметры или константы, и оптимизатору будет проще выбрать лучший план запроса. Например, рассмотрим представленный ниже запрос, в котором используется локальная переменная:

declare @StartOrderDate datetime set @StartOrderDate = '20040731' select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >= @StartOrderDate

Число строк в Sales.SalesOrderHeader, по оценке оптимизатора будет квалифицировано согласно условия h.OrderDate> = @StartOrderDate, и составит 9439.5, что составляет ровно 30% от размера таблицы. Вы можете воспользоваться графическим планом исполнения запроса, посмотрев информацию об узле Sales.SalesOrderHeader, в которой содержится оценка числа элементов. В бета - версии SQL Server 2005, которая использовалась для написания этой статьи, в плане использовался MERGE JOIN (наблюдаемый план может отличаться даже на той же версии SQL Server 2005, из-за отличного объёма доступной памяти, и т.п.). Теперь, рассмотрим эквивалентный запрос, который не использует локальную переменную:

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >= '20040731'

В графическом плане исполнения запроса, количество элементов результирующего набора для предиката "h.OrderDate> = '20040731'" получило оценку, равную 40, что соответствует селективности 0.13%. План, выбранный для этого запроса, использует NESTED LOOP JOIN вместо MERGE JOIN, из-за чего оценка получается более качественной.
Даже если в запросе используются локальные переменные, можно добиться более качественной оценки, чем у основанных на предикате равенства предположениях. Селективность условий вида: "@local_variable = column_name" будет оцениваться на основе средней частоты значений из гистограммы для column_name. Например, если поле column_name содержит только уникальные значения, то будет использоваться оценка селективности: 1/(число уникальных значений поля), что является вполне точным.
Чтобы избежать использования локальных переменных, старайтесь заменять их в запросах на константы; используйте sp_executesql с параметрами, которые заменят локальные переменные; используйте хранимые процедура с параметрами вместо переменных. Динамический SQL через EXEC также может быть полезен для этих целей, т.к. позволяет отказаться от локальных переменных, но обычно он приводит к дополнительным затратам на компиляции.

[В начало]

Старайтесь избегать использования сложных TVF и табличных переменных

Содержащие много операторов и возвращающие табличные значения функции (table-valued functions, TVF) не имеют статистики. Поэтому оптимизатор вынужден угадывать размер их результирующего набора. Аналогично, табличные переменные тоже не имеют статистики, и оптимизатор может только догадываться о количестве их элементов. Если в результате использования табличных переменных или табличных функций Вы получаете плохой план исполнения запроса, рассмотрите возможность замены их на обычные таблицы или на временную таблицу, используя их как временное хранилище для результатов, которыми оперируют такие TVF. Это позволит оптимизатору использовать более качественные оценки числа элементов.

[В начало]

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

SQL Server способен хорошо оценивать выражения, которые во время компиляции используют только константы. Такое поведение называют constant folding. Выражения, которые могут быть подвержены свертке, обрабатываются во время оценки селективности как константы. Выражения, которые не могут быть свернуты (Non-foldable), вынуждают оптимизатор использовать догадки для оценки селективности. Например, рассмотрим представленный ниже Transact-SQL скрипт, который заполняет таблицу UserLog двумя сотнями строк, половина из которых имеет уникальное значения UserName, а другая половина имеет одно и то же значение UserName, создавая искаженное распределение.

IF object_id('UserLog') IS NOT NULL DROP TABLE UserLog GO CREATE TABLE UserLog (UserName NVARCHAR(255), Action NVARCHAR(1000)) DECLARE @i INT SET @i = 1 SET nocount ON WHILE @i <= 100 BEGIN INSERT UserLog VALUES(suser_sname(), 'login') INSERT UserLog VALUES(newid(), 'login') SET @i = @i + 1 END GO

suser_sname() - встроенная функция, которая возвращает domain_name\user_name для подключения пользователь Windows, а функция newid() выдает уникальное значение. Теперь, мы выполним два других, эквивалентных друг другу запроса. Первый, показанный ниже, содержит предикат UserName = suser_sname(). Оптимизатор должен угадывать количество элементов в результирующем наборе, и его предположение составит 1.98 (Вы можете это увидеть в XML - плане исполнения, в атрибуте EstimateRows, и причиной этому является директива SET STATISTICS XML ON). Так произошло из-за того, что коэффициент больше 50-ти, и поскольку реальное количество элементов - 100.

SET STATISTICS XML ON GO SELECT * FROM UserLog WHERE UserName = suser_sname() GO SET STATISTICS XML OFF GO

Второй запрос исполняется как параметризованный, через sp_executesql.
Значении suser_sname() передаётся ему как параметр вместо того, чтобы включать его в запрос как выражение.

SET STATISTICS XML ON GO DECLARE @UserName NVARCHAR(255) SET @UserName = suser_sname() EXEC sp_executesql N'SELECT * FROM UserLog WHERE UserName = @n', N'@n nvarchar(255)', @UserName GO SET STATISTICS XML OFF GO

На этот раз, оптимизатор использует для селективности UserName = @n точную оценку - 50%. Если Вы посмотрите на получившийся XML - план исполнения, Вы увидеть, что EstimateRows равен 100, что является абсолютно точным результатом. В больших и более сложных запросах, которые охватывают большие объёмы данных, такой тип ошибки может привести к выбору плохого плана исполнения запроса. Если Вы сталкиваетесь с подобными проблемами в своих приложениях, рассмотрите возможность использования подобной проиллюстрированной выше методики. Используйте sp_executesql или хранимую процедуру, непосредственно содержащую сам запрос, и передачу в предвычисленном виде не сворачиваемого выражения в качестве параметра. Это позволит решить подобные проблемы и получать хорошие оценки числа элементов.

[В начало]

Используйте статистику по нескольким полям, когда запросы имеют условия по нескольким полям

Когда запрос имеет в условии несколько полей, и Вы подозреваете, что оптимизатор не выбирает лучший план исполнения для таких запросов, стоит рассмотреть возможность использования статистики по нескольким полям, которая может быть получена как побочный продукт создания индекса по нескольким полям. Т.о., если уже есть индекс по подмножеству полей, в которое входят все поля условия, нет необходимости явно создать статистику по этому множеству полей. Автоматический сбор статистики не создает статистику по нескольким полям. Поэтому, если нужна такая статистика, создавайте её вручную, или создавайте индекс по этим полям.
Рассмотрим запрос, который обращается к таблице AdventureWorks.Person.Contact, и содержит следующее условие:

FirstName = 'Catherine' AND LastName = 'Abel'

Чтобы получить более точную оценку селективности для этого запроса, создайте следующий объект статистики:

CREATE STATISTICS LastFirst ON Person.Contact(LastName,FirstName)

Этот объект статистики будет полезен для тех запросов, которые содержат предикаты на LastName и FirstName, а так же и по одном полю LastName. Вообще, с помощью этого объект с татистики, можно оценить селективность предиката для любого префикса набора полей в объекте статистики по подмножеству полей.
Для поддержки статистики по всем полям условия, префикс столбцов объекта статистики по нескольким полям должен содержать все поля условия. Например, объект статистики по полям (a, b, c) поддерживает условие a=1 AND c=1 только частично; для оценки селективности a=1 будет использоваться гистограмма, но не будет использоваться информация о плотности c, до тех пор, пока b будет отсутствовать в условии. Статистика по (a, c) или по (a, c, b) обеспечила бы поддержку условия a=1 AND c=1, и информация о плотности тогда могла быть использована для лучшей оценки селективности (обратите внимание на изменение порядка полей в объектах статистики).

[В начало]

Контролируйте ситуации, когда производительность SQL Server падает из-за догадок о селективности

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

  • Отсутствует статистика: Проверьте, включена ли опция автоматического создания статистики, или убедитесь в том, что статистика создаётся вручную, с использованием CREATE STATISTICS или sp_createstats. Стоит также проверить то, что если база данных работает в режиме read-only, это предотвращает автоматическое создание статистики.

  • Использование локальных переменных в условии запроса (об этом мы говорили выше).

  • Не свёрнутые до констант выражения в условии запроса (например, T.x +1 = 0, suser_sname() = T.UserName). Стоит переписать запрос так, чтобы устранить подобные выражения, или оценить это выражение до исполнения запроса и передать его результат в запрос в качестве параметра (не как локальную переменную). Например, условие T.x +1 = 0 стоит переписать так: T.x =-1. Это эквивалентное выражение, но оно позволяет получить точную оценку, а не угадывать.

  • Сложные выражения, такие как: "Price + Tax > 100" или "Price * (1+TaxRate) > 100". Если Вы в такой ситуации замечаете снижение производительности запроса, рассматриваете возможность создания вычисляемого по эквивалентному выражению поля, и создания статистики или индекса по вычисляемому полю. При включенном режиме автоматического создания статистики, статистика будет собираться и по вычисляемым полям, если для них она уже существует, поэтому нужно предварительно создать статистику по вычисляемому полю вручную.

[В начало]

Избегите изменения параметров хранимой процедуры в её теле до их использования в запросе

Для повышения производительности запроса, в ограниченном числе ситуаций, нужно будет избегать присвоения новых значений параметрам хранимой процедуры внутри кода этой процедуры, что бы потом использовать новые значения параметров в запросе. Хранимая процедура и все её запросы изначально компилируются с первоначальным значением параметра, оценивая запроса, как будто он использует не изменённое значение параметра. Такое поведение иногда называют parameter sniffing. Рассмотрим представленную ниже хранимую процедуру, которая выбирает продажи после передаваемой через параметр даты, или если значение этого параметра - NULL, выбирает продажи за прошедшие три месяца:

CREATE PROCEDURE GetRecentSales (@date datetime) WITH RECOMPILE AS BEGIN IF @date IS NULL SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE) FROM Sales.SalesOrderHeader)) SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date END

Если вызывать эту процедуру с равным NULL значением параметра, оптимизатор будет рассматривать инструкцию SELECT, оптимизированную для @date = NULL. Поскольку нет записей с OrderDate = NULL, оценка числа элементов для результирующего набора с применением этого фильтра к SalesOrderHeader будет заниженной (1 запись). Однако, во время исполнения процедуры дата окажется не равной NULL, а будет иметь значение отстоящее на три месяца от последнего OrderDate. Реально, число строк в SalesOrderHeader, которое будет квалифицировано, равно 5736. Оптимизатор же при передаче параметром в GetRecentSales значения NULL выберет для запроса nested loop join, в то время как оптимальный план исполнения должен был содержать merge join. Вы можете увидеть выбранный план, а также ожидаемое и фактическое число элементов, используя этот скрипт:

SET STATISTICS PROFILE ON GO EXEC GetRecentSales NULL GO SET STATISTICS PROFILE OFF GO

Обратите внимание, что опция WITH RECOMPILE, указанная выше у хранимой процедуры GetRecentSales, не устраняет ошибочную оценку числа элементов. Единственным способом, гарантирующим оптимизацию последнего примера с этими значениями параметра и качественной оценкой, состоит в том, чтобы изменить эту хранимую процедуру, разделив её на две:

CREATE PROCEDURE GetRecentSales (@date datetime) AS BEGIN IF @date IS NULL SET @date = dateadd("mm",-3,(SELECT MAX(OrderDATE) FROM Sales.SalesOrderHeader)) EXEC GetRecentSalesHelper @date END CREATE PROCEDURE GetRecentSalesHelper (@date datetime) WITH RECOMPILE AS BEGIN SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date -- @date не меняется во время компиляции, -- поэтому будет получен хороший план. END

[В начало]

Чаще собирайте статистику для возрастающих ключей

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

[В начало]

Используйте асинхронное обновление статистики, если синхронное обновление происходит с недопустимой задержкой

Если у Вас большая база данных с высокой OLTP нагрузкой, то при включённой опции AUTO_UPDATE_STATISTICS некоторые обычно исполняемые очень быстро (доли секунды) транзакции могут периодически начать исполняться дольше, вплоть до нескольких секунд или даже больше, и причиной этому может стать необходимость обновления статистики. Что бы предотвратить такие задержки, используйте AUTO_UPDATE_STATISTICS_ASYNC. Для рабочей нагрузки, характеризующейся продолжительно исполняющимися запросами, получение более качественных планов намного важнее, чем редко возникающие задержки на этапе компиляции. В таких случаях, стоит использовать опцию синхронного а не асинхронного автоматического обновления статистики.

[В начало]

Резюме

SQL Server 2005 содержит множество разнообразных возможностей управления статистикой. Наиболее важное из них это то, что Вы можете полагаться на автоматическое создание и обновление статистики, и это в большинстве случаев помогает получать хорошие планы исполнения запросов. Когда не достаточно автоматически собранных с заданной по умолчанию частотой статистических данных, Вы можете явно управлять частотой создания и обновления статистики. Если Вы обнаружите не оптимальный план запроса, что может быть связано со статистикой или оценкой стоимости, рассмотрите возможности улучшения запроса, используя рекомендации из этой статьи.

[В начало]

Ссылки

Arun Marathe, Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005, July 2004.

[В начало]

Перевод: Александра Гладченко  2005г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013