1с sql статистика автоматического создания

Обновлено: 03.07.2024

Эта статья написана для администраторов, обслуживающих сервера СУБД MS SQL Server, которые используются вместе с 1С:Предприятием. Статья скорее практическая, чем разъяснительная, но я постарался хотя бы кратко обосновать те или иные решения, хотя большая часть информации дана несколько упрощенно и поверхностно.

Индексы и статистики в MS SQL Server — основа эффективного выполнения запросов. Без них сервер не сможет выполнять запросы за разумное время.

Статистика — небольшая таблица, до 200 строк, в которой хранится обобщенная информация о том, какие значения и как часто встречаются в таблице. На основании статистики сервер принимает решение, какой индекс использовать при выполнении запроса.

Индекс — особым образом структурированные данные (хранящиеся в базе данных), которые позволяют быстро найти нужные записи. Устроен он примерно так, как оглавление в книге или предметный указатель. Большинство баз данных 1С по объёму более чем наполовину состоят из индексов. Для каждого индекса обязательно хранится его статистика.

За подробностями внутреннего устройства, как обычно отсылаю в BOL:

Для обслуживания есть специальные "кирпичики" в планах обслуживания (maitenance plan), которые так и называются:

  • Update Statistics Task
  • Rebuild Index Task
  • Reorganize Index Task

Казалось бы всё просто: накидал кирпичиков, соединил стрелочкой и поехали. Такое решение возможно, но оно очень неэффективно:

  1. Индексы перестраиваются/реорганизуются только все сразу в данной базе. То есть даже если таблица никогда не меняется, её индексы будут перестраиваться. Это очень расточительно, а при полной модели восстановления еще и приводит к огромному росту журналов транзакций.
  2. Статистики тоже перестраиваются вне зависимости от актуальности, причем даже если они были только что обновлены при перестроении индексов.
  3. Нет никаких гарантий, что операция обслуживания завершится за то время, которое вы ей выделили.

Решение очень простое: пишется свой скрипт обслуживания, который убирает эти ограничения. Такой скрипт можно запускать из задания (job) MS SQL Server Agent или из "кирпичика" Execute T-SQL Statement Task в планах обслуживания (кому как удобнее). В интернете можно найти много подобных скриптов (в простейшем виде они даже в документации есть), но мне ни один не подошёл, и поэтому я пользуюсь своим "велосипедом". Этот скрипт и приведён ниже. Он подходит без изменений для большинства баз данных 1С до примерно 0,5-0,7 ТБ (дальше его уже лучше немного доработать, если кому-то интересно/актуально могу пояснить в комментариях).

  1. Как и в большинстве подобных скриптов, анализируется динамическое представление sys.dm_db_index_physical_stats, по которому выясняется степень фрагментации и заполненности страниц индекса.
  2. Можно задать для обработки лишь часть баз данных, можно, наоборот, исключить некоторые БД из обслуживания.
  3. Контролируется время выполнения скрипта.
  4. Очень грубо, но оценивается размер записи в журналы транзакций.
  5. Есть возможность исключить из обработки совсем небольшие таблицы.
  6. У скрипта есть режим "эмуляции" работы, чтобы оценить то, как он будет работать.
  7. Сначала обрабатываются самые большие таблицы (так как обычно их обслуживание важнее).
  8. Скрипт работает на SQL Server 2008 и более поздних (на 2005 тоже должен работать, но мне уже негде проверить)
  9. Результат вывода в режиме эмуляции сам является корректным TSQL скриптом.
  10. Ну и конечно, при регулярном выполнении этот скрипт на порядок легче, чем стандартные операции плана обслуживания.

