Статья
04/01/2012

Внутренние таблицы

SQL Server автоматически создает внутренние таблицы для поддержки следующих функциональных возможностей:

первичных XML-индексов;
пространственных индексов;
компонента Service Broker;
уведомлений о запросах;
отслеживания изменений.

Внутренние таблицы являются побочным результатом других действий пользователя. Например, при создании первичного XML-индекса SQL Server автоматически создает внутреннюю таблицу для сохранения разобранных данных XML-документа. Внутренние таблицы создаются в схеме sys любой базы данных и имеют уникальные имена, сформированные системой, указывающие на их функцию, например xml_index_nodes_2021582240_32001 или queue_messages_1977058079.

Данные внутренних таблиц недоступны для пользователей, а их схема является жесткой и неизменной. Невозможно ссылаться на имена внутренних таблиц в инструкциях языка Transact-SQL. Например, нельзя выполнить инструкцию SELECT * FROM <sys.internal_table_name>. Однако можно обращаться с запросами к представлениям каталогов для просмотра метаданных внутренних таблиц.

Просмотр метаданных внутренних таблиц

Метаданные, связанные с внутренними таблицами, можно просмотреть с помощью представления каталога sys.internal_tables. С помощью него можно просмотреть схему внутренних таблиц. Так как внутренние таблицы во многих характеристиках схожи с пользовательскими, представление sys.internal_tables наследует столбцы из представления каталога sys.objects и имеет тип «IT». Как и для пользовательских таблиц, метаданные столбцов внутренних таблиц видимы в представлении каталога sys.columns, а метаданные созданных системой индексов и статистика по внутренним таблицам видимы в представлениях каталогов sys.indexes и sys.stats.

Также можно получить информацию о выделении пространства и его использовании путем объединения метаданных с другими представлениями каталогов. См. подраздел «Хранение внутренних таблиц» далее в этом разделе.

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

Диаграмма представлений каталога для внутренних таблиц

Разрешения на просмотр метаданных внутренних таблиц

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

разрешение CONTROL SERVER;
разрешение CONTROL в базе данных;
членство в группе db_owner или sysadmin.
Пользователь, который может просматривать родительскую сущность (XML-индекс, очередь или пространственный индекс), может просматривать внутреннюю таблицу для этой сущности.

Просмотр метаданных XML-индекса

На следующей иллюстрации показана структура метаданных для внутренней таблицы XML-индекса.

Диаграмма представлений каталога XML-индексов

Чтобы понять взаимосвязь представлений каталогов, показанную на иллюстрации, представьте, что первичный XML-индекс Xp создан для таблицы T. Метаданные для таблицы находятся в представлении каталога sys.tables, а метаданные для XML-индекса находятся в представлении каталога sys.xml_indexes. Метаданные для внутренней таблицы Ti создаются SQL Server для сохранения данных XML-индекса в представлении sys.internal_tables.

Чтобы выяснить связь между внутренней таблицей Ti и пользовательской таблицей T, можно объединить столбец parent_id представления sys.internal_tables со столбцом object_id представления sys.tables. Чтобы выяснить связь между внутренней таблицей Ti и XML-индексом Xp, можно объединить столбцы parent_id и parent_minor_id представления sys.internal_tables со столбцами object_id и index_id представления sys.xml_indexes. См. пример Ж ниже.

Просмотр метаданных пространственного индекса

Метаданные пространственных индексов очень похожи на метаданные XML-индексов. Разница в том, что пространственные индексы используют представление каталога sys.spatial_indexes вместо sys.xml_indexes, а для просмотра пространственных параметров пространственного индекса следует использовать представление каталога sys.spatial_index_tessellations.

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

Диаграмма представлений каталога пространственных индексов

Чтобы понять взаимосвязь представлений каталогов, показанную на иллюстрации, представьте, что пространственный индекс Si создан для таблицы T. Метаданные для таблицы находятся в представлении каталога sys.tables, а метаданные для пространственного индекса находятся в представлениях каталога sys.spatial_indexes и sys.spatial_index_tessellations. Метаданные для внутренней таблицы Ti создаются SQL Server для сохранения данных пространственного индекса в представлении sys.internal_tables.

Чтобы выяснить связь между внутренней таблицей Ti и пользовательской таблицей T, можно объединить столбец parent_id представления sys.internal_tables со столбцом object_id представления sys.tables. Чтобы выяснить связь между внутренней таблицей Ti и пространственным индексом Si, можно объединить столбцы parent_id и parent_minor_id представления sys.internal_tables со столбцами object_id и index_id представления sys.spatial_indexes. Дополнительные сведения см. в примере М далее в этом разделе.

Просмотр метаданных компонента Service Broker

На следующей иллюстрации показана структура метаданных для внутренней таблицы очереди компонента Service Broker. Сообщения, уведомления о запросах и о событиях компонента Service Broker используют очереди компонента Service Broker. Кроме того, компонент Service Broker также использует внутреннюю таблицу для хранения информации обо всех службах компонента Service Broker во всех базах данных. Эта внутренняя таблица находится в системной базе данных tempdb.

Диаграмма представлений каталога компонента Service Broker

Просмотр метаданных уведомлений о запросах

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

Диаграмма представлений каталога для уведомлений о запросах

Хранение внутренних таблиц

