Обслуживание статистических функций (Статистика) в SQL Server

Аннотация

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

  • Инициализация статистических обновлений в периоды высокой производства. -ИЛИ-

  • Инициализация чрезмерно большое количество процессов обновление СТАТИСТИКИ в заданный момент времени.

Эта статья является описывает условия, при которых можно ожидать создается статистика и выполнить инструкцию UPDATE STATISTICS для таблиц в базе данных. Сведения о Статистика в SQL Server 2000 см «Статистика используется оптимизатором запросов в Microsoft SQL Server 2000» на веб-узле MSDN:

http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx Примечание. Если вы используете Microsoft SQL Server 2005, см. в следующем техническом документе сведения об использовании статистики оптимизатором запросов в SQL Server 2005:

http://technet.microsoft.com/en-us/library/cc966419.aspx

Дополнительная информация

Общие сведения

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

  • Число строк в таблице.

  • Количество страниц, используемых в таблице.

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

Дополнительные сведения для индексов, включая (для каждого индекса):

  • Эквивалентное высоты гистограммы в первом столбце.

  • Плотности на все префиксы столбцов.

  • Средняя длина ключа.

Статистическая информация по индексам создаются автоматически при построении нового индекса. Кроме того существует возможность создания и ведения статистики по другим столбцам, а также. Чтобы сохранить статистические данные с самым последним обновлением, SQL Server представлены AutoStat, через мониторинг изменений таблицы SQL Server поддерживает автоматическое обновление статистики для таблицы при изменении определенного порога достигнут. Кроме того SQL Server появилась Авто создать статистики, что заставляет сервер автоматически генерировать все статистические данные, необходимые для точной оптимизации определенного запроса.

Определить, когда приближается поколения AutoStat

Как упоминалось выше, AutoStat автоматически обновляет статистику для конкретной таблицы, достижении «порог изменения». Столбец sysindexes.rowmodctr содержит текущее общее все изменения для таблицы, которая может неблагоприятно повлиять на процесс принятия решений обработчик запросов со временем. Этот счетчик обновляется каждый раз при возникновении любого из следующих событий:

  • Вставка одной строки выполняется.

  • Выполняется удаление одной строки.

  • Выполнено обновление для индексированного столбца.

Примечание. Инструкция TRUNCATE TABLE не обновляет rowmodctr. После таблицы, для которых изменена статистика rowmodctr значение сбрасывается на 0и обновить версию схемы таблицы статистики. Кроме того в ситуациях, в которых план выполнения хранимой процедуры, извлекается из кэша и этого плана зависит от статистики, версия схемы статистики сравнивается с текущей версии. Если есть новая статистика, будут перекомпилированы плана для хранимой процедуры. Основной алгоритм Автоматическое обновление статистики является:

  • Если меньше 6 — количество элементов в таблице, а таблица в базе данных tempdb происходит автоматическое обновление каждые 6 изменений в таблицу.

  • Если количество элементов в таблице больше 6, но меньше или равно 500, обновление происходит каждые 500 изменения.

  • Если количество элементов для таблицы превышает 500, обновление статистики при (500 + 20 процентов таблицы) произошли изменения.

  • Для табличных переменных изменения мощности не вызывает автоматическое обновление статистики.

Примечание. В этом строгом смысле SQL Server подсчитывает количество элементов, равное количеству строк в таблице. Примечание. Кроме мощности избирательность предиката также влияет на создание Статистика. Это означает, что статистика может не обновляться после каждого изменения 500, если количество элементов меньше, чем 500 или каждые 20 процентов изменения Если количество элементов превышает 500. Коэффициент масштабирования (диапазон значений — от 1 до 4) формируется в зависимости от избирательностью, и продукт этот фактор и количество изменений, полученное из алгоритма бы фактическое число изменений, необходимых для создания Статистика. Выше алгоритма можно суммировать в виде таблицы: _________________________________________________________________________________ Table Type | Empty Condition | Threshold When Empty |Threshold When Not Empty _________________________________________________________________________________ Permanent | < 500 rows | # of Changes >= 500 | # of Changes >= 500 + (20% of Cardinality) ___________________________________________________________________________ Temporary | < 6 rows | # of Changes >= 6 | # of Changes >= 500 + (20% of Cardinality) ___________________________________________________________________________ Table Variables | Change in cardinality does not affect AutoStats generation. ___________________________________________________________________________ В двух следующих примерах помогают продемонстрировать эту концепцию.

Пример 1.

Рассмотрим таблицу authors базы данных pubs , содержит 23 строк и имеющий 2 индексы. Уникальный кластеризованный индекс UPKCL_auidindиндексируется на один столбец au_idи au_lname и au_fname столбцов будет создан составной некластеризованный индекс aunmind. Так как эта таблица содержит не более 500 строк, AutoStat начнется после возникновения 500 изменения данных в таблице. Изменения могут быть 500 или более вставляет, удаляется, изменяется на индексированный столбец au_lname или любое сочетание этих. Таким образом, можно предсказать, когда инструкция UPDATE STATISTICS инициируется путем наблюдения за значение sysindexes.rowmodctr , которое увеличивается при каждом обновлении. При достижении 500, можно ожидать обновления СТАТИСТИКИ для запуска.

