Новые метаданные SQL Server 2012

В одной из предыдущих статей я описывала сценарий, используемый для внутреннего хранилища (http://sqlmag.com/sql-server/iams-keeping-track-space-used). На этот раз я продолжу тему и расскажу о другой команде, с помощью которой можно взглянуть на внутреннее хранилище, а также новой функции метаданных SQL Server 2012, благодаря которой устраняется необходимость в использовании команды, описанной в предыдущей статье. Мы познакомились с картами распределения индекса Index Allocation Map (IAM) и узнали, что SQL Server использует IAM для отслеживания пространства, занятого объектами, в том числе пространства для данных больших объектов, а также превышающими размер страницы данными строки. Имеется недокументированная команда DBCC, которая показывает часть информации, отслеживаемой с помощью IAM. В частности, недокументированная команда DBCC IND сообщает все номера страниц, занятых таблицей или определенным индексом для таблицы. Далее будут даны разъяснения для каждого типа страницы. Ниже приводится образец, как может выглядеть информация, переданная из DBCC IND.

Команда DBCC IND - полезная команда в SQL Server 2008 и более ранних версиях. Ее недостаток в том, что хотя переданная информация выглядит табличной, к ней нельзя обращаться с запросами, как к табличным данным. Нельзя выбрать столбцы, которые требуется возвратить, изменить заголовок столбца, фильтровать строки или группировать по столбцам, например типа страницы. Возвращается больше столбцов, нежели показано, но меня ограничивает ширина страницы, поэтому было бы удобно иметь способ перечислить только интересующие нас столбцы. Такой способ есть в SQL Server 2012. Это недокументированное динамическое административное представление (DMV), в сущности, представляющее собой функцию, именуемую sys.dm_db_database_page_allocations. Эта функция возвращает одну строку для каждой страницы, точно так же, как DBCC IND. Но поскольку это DMV, то возвращаемые данные - табличные, и к ним можно обратиться с запросом, как к таблице. Например, приведенный ниже запрос возвращает все страницы большого объекта (LOB) для всех разделов (4-й параметр) индекса 1 (3-й параметр) для таблицы dbo.bigrows (2-й параметр) в базе данных AdventureWorks2012 (1-й параметр). 5-й параметр - LIMITED или DETAILED - указывает, сколько информации нужно возвращать. Приведенная ниже команда показывает все страницы большого объекта для всех разделов индекса 1 для таблицы dbo.bigrows в базе данных AdventureWorks2012.

SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID, FROM sys.dm_db_database_page_allocations(db_id('AdventureWorks2012'), object_id('dbo.bigrows'), 1, null, 'DETAILED') WHERE allocation_unit_type_desc = 'LOB_DATA';

Подробное описание входных и выходных данных этой функции остается за рамками данной статьи, но если у вас есть экземпляр SQL Server 2012, то приведенный выше пример содержит достаточно информации для начала. Отмечу пять значений page_type и page_type_desc, которые могут быть переданы, они представлены в таблице.

Часть информации даст очень многое тем, кто работал с командой DBCC IND в SQL Server 2008 и предшествующих версиях, поэтому полезно провести поиск в интернете (или прочитать мою книгу SQL Server 2008 Internals), чтобы поближе познакомиться с этой командой.

Рисунок. Образец информации, полученной от DBCC IND

При демонстрации новых возможностей мне часто задают вопрос: "Как получить ту же информацию обладателям старых версий?" Обычно приходится объяснять, что новые функции вводятся, чтобы побудить пользователей к обновлению, и многие новые функции не имеют аналогов в старых версиях. Но для этого нового DMV можно указать на нечто похожее в предшествующих версиях. Как уже отмечалось, команде DBCC IND свойственно ограничение - она не выдает табличного вывода, который можно фильтровать и суммировать. Но поскольку этот вывод очень полезен, мною был подготовлен скрипт, создающий представление sp_index_info для вывода DBCC IND, которое можно заполнить с использованием команды INSERT/EXEC. Назначение представлению имени, начинающегося с sp_, обеспечивает доступ к нему из любой базы данных. Сценарий, как и описание ситуации, в которой он может быть полезным, можно найти в блоге Geek City: Nonclustered Index Keys. DBCC IND и сценарий, в котором используется команда, не дают той информации, как новая функция sys.dm_db_database_page_allocations в SQL Server 2012, но это может стать хорошей отправной точкой. Получение доступа к новому DMV может послужить убедительным поводом для перехода к SQL Server 2012.