Внутренние таблицы размещаются в той же файловой группе, что и родительская сущность. С помощью запроса к каталогу, проиллюстрированного далее в примере Е, можно узнать количество страниц, занимаемых внутренними таблицами под хранение данных «в строке», «вне строки» и данных больших объектов (LOB).

Для определения объема, занимаемого внутренними таблицами, можно использовать системную процедуру sp_spaceused. Системная процедура sp_spaceused выдает данные об объеме, занимаемом внутренними таблицами, следующими способами:

При указании имени запроса базовая внутренняя таблица, связанная с запросом, находится по ссылке и сообщается объем пространства, занятого ей.
Страницы, используемые внутренними таблицами XML-индексов, пространственных индексов и полнотекстовых индексов, включаются в столбец index_size. При указании имени таблицы или индексированного представления страницы, используемые XML-индексами, пространственными индексами и полнотекстовыми индексами этого объекта, включаются в столбцы reserved и index_size.

Примеры

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

А. Просмотр внутренних таблиц, наследующих столбцы от представления каталога sys.objects

SELECT * FROM sys.objects WHERE type = 'IT';

Б. Возвращаются все метаданные внутренней таблицы (в том числе те, которые наследуются от представления каталога sys.objects)

SELECT * FROM sys.internal_tables;

В. Возвращаются столбцы и типы данных столбцов внутренней таблицы

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name
    ,itab.name AS internal_table_name
    ,typ.name AS column_data_type 
    ,col.*
FROM sys.internal_tables AS itab
JOIN sys.columns AS col ON itab.object_id = col.object_id
JOIN sys.types AS typ ON typ.user_type_id = col.user_type_id
ORDER BY itab.name, col.column_id;

Г. Возвращаются индексы внутренней таблицы

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name
    , itab.name AS internal_table_name
    , idx.*
FROM sys.internal_tables AS itab
JOIN sys.indexes AS idx ON itab.object_id = idx.object_id
ORDER BY itab.name, idx.index_id;

Д. Возвращается статистика внутренней таблицы

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name
    ,itab.name AS internal_table_name
    , s.*
FROM sys.internal_tables AS itab
JOIN sys.stats AS s ON itab.object_id = s.object_id
ORDER BY itab.name, s.stats_id;

Е. Возвращается информация о секциях и единицах распределения внутренней таблицы

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name
    ,itab.name AS internal_table_name
    ,idx.name AS heap_or_index_name
    ,p.*
    ,au.*
FROM sys.internal_tables AS itab
JOIN sys.indexes AS idx
--     JOIN to the heap or the clustered index
    ON itab.object_id = idx.object_id AND idx.index_id IN (0,1)
JOIN   sys.partitions AS p 
    ON p.object_id = idx.object_id AND p.index_id = idx.index_id
JOIN   sys.allocation_units AS au
--     IN_ROW_DATA (type 1) and ROW_OVERFLOW_DATA (type 3) => JOIN to partition's Hobt
--     else LOB_DATA (type 2) => JOIN to the partition ID itself.
ON au.container_id =  
    CASE au.type 
        WHEN 2 THEN p.partition_id 
        ELSE p.hobt_id 
    END
ORDER BY itab.name, idx.index_id;

Ж. Возвращаются метаданные внутренней таблицы для XML-индексов

SELECT t.name AS parent_table
    ,t.object_id AS parent_table_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
    ,xi.name AS primary_XML_index_name
    ,xi.index_id as primary_XML_index_id
FROM sys.internal_tables AS it
JOIN sys.tables AS t 
    ON it.parent_id = t.object_id
JOIN sys.xml_indexes AS xi 
    ON it.parent_id = xi.object_id
    AND it.parent_minor_id  = xi.index_id
WHERE it.internal_type_desc = 'XML_INDEX_NODES';
GO

З. Возвращаются метаданные внутренней таблицы для очередей компонента Service Broker

SELECT q.name AS queue_name
    ,q.object_id AS queue_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.service_queues  AS  q ON it.parent_id = q.object_id
WHERE it.internal_type_desc = 'QUEUE_MESSAGES';
GO

И. Возвращаются метаданные внутренней таблицы для всех служб компонента Service Broker

SELECT * 
FROM tempdb.sys.internal_tables 
WHERE internal_type_desc = 'SERVICE_BROKER_MAP';
GO

К. Возвращаются метаданные внутренней таблицы для подписок на уведомления о запросах

SELECT qn.id AS query_subscription_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.dm_qn_subscriptions AS qn ON it.object_id = qn.object_id
WHERE it.internal_type_desc = 'QUERY_NOTIFICATION';

Л. Возвращаются метаданные внутренней таблицы для пространственных индексов

SELECT t.name AS parent_table
    ,t.object_id AS parent_table_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
    ,si.name AS spatial_index_name
    ,si.index_id as spatial_index_id
FROM sys.internal_tables AS it
JOIN sys.tables AS t 
    ON it.parent_id = t.object_id
JOIN sys.spatial_indexes AS si 
    ON it.parent_id = si.object_id
    AND it.parent_minor_id  = si.index_id
WHERE it.internal_type_desc = 'EXTENDED_INDEXES';
GO

См. также

Основные понятия

Индексы для столбцов типа данных xml

Очереди

Основные сведения уведомлений о событиях

Использование уведомлений запросов

Архитектура компонента Full-Text Search

Поделиться через