(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Дефрагментация индексов со сбором статистики MS SQL 2008 R2

Источник: habrahabr
sp00n

Одна из первых задач, которая возникает перед DBA после развертывания новой БД - это настройка планов по ее обслуживанию. Зачастую, в план обслуживания включается задача по дефрагментации индексов. Мне нравится, когда я знаю не только то, что дефрагментация выполнилась ночью с воскресенья на понедельник, но и то, как она прошла, сколько выполнялась, какие индексы были перестроены и в каком состоянии они остались после дефрагментации.

Для сбора такой статистики мною был написан небольшой скриптик, который собирает информацию о выполненной работе, а так же дает максимально подробное описание
о состоянии индексов до и после проделанной процедуры.

Но начнем с простого, создадим таблицу для хранения этих самых данных (я создал отдельную БД, куда складываю таблицы, которыми пользуюсь во время обслуживания баз данных сервера):

Столбец Тип Комментарий
proc_id int Порядковый номер процедуры, для идентификации
start_time datetime Начало выполнения запроса ALTER INDEX
end_time datetime Завершение выполнения запроса ALTER INDEX
database_id smallint Идентификатор БД
object_id Int Идентификатор таблицы
table_name varchar(50) Имя таблицы
index_id Int Идентификатор индекса
index_name varchar(50) Имя индекса
avg_frag_percent_before float Процент фрагментации индекса перед выполнением ALTER INDEX
fragment_count_before bigint Количество фрагментов до дефрагментации
pages_count_before bigint Количество страниц индекса до дефргаментации
fill_factor tinyint Уровень заполнения страниц индекса
partition_num int Номер секции
avg_frag_percent_after float Процент фрагментации индекса после выолнения ALTER INDEX
fragment_count_after bigint Количество фрагментов после дефрагментации
pages_count_after bigint Количество страниц индекса после дефргаментации
action varchar(10) Выполняемое действие

Вся процедура дефрагментации, будет брать данные из этой таблицы, а значит, надо ее заполнить:
DECLARE @currentProcID INT --Порядковый номер процедуры дефрагментации --Выбираем последний номер, и просто добавляем единичку SELECT @currentProcID = ISNULL(MAX(proc_id), 0) + 1 FROM dba_tasks.dbo.index_defrag_statistic --И заполняем таблицу данными о состоянии индексов INSERT INTO dba_tasks.dbo.index_defrag_statistic ( proc_id, database_id, [object_id], table_name, index_id, index_name, avg_frag_percent_before, fragment_count_before, pages_count_before, fill_factor, partition_num) SELECT @currentProcID, dm.database_id, dm.[object_id], tbl.name, dm.index_id, idx.name, dm.avg_fragmentation_in_percent, dm.fragment_count, dm.page_count, idx.fill_factor, dm.partition_number FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm INNER JOIN sys.tables tbl ON dm.object_id = tbl.object_id INNER JOIN sys.indexes idx ON dm.object_id = idx.object_id AND dm.index_id = idx.index_id WHERE page_count > 8 AND avg_fragmentation_in_percent > 10 AND dm.index_id > 0
Условия выборки:
page_count > 8 - я считаю, что перестраивать индексы с малым количеством страниц не имеет смысла, т.к. лучше не станет, а время, затраченное на выполнение процедуры - очень ценно, особенно если база работает круглосуточно и постоянно находится под высокой нагрузкой. (После замечания unfilled поднял планку до 8 страниц)
avg_fragmentation_in_percent > 10 - Тоже очень субъективная цифра, практически во всей документации предлагают не трогать индекс, если его фрагментация составляет 10 или менее процентов, с чем я согласен, если у вас дела обстоят по другому, меняем.
dm.index_id > 0 - 0 - это куча

После того, как таблица заполнена, нам известно какие индексы необходимо обслужить.
Займемся делом:
--Обьявим необходимые переменные DECLARE @partitioncount INT --Количество секций DECLARE @action VARCHAR(10) --Действие, которые мы будем делать с индексом DECLARE @start_time DATETIME --Начало выполнения запроса ALTER INDEX DECLARE @end_time DATETIME --Конец выполнения запроса ALTER INDEX --см описание таблицы DECLARE @object_id INT DECLARE @index_id INT DECLARE @tableName VARCHAR(250) DECLARE @indexName VARCHAR(250) DECLARE @defrag FLOAT DECLARE @partition_num INT DECLARE @fill_factor INT --Сам запрос, который мы будем выполнять, я поставил MAX, потому как иногда меняю такие скрипты, и забываю поправить размер данной переменной, в результате получаю ошибку. DECLARE @sql NVARCHAR(MAX) --Далее объявляем курсор DECLARE defragCur CURSOR FOR SELECT [object_id], index_id, table_name, index_name, avg_frag_percent_before, fill_factor, partition_num FROM dba_tasks.dbo.index_defrag_statistic WHERE proc_id = @currentProcID ORDER BY [object_id], index_id DESC --Сначала не кластерные индексы OPEN defragCur FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num WHILE @@FETCH_STATUS=0 BEGIN SET @sql = N'ALTER INDEX ' + @indexName + ' ON ' + @tableName SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @object_id AND index_id = @index_id; --В моем случае, важно держать неможко пустого места на страницах, потому, что вставка в тоже таблицы имеете место, и не хочеться тратить драгоценное время пользователей на разбиение страниц IF (@fill_factor != 80) BEGIN @sql = @sql + N' REBUILD WITH (FILLFACTOR = 80)' SET @action = 'rebuild80' END ELSE BEGIN --Тут все просто, действуем по рекомендации MS IF (@defrag > 30) --Если фрагментация больше 30%, делаем REBUILD BEGIN SET @sql = @sql + N' REBUILD' SET @action = 'rebuild' END ELSE --В противном случае REORGINIZE BEGIN SET @sql = @sql + N' REORGANIZE' SET @action = 'reorginize' END END --Если есть несколько секций IF @partitioncount > 1 SET @sql = @sql + N' PARTITION=' + CAST(@partition_num AS nvarchar(5)) print @sql --Вывод выполняемого запроса --Фиксируем время старта SET @start_time = GETDATE() EXEC sp_executesql @sql --И время завершения SET @end_time = GETDATE() --Сохраняем время в таблицу UPDATE dba_tasks.dbo.index_defrag_statistic SET start_time = @start_time, end_time = @end_time, [action] = @action WHERE proc_id = @currentProcID AND [object_id] = @object_id AND index_id = @index_id FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num END CLOSE defragCur DEALLOCATE defragCur

Ну и на последок, соберем информацию о индексах после процедуры дефрагментации:
UPDATE dba SET dba.avg_frag_percent_after = dm.avg_fragmentation_in_percent, dba.fragment_count_after = dm.fragment_count, dba.pages_count_after = dm.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm INNER JOIN dba_tasks.dbo.index_defrag_statistic dba ON dm.[object_id] = dba.[object_id] AND dm.index_id = dba.index_id WHERE dba.proc_id = @currentProcID AND dm.index_id > 0

После выполнения такого скрипта, можно получить и посчитать очень много полезной информации. Например, время обслуживания всех индексов и каждого отдельно. Понять как это связано с размером индекса, увидеть эффективность данной операции. Собрав такую информацию за несколько раз, можно немного поменять процедуру, наверняка какие-то индексы фрагментируются больше и быстрее. В таком случае их обслуживание следует выполнять чаще. Как воспользоваться полученной информацией, решайте сами. Что же касается меня, то я, после анализа каждой такой процедуры, меняю планы обслуживания, если того требует ситуация. Мои базы работают под высокой нагрузкой круглосуточно. Поэтому постоянно перестраивать все индексы и на 1-2 часа снижать производительность сервера я не могу. Если Ваша БД, тоже работает круглосуточно, для выполнения таких вещей, следует настроить Resource Governor.

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

Ссылки по теме


 Распечатать »
 Правила публикации »
  Написать редактору 
 Рекомендовать » Дата публикации: 09.01.2014 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Microsoft Windows Professional 10, Электронный ключ
Microsoft 365 Business Basic (corporate)
Microsoft 365 Business Standard (corporate)
Microsoft 365 Apps for business (corporate)
Microsoft Office для дома и учебы 2019 (лицензия ESD)
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Компьютерные книги. Рецензии и отзывы
3D и виртуальная реальность. Все о Macromedia Flash MX.
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
 



    
rambler's top100 Rambler's Top100