Организация индексов в SQL Server 2005 - Управление возвращаемыми данными

Управление возвращаемыми данными

Первый параметр — это идентификатор 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 выполняет все большее количество работы.