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에 따른 쿼리 실행 계획의 변화와 이로 인한 쿼리의 성능 저하 사례를 살펴본다. 이 포스트는 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

 



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

No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 528
1886 SQL Server Geography 및 Geometory 데이터 형식 jevida(강성욱) 2016.10.11 2524
1885 SQL Server를 이용한 신용카드 보안 -조직이 PCI DSS 준수를 달성 하기 위한 요건 jevida(강성욱) 2016.10.11 1395
1884 TDE 암호화 사용하기 jevida(강성욱) 2016.10.11 3539
1883 SQL Server 암호화 하기 jevida(강성욱) 2016.10.11 2791
1882 의도하지 않은 분산트랜잭션 사용 jevida(강성욱) 2016.10.11 902
1881 Affinity I/O Mask jevida(강성욱) 2016.10.11 1187
1880 LPE_BATCH 스핀락 jevida(강성욱) 2016.10.11 904
1879 SQL Server 17953 오류 jevida(강성욱) 2016.10.11 1032
1878 SQL 연결 18056 오류 jevida(강성욱) 2016.10.11 1385
1877 SQL 2008 R2 Sp1 적용과 9013 오류 - 포맷 섹터 크기에 따른 오류 jevida(강성욱) 2016.10.11 1920
1876 JDBC 로깅 파일 위치 설정 jevida(강성욱) 2016.10.11 982
1875 NUMA Node 메모리 블록 jevida(강성욱) 2016.10.11 1059
1874 AppDomain unloading 오류 로그 – CLR 오류 jevida(강성욱) 2016.10.11 1214
1873 SQL Server IO and Latch 설명 jevida(강성욱) 2016.10.11 3422
1872 마지막 백업 시간 및 DBCC 확인 [1] jevida(강성욱) 2016.10.08 1626
1871 DMV를 활용한 CPU 트러블슈팅 - Sys.dm_exec_query_stats, sys.dm_os_ring_buffers 활용 [1] jevida(강성욱) 2016.10.08 1955
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1509
» Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1703
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2345
1867 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1752





XE Login