인덱스 환경에 따라 인덱스페이지의 Uniquifier 포함여부에
대해 확인하는 테스트를 해보겠습니다.
(예전 지인의 아티클을 보고 테스트했던 내용입니다.)
DROP DATABASE Internal
GO
CREATE DATABASE Internal
GO
USE Internal
GO
/* 추적 설정 */
DBCC TRACEON (3604)
-- DBCC TRACEON (2520) -- sql server 2000
DBCC TRACEON (2588) -- sql server 2008
DBCC HELP('?')
DBCC HELP('page')
DBCC HELP('ind')
Syntax:
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
dbcc ind ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )
/* 테스트 테이블을 생성합니다. */
IF OBJECT_ID('TBL_Temp') IS NOT NULL
DROP TABLE TBL_Temp
GO
CREATE TABLE TBL_Temp(
Col1 BIGINT NOT NULL /* 중복 */
,Col2 BIGINT /* 중복 */
,Col3 BIGINT /* 고유 */
,Col4 CHAR(5000))
GO
/* 테스트 데이터를 입력합니다. */
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @i INT
SET @i = 1
SET @i = 1
WHILE(1=1)
BEGIN
INSERT INTO TBL_Temp VALUES(@i%20000,@i%20000,@i,REPLICATE('A',100))
IF(@i>= 40000) BREAK;
SET @i = @i + 1
END
COMMIT TRANSACTION
--ROLLBACK TRANSACTION
SET NOCOUNT OFF
GO
/*
HEAP 페이지 보기
DBCC IND 실행 후 IndexID가 0 번 인덱스는 HEAP 페이지.
IAMFID가 NULL인경우 자기자신이 IAM 페이지. */
DBCC IND ('Internal','TBL_Temp',0) WITH TABLERESULTS
GO
DBCC PAGE ('Internal',1,77,3) WITH TABLERESULTS
GO
IAM페이지에서 할당맵페이지들에 대한 페이지정보와
힙페이지주소(인덱스페이지처럼 m_prevPage,m_nextPage 정보가 없어, Field필드의 Slot0~ 으로 확인가능합니다.),
할당 Extent정보등을 확인할수 있습니다.
/*
CLUSTERED INDEX 를 생성합니다.
TBL_Temp 테이블에 중복가능한 컬럼에 클러스터 인덱스를 만들면 유니크성을 보장하기 위해 키를 하나 더 만듭니다.
*/
SELECT
Col1,COUNT(*) Cnt FROM TBL_Temp
GROUP BY Col1
ORDER BY 1
GO
CREATE CLUSTERED INDEX CIX_TBL_Temp_Col1 ON TBL_Temp(Col1)
GO
DBCC IND ('Internal','TBL_Temp',1) /* 결과값중 Indexlevel을 보고 숫자가 가장 높은것이 Root 그 아래가 Non Leaf 0이 Leaf 입니다.*/
DBCC PAGE ('Internal',1,40320,3) WITH TABLERESULTS
DBCC PAGE ('Internal',1,40319,3) WITH TABLERESULTS
/* 확인해 보면 CLUSTERED INDEX 가 Unique 하지 못하면 Non-Leaf Page, Leaf Page 모두 Unique 성을 보장하기 위한 Uniquifier 가 존재함을 알 수 있습니다.*/
/* NONCLUSTERED INDEX 관찰 CIX중복 CIX중복 */
CREATE NONCLUSTERED INDEX NIX_TBL_Temp_Col2 ON TBL_Temp(Col2)
DBCC IND ('Internal','TBL_Temp',3)
DBCC PAGE ('Internal',1,82056,3) WITH TABLERESULTS
/* NON-Leaf Page 에도 클러스터 인덱스 + Uniquifier 키로 구성되어 있습니다. */
/* NONCLUSTERED INDEX 에 대한 Indid 확인 */
SELECT * FROM sysindexes WHERE id = OBJECT_ID('TBL_Temp')
/* NONCLUSTERED INDEX 관찰 CIX중복 NIX유니크 */
CREATE UNIQUE NONCLUSTERED INDEX NIX_TBL_Temp_Col3 ON TBL_Temp(Col3)
GO
DBCC IND ('Internal','TBL_Temp',4)
DBCC PAGE ('Internal',1,448,3)
-- Non-Leaf
DBCC PAGE ('Internal',1,521,3)
-- Leaf 키 + 클러스터키 + uniquifier 로 이루어져 있음
SELECT * FROM sysindexes WHERE id = OBJECT_ID('TBL_Temp')
GO
클러스터형 인덱스가 고유한 인덱스가 아니면 SQL Server 2005에서는 uniqueifier라는 내부적으로 생성된 값을 추가하여
중복 키를 고유 키로 변경합니다. 비클러스터형 인덱스에서 사용하기 위해 클러스터형 키를 고유한 키로 바꿔야 할 때만
이 값이 추가돼는것 같습니다. SQL Server는 비클러스터형 인덱스의 리프 행에 저장된 클러스터형 인덱스 키로 클러스터형 인덱스를
찾아 데이터 행을 검색하기 때문입니다.
EXEC sp_AllocationMetadata 'TBL_Temp'
GO
IF OBJECT_ID ('sp_AllocationMetadata') IS NOT NULL
DROP PROCEDURE sp_AllocationMetadata;
GO
CREATE PROCEDURE sp_AllocationMetadata
(
@object VARCHAR (128) = NULL
)
AS
/*
DECLARE @object VARCHAR(128)
SET @object = ''
*/
SELECT
OBJECT_NAME (sp.object_id) AS [Object Name],
sp.index_id AS [Index ID],
sa.allocation_unit_id AS [Alloc Unit ID],
sa.type_desc AS [Alloc Unit Type],
'(' + CONVERT (VARCHAR (6),
CONVERT (INT, SUBSTRING (sa.first_page, 6, 1) +
SUBSTRING (sa.first_page, 5, 1))) +
':' + CONVERT (VARCHAR (20),
CONVERT (INT, SUBSTRING (sa.first_page, 4, 1) +
SUBSTRING (sa.first_page, 3, 1) +
SUBSTRING (sa.first_page, 2, 1) +
SUBSTRING (sa.first_page, 1, 1))) +
')' AS [First Page],
'(' + CONVERT (VARCHAR (6),
CONVERT (INT,
SUBSTRING (sa.root_page, 6, 1) +
SUBSTRING (sa.root_page, 5, 1))) +
':' + CONVERT (VARCHAR (20),
CONVERT (INT,
SUBSTRING (sa.root_page, 4, 1) +
SUBSTRING (sa.root_page, 3, 1) +
SUBSTRING (sa.root_page, 2, 1) +
SUBSTRING (sa.root_page, 1, 1))) +
')' AS [Root Page],
'(' + CONVERT (VARCHAR (6),
CONVERT (INT,
SUBSTRING (sa.first_iam_page, 6, 1) +
SUBSTRING (sa.first_iam_page, 5, 1))) +
':' + CONVERT (VARCHAR (20),
CONVERT (INT,
SUBSTRING (sa.first_iam_page, 4, 1) +
SUBSTRING (sa.first_iam_page, 3, 1) +
SUBSTRING (sa.first_iam_page, 2, 1) +
SUBSTRING (sa.first_iam_page, 1, 1))) +
')' AS [First IAM Page]
FROM
sys.system_internals_allocation_units AS sa,
sys.partitions AS sp
WHERE
sa.container_id = sp.partition_id
AND sp.object_id =
(CASE WHEN (@object IS NULL)
THEN sp.object_id
ELSE OBJECT_ID (@object)
END);
GO