Андрей Смирнов
Время чтения: ~9 мин.
Просмотров: 0

План обслуживания «на каждый день» – Часть 2: Автоматическое обновление статистики

  • Tutorial

ce69c7290f523afa94874252b03ee03e.jpg В предыдущем посте была рассмотрена автоматизация процесса дефрагментации индексов. Теперь пришла очередь статистики. Собственно для чего она нужна? При выполнении любого запроса, оптимизатор запросов, в рамках имеющейся у него информации, пытается построить оптимальный план выполнения — который будет отображать из себя последовательность операций, за счет выполнения которых можно получить требуемый результат, описанный в запросе. В процессе выбора той или иной операции, оптимизатор запросов к числу наиболее важных входных данных относит статистику, описывающую распределение значений данных для столбцов внутри таблицы или индекса. Такая оценка количества элементов позволяет оптимизатору запросов создавать более эффективные планы выполнения. В то же время, если статистика будет содержать устаревшие данные, могут быть выбраны менее эффективные операции, которые приведут к созданию медленных планов выполнения. Например, когда для небольшой выборки на устаревшей статистике выбирается более затратный оператор Index Scan, вместо оператора Index Seek. Как Вы видите, чтобы быть максимально полезной для оптимизатора запросов, статистика должна быть точной и свежей. Время от времени SQL Server периодически сам обновляет статистику — данное поведение регулируется опциями AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS. Кроме того, при пересоздании индексов, статистика по ним обновляется автоматически с включенным флагом FULLSCAN, гарантирующим наиболее точное распределение данных. При реорганизации индексов же — статистика не обновляется. Когда данные в таблицах изменяются очень часто, целесообразно выполнять избирательное обновление статистики вручную, с помощью операции UPDATE STATISTICS. Также ручное обновление, очень важно, когда для статистики задан флаг NORECOMPUTE, означающий, что автоматическое обновление статистики в дальнейшем не требуется. Просмотреть это свойство, как впрочем и на все остальные, можно в свойствах статистики:

SELECT s.* FROM sys.stats s JOIN sys.objects o ON s.[object_id] = o.[object_id] WHERE o.is_ms_shipped = 0 

Применяя возможности динамического SQL, напишем скрипт по автоматическому обновлению устаревшей статистики:

DECLARE @DateNow DATETIME SELECT @DateNow = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))  DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = (     SELECT ' UPDATE STATISTICS [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] [' + s.name + '] WITH FULLSCAN' + CASE WHEN s.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';' FROM sys.stats s WITH(NOLOCK) JOIN sys.objects o WITH(NOLOCK) ON s.[object_id] = o.[object_id] WHERE o.[type] IN ('U', 'V') AND o.is_ms_shipped = 0 AND ISNULL(STATS_DATE(s.[object_id], s.stats_id), GETDATE()) <= @DateNow     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')  PRINT @SQL EXEC sys.sp_executesql @SQL 

При выполнении будут генерироваться следующие стейтменты:

UPDATE STATISTICS [Production].[Shift] [PK_Shift_ShiftID] WITH FULLSCAN; UPDATE STATISTICS [Production].[Shift] [AK_Shift_Name] WITH FULLSCAN, NORECOMPUTE; 

Критерий устаревания статистики в каждой конкретной ситуации может быть свой. В данном примере — 1 день. В некоторых случаях слишком частное обновление статистики для больших таблиц может заметно снижать производительность базы данных, поэтому данный скрипт можно модифицировать. Например, для больших таблиц обновлять статистику реже:

DECLARE @DateNow DATETIME SELECT @DateNow = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))  DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = (     SELECT ' UPDATE STATISTICS [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] [' + s.name + '] WITH FULLSCAN' + CASE WHEN s.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';' FROM ( SELECT    [object_id] , name , stats_id , no_recompute , last_update = STATS_DATE([object_id], stats_id) FROM sys.stats WITH(NOLOCK) WHERE auto_created = 0 AND is_temporary = 0 -- 2012+ ) s JOIN sys.objects o WITH(NOLOCK) ON s.[object_id] = o.[object_id] JOIN ( SELECT   p.[object_id] , p.index_id , total_pages = SUM(a.total_pages) FROM sys.partitions p WITH(NOLOCK) JOIN sys.allocation_units a WITH(NOLOCK) ON p.[partition_id] = a.container_id GROUP BY    p.[object_id] , p.index_id ) p ON o.[object_id] = p.[object_id] AND p.index_id = s.stats_id WHERE o.[type] IN ('U', 'V') AND o.is_ms_shipped = 0 AND (   last_update IS NULL AND p.total_pages > 0 -- never updated and contains rows OR   last_update <= DATEADD(dd,  CASE WHEN p.total_pages > 4096 -- > 4 MB THEN -2 -- updated 3 days ago ELSE 0  END, @DateNow) )     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')  PRINT @SQL EXEC sys.sp_executesql @SQL 

В следующей части мы рассмотрим автоматизацию резервного копирования баз данных. Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод:SQL Server Typical Maintenance Plans: Automatic Statistics Update

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

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

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

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

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

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

Например, в период минимальной нагрузки на систему — в ночные часы.

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

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

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

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

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

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

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

5.2) Список таблиц установите «All» — это означает что будет обновлена статистика по всем таблицам БД

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