С чем нужно быть осторожным при запуске скрипта:

  1. Нежелательно пересечение работы скрипта с интенсивной работой пользователей или с полным резервным копированием.
  2. Чтение из sys.dm_db_index_physical_stats в режиме DETAILED достаточно интенсивно читает с дисков.
  3. Скрипт предназначен для баз 1С или подобных. Не стоит экспериментировать с ним на совсем специфичных базах данных с нестандартными индексами.
  4. Если у вас есть таблицы 100-200 ГБ и больше, то при распараллеливании построения индекса, после перестроения он формально снова может оказаться фрагментированным.
  5. Статистики пересчитываются без полного сканирования. Это заметно быстрее. Если вам нужно полное сканирование каких-то таблиц, то пишите отдельный скрипт.

Рекомендации по запуску:

  1. Никаких регулярных "шринков" на рабочих базах быть не должно. Еще раз: шринкам не место в регулярном обслуживании!
  2. При полной модели восстановления я бы поставил полное резервное копирование после обслуживания индексов. Иначе при необходимости восстановления придётся донакатывать достаточно тяжёлый кусок журналов транзакций после восстановления основного образа. Простую модель восстановления на промышленно используемых БД я считаю либо редким исключением, либо частым недоразумением.
  3. Первый запуск лучше выполнить вручную в SSMS чтобы оценить время работы.

Остальное можно прочитать в коде и в комментариях.

PS: Движок сайта некорректно отобажает текст со знаками больше-меньше, поэтому скрипт приложен файлом, а в статье оставлено только начало скрипта.

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

Для того, чтобы гарантировать максимально правильную работу оптимизатора MS SQL Server рекомендуется регулярно обновлять статистики базы данных MS SQL.

Для обновления статистик по всем таблицам базы данных необходимо выполнить следующий SQL запрос:

Оптимальная частота обновления статистик зависит от величины и характера нагрузки на систему и определяется экспериментальным путем. Рекомендуется обновлять статистики не реже одного раза в день.

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

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

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

Можно сделать, например, так: В MS SQL:

1) Создайте новый план обслуживания

2) Создайте субплан (Add Subplan) и назовите его «Обновление статистик».


3) Добавьте в него задачу Update Statistics Task из панели задач:

4) Настройте расписание обновления статистик (рекомендация не реже 1 раза в день).

5) Настройте саму задачу:

5.1) Базу данных для который выполняется обновление статистики

5.3) Укажите опцию Full scan


(Примечание. Такой режим будет эквивалентен скрипту

где dname имя вашей базы)

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

Поэтому в субплан:

6) Добавьте задачу Execute T-SQL Statement Task.


7) Соедините задачу Update Statistics Task стрелочкой с новой задачей

8) В тексте созданной задачи Execute T-SQL Statement Task следует указать запрос «DBCC FREEPROCCACHE»

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

Если используется версия до SQL Server 2014 (12.x), SQL Server применяет пороговое значение в зависимости от процента измененных строк. Это значение не зависит от числа строк в таблице. Пороговое значение:
Если на момент оценки статистических данных кратность в таблице не превышала 500, обновление выполняется для каждых 500 модификаций.
Если на момент оценки статистических данных кратность в таблице превышала 500, обновление выполняется для каждых 500 + 20 % модификаций


Начиная с версии SQL Server 2016 (13.x) и при уровне совместимости базы данных 130 SQL Server используется пороговое значение для динамического обновления статистических данных по убыванию. Значение изменяется в зависимости от числа строк в таблице. Оно вычисляется как квадратный корень из произведения текущего значения кратности в таблице и 1000. Например, если таблица содержит 2 миллиона строк, значение вычисляется как квадратный корень из (1000 * 2000000) = 44721,359. Благодаря этому изменению статистика для больших таблиц будет обновляться чаще. Но если уровень совместимости для базы данных ниже 130, применяется пороговое значение SQL Server 2014 (12.x).