Пример 2

Рассмотрим второй таблицы t2, с количеством 1 000. Для таблиц с более чем 500 строк SQL Server будет обновление СТАТИСТИКИ при (500 + 20 процентов) были внесены изменения. Математических 20 процентов от 1000 – 200, так что можно ожидать при запуске приблизительно 700 изменения были внесены в таблицу AutoStat.

Автоматизация определения Статистика

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

   if (sysindexes.rows > 500)
      if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
      hours) //500 change leeway
         begin
            disable autostats
            log autostats disable
         end
      else
         begin
            stats ok
         end
   else
      if (sysindexes.rowmodctr >= 425) //75 change leeway
         begin
            disable autostats
            log autostats disable
         end

Позже может запланировать задание для следующих:

  • Запустите инструкцию UPDATE STATISTICS для всех таблиц были вынуждены отключать на время дня. - И -

  • Включите AutoStat, так как счетчик изменения каждой таблицы будут перенастроены 0 при выполнении инструкции UPDATE STATISTICS.

Управление, выполняются ли обновление СТАТИСТИКИ для таблицы

Наиболее очевидным решением на этот вопрос при AutoStat оказалось проблематичным, является отключение автоматического создания статистики, тем самым оставляя свободных запланировать обновление СТАТИСТИКИ во время меньше вмешивается администраторов баз данных. Для этого используется инструкция UPDATE STATISTICS или sp_autostats хранимой процедуры. Для инструкции UPDATE STATISTICS используется синтаксис:

   UPDATE STATISTICS <table>...with NORECOMPUTE

Используется синтаксис sp_autostats хранимые процедуры:

sp_autostats <table_name>, <stats_flag>, <index_name> где < stats_flag > — «on» или «off». Для отключения автоматического вхождения UPDATE STATISTICS или создать СТАТИСТИКУ на уровне каждой базы данных можно также использовать процедуры sp_dboption :

процедуры sp_dboption < dbname>, «автоматическое обновление статистики» < на | off >- OR-< dbname >sp_dboption, «автоматическое создание статистики», < на | off >

Контролирует количество параллельных процессов обновления СТАТИСТИКИ

В настоящее время немного не отключение AutoStat для отдельных таблиц, это не позволяет настроить число автоматическое обновление СТАТИСТИКИ инструкции, которые выполняются параллельно. Сервер Однако ограничить количество параллельных процессов обновления СТАТИСТИКИ до четырех на каждый процессор.

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

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

1998-10-15 11:10:51.98 spid9 выдан перекомпиляции: ProcName: sp_helpindex LineNo: StmtNo 75: 29

При включении флага трассировки 205 следующее сообщение также будет квадратная скобка сообщение AutoStat от 8721 при обновлении статистики. Сообщение открывающей скобки можно отличить по значению RowModCnt, которое должно быть больше 0. Закрывающая скобка после обновление СТАТИСТИКИ будут иметь RowModCnt значение 0:

Изменение схемы spid8 1998-10-15 11:38:43.68: Tbl Dbid: 7 Objid: 133575514 RowModCnt: 60500 RowModLimit: 60499

Для этого сообщения «RowModCnt» — общее количество изменений в таблице. «RowModLimit» является порог, при превышении, результатом выполнения инструкции UPDATE STATISTICS для таблицы. Можно также включить флаг трассировки 8721, который будет дамп сведения в журнале ошибок при AutoStat. Ниже приведен пример типа сообщений, можно ожидать.

1998-10-14 16:22:13.21 spid13 СТАТИСТИКА: Tbl ОБНОВЛЕНО: [авторы] Строки: 23 режимы: 501 граница: 500 длительность: 47ms UpdCount: 2

Для этого сообщения «Режимы» — общее количество изменений в таблице, «Связанный» является изменение порогового значения, «Длительность» — количество времени, инструкция UPDATE STATISTICS необходимо выполнить и «UpdCount» — это количество обновленной статистики. Можно также использовать SQL Server Profiler для идентификации при выполнении инструкции UPDATE STATISTICS. Чтобы сделать это, выполните следующие действия:

  1. В меню профилировщикавыберите в меню Сервиси выберите Параметры.

  2. На вкладке Общие перейдите на событияи выберите Все классы событий.

  3. Определить новую трассировку и установите Прочиесобытия, выберите вложенного события Автоматического обновления статистики .

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

Блокировки схем

SQL Server использует два типа блокировки схем, которые принимаются при его обновления статистики для таблицы: Sch-S: Schema Stability Lock ---------------------------- This lock ensures that a schema element, such as a table or index, will not be dropped while any session holds a schema stability lock on the schema element. Sch-M-UPD-STATS: Schema Modification Lock ----------------------------------------- This is a non-blocking lock that is used by the system to ensure that only one automatic UPDATE STATISTICS process is run against a table at any given point in time. The sp_lock stored procedure will report this lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M. Эти блокировки можно просмотреть, запустив sp_lock или выбора из таблицы syslockinfo .  

Нужна дополнительная помощь?

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединение к программе предварительной оценки Майкрософт

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×