Статистика индексов

Изменения: 12 декабря 2006 г.

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

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

SQL Server 2005 предоставляет дополнительные сведения, которые собираются с помощью статистики на столбцах, относящихся к типу char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text и ntext. Эти сведения, называемые сводкой строк, помогают оптимизатору запросов оценить избирательность предикатов запросов на шаблонах строк. Если в запросе присутствуют условия LIKE, то сводки строк приводят к более точным оценкам размеров результирующего набора и часто к лучшим планам запросов. Сюда относятся такие запросы, как WHERE ProductName LIKE '%Bike' и WHERE Name LIKE '[CS]heryl'.

Примечание.

Данные сводки строк не сохраняются, если сводка для шаблона столбца больше, чем компонент Database Engine может поддержать. Например, сводка строк не будет поддерживаться для статистики, созданной с использованием WITH FULLSCAN на уникальном столбце, который относится к типу varchar(80) и содержит 80 символов в каждой строке, и почти не имеет различий между строками, а таблица содержит 85 000 строк. Чтобы определить, сохраняется ли сводка строк для определенного объекта статистики, используйте DBCC SHOW_STATISTICS (Transact-SQL).

Как работает автоматическая статистика

Когда создается индекс, оптимизатор запросов автоматически сохраняет статистические данные о столбцах индекса. А если параметр базы данных AUTO_CREATE_STATISTICS установлен в значение ON (значение по умолчанию), то компонент Database Engine автоматически создает статистику и для столбцов, по которым не построены индексы, входящим в предикат.

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

Примечание.

Устаревшая или потерянная статистика показывается в виде предупреждений (имя таблицы красным шрифтом), когда план выполнения запроса графически отображается в среде SQL Server Management Studio. Дополнительные сведения см. в разделе Графическое отображение планов выполнения (SQL Server Management Studio). Кроме того, мониторинг класса событий Статистика по потерянным столбцам с использованием приложения SQL Server Profiler позволяет определить, когда статистика потеряна. Дополнительные сведения см. в разделе Категория событий «Ошибки и предупреждения» (ядро СУБД).

Если параметр базы данных AUTO_UPDATE_STATISTICS установлен в значение ON (значение по умолчанию), то оптимизатор запросов автоматически обновляет статистические данные периодически, по мере изменения данных в таблице. Обновление статистики инициализируется всякий раз, когда статистика, используемая в плане выполнения запроса, не проходит проверку. Выборка случайна по страницам данных, и берется из таблицы или из наименьшего некластеризованного индекса по столбцам, нужным для статистики. Когда страница данных считана с диска, все строки на странице данных используются для обновления статистических данных. Почти всегда статистические данные обновляются при изменении примерно 20 процентов строк данных. Однако оптимизатор запросов всегда удостоверяется в том, что минимальное число строк выбрано. Таблицы, занимающие менее 8 мегабайт (МБ), всегда полностью просматриваются для сбора статистики.

Выборка данных вместо анализа всех данных минимизирует издержки автоматического обновления статистики. В некоторых обстоятельствах статистическая выборка не в состоянии точно описать изменения данных в таблице. Невозможно контролировать количество данных, которые берутся для выборки во время ручных поочередных обновлений статистики таблиц с использованием предложений SAMPLE и FULLSCAN инструкции UPDATE STATISTICS. Предложение FULLSCAN указывает, что для сбора статистики необходимо сканировать все данные таблицы, тогда как предложение SAMPLE используется, чтобы указать либо процент строк для выборки, либо количество строк для выборки.

Асинхронное обновление статистики

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

В приложении SQL Server 2005 параметр базы данных AUTO_UPDATE_STATISTICS_ASYNC обеспечивает асинхронное обновление статистики. Если этот параметр установлен в значение ON, то перед компиляцией запроса не происходит ожидание обновления статистики. Вместо этого устаревшая статистика ставится в очередь на обновление фоновым рабочим потоком. Запрос, вызывающий обновление статистики и все другие параллельные запросы компилируются немедленно с использованием существующих устаревших статистических данных. Так как отсутствует задержка, связанная с обновлением статистики, продолжительность выполнения запросов предсказуема; однако устаревшая статистика может привести к тому, что оптимизатор запросов будет выбирать не самые оптимальные решения по обработке запроса. Запросы, которые выполняются после того, как обновленные статистические данные готовы, используют обновленные статистические данные. Это может привести к повторной компиляции кэшированных планов запросов, которые зависят от статистических данных предшествующей версии. Асинхронное обновление статистики не произойдет, если в той же явной пользовательской транзакции выполняется любая из инструкций DDL (CREATE, ALTER, DROP и др.).

Параметр базы данных AUTO_UPDATE_STATISTICS_ASYNC устанавливается на уровне базы данных и задает метод обновления всей статистики базы данных. Он применяется только для обновления статистики и не может использоваться для асинхронного создания статистики. Установка данного параметра в значение ON имеет смысл только в том случае, если параметр AUTO_UPDATE_STATISTICS тоже установлен в значение ON. По умолчанию, параметр AUTO_UPDATE_STATISTICS_ASYNC установлен в значение OFF. Дополнительные сведения о настройке данного параметра см. в разделе ALTER DATABASE (Transact-SQL).

Перед установкой базы данных в режим SINGLE_USER убедитесь в том, что параметр AUTO_UPDATE_STATISTICS_ASYNC установлен в значение OFF. Если он установлен в значение ON, то в однопользовательском режиме обновляющий статистику фоновый поток подключится к базе данных, и закроется доступ к ней. Если параметр установлен в значение ON, выполните следующие действия.

