아티클
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 카탈로그 뷰에 있습니다. XML 인덱스 데이터를 유지하기 위해 SQL Server에서 만든 내부 테이블 Ti에 대한 메타데이터는 sys.internal_tables 뷰에 있습니다.

내부 테이블 Ti와 사용자 테이블 T 간의 관계를 찾으려면 sys.internal_tables 뷰의 parent_id 열을 sys.tables 뷰의 object_id 열에 조인할 수 있습니다. 내부 테이블 Ti와 XML 인덱스 Xp 간의 관계를 찾으려면 sys.internal_tables의 parent_id 및 parent_minor_id 열을 sys.xml_indexes의 object_id 및 index_id 열에 조인할 수 있습니다. 아래의 예 7을 참조하십시오.

공간 인덱스 메타데이터 보기

공간 인덱스의 메타데이터는 대부분 XML 인덱스의 메타데이터와 동일합니다. 차이점은 공간 인덱스는 sys.xml_indexes보다 sys.spatial_indexes를 사용하고 공간 인덱스의 공간 매개 변수를 보려면 sys.spatial_index_tessellations를 사용해야 한다는 점입니다.

다음 그림에서는 공간 인덱스에 대한 내부 테이블의 메타데이터 구조를 보여 줍니다.

공간 인덱스 카탈로그 뷰의 다이어그램

그림에 표시된 카탈로그 뷰 관계를 이해하기 위해 공간 인덱스 Si가 T 테이블에 생성된다고 가정합니다. 테이블에 대한 메타데이터는 sys.tables 카탈로그 뷰에 있고 공간 인덱스에 대한 메타데이터는 sys.spatial_indexes 카탈로그 뷰와 sys.spatial_index_tessellations 카탈로그 뷰에 있습니다. 공간 인덱스 데이터를 유지하기 위해 SQL Server에서 만든 내부 테이블 Ti에 대한 메타데이터는 sys.internal_tables 뷰에 있습니다.

내부 테이블 Ti와 사용자 테이블 T 간의 관계를 찾으려면 sys.internal_tables 뷰의 parent_id 열을 sys.tables 뷰의 object_id 열에 조인할 수 있습니다. 내부 테이블 Ti와 공간 인덱스 Si 간의 관계를 찾으려면 sys.internal_tables의 parent_id 및 parent_minor_id 열을 sys.spatial_indexes의 object_id 및 index_id 열에 조인할 수 있습니다. 자세한 내용은 이 항목의 뒷부분에 나오는 예 12를 참조하십시오.

Service Broker 메타데이터 보기

다음 그림에서는 Service Broker 큐에 대한 내부 테이블의 메타데이터 구조를 보여 줍니다. Service Broker 메시지, 쿼리 알림 및 이벤트 알림은 Service Broker 큐를 사용합니다. 또한 Service Broker 기능은 내부 테이블을 사용하여 모든 데이터베이스의 모든 Service Broker 서비스에 대한 정보를 저장합니다. 이 내부 테이블은 tempdb 시스템 데이터베이스에 있습니다.

Service Broker 카탈로그 뷰의 다이어그램

쿼리 알림 메타데이터 보기

다음 그림에서는 쿼리 알림 구독에 대한 내부 테이블의 메타데이터 구조를 보여 줍니다. 내부 테이블은 쿼리 알림 구독의 매개 변수를 저장하는 데 사용됩니다.

쿼리 알림 카탈로그 뷰의 다이어그램

내부 테이블 저장소

내부 테이블은 부모 엔터티와 동일한 파일 그룹에 저장됩니다. 아래의 예 6에 표시된 카탈로그 쿼리를 사용하여 내부 테이블에서 행 내부, 행 외부 및 LOB(Large Object) 데이터에 사용되는 페이지 수를 반환할 수 있습니다.

sp_spaceused 시스템 프로시저를 사용하여 내부 테이블에 대한 공간 사용 데이터를 반환할 수 있습니다. sp_spaceused는 다음 방식으로 내부 테이블 공간을 보고합니다.

큐 이름을 지정하면 큐와 연결된 기본 내부 테이블을 참조하여 해당 저장소 사용을 보고합니다.
XML 인덱스, 공간 인덱스 및 전체 텍스트 인덱스의 내부 테이블에서 사용하는 페이지는 index_size 열에 포함됩니다. 테이블 또는 인덱싱된 뷰 이름을 지정하면 해당 개체의 XML 인덱스, 공간 인덱스 및 전체 텍스트 인덱스에 대한 페이지가 reserved 및 index_size 열에 포함됩니다.

예

다음 예에서는 카탈로그 뷰를 사용하여 내부 테이블 메타데이터를 쿼리하는 방법을 보여 줍니다.

1. sys.objects 카탈로그 뷰에서 열을 상속하는 내부 테이블 표시

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

2. 모든 내부 테이블 메타데이터 반환(sys.objects에서 상속된 메타데이터 포함)

SELECT * FROM sys.internal_tables;

3. 내부 테이블 열과 열 데이터 형식 반환

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;

4. 내부 테이블 인덱스 반환

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;

5. 내부 테이블 통계 반환

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;

6. 내부 테이블 파티션 및 할당 단위 정보 반환

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;

7. 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

8. 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

9. 모든 Service Broker 서비스에 대한 내부 테이블 메타데이터 반환

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

10. 쿼리 알림 구독에 대한 내부 테이블 메타데이터 반환

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';

11. 공간 인덱스에 대한 내부 테이블 메타데이터 반환

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

참고 항목

개념

다음을 통해 공유