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

  • Tutorial
В предыдущем посте была рассмотрена автоматизация процесса дефрагментации индексов. Теперь пришла очередь статистики.

Собственно для чего она нужна?

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

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

Такая оценка количества элементов позволяет оптимизатору запросов создавать более эффективные планы выполнения. В то же время, если статистика будет содержать устаревшие данные, могут быть выбраны менее эффективные операции, которые приведут к созданию медленных планов выполнения. Например, когда для небольшой выборки на устаревшей статистике выбирается более затратный оператор 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
  • +4
  • 40,7k
  • 5
Поделиться публикацией

Комментарии 5

    +1
    Хорошо пишите. Жду продолжения. Особенно будет интересно узнать, как автоматизировать создания бекапов в SQL Express, где нет агента и планировщика.
      0
      Спасибо. Учту данное пожелание, когда буду подготавливать пост по бекапам.
        +1
        Я написал .bat который запускается по расписанию и делает копию базы. Если интересно скину сюда содержимое
          +1
          Я думаю лучше дождаться продолжения и там уже выложить. Чтобы было в тему.
          0
          есть такая вещь — SQL Scheduler, работает с Express, Очень выручает иногда.

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

        Самое читаемое