1. Установите AUTO_CREATE_STATISTICS_ASYNC в значение OFF.
2. Проверьте наличие активных асинхронных задач статистики, выполнив запрос к динамическому административному представлению sys.dm_exec_background_job_queue.
3. При наличии активных задач следует либо разрешить завершение задач, либо вручную отменить их при помощи инструкции KILL STATS JOB.

Советы и рекомендации

Параметр AUTO_UPDATE_STATISTICS_ASYNC имеет смысл установить в значение ON, если к приложению применимы следующие характеристики:

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

Просмотр свойств асинхронного обновления статистики

Чтобы просмотреть значение параметра AUTO_UPDATE_STATISTICS_ASYNC (ON или OFF), выберите столбец is_auto_update_stats_async_on из каталога представлений sys.databases. Дополнительные сведения см. в разделе sys.databases (Transact-SQL).

Чтобы просмотреть, находятся ли статистические данные в очереди на обновление или в процессе обновления, используйте динамическое административное представление sys.dm_exec_background_job_queue. Для статистики столбец object_id1 отображает идентификатор (ID) таблицы или представления, а столбец object_id2 отображает идентификатор (ID) статистики. Используйте динамическое административное представление sys.dm_exec_background_job_queue_stats для просмотра сводных статистических данных очереди фоновых заданий, таких как рабочих запросов, ожидающих выполнения, числа неудачных запросов и среднего числа выполнения ранее отправленных запросов.

Отключение автоматической статистики

Можно отключить автоматическое формирование статистики для определенного столбца или индекса следующими способами.

• Используйте системную хранимую процедуру sp_autostats.
• Используйте предложение STATISTICS_NORECOMPUTE в инструкции CREATE INDEX.
• Используйте предложение NORECOMPUTE в инструкции UPDATE STATISTICS.
• Используйте предложение NORECOMPUTE в инструкции CREATE STATISTICS.
• Установите параметры базы данных AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS в значение OFF с помощью инструкции ALTER DATABASE. Дополнительные сведения см. в разделе Установка параметров базы данных.

Если для компонента Database Engine дано указание не поддерживать статистику автоматически, то необходимо вручную обновлять статистические данные.

Примечание.
Инструкция UPDATE STATISTICS позволяет заново включить автоматическое обновление статистики, если не указано предложение NORECOMPUTE.

Создание и обновление статистики вручную

Статистика также может быть создана на всех подходящих столбцах, во всех пользовательских таблицах текущей базы данных с помощью одной инструкции с использованием системной хранимой процедуры sp_createstats. Статистика может быть создана на определенных столбцах таблицы или представления с помощью инструкции CREATE STATISTICS или обновлена с помощью инструкции UPDATE STATISTICS. На каждой таблице или представлении можно создать не более 2000 статистических показателей независимо от индекса. Любой столбец или комбинация столбцов, которые подходят для ключа индекса, также подходят для статистики, за некоторым исключением.

• Могут быть указаны столбцы, относящиеся к типу больших объектов, за исключением xml. Могут быть указаны типы varchar(max), nvarchar(max), varbinary(max), image, text и ntext.
• Максимальный допустимый размер комбинированных значений столбцов может превысить ограничение, равное 900 байт, которое существует для значения ключа индекса.

Можно удалить сформированные для столбца статистические данные, если более не нужно хранить или обслуживать их.

При ручном создании можно создать статистику по концентрации для нескольких столбцов. Это среднее число дубликатов для комбинации столбцов. Например, в запросе содержится предложение WHERE a = 7 and b = 9.

Создание ручной статистики на обоих столбцах вместе (a, b) может позволить компоненту Database Engine выполнять лучшую оценку запрос, так как статистика также содержит среднее число отдельных значений для комбинации столбцов a и b.

Создание статистики для столбца

CREATE STATISTICS (Transact-SQL)

Создание статистики для всех подходящих столбцов всех пользовательских таблиц

sp_createstats (Transact-SQL)

Обновление статистики вручную

UPDATE STATISTICS (Transact-SQL)

Просмотр статистики для таблицы

DBCC SHOW_STATISTICS (Transact-SQL)

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

DROP STATISTICS (Transact-SQL)

Работа со статистикой после обновления базы данных до версии SQL Server 2005

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

Преимущество обновления статистики до формата SQL Server 2005 состоит в том, что статистические показатели SQL Server 2005 для определенного показателя выборки обычно имеют более высокое качество, чем аналогичные показатели в SQL Server 2000 и более ранних версиях. Также SQL Server 2005 создает особую сводку строк для столбцов символьного типа, как описано выше. Дополнительные сведения о статистике в SQL Server 2005 см. на веб-узле Майкрософт.

Советы и рекомендации

В большинстве случаев не нужно предпринимать дополнительных действий, касающихся статистики после обновления базы данных. Однако если имеется большая база данных с высокими требованиями к производительности, рекомендуется после обновления выполнить хранимую процедуру sp_updatestats (Transact-SQL) с параметром RESAMPLE. Она сохраняет прежние показатели выборки и обновляет статистику до нового формата. Учтите, что во время создания индексов статистические данные создаются с использованием показателя выборки FULLSCAN. Эта и другая статистика, созданная с использованием FULLSCAN, при обновлении в результате установленного параметра AUTO_UPDATE_STATISTICS использует показатель выборки по умолчанию. Если предпочтительнее не обновлять все статистические данные с помощью выполнения процедуры sp_updatestats, то можно воспользоваться инструкцией UPDATE STATISTICS для выборочного обновления статистики на индексах и другой статистики FULLSCAN с показателем выборки FULLSCAN после обновления базы данных.

См. также

Основные понятия

Оптимизация индексов

Другие ресурсы

CREATE INDEX (Transact-SQL)
Настройка запроса
Хранимая процедура sp_autostats (Transact-SQL)

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений