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

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

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
1871 DMV를 활용한 CPU 트러블슈팅 - Sys.dm_exec_query_stats, sys.dm_os_ring_buffers 활용 [1] jevida(강성욱) 2016.10.08 1907
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1451
» Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1667
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2279
1867 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1710
1866 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 2871
1865 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2011
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1161
1863 SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 4051
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1112
1861 DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1348
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1446
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1290
1858 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1462
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2664
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2342
1855 MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 2018
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2019
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1842
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3441





XE Login