데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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로 업데이트 후 수행 하는 것이 좋다.

 

 

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

 

 

[참고자료]

 강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1582
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1932
1828 Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1942
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2054
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2222
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2512
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1717
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1543
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1872
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 820
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1608
» VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 4579
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1077
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1273
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1060
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1191
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1331
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1233
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1638





XE Login