При активном добавлении и удалении записей в информационной базе, созданной на 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% |
где:
Описания этих параметров были взяты с форума сайта 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