В предыдущем посте мы рассматривали автоматическое обслуживание фрагментированных индексов. Теперь пришла очередь статистики.
Собственно для чего она нужна?
При выполнении любого запроса, оптимизатор запросов, в рамках имеющейся у него информации, пытается построить оптимальный план выполнения — который будет отображать из себя последовательность операций, за счет выполнения которых можно получить требуемый результат, описанный в запросе.
В процессе выбора той или иной операции, оптимизатор запросов к числу наиболее важных входных данных относит статистику, описывающую распределение значений данных для столбцов внутри таблицы или индекса. Фактически, строится диаграмма распределения с числом элементов по определенному диапазону значений. Такая оценка количества элементов позволяет оптимизатору запросов создавать более эффективные планы выполнения.
В то же время, если статистика будет содержать устаревшие данные, могут быть выбраны менее эффективные операции, которые приведут к созданию медленных планов выполнения. Например, когда для небольшой выборки на устаревшей статистике выбирается более затратный оператор 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
) 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
В следующей части мы рассмотрим автоматизацию бекапирования баз данных и проверку их целостности.
Автор: AlanDenton
