Предисловие
В интернете можно найти массу информации о дефрагментации или перестроению индексов. Однако, большинство рекомендаций направлены на базы данных, у которых есть время минимальной нагрузки (в основном ночью).
А как быть с базами данных, которые постоянно используются как для изменения данных, так и для получения информации 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