Организация индексов в SQL Server 2005

SQL Server 2005 представляет новую парадигму для языка DDL. Здесь все объекты создаются с использованием команды CREATE, удаляются посредством команды DROP и модифицируются с помощью ALTER. SQL Server 2005 не имеет отдельных хранимых процедур для изменения одного аспекта объекта, подобно процедуре p_defaultdb в версиях SQL Server 2000 и 7.0, которая изменяет определенную по умолчанию базу данных пользователя, или специальных одноцелевых команд для создания атрибутов, вроде sp_addtype. SQL Server 2000 начал движение в этом направлении с расширения функциональных возможностей команды ALTER DATABASE, позволяющей выполнять все изменения в свойствах базы данных, и с ограничения использования команды sp_dboption.

Некоторые действия, которые в ранних версиях требовали использования ключей модуля DBCC, будут выполняться в SQL Server 2005 командами ALTER. Прежде, в SQL Server 2000, DBCC был акронимом модуля проверки непротиворечивости базы данных DataBase Consistency Checker, и ключи DBCC, доступные в первых версиях SQL Server, CHECKDB и CHECKTABLE, действительно выполняли набор последовательных проверок. Но поскольку продукт разрастался, а с ним и DBCC, разработчики из компании Microsoft начали давать бедному перегруженному DBCC и другие задания, так что в конце концов получилось так, что меньше всего он как раз занимается проверкой целостности базы данных. В оперативной документации SQL Server 2000 Books Online (BOL) уже дали новую расшифровку DBCC: DataBase Console Command.

Одни ключи DBCC в SQL Server 2000 требуют ввода информации, другие — производят изменения. Две из команд DBCC, до версии 2005, имели дело с индексами: DBCC SHOWCONTIG выводила отчет о фрагментации индекса, а DBCC INDEXDEFRAG уменьшала фрагментацию, изменяя страницы, которые использует индекс. В SQL Server 2005 команда ALTER INDEX заменяет DBCC INDEXDEFRAG. Заменой для DBCC SHOWCONTIG стал новый динамически управляемый объект, названный sys.dm_db_index_physical_stats().

Разработчики Microsoft в течение долгого времени испытывали потребность в замене DBCC SHOWCONTIG. Одним из поводов было то, что DBCC как команда не предлагала простого способа фильтрации возвращаемой информации или только получения интересующей информации. Для сохранения вывода DBCC SHOWCONTIG в таблицу приходилось задействовать INSERT EXEC, но предварительно требуется создать таблицу отдельной операцией и только после сохранения данных в новую структуру можно фильтровать результат запроса. В SQL Server 2005 при использовании функции sys.dm_db index_physical_stats () возвращается фрагментированная информация. Запрос по всем столбцам, которые эта функция может вернуть, позволяет получить намного больше информации, чем дает DBCC SHOWCONTIG. Однако из-за того, что данные возвращаются через функцию типа table-valued function (TVF), количество возвращаемых столбцов и строк можно ограничить.

Функция sys.dm_db_index_physical_stats () принимает пять параметров, и все они имеют значения по умолчанию. Можно выставить все параметры по умолчанию и не накладывать фильтр на столбцы и строки, например:

SELECT * FROM sys.dm_db_index_physical_stats
(NULL, NULL, NULL, NULL, NULL)

Тогда функция возвращает 20 столбцов с данными для (почти) каждого уровня для каждого индекса по каждой таблице каждого раздела в каждой базе данных текущего экземпляра SQL Server. Когда я запустила эту команду на своем небольшом экземпляре, который включает только базы AdventureWorks, Pubs и Northwind (плюс системные базы данных), она вернула более 390 строк. Не буду здесь представлять весь вывод в 390 строк и 20 столбцов, а предлагаю читателям выполнить команду самостоятельно.

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