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
-- Создание индекса, при чём также создастся и статистика.
CREATE NONCLUSTERED INDEX Phone on Person.Contact(Phone)
GO
-- Проверяем, что создание индекса создало связанный объект статистики.
sp_helpstats N'Person.Contact', 'ALL'
GO
-- Создаём объект статистики для нескольких полей: имя и фамилия.
CREATE STATISTICS FirstLast ON Person.Contact(FirstName,LastName)
GO
-- Показываем, что теперь у таблицы есть три объекта статистики.
sp_helpstats N'Person.Contact', 'ALL'
GO
-- Отображаем статистику для 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
-- Если Вы берете имя объекта статистики, отображенного показанной выше командой,
-- и его 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). Объекты статистики считаются
устаревшими в следующих случаях:
Когда статистика собирается для постоянной таблицы, она устаревает
если:
Размер таблицы в диапазоне от 0 до > 0 строк.
Число строк в таблице после сбора статистических данных было не
более 500, и colmodctr для первого поля объекта статистики изменился с
тех пор более чем на 500.
Таблица, в момент сбора статистических данных, имела более 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 STATISTICS … WITH FULLSCAN, NORECOMPUTE
Можно создать запускаемое по
расписанию задание, которое будет периодически делать следующее:
UPDATE STATISTICS … WITH 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" или "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 содержит множество разнообразных возможностей
управления статистикой. Наиболее важное из них это то, что Вы можете
полагаться на автоматическое создание и обновление статистики, и это в
большинстве случаев помогает получать хорошие планы исполнения запросов.
Когда не достаточно автоматически собранных с заданной по умолчанию
частотой статистических данных, Вы можете явно управлять частотой создания
и обновления статистики. Если Вы обнаружите не оптимальный план запроса,
что может быть связано со статистикой или оценкой стоимости, рассмотрите
возможности улучшения запроса, используя рекомендации из этой статьи.
Теги:
Оценить статью:
Комментарии посетителей
Комментарии отключены. С вопросами по статьям обращайтесь в форум.
