Обсудить
бизнес-задачи
бизнес-задачи
Обслуживание базы данных
блог о bi, №1 в рунете
Обслуживание базы данных — это комплексная задача, охватывающая весь жизненный цикл хранилища данных. Это важнейший процесс, который позволяет предотвратить деградацию БД.
В этой статье будут рассмотрены те аспекты обслуживания базы данных, на которые необходимо обратить особое внимание: обслуживание таблиц, индексов, статистики и контроль фрагментации.
Индексы, фрагментация и статистика
Индексы в базах данных
Индекс — это структура данных, которая используется для ускорения поиска данных в таблице базы данных. Он работает аналогично индексу в книге, позволяя быстро находить информацию без необходимости просматривать каждую страницу (или строку в случае базы данных). Индексы могут существенно улучшить производительность запросов, особенно при работе с большими объемами данных.
Статистика — это набор метрик о состоянии таблицы и индексов, используемых для оптимизации планов выполнения запросов. Эта информация используется для выбора наиболее эффективного способа обработки запроса.
Системная и пользовательская статистика
Системная статистика генерируется автоматически SQL Server и содержит информацию о распределении данных в таблицах и индексах. По умолчанию SQL Server автоматически обновляет системную статистику при выполнении DML-операций (INSERT, UPDATE, DELETE).
Пользовательская статистика позволяет пользователям базы данных явно задавать распределение данных в таблицах и индексах, что может быть полезно для оптимизации запросов в случаях, когда автоматическая статистика не дает достаточной информации. Она генерируется с помощью процедуры CREATE STATISTICS и может быть использована движком запросов вместо системной статистики. Пользовательская статистика создается с использованием ключевого слова WITH FULLSCAN, что означает, что все строки будут считаны для генерации статистики.
CREATE STATISTICS
s_sales_customer_product
ON
Sales.Sales (CustomerID, ProductID)
WITH FULLSCAN;Почему необходимо обновлять статистику?
Система может использовать устаревшую статистику для планирования запросов, что приведет к их неправильной оптимизации и ухудшению производительности. При добавлении, удалении или изменении значительного количества данных статистика может стать неточной, что также влияет на планы выполнения запросов. Регулярное обновление статистики помогает поддерживать высокую производительность системы, предотвращая негативные эффекты от старой информации.
Как обновлять статистику
В SQL Server используется команда UPDATE STATISTICS для обновления статистики конкретных объектов.
UPDATE STATISTICS Sales;В настройках БД можно активировать опцию «Автоматическое обновление статистики», которая автоматически обновляет статистику после определенного числа операций вставки, обновления или удаления. По умолчанию установлено значение “True”. Автоматическое обновление статистики используется в большинстве случаев. Однако, если необходимо регулярное обновление статистики или частое автоматическое обновление статистики занимает много времени/ресурсов, то его можно отключить, переведя флаг в значение “False”.
Еще одним важным параметром является «Автоматическое создание статистики с добавлением». Статистика с добавлением (инкрементальная статистика) обновляется не вся целиком, а только те строки, которые были изменены. Сбор статистики с добавлением происходит быстрее. Однако, такой способ сбора статистики может давать небольшую погрешность, поэтому он не используется там, где это критично. По умолчанию установлено значение “False”.
Статистика в секционированных таблицах
Для секционированных таблиц SQL Server создает отдельные статистические объекты для каждой секции. Это означает, что статистика для каждой секции может быть обновлена независимо друг от друга. Автоматическое обновление статистики происходит при выполнении DML-операций в рамках каждой секции, но иногда может потребоваться принудительное обновление статистики для улучшения производительности запросов.
Для обновления статистики на секционированных таблицах используется команда UPDATE STATISTICS с указанием номера секции. Это позволяет точно контролировать, какие секции будут обновлены.
Предположим, у нас есть таблица OrderDetails в схеме Sales, секционированная по полю SaleYear:
CREATE TABLE Sales.OrderDetails
(
OrderDetailID INT PRIMARY KEY IDENTITY(1,1),
SaleYear INT NOT NULL,
OrderID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
Price DECIMAL(10, 2) NOT NULL
)
CREATE CLUSTERED INDEX CI_OrderDetails_SaleYear
ON Sales.OrderDetails(SaleYear);Для обновления статистики можно использовать следующий код:
DECLARE @i INT = 1;
WHILE @i <= 5 -- Предполагаем, что таблица секционирована по 5 годам
BEGIN
EXEC('UPDATE STATISTICS Sales.OrderDetails PARTITION(' + CAST(@i AS NVARCHAR(10)) + ') WITH RESAMPLE;');
SET @i = @i + 1;
END;Фрагментация
Фрагментация индекса происходит, когда данные в индексе не упорядочены или распределены неравномерно. Это может привести к снижению эффективности поиска и увеличению времени выполнения запросов. Фрагментация может возникнуть из-за удаления или вставки записей, что приводит к перемещению оставшихся элементов индекса.
Просмотр фрагментации RowStore
Чтобы получить детальную информацию о физическом состоянии индексов RowStore, можно использовать системные представления и функции, например sys.dm_db_index_physical_stats.
SELECT
DB_NAME(database_id) as DatabaseName,
object_name(object_id) as TableName,
index_id,
avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, -1, NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent > 0
ORDER BY avg_fragmentation_in_percent DESC;Запрос вернет список таблиц и индексов с процентом фрагментации выше нуля, что позволяет идентифицировать области для дальнейшего исследования и возможного восстановления.
Просмотр фрагментации ColumnStore
Для индексов ColumnStore, доступны специализированные системные представления и функции, такие как sys.dm_db_column_store_row_group_physical_stats.
SELECT
100.0*(ISNULL(sum(deleted_rows),0))/NULLIF(sum(total_rows),0) as frag,
o.name as table_name,
i.name as object_name,
st.partition_number as partition
FROM
sys.dm_db_column_store_row_group_physical_stats st
inner join sys.objects o
on o.object_id = st.object_id
inner join sys.indexes i
on i.object_id = o.object_id
inner join sys.partitions p
on p.object_id = o.object_id
and p.index_id = i.index_id
and p.partition_number = st.partition_number
GROUP BY
o.name,
i.name,
st.partition_number
HAVING
100.0*(ISNULL(sum(deleted_rows),0))/NULLIF(sum(total_rows),0) > 0
Данный запрос позволяет увидеть все фрагментированные сегменты ColumnStore-индексов.
Rebuild и Reorganize
Операции REBUILD и REORGANIZE используются для улучшения производительности и уменьшения фрагментации в индексах и таблицах. Эти операции имеют разные цели и механизмы действия, что делает их подходящими для разных сценариев.
Rebuild
Цель:
Полная перестройка индекса или таблицы. Это самый радикальный способ борьбы с фрагментацией, поскольку он полностью пересоздает индекс или таблицу с нуля, удаляя все старые данные и восстанавливая структуру в идеальное состояние.
Полная перестройка индекса или таблицы. Это самый радикальный способ борьбы с фрагментацией, поскольку он полностью пересоздает индекс или таблицу с нуля, удаляя все старые данные и восстанавливая структуру в идеальное состояние.
Механизм:
Операция REBUILD перемещает все данные в новый блок данных, создает новые страницы и индексы, и удаляет старые данные. Это может занять значительное время, особенно для больших объектов, и требует дополнительного места на диске для временных файлов.
Операция REBUILD перемещает все данные в новый блок данных, создает новые страницы и индексы, и удаляет старые данные. Это может занять значительное время, особенно для больших объектов, и требует дополнительного места на диске для временных файлов.
Пример использования для индексов:
ALTER INDEX ix_cl_dim_stores_store_id ON dbo.dim_stores
REBUILD
Пример использования для таблиц:
ALTER TABLE dbo.dim_items
REBUILD;
Reorganize
Цель:
Улучшение производительности за счет уменьшения фрагментации, сохраняя при этом основную структуру индекса или таблицы. Это менее затратная по времени и ресурсам операция по сравнению с REBUILD.
Улучшение производительности за счет уменьшения фрагментации, сохраняя при этом основную структуру индекса или таблицы. Это менее затратная по времени и ресурсам операция по сравнению с REBUILD.
Механизм:
REORGANIZE перемещает только необходимые части данных, чтобы уменьшить фрагментацию, сохраняя при этом остальные данные и индексы. Это позволяет достичь значительного улучшения производительности с меньшим временем выполнения и использованием ресурсов.
REORGANIZE перемещает только необходимые части данных, чтобы уменьшить фрагментацию, сохраняя при этом остальные данные и индексы. Это позволяет достичь значительного улучшения производительности с меньшим временем выполнения и использованием ресурсов.
Пример использования для индексов:
ALTER INDEX ix_ncl_dim_items_item_source_id ON dbo.dim_items
REORGANIZE;Пример использования для таблиц:
ALTER TABLE dbo.dim_items
REORGANIZE;Разница между Rebuild и Reorganize
REBUILD занимает больше времени и ресурсов, поскольку полностью пересоздает индекс или таблицу, в то время как REORGANIZE выполняет более легкую операцию, перемещая только фрагментированные данные. Хотя REORGANIZE быстрее, REBUILD может предложить более долгосрочное решение для серьезной фрагментации, особенно если она вызвана удалением большого количества данных.
Выбор между REBUILD и REORGANIZE зависит от конкретной ситуации, уровня фрагментации и доступных ресурсов. В большинстве случаев REORGANIZE является предпочтительным вариантом для ежедневного обслуживания, в то время как REBUILD может быть использован для решения более сложных проблем с фрагментацией.
Обновление индексов в секционированных таблицах
Индексы секционированных таблиц можно обновлять для каждой партиции отдельно, добавляя PARTITION = номер партиции после REBUILD/REORGANIZE:
ALTER INDEX CI_OrderDetails_SaleYear
ON Sales.OrderDetails
REBUILD PARTITION = 4;Использование Hints вместе с REBUILD
Hints (хинты) — это инструкции, которые дают дополнительные подсказки оптимизатору запросов о том, как лучше всего выполнить запрос. Они могут быть использованы для улучшения производительности запросов, но их использование должно быть обоснованным, поскольку неправильное использование хинтов может привести к нежелательным последствиям.
Ниже представлены наиболее часто используемые из них в связке с REBUILD:
- Online («в сети»): позволяет сохранять доступность таблиц/индексов при проведении операции REBUILD.
ALTER INDEX ix_cl_dim_stores_store_id ON dbo.dim_stores
REBUILD WITH (ONLINE = ON)(Операции с индексом в режиме «в сети» поддерживаются только в SQL Server выпуска Enterprise Edition)
- SORT_IN_TEMPDB: определяет, где будет происходить сортировка данных во время этих операций. По умолчанию, сортировка выполняется непосредственно на месте, что может привести к значительному использованию памяти и CPU на сервере. Использование SORT_IN_TEMPDB позволяет перенести этот процесс в tempdb, что может снизить нагрузку на основную базу данных и улучшить производительность сервера.
ALTER INDEX ix_cl_dim_stores_store_id ON dbo.dim_stores
REBUILD WITH (SORT_IN_TEMPDB = ON)Вывод
Правильное использование описанных правил и инструментов позволит оптимизировать работу БД и скорость выполнения запросов на высоком уровне. При пренебрежении обслуживанием база данных неизбежно будет терять в производительности.