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

При активном добавлении и удалении записей в информационной базе, созданной на MS SQL Server, индексы начинают фрагментироваться, что может существенно сказаться на быстродействии системы. Для борьбы с этим эффектом рекомендуется производить реиндексацию базы по расписанию (на серьезных проектах - не реже раза в сутки). Для этого можно воспользоваться командой

DBCC DBREINDEX ('TABLE_NAME')

С помощью команды

DBCC SHOWCONTIG('TABLE_NAME')

можно получить статистику, в т.ч. о состоянии индексов, и проанализировать необходимость дефрагментации и ее эффект.

Сами команды можно выполнить с помощью интерактивного средства выполнения запросов, такого как Query Analyzer, либо из командной строки, используя программу OSQL.EXE, входящую в состав MS SQL Server.

Вот описание эффекта от дефрагментации на одном из проектов (версия 7.4):

К таблице базы SQL «Операционные документы» была применена следующая команда:

DBCC DBREINDEX('опердокумент'). 

Результаты сведены в следующую таблицу.

где:

• Pages Scanned - указывает количество страниц в таблице. В нашем примере их 20.
• Extents Scanned - показывает количество экстентов занимаемых таблицей. Это сразу указывает на фрагментированность данных - для сохранения 20 страниц хватает 3х экстентов.
• Extent Switches - говорит о количестве раз переключения с экстента на экстент при последовательном чтении данных. В идеальной ситуации это число равно Extents Scanned - 1
• Avg. Pages per Extent - говорит о среднем количестве страниц на экстент при перемещении по цепочке страниц. Это значение должно быть как можно ближе к 8
• Scan Density - представляет собой значение для внешней фрагментации. Этот результат получается от соотношения идеальной смены экстентов к фактической. Вполне очевидно, это что должно быть близко к 100%
• Logical Scan Fragmentation - дает процент страниц не в логическом порядке. Если страницы находятся в строгой последовательности слева направо, то данный параметр будет иметь значение 0
• Extent Scan Fragmentation - дает процент экстентов не в логическом порядке. Имеет то же логическое значение, что и Logical Scan Fragmentation
• Avg. Bytes Free per Page - должно быть как можно ближе к 0 если fill factor 100. Иное значение требует незначительных расчетов. Если fill factor 80, это обеспечивает примерно 1600 свободных байтов на страницу.
• Avg. Page Density - должно быть как можно ближе к 100%.
• Avg. Bytes Free per Page и Avg. Page Density дают хорошее представление о внутренней фрагментации.

Описания этих параметров были взяты с форума сайта www.sql.ru.

На этом проекте регулярная дефрагментация дала ускорение работы системы на порядки, поскольку сильно фрагментированные индексы замедляют выполнение запросов, те, в свою очередь, приводят к блокировкам, и ситуация нарастает, как снежный ком.

В приложенном архиве находится пример sql-скрипта для дефрагментации таблиц и пример XML-файла настроек планировщика для запуска этого скрипта по расписанию. Для размещения sql-скрипта в информационной базе можно выполнить команду:

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" -E -d<ИмяБазы> -isp_reindex_all_tables.txt

Для ручного запуска процедуры дефрагментации индексов достаточно в Query Analyzer или в аналогичном средстве интерактивного запуска запросов выполнить запрос

sp_reindex_all_tables  

либо выполнить команду:

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" -E -d<ИмяБазы> -Q "sp_reindex_all_tables"

В приложеном архиве так же находится пример sql-скрипта для просмотра статистики таблиц и пример XML-файла настроек планировщика для запуска этого скрипта по расписанию. Для размещения sql-скрипта в информационной базе можно выполнить команду:

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" -E -d<ИмяБазы> -isp_showcontig_all_tables.txt

Для ручного запуска процедуры просмотра статистики достаточно в Query Analyzer или в аналогичном средстве интерактивного запуска запросов выполнить запрос

sp_showcontig_all_tables

либо выполнить команду:

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" -E -d<ИмяБазы> -Q"sp_showcontig_all_tables"

Для ручного запуска процедуры и сохранении статистики в текстовый файл можно выполнить команду:

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" -E -d<ИмяБазы> -Q"sp_showcontig_all_tables" -oc:\showconfig_all_tables.txt

sp_reindex_all_tables

CREATE PROCEDURE sp_reindex_all_tables
AS
DECLARE reindex_cursor CURSOR 
FOR 
SELECT name FROM sysobjects WHERE type = 'U'

OPEN reindex_cursor

DECLARE @tablename sysname

FETCH NEXT FROM reindex_cursor INTO @tablename 

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXECUTE ('DBCC DBREINDEX(['+@tablename+'])')

FETCH NEXT FROM reindex_cursor INTO @tablename

END 

CLOSE reindex_cursor

DEALLOCATE reindex_cursor

sp_showcontig_all_tables

CREATE PROCEDURE sp_showcontig_all_tables 
AS
DECLARE showcontig_cursor CURSOR 
FOR 
SELECT name FROM sysobjects WHERE type = 'U'

OPEN showcontig_cursor

DECLARE @tablename sysname

FETCH NEXT FROM showcontig_cursor INTO @tablename 

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXECUTE ('DBCC SHOWCONTIG(['+@tablename+'])')

FETCH NEXT FROM showcontig_cursor INTO @tablename

END 

CLOSE showcontig_cursor

DEALLOCATE showcontig_cursor

GO