222.png

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

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

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

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

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

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

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

9) если у вас значительная нагрузка на базу в пиковые моменты (сотни пользователей, роботы-фоновики и т.п.) то наряду с параметром «Автоматическое создание статистики (AUTO_CREATE_STATISTICS)» будет полезно также для базы данных включить параметр «Автоматическое асинхронное обновление статистики (AUTO_UPDATE_STATISTICS_ASYNC)»

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

Одной из распространенных причин не оптимальной работы СУБД MS SQL являет неправильная настройка выполнения регламентных операций в системе управления базы данных. Обычно правильная настройка сразу же оказывает положительный эффект на производительность системы в целом. Эту информацию должен знать каждый программист 1С.

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

reglamentnie-operaciya.jpg

Виды регламентных операций СУБД MS SQL

  1. обновление статистик;
  2. очистка процедурного кеша;
  3. дефрагментация индексов;
  4. реиндексация таблиц БД.

Для успешной сдачи сертификата «1С Эксперт» необходимо знать эти виды операций и как их правильно настроить. Далее подробно остановимся на каждой: зачем необходимо выполнять их и с какой периодичностью.

Обновление статистик

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

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

По умолчанию SQL Server настроен на частичное автоматическое обновление статистики. Однако желательно периодически запускать полное обновление статистики (full scan).

Если вы только начинаете программировать в 1С или просто хотите систематизировать свои знания — попробуйте Школу программирования 1С нашего друга Владимира Милькина. Пошаговые и понятные уроки даже для новичка с поддержкой учителя. Попробуйте бесплатно по ссылке >>

Обновление статистики не требует монопольного режима — оно не блокирует таблицы БД.

Рекомендуется выполнять не реже одного раза в день.

Очистка процедурного кеша

Оптимизатор MS SQL обработки запроса кеширует данные плана запроса. Это сделано для лучшей производительности системы. Но однако так же, как в примере со статистикой, это может и помешать оптимальному выполнению запроса.

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

Время выполнения — сразу после обновления статистики (не реже раза в день).

Дефрагментация индексов

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

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

Рекомендуется выполнять данную регламентную операцию не реже раза в неделю.

Реиндексация таблиц БД

Реиндексация  — полное перестроение индексов для заданных таблиц. Реиндексация существенно повышает производительность системы в целом. После реиндексации не имеет смысл производить дефрагментацию индексов — фрагментация индекса будет исправлена.

Выполнение операции доступно только в монопольном режиме — она блокирует таблицу БД MS SQL.

Данную регламентную операцию рекомендует производить не реже одного раза в неделю.

К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.

Оцените статью, пожалуйста!

Используемые источники:

  • https://habr.com/post/209816/
  • http://www.gilev.ru/updatestat/
  • https://programmist1s.ru/1s-ekspert-reglamentnyie-operatsii-subd/

Рейтинг автора
5
Подборку подготовил
Максим Уваров
Наш эксперт
Написано статей
171
Ссылка на основную публикацию
Похожие публикации