Дефрагментация индексов 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 32793 18958
Extents Scanned 4117 2374
Extent Switches 10853 2373
Avg. Pages per Extent 8.0 8.0
Scan Density [Best Count:Actual Count]. 37.77% [4100:10854] 99.83% [2370:2374]
Logical Scan Fragmentation 49.47% 0.00%
Extent Scan Fragmentation 81.47% 5.22%
Avg. Bytes Free per Page 3518.0 177.1
Avg. Page Density (full). 56.54% 97.81%

где:

  • 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
 
doc/mssql_defrag.txt · Последние изменения: 2016/04/15 15:26 (внешнее изменение)
 
За исключением случаев, когда указано иное, содержимое этой вики предоставляется на условиях следующей лицензии:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki