Предисловие:
В интернете можно найти массу информации о дефрагментации или перестроению индексов. Однако, большинство рекомендаций направлены на базы данных, у которых есть время минимальной нагрузки (в основном ночью).
А как быть с базами данных, которые постоянно используются как для изменения данных, так и для получения информации 24 часа в сутки, 7 дней в неделю?
В данной статье приведу реализованный механизм автоматизации дефрагментации индексов в базе данных для поддержки базы данных у нас на предприятии. Этот механизм позволяет все время дефрагментировать нужные индексы, т к в системе 24×7 фрагментация индексов происходит постоянно. И часто дефрагментация даже 1 раз в день для индексов недостаточна.
Решение:
Сначала общий подход:
1) создать для нужной базы данных представление, с помощью которого можно получить какие индексы и на сколько в процентах дефрагментированы
2) создать таблицу для сохранения результатов дефрагментации индексов
3) создать хранимую процедуру, которая и будет анализировать и дефрагментировать выбранный индекс
4) создать представление для просмотра статистики по результатам дефрагментации индексов
5) создать задание в Агенте, которое будет запускать реализованную хранимую процедуру в п.3.
А теперь реализация:
1) создать для нужной базы данных представление, с помощью которого можно получить какие индексы и на сколько в процентах дефрагментированы:
USE [Название_Базы_Данных] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vIndexDefrag] as with info as (SELECT [object_id], database_id, index_id, index_type_desc, index_level, fragment_count, avg_fragmentation_in_percent, avg_fragment_size_in_pages, page_count, record_count, ghost_record_count FROM sys.dm_db_index_physical_stats (DB_ID(N'Название_Базы_Данных') , NULL, NULL, NULL , N'DETAILED') where index_level = 0 ) SELECT b.name as db, s.name as shema, t.name as tb, i.index_id as idx, i.database_id, idx.name as index_name, i.index_type_desc,i.index_level as [level], i.[object_id], i.fragment_count as frag_num, round(i.avg_fragmentation_in_percent,2) as frag, round(i.avg_fragment_size_in_pages,2) as frag_page, i.page_count as [page], i.record_count as rec, i.ghost_record_count as ghost, round(i.avg_fragmentation_in_percent*i.page_count,0) as func FROM Info as i inner join [sys].[databases] as b on i.database_id = b.database_id inner join [sys].[all_objects] as t on i.object_id = t.object_id inner join [sys].[schemas] as s on t.[schema_id] = s.[schema_id] inner join [sys].[indexes] as idx on t.object_id = idx.object_id and idx.index_id = i.index_id where i.avg_fragmentation_in_percent >= 30 and i.index_type_desc <> 'HEAP'; GO Данное представление выводит только те индексы, у которых процент дефрагментации не меньше 30. Т е те индексы, которых нужно дефрагментировать. Выводятся только те индексы, которые не являются кучами, т к последние при дефрагментации могут дать отрицательный эффект, выражающийся либо блокировкой такой кучи, либо еще большей фрагментацией индекса.
В представлении используется важное системное представление sys.dm_db_index_physical_stats
2) создать таблицу для сохранения результатов дефрагментации индексов:
USE [Название_Базы_Данных] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[Defrag]( [ID] [bigint] IDENTITY(794,1) NOT NULL, [db] [nvarchar](100) NULL, [shema] [nvarchar](100) NULL, [table] [nvarchar](100) NULL, [IndexName] [nvarchar](100) NULL, [frag_num] [int] NULL, [frag] [decimal](6, 2) NULL, [page] [int] NULL, [rec] [int] NULL, [func] [int] NULL, [ts] [datetime] NULL, [tf] [datetime] NULL, [frag_after] [decimal](6, 2) NULL, [object_id] [int] NULL, [idx] [int] NULL, [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_Defrag] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]; GO ALTER TABLE [srv].[Defrag] ADD CONSTRAINT [DF_Defrag_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]; GO Главное-в этой таблице не забывать удалять данные (например, которым больше месяца или еще чаще).
Поля в таблице будут понятны по следующему пункту.
3) создать хранимую процедуру, которая и будет анализировать и дефрагментировать выбранный индекс:
USE [Название_Базы_Данных] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoDefragIndex] AS BEGIN SET NOCOUNT ON; --объявляем необходимые переменные declare @IndexName nvarchar(100) --название индекса ,@db nvarchar(100) --название базы данных ,@Shema nvarchar(100) --название схемы ,@Table nvarchar(100) --название таблицы ,@SQL_Str nvarchar (2000) --строка для формирования команды ,@frag decimal(6,2) --% фрагментации до процесса дефрагментации ,@frag_after decimal(6,2) --% фрагментации после процесса дефрагментации --Количество фрагментов на конечном уровне единицы распределения IN_ROW_DATA ,@frag_num int ,@func int --round(i.avg_fragmentation_in_percent*i.page_count,0) ,@page int --кол-во страниц индекса ,@rec int --общее кол-во записей ,@ts datetime --дата и время начала дефрагментации ,@tf datetime --дата и время окончания дефрагментации --идентификатор объекта таблицы или представления, для которых создан индекс ,@object_id int ,@idx int; --ID индекса --получаем текущую дату и время set @ts = getdate(); --получаем очередной индекс для дефрагментации --здесь именно важный индекс выбирается. При этом никогда не случиться, что один индекс будет --постоянно дефрагментироваться, а все остальные не будут выбраны для дефрагментации select top 1 @IndexName = index_name, @db=db, @Shema = shema, @Table = tb, @frag = frag, @frag_num = frag_num, @func=func, @page =[page], @rec = rec, @object_id = [object_id], @idx = idx from [srv].[vIndexDefrag] order by func*power((1.0- convert(float,(select count(*) from SRV.[srv].[Defrag] vid where vid.db=db and vid.shema = shema and vid.[table] = tb and vid.IndexName = index_name)) / convert(float, case when (exists (select top 1 1 from SRV.[srv].[Defrag] vid1 where vid1.db=db)) then (select count(*) from SRV.[srv].[Defrag] vid1 where vid1.db=db) else 1.0 end)) ,3) desc --если такой индекс получен if(@db is not null) begin --непосредственно реорганизация индекса set @SQL_Str = 'alter index ['+@IndexName+'] on ['+@Shema+'].['+@Table+'] Reorganize'; execute sp_executesql @SQL_Str; --получаем текущую дату и время set @tf = getdate() --получаем процент дефрагментации после дефрагментации SELECT @frag_after = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(@db), @object_id, @idx, NULL , N'DETAILED') where index_level = 0; --записываем результат работы insert into SRV.srv.Defrag( [db], [shema], [table], [IndexName], [frag_num], [frag], [page], [rec], ts, tf, frag_after, object_id, idx ) select @db, @shema, @table, @IndexName, @frag_num, @frag, @page, @rec, @ts, @tf, @frag_after, @object_id, @idx; --обновляем статистику для индекса set @SQL_Str = 'UPDATE STATISTICS ['+@Shema+'].['+@Table+'] ['+@IndexName+']'; execute sp_executesql @SQL_Str; end END 4) создать представление для просмотра статистики по результатам дефрагментации индексов:
USE [Название_Базы_Данных] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [srv].[vStatisticDefrag] as SELECT top 1000 [db] ,[shema] ,[table] ,[IndexName] ,avg([frag]) as AvgFrag ,avg([frag_after]) as AvgFragAfter ,avg(page) as AvgPage FROM [srv].[Defrag] group by [db], [shema], [table], [IndexName] order by abs(avg([frag])-avg([frag_after])) desc; GO Данное представление можно использовать для ежедневного уведомления администраторов о проделанной работы автоматизации дефрагментации индексов.
5) создать задание в Агенте, которое будет запускать реализованную хранимую процедуру в п.3:
Здесь нужно подбирать время экспериментальным путем. У меня получилось где-то 5 минут, а где-то и час.
Данный алгоритм можно расширить на несколько баз данных, но тогда нужно ввести еще и п.6:
Собрать всю статистику по автоматизации дефрагментации индексов в базах данных в одном месте для последующей отправки администраторам.
А теперь хотелось бы остановиться поподробнее о уже выложенных рекомендациях по поддержке индексов:
1) дефрагментация сразу всех индексов во время минимальной нагрузки базы данных — для систем 24×7 это недопустимо, т к индексы фрагментируются постоянно и времени простоя базы данных практически не бывает
2) перестроение индекса — данная операция блокирует таблицу или секцию (в случае секционированного индекса), что для систем 24×7 не есть хорошо. Далее, перестроение индекса в режиме реального времени поддерживается только в Enterprise-решении, а также может привести к повреждению данных.
Данный метод не является оптимальным, но успешно справляется с тем, чтобы индексы были достаточно дефрагментированы (не выше 30-40% фрагментации) для использования их оптимизатором для построения планов выполнения.
Буду очень признателен, если в комментариях появятся аргументированные плюсы и минусы данного подхода, а также проверенные альтернативные предложения.
Источники:
Реорганизация и перестроение индексов
sys.dm_db_index_physical_stats
ссылка на оригинал статьи https://habrahabr.ru/post/314454/
Добавить комментарий