Оптимизатор запросов проверяет наличие устаревшей статистики перед компиляцией запроса и до выполнения кэшированного плана запроса.
Параметр AUTO_UPDATE_STATISTICS_ASYNC (асинхронное обновление статистики) определяет, какой режим обновления статистики использует оптимизатор запросов: синхронный или асинхронный. По умолчанию параметр асинхронного обновления статистики отключен, и оптимизатор запросов обновляет статистику синхронно.
При синхронном обновлении статистики запросы всегда компилируются и выполняются с актуальной статистикой. Если статистика оказывается устаревшей, оптимизатор запросов ожидает появления обновленной статистики, прежде чем начать компиляцию и выполнение запроса. При асинхронном обновлении статистики запросы компилируются с существующей статистикой, даже если она устарела. Если на момент компиляции запроса статистика оказывается устаревшей, оптимизатор запросов может выбрать неоптимальный план запроса. Запросы, которые компилируются после выполнения асинхронного обновления, будут усовершенствованы благодаря использованию обновленной статистики.

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

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

Зачем нужна статистика

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

Статистика обеспечивает сведения о том, какие значения существуют в домене данных, насколько эти значения отличаются друг от друга, каковы размеры хранилища и количество значений. Статистика способствует тому, чтобы оптимизатор запросов (QO) мог выявить и подготовить наиболее эффективные операции поиска, просмотра, уточняющие запросы, объединения различных видов и т. д., а также определить размер памяти, выделяемой для обработки результатов.

Статистика может быть автоматически сформирована и обновлена в зависимости от определенных условий, задаваемых на уровне базы данных. Она также формируется автоматически, когда индексы создаются и перестраиваются (но не реорганизуются). Дополнительные сведения об этих параметрах и порогах, определяющих время перестроения статистики для сбора текущих метаданных распространения, вы можете найти в первой статье цикла. Здесь же рассматриваются две проблемы: желание взглянуть на объекты, создаваемые автоматически, и узнать время событий, чтобы определить промежутки для создания объектов вручную.

Идентификация объектов статистики в базе данных

Существует много способов сделать SQL Server запрос о свойствах имеющихся объектов статистики, чтобы получить представление об их составе и содержащихся сведениях о распространении данных, но мы сосредоточимся на простом процессе идентификации. Для этого необходимо изучить всего два простых запроса SQL Server. Мы рассмотрим как системное представление каталога sys.stats, так и функцию динамического управления sys.dm_db_stats_properties.

С помощью описанных ниже запросов можно возвратить базовую информацию «не существует» и другие полезные сведения о ваших объектах статистики в базе данных. Сначала рассмотрим подход с использованием sys.stats, он показан в листинге 1.

Я выполняю присоединение к другому системному представлению каталога, sys.objects, с целью разрешения имени объекта, к которому привязана статистика (через object_id), а также для фильтрации результатов, чтобы искать только объекты, созданные пользователем (с использованием столбца is_ms_shipped в sys.objects).

На этом этапе еще не создано никаких объектов в базе данных Tech_Outbound, используемой в данной серии статей. Поэтому не возвращается никаких результатов. Однако я хочу построить несколько базовых таблиц для просмотра статистики, так что сначала я создаю таблицу чисел с использованием программного кода, подготовленного Ициком Бен-Ганом. Бен-Ган является автором многочисленных статей, в том числе опубликованных в этом журнале, и данный программный код заимствован из его арсенала инструментов (листинг 2).

Если повторно запустить запрос на обнаружение, приведенный в листинге 1, то его результаты, в частности, объясняются созданием первичного ключа. Соглашение об именовании для имени статистики соответствует автоматическому именованию первичного ключа. Кроме того, в результате создания первичного ключа по умолчанию формируется кластеризованный индекс, что в свою очередь приводит к созданию базовой статистики для этого индекса, так как каждая страница должна быть просмотрена, и получается полный просмотр. Это самое полное представление вашего распространения данных, которое можно получить. Обычно мы получаем выборочную статистику, но, так как при перестроении индекса был произведен полный просмотр, полезно выполнить полное построение статистики (или обновление статистики через полный просмотр, если условия подходящие).

С помощью запроса к sys.dm_db_stats_properties можно получить дополнительные сведения, помимо тех, которые предоставляются из sys.stats. Для запроса к этой функции динамического управления Dynamic Management Function (DMF) нужно предоставить два значения параметров: object_id и stats_id. Эти значения известны из результатов, возвращенных ранее из sys.stats. Используя их и присоединение к sys.objects для извлечения разъясняющей информации, мы получаем такой запрос, как показан в листинге 3.

Функция динамического управления sys.dm_db_stats_properties позволяет установить время, когда статистика была обновлена в последний раз, а также получить информацию о базовом домене объекта (в данном случае 50 000 строк) и количестве строк, включенном в выборку при создании или обновлении статистики, количестве шагов в статистической гистограмме, количестве нефильтрованных строк, которое учитывается при работе с фильтрованной статистикой и, наконец, modification_counter для объекта статистики.

Счетчик изменений отслеживает число шагов изменений, сделанных в базовом столбце для объекта, описываемого статистикой. В данном случае это будет столбец первичного ключа dbo.Numbers. В статистике со многими столбцами это значение существует только для начального столбца, как и метрика для шагов, так как только начальный столбец в статистике со многими столбцами имеет связанную с ним гистограмму.

Результаты подтверждают сказанное ранее об использовании полного просмотра при создании или перестроении индекса: этот вывод можно сделать, взглянув на столбцы rows и rows_sampled из sys.dm_db_stats_properties. Учитывая, что они равные, можно заключить, что выбрано 100% строк для построения статистики для первичного ключа в этой таблице.

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

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

Я собираюсь задействовать таблицу dbo.Numbers, чтобы создать другую таблицу, которая будет использована позднее в статьях цикла для углубленного изучения статистики.

Если повторно выполнить запрос к sys.stats (первый запрос обнаружения), будут получены результаты, приведенные на рисунке 1, которые показывают, что просто акт создания таблицы не сформирует новых объектов статистики:

Теперь воспользуемся таблицей dbo.Numbers, чтобы загрузить в таблицу 100 000 строк со значением 1000 в столбец c1 и одной записью со значением 2000. Эта таблица будет использована позднее при рассмотрении влияния кратности — величины, характеризующей уникальность значений данных, на планы выполнения и статистику (листинг 4).

На данном этапе мы еще не создали никаких новых объектов статистики. Однако с помощью кода в листинге 5 я хочу создать некластеризованный индекс для столбца c1.

Теперь при запуске запроса обнаружения sys.stats мы видим в базе данных дополнительный объект статистики (рисунок 2).

Этого вполне следует ожидать, то же самое произошло с таблицей dbo.Numbers. Но что можно сказать о другой стороне автоматического создания статистики? Она проявляется, когда в статистике необходимо отразить, как обрабатываются объединения и предикаты поиска при создании плана выполнения. На сегодня понимание статистики распространения существует лишь для столбца dbo.Numbers.n (его столбца первичного ключа) и столбца dbo.T0.c1, для которого был вручную создан кластеризованный индекс. Нам предстоит создать еще одну таблицу, специально чтобы показать результирующее поведение:

Эта инструкция не создаст никаких связанных объектов статистики, как было показано ранее. Для таблицы T1 не существует ни одного индекса, когда я добавил строку с помощью листинга 6.

Но что произойдет, если выполнить следующий запрос?

Будет получен единственный результат (2000), но следует подчеркнуть, что происходит со статистикой. Не существует объекта статистики для T1 до тех пор, пока запрос не выполнен (вернее, до момента непосредственно перед выполнением запроса), так как оптимизатор запросов определил, что у него нет понимания данных для выполнения объединения. Поэтому он создал статистику в процессе построения плана выполнения. Выходные данные, возвращаемые теперь запросом обнаружения sys.stats, показаны на рисунке 3.

