11.5.6. Дефрагментация индексов и таблиц

Перестроение и дефрагментация индексов, DBCC SHOWCONTIG, Scan Density, Logical Scan Fragmentation и Avg. Page Density, ALTER INDEX REBUILD, DBCC DBREINDEX, ALTER INDEX REORGANIZE, DBCC INDEXDEFRAG

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

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

Прежде чем бороться с фрагментацией, рекомендуется оценить ее уровень. Сделать это можно при помощи команды DBCC SHOWCONTIG. Ее можно запускать с разным набором параметров. Если выполнить эту команду совсем без параметров, то она вернет информацию о степени фрагментации первого индекса для каждой из таблиц в текущей базе данных. Наиболее часто используемый вариант синтаксиса для этой команды выглядит как DBCC SHOWCONTIG (имя_таблицы, имя_индекса). Например, в базе данных AdventureWorks можно выполнить такую команду:

DBCC SHOWCONTIG ('HumanResources.Employee', 'PK_Employee_EmployeeID');

Расшифруем то, что возвращает эта команда:

q      Pages scanned (Просканировано страниц) — количество страниц в базе данных, используемых индексом;

q      Extents switches (Переключения экстентов) — сколько переходов между экстентами пришлось выполнить при просмотре страниц индекса. В идеале страницы индекса должны идти друг за другом в смежных экстентах, поэтому в идеальной ситуации этот параметр не должен превышать количество страниц, деленное на 8. Но такая ситуация встречается редко;

q      Avg. Pages per Extent (Средние страницы на экстент) — сколько просмотренных страниц в среднем приходится на экстент. В идеале это значение должно быть равно 8 (или близко к нему). Если это значение существенно меньше, то страницы одного индекса распределены между разными экстентами. Между ними может быть пустое пространство или страницы других объектов в базе данных, но в любом случае такой вариант означает фрагментацию индекса;

q      Scan Density [Best Count: Actual Count] (Плотность сканирования [Лучший показатель: Реальный показатель]) — отношение идеально возможного количества переходов между экстентами к реальному. Чем ближе этот показатель к 100%, тем лучше;

q      Logical Scan Fragmentation (Фрагментация логического сканирования) — в идеале все страницы индекса должны идти строго друг за другом не только по номеру, но и по физическому расположению в файле базы данных (в соответствии с таблицей IAM — Index Allocation Map, карта размещения индекса). Этот показатель возвращает, какой процент страниц индекса (из просмотренных) нарушает это требование (и, соответственно, на сколько процентов фрагментирован индекс);

q      Extent Scan Fragmentation (Фрагментация сканирования экстентов) — это почти то же самое, только оцениваются не страницы индекса, которые идут не по порядку, а экстенты, принадлежащие этому индексу, которые идут не друг за другом. Этот параметр оказывает меньшее влияние на скорость работы (и обычно он сам существенно меньше, чем параметр Logical Scan Fragmentation);

q      Avg. Bytes free per page (Средний размер пустого пространства на страницах) — чем выше этот показатель, тем хуже. Как правило, большое значение этого показателя возникает после удаления большого количества данных из таблицы;

q      Avg. Page Density (Средняя плотность страницы) — этот показатель, обратный предыдущему, определяет, на сколько в среднем заполнена каждая страница индекса. Чем выше значение, тем лучше.

Основные показатели, на которые следует обращать внимание — это Scan Density, Logical Scan Fragmentation и Avg. Page Density. Плотность сканирования и средняя плотность страницы должны быть максимально близки к 100%, а фрагментация логического сканирования — к 0. Если уровень фрагментации составляет 30—40% и выше, имеет смысл подумать о проведении дефрагментации.

Другая возможность оценить фрагментацию индексов — воспользоваться графическими средствами SQL Server Management Studio. Для этого нужно раскрыть в Object Explorer контейнер имя_сервера | Databases | имя_базы данных | Tables | имя_таблицы | Indexes, открыть свойства для нужного индекса и перейти на вкладку Fragmentation (Фрагментация) (рис. 11.14). Информации по фрагментации на этой вкладке приводится меньше, но самое важное (о заполнении страниц и общая оценка фрагментации) указано.

Рис. 11.14. Оценка степени фрагментации индекса из SQL Server Management Studio

Дефрагментацию индексов можно проводить разными способами.

Самый радикальный способ — воспользоваться командой ALTER INDEX REBUILD. Эта команда полностью удаляет существующий индекс или индексы и создает их заново, устраняя всякую фрагментацию. В SQL Server 2000 эта операция выполнялась при помощи команды DBCC DBREINDEX. Она поддерживается и в SQL Server 2005, но только для обеспечения обратной совместимости. Рекомендуется использовать вместо нее команду ALTER INDEX REBUILD. Например, чтобы произвести полное перестроение проанализированного индекса PK_Employee_EmployeeID, можно воспользоваться командой:

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

Можно перестроить все индексы для данной таблицы или представления:

ALTER INDEX ALL ON HumanResources.Employee REBUILD;

Отметим, что при перестроении некластерного индекса его можно отключить командой ALTER INDEX DISABLE, в результате места на диске для этой операции потребуется намного меньше.

Если же вы хотите произвести перестроение всех индексов для всех таблиц в базе данных, придется заняться программированием. Можно написать скрипт Transact-SQL или воспользоваться возможностями объектных моделей SMO или SQL-DMO. Например, следующий скрипт VBScript произведет перестроение всех индексов в базе данных AdventureWorks, подключившись к серверу LONDON2\SQL2005 средствами аутентификации Windows:

Dim oServer, oDB, oTable

Set oServer = CreateObject("SQLDmo.SqlServer2")

oServer.LoginSecure = True

oServer.Connect "LONDON2\SQL2005"

Set oDB = oServer.Databases("Northwind")

For Each oTable In oDB.Tables

    If oTable.SystemObject = False Then oTable.RebuildIndexes

Next

Еще более интересный, но намного более сложный способ — производить перестроение только фрагментированных индексов, например, в зависимости от значения параметра Scan Density, возвращаемого командой DBCC SHOWCONTIG. Решение может выглядеть так, как представлено далее (оно было найдено в статье Тома Паллена (Tom Pullen) "Automating Reindexing In SQL Server" (Автоматическая переиндексация SQL Server) на сайте www.sql-server-performance.com).

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

CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL AS

-- Объявляем необходимые переменные

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @objectowner VARCHAR(255)

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @indexname CHAR(255)

DECLARE @dbname sysname

DECLARE @tableid INT

DECLARE @tableidchar VARCHAR(255)

-- На всякий случай проверяем,что база данных пользовательская

SELECT @dbname = db_name();

IF @dbname IN ('master', 'msdb', 'model', 'tempdb')

BEGIN

    PRINT 'Эта процедура не может быть запущена для системных БД';

    RETURN

END

-- Начинаем проверку уровня фрагментации

-- Вначале объявляем курсор

DECLARE tables CURSOR FOR

SELECT convert(varchar,so.id) FROM sysobjects so JOIN sysindexes si ON so.id = si.id WHERE so.type ='U' AND si.indid < 2 AND si.rows > 0;

-- Затем создаем временную таблицу для хранения информации о фрагментации

CREATE TABLE #fraglist (ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255),IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL);

-- Открываем курсор

OPEN tables

-- Для каждой таблицы в базе данных выполняем команду DBCC SHOWCONTIG

FETCH NEXT FROM tables INTO @tableidchar

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Проходим по всем индексам для таблицы

    INSERT INTO #fraglist

    EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, _

        TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

    FETCH NEXT

    FROM tables

    INTO @tableidchar

END

-- Закрываем курсор

CLOSE tables

DEALLOCATE tables

-- Для проверки выводим информацию из временной таблицы

SELECT * FROM #fraglist

-- Теперь необходимо произвести дефрагментацию

-- Вначале опять объявляем курсор

DECLARE indexes CURSOR FOR

SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity FROM #fraglist f JOIN sysobjects so ON f.ObjectId=so.id WHERE ScanDensity <= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Выводим для проверки информацию о начале дефрагментации

SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- Открываем курсор

OPEN indexes

-- Проходим циклом по всем индексам

FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0

BEGIN

    SET QUOTED_IDENTIFIER ON

    SELECT @execstr = 'DBCC DBREINDEX (' + "'" +      RTRIM(@objectowner) + '.' + RTRIM(@tablename) + "'" + ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'

    SELECT 'Выполняем: ' SELECT(@execstr)

    EXEC (@execstr)

    SET QUOTED_IDENTIFIER OFF

    FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

END

-- Затем закрываем курсор

CLOSE indexes;

DEALLOCATE indexes;

-- Отчитываемся о времени завершения

SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE());

-- Удаляем временную таблицу

DROP TABLE #fraglist;

GO

Если вам нужно выполнить дефрагментацию для всех индексов в интересующей базе данных, для которых значение параметра Scan Density меньше 80%, можно использовать команду:

EXEC sp_deframent_indexes 80.00;

Перестроение индексов — операция достаточно тяжелая. Она требует большого количества времени и места на диске, при этом пользователи не могут работать с таблицами, для которых выполняется такое перестроение. Поэтому во многих случаях имеет смысл производить не перестроение, а реорганизацию индексов при помощи команды ALTER INDEX REORGANIZE (другая устаревшая, но еще поддерживаемая команда — DBCC INDEXDEFRAG). При реорганизации индексов производится дефрагментация только страниц самого нижнего уровня индекса (листьевых). Такой способ, конечно, в меньшей степени снижает фрагментацию, но его можно применять без отключения пользователей, и он требует меньшего количества ресурсов.

Для выполнения операций по перестроению или реорганизации индексов можно также использовать графические средства SQL Server Management Studio. Команды Rebuild (Перестроить) и Reorganize (Реорганизовать) предусмотрены в контекстном меню для самого индекса и для всего контейнера Indexes (Индексы). Можно также воспользоваться средствами планов обслуживания баз данных (database maintenance plans).

Важная проблема, которую необходимо учитывать, связана с тем, что при перестроении индексов статистика не обновляется автоматически. Поэтому выполнение практически любого запроса к базе данных будет приводить к автоматическому пересчету статистики. Это, конечно, не ускорит их выполнение. Поэтому, если вы производите перестроение индексов на регулярной основе, очень рекомендуется после завершения этой операции сразу произвести обновление статистики во всей базе данных. Если вы используете для этой цели скрипт SQL-DMO, то можно добавить в него единственную строку с вызовом метода UpdateStatistics():

Dim oServer, oDB, oTable

Set oServer = CreateObject("SQLDmo.SqlServer2")

oServer.LoginSecure = True

oServer.Connect "LONDON2\SQL2005"

Set oDB = oServer.Databases("AdventureWorks")

For Each oTable In oDB.Tables

    If oTable.SystemObject = False Then

        oTable.RebuildIndexes

        oTable.UpdateStatistics

    End if

Next

Подробно про статистику будет рассказываться в разд. 11.5.8.

Получить учебные материалы по этому курсу