По материалам статьи Juergen Thomas: Changes to automatic update statistics in SQL Server - traceflag 2371
Перевод: Александр Гладченко
Технический редактор: Ирина Наумова
C 1998 года, когда вышел SQL Server 7.0, базовым принципом дизайна автоматического обновления статистики было отслеживание количества изменений в таблице. Когда количество изменений в таблице превышало определённый порог (процент), выполнялось автоматическое обновление статистики.
В последующих версиях, вплоть до SQL Server 2005, в дизайне сбора статистической информации произошли некоторые изменения, например, была добавлена возможность асинхронного обновления статистики. Кроме этого, в SQL Server была добавлена возможность оптимизации исполнения запросов за счёт отслеживания изменений на уровне столбцов с целью предоставления возможности обновления только статистики по селективности строк таблицы. Для получения более подробной информации можно почитать более полный обзор статистических данных, используемых оптимизатором запросов, который доступен здесь: http://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx
Изначально, в версиях SQL Server 2008 и 2008 R2 не было никаких крупных изменений в дизайне сбора и использования статистики. Появившийся ещё во времена разработки SQL Server 7.0 алгоритм, основанный на фиксированном пороговом значении, остался без существенных изменений. Превышение этого порога вызывало автоматическое обновление статистики. Пороговое значение, используемое изначально, равнялось 20%, и оно оставалось таким во всех выпусках SQL Server, которые до недавнего времени появлялись на рынке. Это означает, что до недавнего времени все выпуски SQL Server инициировали процедуру автоматического обновления статистики, если для столбца таблицы были зафиксированы изменения, которые затрагивали более 20% от числа строк таблицы. Обновлению подлежит индекс на основе B-дерева, который включает такой столбец в качестве первого столбца индекса, или если существует отдельная статистика для этого столбца среди относящихся к таблице статистик столбцов. Индексы, в которых не участвует этот столбец в качестве первого (ведущего) столбца индекса, не нуждаются в обновлении статистики. Ещё одно ограничение, это реализация в коде SQL Server 7.0 специальных защитных мер для маленьких таблиц от частого обновления статистики. Требуется, чтобы в таблице было, по меньшей мере, 500 строк (6 строк для временных таблиц), тогда автоматический пересчёт статистики у этой таблицы будет происходить после превышения двадцатипроцентного порога изменений.
Как автоматическое обновление статистики используется приложениями SAP
SAP взяло на вооружение автоматическое обновление статистики сразу же после его появления, и десятки тысяч клиентов много лет с успехом использовали эту функциональность в своих приложениях на базе SAP Netweaver. Это положение заметно отличается от других СУБД, для которых обновление статистики у таблиц необходимо планировать как постоянную задачу. Компания SAP рекомендует использовать эту функциональность SQL Server в полном объёме и для всех своих приложений. Применительно к SAP BW существовали небольшие проблемы с тем, что требовалось иметь в таблице не меньше 500 строк. Поэтому в SAP BW были разработаны и документированы специальные процессы, предназначенные осуществлять дополнительное обновление статистики там, где не работало автоматическое обновление статистики. Документ называется: SAP OSS Note №849062 - "Optimizer statistics for InfoCubes in BW". Однако, для заказчиков систем SAP сегодня характерно увеличение объёмов данных, поддержка 64-разрядности и общая тенденция использования платформы Intel для обработки большого количества информации, необходимой бизнес - приложениям. С SAP ERP сегодня работает большое количество клиентов, а некоторые таблицы могут содержать миллиарды строк. Во многих случаях приходится сталкиваться с ситуациями, когда автоматическое обновление статистики с фиксированным порогом 20% требует изменения сотен миллионов строк в таблице, и только после этого будет запущено очередное автоматическое обновление статистики. В результате пользователи жаловались, что обновление статистики выполняется крайне редко, устаревшая статистика иногда приводит к использованию неоптимальных планов исполнения запросов.
Новое решение
В SP1 для SQL Server 2008 R2 и в последующих версиях SQL Server можно использовать флаг трассировки, который изменяет фиксированный порог обновления статистики 20% на динамически изменяющуюся величину процента изменений таблицы. Чем в таблице больше строк, тем ниже порог обновления статистики. Например, если этот флаг трассировки активирован, процедура автоматического обновления статистики для таблицы с миллиардом строк будет выполнена при изменении миллиона строк. Если же флаг трассировки не активирован, та же таблица с миллиардом записей не получит автоматического обновления статистики, пока в ней не накопится 200 миллионов изменений. Для того чтобы активировать эту новую возможность, нужно включить флаг трассировки 2371. После этого, порог для триггера обновления статистики будет зависеть от числа строк в таблице. Однако, требование того, что в таблице для возможности автоматического обновления статистики должно быть не меньше 500 строк, сохраняется. Как и прежде, обновление статистики будет запускаться на основании результатов исследования пробной выборки значений, предустановленной величины, которая динамически изменяется в зависимости от числа строк в таблице.
Более подробно о том, что даёт использование флага трассировки 2371
На представленном ниже графике видно, как работает новая функциональность. Для небольших таблиц порог по-прежнему будет примерно 20%. Только когда таблица превысит 25000 строк, начнёт действовать динамическое изменение порога срабатывания, когда при увеличении количества строк, процент изменённых записей становится все ниже и ниже. Например, в таблице с 100000 строк порог для триггера обновления статистики будет снижен до 10%. В таблице с 1000000 строк потребуется изменить около 3,2%, после чего сработает триггер автоматического обновления статистики.
В таблицах с 10000000 и 50000000 строк необходимые для обновления статистики изменения будут составлять не более 1% или 0,5% от общего числа строк. Для таблицы с 100000000 строк потребуется около 0,31%. Такое поведение нового механизма автоматического обновления статистики приведёт к тому, что для наиболее важных таблиц в схемах SAP, которые подвержены большому числу изменений, статистика будет обновляться чаще. Применительно к приложениям SAP это может быть в 20-60 раз чаще, чем это было раньше, когда после перехода в новый финансовый месяц или год необходимое число изменений накапливалось только через несколько недель или несколько месяцев, с новым флагом счёт пойдёт на дни.
Последствия включения флага трассировки
Как было показано выше, автоматическое обновление статистики будет выполняться чаще для тех таблиц, у которых изменения происходят с высокой частотой. Недостатком такого подхода является то, что обновление статистики приводит к перекомпиляции запросов, относящихся к этой таблице. Это может повысить риски получения другого плана исполнения запроса при повторном его исполнении. С другой стороны, динамический порог для триггера обновления статистики необходимо рассматривать как средство, призванное решать известные проблемы выбора оптимального плана для нового месяца или нового финансового года, когда статистика ещё актуальна для месяца предыдущего отчётного периода.
Поскольку новая функциональность только появилась, ещё нет никаких рекомендаций о том, как в целом её следует использовать для приложений SAP. Тесты этого нового флага, проведённые Microsoft в течение нескольких месяцев, оказались весьма успешными, и эта функциональность будет добавлена в новую версию SQL Server 2012, и в SAP ERP, которая будет построена на основе поддерживающих флаг версий SQL Server уже в середине ноября 2011 года. Однако авторы статьи хотели бы призвать клиентов SAP испытать новую функциональность, если запросы с устаревшей статистикой создают проблемы, или используются специальные процедуры, описанные в "OSS note #1558087". Новая функциональность становится доступной после установки Service Pack 1 для SQL Server 2008 R2, и будет доступна в следующей версии SQL Server 2012. В новой версии тоже нужно будет включать соответствующий флаг трассировки. Планов включить эту функциональность в более ранние версии SQL Server, такие, как SQL Server 2008 или 2005, нет.