Установив значение AUTO_CREATE_STATISTICS = ON, вы увидите статистику, созданную для объединений при отсутствии начального столбца статистики.

Такое же поведение можно увидеть при фильтрации через предикат предложения WHERE. В листинге 7 два запроса не создают никакой статистики. Дело в том, что оптимизатор запросов не нуждается в дополнительных сведениях о распространении данных для любого из этих запросов. Для первого запроса это объясняется тем, что мы уже имеем статистику благодаря режиму автоматического создания из только что выполненного объединения, что привело к формированию запросов _WA_Sys_… (это соглашение об именовании по умолчанию для автоматически формируемой статистики). Причина, по которой второй запрос не создает статистики, заключается в том, что оптимизатор запросов может определить результаты на основе определения таблицы, не допускающего значений NULL.

Но что происходит, когда оптимизатору запросов требуется больше информации относительно распределения значений для предиката? Мы получаем автоматическое формирование статистики, как показано ниже:

Это приводит к созданию выделенной статистики, показанной в строке 4 на рисунке 4.

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

Недавно на конференции IT/Dev Connections я выступал с докладом на тему статистики распределения для Microsoft SQL Server. На мой взгляд, эта тема достаточно важна, чтобы посвятить ей несколько статей.

Почему для SQL Server важны статистические данные? Потому что без сведений о том, как распределены данные по таблицам и индексам, оптимизатор запросов SQL Server Query Optimizer (QO) не сможет составить полную картину распределения ваших данных. Следовательно, оптимизатору не удастся выяснить, какие операции наиболее эффективны для обработки запросов. В результате в планах выполнения, создаваемых оптимизатором, постоянно используются просмотр индексов и таблиц и неэффективные объединения, из-за которых эксплуатировать платформу неудобно.

Что такое статистика распределения

Статистика представляет распределение данных в столбце или столбцах. QO хранит статистику для таблицы или индексированного представления в объекте статистики. Для таблицы объект статистики создается на индексе или на списке столбцов таблицы. Объект статистики состоит из трех компонентов: заголовка с метаданными о статистике, гистограммы с распределением значений в первом ключевом столбце объекта статистики и вектора плотности для оценки различимости значений между столбцами, который также именуют кратностью. Компонент Database Engine может оценивать кратность с использованием любых данных в объектах статистики.

Как используется статистика

Оптимизатор запросов задействует статистику, чтобы определить, как обслужить запрос SQL в зависимости от распределения значений данных, выяснив, сколько строк будет возвращено из каждой операции в плане запроса, а также выбирая наиболее подходящую операцию плана запроса. Варианты таких операций могут быть следующие: поиск в таблице или индексе или просмотр таблицы или индекса для выбора данных; хеш-соединение или соединение слиянием для объединения наборов данных из различных операций на пути в плане выполнения и т. д. Если оптимизатор запросов QO не может определить, имеет ли данный предикат в поиске или соединении высокую кратность (уникален в сравнении с полным доменом значений), то на таких участках планов выполнения всегда будет преобладать неоптимальное соединение, в частности постоянные просмотры полных таблиц или индексов. Зная значения кратности, оптимизатор запросов может использовать менее затратные операции для ускоренного возврата результатов по запросам, направленным в базу данных.

Как создается статистика

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

Кроме того, существуют параметры уровня базы данных, управляющие способами автоматического создания и обновления статистики. Эти параметры: Auto Create Statistics, Auto Update Statistics и Auto Update Statistics Asynchronously. Они устанавливаются с помощью команды ALTER DATABASE, как показано в приведенном листинге.

Описание AUTO_CREATE_STATISTICS

