Collation에 따른 실행계획 변경과 성능 문제

Collation에 따른 실행계획 변경과 성능 문제

 

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

 

SQL Server에서 Collate는 데이터베이스 정의 또는 열 정의에 적용하여 데이터를 정렬을 정의하거나 문자열 식에 적용하여 데이터 정렬 캐스트를 정의한다.

   

Collate에 대한 수준은 데이터베이스를 생성하거나 변경, 테이블 생성 또는 컬럼 변경 작업을 할때 지정 할 수 있으며 char, varchar, text, nchar, nvarchar, ntext 데이터 형식에 대해서만 적용 할 수 있다.

 

대부분 기본값을 사용하면 Windows의 기본 값 또는 SQL Server 설치 시 설정된 기본값으로 데이터베이스 또는 테이블이 생성 된다. 따라서 국가에(또는 사용자) 따라 기본 Collation이 다를 수 있다.

 

이러한 환경에서 Collation에 따른 여러 가지 이슈가 있다. 예를 들면 ALTER DATABASE의 실패 같은 경우이다.

• Collation에 따른 ALTER DATABASE 실패 : http://sqlmvp.kr/140194406833
• Collation에 따른 DMV 실행 오류 : http://sqlmvp.kr/140185365035

 

이번 시간에는 Collation에 따른 쿼리 실행 계획의 변화와 이로 인한 쿼리의 성능 저하 사례를 살펴본다. 이 포스트는 SQL Server Premier Field Engineer Blog에 게시된 내용으로 필자가 읽고 이해한 내용을 정리하였으며 번역의 오류나 기술적 오류가 있음을 알려둔다. 자세한 내용을 원문을 참고 하길 바란다.

 

현재 데이터베이스의 정렬을 확인 하여보자. 스크립트를 이용한 방법과 SSMS를 이용한 방법이 있다.

 

Select DATABASEPROPERTYEX('SW_TEST','COLLATION')

 

다음 SSMS 및 스크립트를 통해 오브젝트의 컬럼에 따른 Collation 속성을 확인 할 수 있다.

 

 

create table Collate_Test(ID Int, Name nvarchar(50)) go   select object_name(object_id) as ObjectName,name As ColName,collation_name from sys.columns where object_id = object_id('Collate_Test')

 

 

 

Collation을 확인 하는 방법에 대해서 알아 보았다. 이제 Collation이 실제 쿼리에 어떤 영향을 주는지 알아보자.

 

다음 스크립트를 실행하여 테스트 환경을 구성한다.

set nocount on   create table testcollate( myid int identity, myname varchar(810)) go   insert into testcollate values(replicate('a',800)) go 10000   insert into testcollate values('KSW') go   create index myind on testcollate(myname) go

 

 

실행 계획 보기를 활성화하고 다음 조건에 따라 데이터를 검색 한다.

set statistics io on go   select myname from testcollate where myname = 'KSW'

 

 

테이블 'testcollate'. 검색 수 1, 논리적 읽기 수 5, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

 

다음은 다른 Collation을 사용하거나 검색 조건에 다른 리터럴 값을 사용하였을 실행 계획이 변화하는 것을 확인 할 수 있다.

 

테이블 'testcollate'. 검색 수 1, 논리적 읽기 수 1240, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

이처럼 Collation에 따라 실행 계획이 다르게 해석되며 결국에는 성능 문제로까지 발전할 수 있다. 대부분의 사용자에게서는 발생하지 않을 수도 있지만 글로벌 서비스의 경우 한국에서 만든 스키마를 해외에 적용하였을 때 해당 국가와 한국의 Collation이 다르거나 또는 DBA에게 부여된 기본Collation이 동일하지 않은 스키마로 생성하거나 변경하였을 경우 발생 할 가능성이 높다.

 

동일한 데이터와 동일한 쿼리가 어디에서나 동일한 실행계획과 성능을 보여주지 않는 다는 것을 기억하자.

 

 

[참고자료]

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/02/04/sql-collation-and-related-performance-impact-viewing-collation-in-query-plans.aspx

 

 

 

2013-10-24 / 강성욱 / http://sqlmvp.kr