Pull to refresh

Автоматизация дефрагментации индексов в базе данных MS SQL Server

Reading time 6 min
Views 24K

Предисловие


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

А как быть с базами данных, которые постоянно используются как для изменения данных, так и для получения информации 24 часа в сутки, 7 дней в неделю?

В данной статье приведу реализованный механизм автоматизации дефрагментации индексов в базе данных для поддержки базы данных у нас на предприятии. Этот механизм позволяет все время дефрагментировать нужные индексы, т к в системе 24x7 фрагментация индексов происходит постоянно. И часто дефрагментация даже 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'LIMITED')
	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) дефрагментация сразу всех индексов во время минимальной нагрузки базы данных — для систем 24x7 это недопустимо, т к индексы фрагментируются постоянно и времени простоя базы данных практически не бывает.

2) перестроение индекса — данная операция блокирует таблицу или секцию (в случае секционированного индекса), что для систем 24x7 не есть хорошо. Далее, перестроение индекса в режиме реального времени поддерживается только в Enterprise-решении, а также может привести к повреждению данных.

Данный метод не является оптимальным, но успешно справляется с тем, чтобы индексы были достаточно дефрагментированы (не выше 30-40% фрагментации) для использования их оптимизатором для построения планов выполнения.

Буду очень признателен, если в комментариях появятся аргументированные плюсы и минусы данного подхода, а также проверенные альтернативные предложения.

Источники:


» Реорганизация и перестроение индексов
» sys.dm_db_index_physical_stats
Tags:
Hubs:
+7
Comments 59
Comments Comments 59

Articles