Если присвоено значение ON, параметр AUTO_CREATE_STATISTICS обеспечивает автоматическое создание статистики для столбцов, используемых для поиска, или предикатов соединения. Это означает, что если нет существующего объекта статистики, созданного одновременно с созданием индекса, или статистики, созданной вручную, то SQL Server создаст необходимую статистику в рамках построения плана выполнения запроса как часть дополнительных действий по подготовке плана. Это также произойдет, если столбец является частью статистики нескольких столбцов, но не начальным столбцом, так как начальному столбцу индекса уделяется особое внимание по сравнению с другими столбцами, которые могут следовать за ним. Более подробно о различии во внимании, уделяемом начальному и остальным столбцам, будет рассказано в следующих статьях на тему статистики распределения в SQL Server. Напомню, это событие создания произойдет в рамках формирования плана и увеличивает накладные затраты при его подготовке. По умолчанию этот параметр имеет значение ON.

Описание AUTO_UPDATE_STATISTICS

Если присвоено значение ON, параметр AUTO_UPDATE_STATISTICS обеспечивает автоматическое обновление статистики программой SQL Server, когда существующая статистика признана устаревшей обработчиком запросов. Процесс и дополнительные затраты обновления статистики возникают, когда связанные столбцы востребованы как часть следующего запроса, выполняемого после того, как SQL Server обнаруживает устаревание статистики. Это достигается с помощью счетчика изменений, который отслеживает количество изменений в объекте, связанном с объектом статистики (со времени последнего события создания или обновления, в зависимости от того, какое из них произошло последним). До выпуска SQL Server 2016 поведением по умолчанию по отношению к статистике, отмеченной как устаревшая, было следующее:

  • для таблиц без строк статистика отмечается как подлежащая обновлению после первой вставки строки;
  • для таблиц, содержащих до 500 строк, статистика отмечается как подлежащая обновлению, когда начальный столбец в объекте статистики встречает более 500 обновлений со времени последнего обновления статистики;
  • для таблиц, содержащих более 500 строк, статистика отмечается как подлежащая обновлению, когда начальный столбец в объекте статистики встречает более 500 обновлений + 20% количества строк в столбцах со времени последнего обновления статистики.

Очевидно, что последняя ситуация не способствует своевременному обновлению статистики в больших таблицах: удовлетворить требование 20% значений столбцов + 500 становится все сложнее по мере роста таблицы. С момента выпуска SQL Server 2008 с пакетом обновления SP1 вы можете воспользоваться флагом трассировки 2371, чтобы сделать 20-процентный порог более гибким (читай «низким») при увеличении количества строк в таблице, как показано на рисунке.

В версии SQL Server 2016 использование этого флага трассировки представляет собой новый стандарт для автоматического обновления статистики, и задействовать флаг TF2371 более не требуется. Как и в случае с AUTO_CREATE_STATISTICS, статистика, удовлетворяющая условиям устаревания, будет обновлена при построении плана выполнения запроса. Обновление статистики таким способом неявно происходит синхронно с формированием плана выполнения. По умолчанию этот параметр имеет значение ON.

Описание AUTO_UPDATE_STATISTICS_ASYNC

Если присвоено значение ON, параметр AUTO_UPDATE_STATISTICS_ASYNC автоматически переопределяет синхронное поведение обновления статистики, разрешая асинхронное обновление. В сущности, это означает, что обновления статистики более не происходят обязательно на этапе создания плана выполнения, но в любое время, определяемое SQL Server. Негативной стороной разделения дополнительных действий обновления статистики с формированием плана выполнения является то, что при составлении плана выполнения иногда используется устаревшая статистика, которая может влиять на производительность. Если изменились значения в подмножестве данных, связанном с объектом статистики, но план не изменился, то проблемы не возникает, поскольку тот же самый план использовался бы, если бы статистика была обновлена в рамках формирования плана запроса. Однако если в результате изменений произошли перемены, например при выборе операции поиска или просмотра, то отделение обновления статистики от создания плана может отрицательно повлиять на производительность. По умолчанию этот параметр имеет значение OFF.

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

Читайте также: