Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 07 August 2020 13:01:51


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Обнаружение фрагментации индексов в SQL Server 2005

Tim Chapman (оригинал: Detecting index fragmentation in SQL Server 2005)
Перевод Моисеенко С.И.

Фрагментация в действии

Имеется два типа фрагментации: внешняя и внутренняя. Внешняя фрагментация имеет место, когда логический порядок индекса не соответствует его физическому порядку. Это заставляет SQL Server 2005 выполнять дополнительную работу, чтобы вернуть упорядоченные результаты. По большей части, внешняя фрагментация не является слишком большой проблемой для конкретного поиска, который возвращают очень небольшое число записей, или запросов, которые возвращают результирующие наборы, не требующие упорядочения.

Внутренняя фрагментация имеет место, когда есть слишком много свободного пространства на страницах индекса. Как правило, некоторое свободное пространство желательно, особенно когда индекс только что создан или перестроен. Вы можете задать коэффициент заполнения (Fill Factor) при создании или перестройке индекса, чтобы указать процент заполнения страниц индекса во время его создания. Если страницы индекса будут слишком сильно фрагментированы, это приведет к более длительному выполнению запросов (из-за дополнительных операций чтения, требуемых для нахождения данных), и заставит ваши индексы становиться большими, чем это необходимо. Если, напротив, свободного пространства нет на страницах данных индекса, то изменение данных (прежде всего в результате вставки) вызовет разделение страниц, выполнение чего также потребует дополнительных расходов ресурсов системы.

Обнаружение фрагментации

В версиях до SQL Server 2005, для определения фрагментации конкретной таблицы или индекса использовалась команда DBCC SHOWCONTIG. Этот инструмент хорошо выполняет работу по детализации фрагментации и дает возможность вернуть данные в виде dataset для дополнительного программирования.

SQL Server 2005 обеспечивает лучшие возможности по сравнению с DBCC SHOWCONTIG, предоставляя динамическую функцию управления для обнаружения фрагментации индекса. Функция sys.dm_db_index_physical_stats принимает в качестве параметров базу данных, таблицу базы данных и индекс, для которого Вы хотите определить фрагментацию. Есть несколько опций, которые позволяют Вам указать уровень детализации при рассмотрении фрагментации индексов. Функция sys.dm_db_index_physical_stats возвращает табличные данные, относящиеся к одной конкретной таблице или индексу.

Пример фрагментации в действии

Так как sys.dm_db_index_physical_stats является функцией, я мог бы использовать новый оператор CROSS APPLY , чтобы соединить c моим системным представлением sys.tables для возвращения фрагментации для всех таблиц в базе данных; но эта функция не позволяет мне сделать так. Я думаю, что это происходит оттого, что функция была написана до того, как появилась функциональная возможность CROSS APPLY. В результате придется написать функцию обертки вокруг функции sys.dm_db_index_physical_stats, чтобы иметь возможность выполнить соединение с представлением sys.tables. Вот скрипт создания этой функции-обертки:

 

CREATE FUNCTION sys_PhysicalIndexStatistics_Wrapper
 (
         @DatabaseID INT,
         @ObjectID INT,
         @IndexID INT,
         @PartitionNumber INT,
         @Mode INT
 )
 RETURNS @IndexStats TABLE
 (
         DatabaseID SMALLINT,
         ObjectID INT,
         IndexID INT,
         PartitionNumber INT,
         IndexDescription VARCHAR(100),
         AllocationTypeDescription VARCHAR(100),
         IndexDepth TINYINT,
         IndexLevel TINYINT,
         AverageFragmentation FLOAT,
         FragmentCount BIGINT,
         AverageFragmentSize FLOAT,
         TablePageCount BIGINT,
         AveragePageSpaceUsed FLOAT,
         RecordCount BIGINT,
         GhostRecordCount BIGINT,
         VersionGhostRecordCount BIGINT,
         MinimumRecordSize INT,
         MaxRecordSize INT,
         AverageRecordSize FLOAT,
         ForwardedRecordCount BIGINT
 )
 BEGIN     INSERT INTO @IndexStats
         (
             DatabaseID, ObjectID, IndexID, PartitionNumber, IndexDescription, AllocationTypeDescription, IndexDepth,
             IndexLevel, AverageFragmentation, FragmentCount, AverageFragmentSize, TablePageCount,
             AveragePageSpaceUsed, RecordCount, GhostRecordCount, VersionGhostRecordCount, MinimumRecordSize,
             MaxRecordSize, AverageRecordSize, ForwardedRecordCount
         )
     SELECT
             database_id, object_id, index_id, partition_number, index_type_desc,
             alloc_unit_type_desc, index_depth,
             index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count,
             avg_page_space_used_in_percent, record_count, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes,
             max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
        FROM
                 sys.dm_db_index_physical_stats
              (
                        @DatabaseID,
                        @ObjectID,
                   @IndexID,
                   @PartitionNumber,
                   @Mode
              )
     RETURN
END

 

Чтобы вызвать эту функцию, я просто использую оператор CROSS APPLY как соединение представления sys.tables с функцией обертки. Ниже приведен типичный запрос, который возвращает все таблицы, у которых средняя фрагментация (AverageFragmentation) больше ноля:

 

SELECT t.name,s.*
 FROM sys.tables t
 CROSS APPLY sys_PhysicalIndexStatistics_Wrapper(DB_ID(), object_id, NULL, NULL, NULL) s
 WHERE AverageFragmentation > 0

 

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

Теперь мне нужно понять, что говорят результаты. Меня больше всего интересует столбец AverageFragmentation, который говорит о логической фрагментации индекса или кучи. Если показатель превышает 30 %, я серьезно рассмотрю вопрос о переиндексацию таблицы. Я могу смириться с показателем менее чем 30 %, но можно бы выполнить и реорганизацию индекса.

Почитайте статью в BOL о sys.dm_db_index_physical_stats. В ней подробно описаны остальные столбцы, которые возвращает эта функция.

Процедура перестройки

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

Чем больше Вы будете работать с функцией sys.dm_db_index_physical_stats, тем больше Вы будете понимать, когда ваши таблицы требуют переиндексации. Я надеюсь, что использование функции sys.dm_db_index_physical_stats поможет Вам гарантировать максимально эффективное использование ваших таблиц и индексов.

17-12-2007

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.