DBCC SHOWCONTIG заменен на нового игрока
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 столбцов, а предлагаю читателям выполнить команду самостоятельно.
Рассмотрим эти пять параметров, которые можно передавать с помощью данной функции. Заодно обращаю внимание читателей на потенциальные ошибки при использовании параметров. В следующий раз я объясню некоторые столбцы вывода, значения которых могут быть не так очевидны.
Управление возвращаемыми данными
Первый параметр — это идентификатор ID базы данных, который должен быть указан как число. Если указать NULL, что является значением по умолчанию, функция вернет информацию обо всех базах данных. Если ID не определено, SQL Server будет игнорировать следующие три параметра и предполагает, что они все — NULL.
Второй параметр — это object_id, который также должен быть задан числом, а не именем. Здесь то же самое: если выставить NULL, то функция предоставит информацию обо всех объектах, и SQL Server назначит следующие два параметра, index _id и partition_id, также по умолчанию, что означает включение всех индексов и всех разделов. Раздел — это новая особенность SQL Server 2005. Чтобы упростить получение ID базы данных и ID объекта, можно задействовать функции db_id () и object_id () и передавать имя базы данных или объекта как параметр.
При использовании этих встроенных функций следует соблюдать осторожность. Если передать неправильное имя или просто сделать в имени орфографическую ошибку, никакого сообщения об ошибке не появится, а возвращаемым значением будет NULL. Поскольку NULL — это допустимый параметр, SQL Server решит, что это как раз то, что требовалось. Предположим, например, что я хочу видеть всю информацию, о которой говорилось выше, на этот раз для базы данных, и я наберу с ошибкой:
SELECT * FROM sys.dm_db_index_physical_stats (db_id ?AdventureWords?, NULL, NULL, NULL, NULL)
Такой базы данных, как AdventureWords нет, поэтому функция db_id () возвратит NULL и результат будет тот же самый, как будто я вызываю функцию со значениями всех параметров как NULL. И система не выдаст никакого сообщения об ошибке или предупреждения.
По количеству возвращаемых строк можно догадаться, что допущена ошибка, но если нет никаких предположений о том, чего следует ожидать, это не может быть очевидно сразу. В документации BOL предлагается, что пользователь может избежать этой проблемы, помещая ID в переменные и делая проверку значений переменных, как показано в листинге, перед запуском функции sys.dm_db_index_physical_stats ().
Более коварная проблема, которую я обнаружила, проводя тестирование по этому столбцу, состоит в том, что SQL Server вызывает функцию object_id () в контексте текущей базы данных до того, как вызывается любой запрос к динамически управляемой функции (dynamic management function, DMF). Я обращалась к базе данных AdventureWorks, а хотела получить информацию из таблицы базы данных Pubs. Поэтому я сделала это следующим образом:
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N?pubs?), OBJECT_ID(N?dbo.authors?), null, null, null);
Поскольку таблицы dbo.authors в моей текущей базе данных не существует, SQL Server будет возвращать в object_id значение NULL, и я буду получать информацию обо всех объектах в базе данных Pubs. Но если таблица dbo.authors в базе AdventureWorks есть, SQL Server будет использовать ID этой таблицы и пытаться получить данные из базы данных Pubs. Поэтому я получу либо сообщение о том, что объекта с этим ID в базе данных нет, либо есть данные из другой таблицы, а не из той, которая мне нужна. Эта задача может быть трудной в плане поиска ошибок, если, конечно, пользователь понял, что проблема существует.
Единственное решение — полностью квалифицировать имя таблицы в запросе к TVF или, как в представленном выше коде, задействовать переменные для получения ID и вполне определенного названия таблицы. Мне кажется несколько странным, что требуется полностью квалифицировать объект с именем базы данных, в то время как параметр уже определяет имя базы данных. Но что есть, то есть. Если будет создаваться упаковщик процедур для вызова функции sys.dm_db_index_physical_stats (), можно сцепить имя базы данных с именем объекта перед поиском ID объекта и избежать проблемы. Результат работы такой функции скрыт, так что возникает желание написать процедуру, которая обращается к этой функции и возвращает информацию немного более дружественным способом.
Третий параметр позволяет устанавливать ID индекса для указанной таблицы, и опять значение по умолчанию NULL говорит о том, что пользователь получит на выходе все индексы. Четвертый параметр указывает на номер раздела, и NULL снова означает, что нужна информация по всем разделам. Пятый и последний параметр — это единственный параметр, для которого заданное по умолчанию значение NULL не предполагает возвращения максимума информации. Последний параметр указывает режим выборки, который SQL Server будет использовать при получении данных. Принимаемые параметры следующие: DEFAULT, NULL, LIMITED, SAMPLED или DETAILED. Установка значения NULL по умолчанию соответствует режиму LIMITED.
Заданный режим определяет уровень сканирования, которое выполняет SQL Server для получения информации, необходимой данной функции. При вызове функции SQL Server просматривает цепочку страниц для предварительно заданного раздела таблицы или индекса. В отличие от DBCC SHOWCONTIG, которой обычно необходима разделяемая (типа S) блокировка таблицы, процедуре sys.dm _db_index_physical_stats () требуется разделяемая блокировка намерения (IS), которая совместима с большинством других блокировок.
Режим LIMITED — самый быстрый и сканирует наименьшее число страниц. Он просматривает все страницы кучи, но только на уровне родительских страниц, которые по индексу располагаются выше уровня листьев. Режим SAMPLED возвращает статистику, основанную на одном проценте всех страниц индекса или кучи. Однако если таблица небольшая, SQL Server преобразует режим SAMPLED в режим DETAILED, и для задач этой функции «небольшая» означает таблица менее чем в 10 000 страниц. Режим DETAILED сканирует все страницы и возвращает всю статистику. Так что можно заметить, что режимы замедляются по мере того, как SQL Server выполняет все большее количество работы.
Установим себе предел
Как я уже упоминала, функция sys.dm_db_index_physical_stats () может возвращать большее количество строк, если пользователь вводит все заданные по умолчанию параметры. Но даже осторожное использование доступных параметров для подмножества таблиц или индексов может вернуть больше, чем требуется, количество данных. Поскольку sys.dm_db_index_physical_stats () — это функция типа TVF, можно накладывать на возвращаемые данные собственные фильтры. Например, пользователь может видеть результаты только для некластеризованных индексов. Пользуясь доступными параметрами, пользователь выбирает, нужно ему видеть все индексы или только один. Если в третьем параметре используется NULL для указания всех индексов, можно добавить фильтр в операторе WHERE для выбора только тех строк, где значение index_id находится между значениями 2 и 250. Или можно просматривать только те строки, которые указывают на высокую степень фрагментации.
Кэлен Дилани - Независимый консультант и инструктор по SQL Server. Имеет сертификаты MCT и MCSE. Автор книги Inside SQL Server 2000 (Microsoft Press). kalen@sqlmag.com
Листинг. Проверка идентификаторов базы данных и объекта до вызова функции
DECLARE @db_id smallint; DECLARE @object_id int; SET @db_id = DB_ID(N?AdventureWorks?); SET @object_id = OBJECT_ID(N?AdventureWorks .Person.Address?); IF @db_id IS NULL BEGIN; PRINT N?Invalid database?; END; ELSE IF @object_id IS NULL BEGIN; PRINT N?Invalid object?; END; SELECT * FROM sys.dm_db_index_physical_stats (@db_id, @object_id, NULL, NULL, NULL)