top of page

UNIQUIFIER

CREATE TABLE UniquifierTest2 (Col1 INT, Col2 INT);

CREATE CLUSTERED INDEX idxClustered ON UniquifierTest2 (Col1);

CREATE NONCLUSTERED INDEX idxNonClustered ON UniquifierTest2 (Col2);

 

INSERT INTO UniquifierTest2 VALUES (1,1);

INSERT INTO UniquifierTest2 VALUES (2,2);

INSERT INTO UniquifierTest2 VALUES (3,3);

DBCC IND (test1, UniquifierTest2, -1);

IndexID가 0번: HEAP

1번 clustered index

2번 non clustered index

PageType이 10은 IAM, 2는 index page이므로

→ IndexID가 2이고 Page Type이 2인거는 352

index_id가 2번 ~ n번까지가 넌 클러스터 번호 (버전마다 max의 값은 다를 수 있다)

PageType
1 DATA 3 TEXT MIX PAGE
2 INDEX 4 TEXT TREE PAGE
7 SORT PAGE 13 BOOT PAGE
8 GAM 15 FILE HEADER PAGE
9 SGAM 16 DIFF MAP PAGE
10 IAM 17 ML MAP PAGE
11 PFS

DBCC IND (sample, UniquifierTest2, -1);

INSERT INTO UniquifierTest2 VALUES (1,1);

INSERT INTO UniquifierTest2 VALUES (1,1);

INSERT INTO UniquifierTest2 VALUES (1,1);

INSERT INTO UniquifierTest2 VALUES (1,1);

똑같은 데이터를 중복으로 넣고

DBCC PAGE(test1, 1, 352, 3); 를 돌리게 되면

uniquifier가 증가하는게 보임

Uniquifier 증가방법
1. 데이터를 추가하고

2. delete from UniquifierTest2 where Col1 =1 and Col2 = 2

결과는

uniquifier가 재정렬이 일어나지 않음.. 그냥 5번이 지워짐

3. INSERT INTO UniquifierTest2 VALUES (1,2) 하게되면

unique랑 unique 선언을 안한 인덱스의 max_record_size_in_bytes 차이점
--unique

SELECT index_level, page_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, * from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.UniquifierTest1'),1, NULL, 'DETAILED')

 

--unique 선언 안한거

SELECT index_level, page_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, * from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.UniquifierTest2'),1, NULL, 'DETAILED')

→ max_record_size_in_bytes가 다름
유니크 하지 않을 경우 유니크한거에 비해 4byte를 더 쓴다

(Basically, uniquifier value added to all rows with exception of the first row per unique value. Interestingly enough that even if uniquifier itself is 4 bytes the total overhead is 8 bytes. ) (참조: https://aboutsqlserver.com/2011/11/24/a-few-more-words-about-uniquifiers-and-uniqueness-of-the-clustered-index/)

** sys.dm_db_index_physical_stats: 인덱스의 크기 및 인덱스의 조각화 정도를 조회할 수 있는 함수

uniquifier는 왜 생기는가?
클러스터형 인덱스 구조의 테이블에서의 비클러스터형 인덱스가 행의 주소 값으로 클러스터형 인덱스 키를 사용하기 때문에 클러스터형 인덱스 키를 고유하게 만들기 위해서

인덱스 키가 고유한데 unique를 선언하지 않으면 각 인덱스 행마다 4byte가 낭비되고, 인덱스 키로 검색을 할 때 현재의 값이 고유한지를 판단하기 위해 다음 차례의 큰 값을 더 읽게 되는 불필요한 비용 추가 발생

uniquifier의 한계는?
auto-incrementing으로 증가하기 때문에 2,147,483,647까지는 괜찮은듯...? (참조:https://www.3chillies.co.uk/blog/2016/04/the-sql-uniquifier#:~:text=In%20a%20nutshell%2C%20if%20you,limit%20of%20an%20integer%3A%202%2C147%2C483%2C647.)

2,147,483,647를 넘기면 에러메세지를 보여줌 (참조:https://www.mssqltips.com/sqlservertip/2082/understanding-and-examining-the-uniquifier-in-sql-server/)

bottom of page