VARCHAR(MAX) and NTEXT 쿼리 성능

VARCHAR(MAX) and NTEXT 쿼리 성능

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

단일 컬럼에 크기가 큰 데이터(게시판 등)를 저장할 때 VARCHAR(MAX) 또는 NTEXT를 사용한다.

 

varchar(max), nvarchar(max), varbinary(max)은 최대 2^31-1 바이트(약2GB)의 데이터를 저장 할 수 있으며 varchar(n), nvarchar(n), varbinary(n) 동작과 유사하여 SQL Server에서 큰 문자, 유니코드 및 이진 데이터를 보다 효율적으로 저장하고 검색 할 수 있다.

.

varchar(max), nvarchar(max), varbinary(max) 형식을 사용하면 이전 버전의 SQL Server에서 제공하는 text, ntext 및 image 데이터 형식으로는 불가능한 방식으로 SQL Server에서 작업을 수행할 수 있다.

큰 값 데이터 형식	이전 버전의 LOB
Varchar(max)	Text
Nvarchar(max)	Ntext
Varbinary(max)	image

 

 

다음 내용은 CSS SQL Server Engineer 팀블로그에 게제된 내용으로 NTEXT의 컬럼을 NVARCHAR(MAX)로 변경 후 쿼리가 늦어지는 원인에 대한 설명을 한다. 필자가 원문을 읽고 이해한 내용을 바탕으로 정리 하였으며 번역의 오류나 기술적 오류가 있음을 인지 한다. 정확한 내용은 원문을 참고하길 바란다.

 

다음과 같이 3개의 컬럼(A = int, B = guid, C = NTEXT)으로 설계된 테이블이 있다. 데이터 페이지의 텍스트 포인터는 NETXT 데이터를 가지고 있으며 행 포인터 텍스트 체인을 가리키도록 최대 16바이트의 크기를 차지한다.

 

 

사용자가 select A, B from TBL_X 라는 쿼리를 실행 하면 SQL Server는 싱글 페이지를 읽는다. C컬럼의 경우 off-page 작업이기 때문에 C컬럼에 대한 데이터를 선택 하지 않는다.

 

다음 그림을 보면 NVARCHAR(MAX)로 인라인 컬럼 테이블을 사용 하였을 때 데이터가 인라인으로 이동 되어 TEXT 페이지는 더 이상 ROW 1 과 ROW2의 데이터가 존재 하지 않는다는 것을 볼 수 있다.

 

sp_tableoption 저장 프로시저의 large value types out of row 옵션을 OFF로 설정되어 있으면 큰 값 행 내부 저장 용량은 8000바이트로 제한되며 8000바이트보다 큰 데이터(ROW3)는 인라인으로 포함되지 않는다. ON으로 설정하면 행 내부에 16바이트의 루트가 저장된다.

 

 

select A, B from TBL_X 쿼리를 실행 하면 2개의 데이터 페이지 I/O가 발생한다. NVARCHAR(MAX) 데이터를 읽지 않더라도 페이지 내의 큰 값 형식은 인라인 데이터에서 압축 하지 않기 때문이다.

 

 

NTEXT 컬럼의 DROP 컬럼에 대해서 생각해 보자. DROP 컬럼을 실행 할 경우 우리는 곧 바로 데이터가 정리 될 것이라 생각하지만 실제로 그렇지 않다. 행이 수정 될 때 정리는 나중에 발생 한다.

 

해당 열을 삭제해도 해당 열의 디스크 공간은 회수 되지 않는다. 테이블의 행 크기가 제한에 근접하거나 제한을 초과하는 경우에는 삭제된 열의 디스크 공간을 회수해야 할 수도 있다. 이 때에는 ALTER INDEX 또는 클러스터형 인덱스를 생성하면 공간을 회수 할 수 있다.

 

테이블의 컬럼 수정 후 공간 회수를 위해 ALTER INDX 또는 클러스터형 인덱스를 생성하면 공유된 데이터 페이지의 체인을 정리하고 할당하는데는 싱글로 처리 하기 때문에 오랜 시간과 오버헤드가 발생 한다.

 

.

 

이럴 때는 해당 NTEXT컬럼을 DROP 하기 전 NULL로 업데이트한다. 업데이트로 텍스트 할당을 제거하면 병렬로 실행 할 수 있다. 그 다음 인덱스 작성을 하면 관계된 해당 열(NULL BIT )만 정리 된다.

 

DBCC CLEANTABLE 명령은 테이블 또는 인덱싱된 뷰의 삭제된 가변 길이의 열에서 공간을 반환한다. 하지만 이 명령은 싱글로 처리 되면 잠금 문제가 발생 할 수 있다. 따라서 NULL로 업데이트 후 수행 하는 것이 좋다.

• DBCC CLEANTABLE : http://msdn.microsoft.com/ko-kr/library/ms174418.aspx

 

 

데이터 모델 설계 시 형식에 대한 처리 방법 및 구조를 이해하고 설계하면 더 좋은 쿼리 성능 및 효율적인 데이터 저장을 할 수 있다.

 

 

[참고자료]

• http://blogs.msdn.com/b/psssql/archive/2012/12/03/how-it-works-gotcha-varchar-max-caused-my-queries-to-be-slower.aspx
• http://msdn.microsoft.com/ko-kr/library/ms178158(v=sql.105).aspx
• http://msdn.microsoft.com/ko-kr/library/ms174418.aspx

 

2013-07-19 / 강성욱 / http://sqlmvp.kr