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

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

Collation에 따른 DMV 실행 오류

 

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

 

대부분 DBA들은 자신만의 스크립트를 준비하여 필요할 때 꺼내어 사용한다. 또는 DB관리 툴에서 자동화된 스크립트를 이용하여 SQL Server의 상태를 확인 하곤 한다. 물론 나 또한 항상 준비된 스크립트 DMV 명령을 통해 잘 사용하고 있다. 그런데 한가지 문제가 발생 했다. 특정 서버에서 작동하지 않는 것이다.

상황을 설명하자면 국내에서 잘 사용하던 스크립트가 있었다. 그래서 이 스크립트를 내가 관리하는 해외 고객님 서버에서 실행 하였더니 에러가 발생 하였다. 무엇이 문제 일까?

 

스크립트에 오류가 있었던 것일까? 원인은 Collation에 따른 데이터 정렬셋으로 인하여 DMV가 실행되지 않는 것이었다.

 

Collation은 데이터 정렬형식이다. 흔히들 언어형식 이라고 하지만 정확히는 언어가 아닌 데이터의 우선순위에 관한 규칙이다.

Collation 정보 : http://msdn.microsoft.com/ko-kr/library/ms184391.aspx

 

대부분의 DBA는 데이터 정렬셋에 대한 영향도가 테이블 또는 프로시저에 국한된다고 알고 있다. 하지만 시스템 테이블 및 DMV 등 모든 내부 객체에 적용된다.

 

다음 실습을 통하여 Collation에 따른 DMV 오류를 확인해 보자.

 

실습용 데이터베이스를 생성 한다. 이 때 Collation을 Latin1_General_BIN 으로 생성 하였다.

CREATE DATABASE [CaseSensitive]

COLLATE Latin1_General_BIN;

GO

 

USE [CaseSensitive];

GO

 

-- This won't work

SELECT table_name, table_type FROM information_schema.tables;

GO

-- This will

SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;

GO

use AdventureWorks2008R2

GO

 

-- This won't work

SELECT table_name, table_type FROM information_schema.tables;

GO

-- This will

SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;

GO

 

 

같은 스크립트를 실행 하였는데 데이터베이스의 정렬셋에 따라 작동이 되지 않는다.

 

다음의 경우도 살펴 보자. Sys.object의 테이블의 대소문자에 따라 동작 여부가 달라진다. 스키마 뷰를 사용하는 경우 대소 문자를 구분하는 데이터베이스가 있는 경우 개체 이름뿐만 아니라 사용자가 지정하는 항목은 모두 대문자로 되어 있는지 확인한다. 따라서 대소문자 구분을 하는 데이터베이스의 경우에는 소문자로 호출 해야 한다.

 

use CaseSensitive

go

 

-- This won't work

SELECT [name], type_desc FROM SYS.OBJECTS;

GO

-- This will

SELECT [name], type_desc FROM sys.objects;

GO

use AdventureWorks2008R2

go

 

-- This won't work

SELECT [name], type_desc FROM SYS.OBJECTS;

GO

-- This will

SELECT [name], type_desc FROM sys.objects;

GO

 

 

 

시스템 테이블이 아닌 DMV는 어떨까? 데이터베이스가 대소문자를 구분하는 환경일 때는 모두 소문자로 호출 해야 한다.

use CaseSensitive

go

 

-- This won't work in a case-sensitive collation DB

SELECT q.execution_count, t.text

FROM sys.Dm_Exec_Query_Stats q

CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t;

GO

-- This will work in a case-sensitive collation DB

SELECT q.execution_count, t.text

FROM sys.dm_exec_query_stats q

CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t;

GO

use AdventureWorks2008R2

go

 

-- This won't work in a case-sensitive collation DB

SELECT q.execution_count, t.text

FROM sys.Dm_Exec_Query_Stats q

CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t;

GO

-- This will work in a case-sensitive collation DB

SELECT q.execution_count, t.text

FROM sys.dm_exec_query_stats q

CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t;

GO

 

 

 

master에서 실행할 경우 Collation에 대하여 대소문자 상관없이 조회가 가능하다.

USE master;

GO

SELECT q.execution_count, t.text

FROM sys.Dm_Exec_Query_Stats q

CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t;

GO

 

 

 

데이터베이스를 운용할 때 각자의 환경을 잘 고려하여 사용해야 한다. 표준이라고 정의 할 수 없지만 (표준이란게 있었으면 여러 정렬셋을 만들지 않았겠지 라는 생각이 든다.) 서비스의 특성에 따라 자신이 운용하는 환경을 잘 파악하고 그에 맞는 스크립트를 준비하여 사용하도록 하자.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/2913/sql-server-case-sensitive-collations-and-dmvs/

 

 


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

No. Subject Author Date Views
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3626
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1582
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1694
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1432
» Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1308
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1560
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1237
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3594
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1238
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1179
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1065
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1129
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1301
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1652
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1228
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1418
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1066
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 958
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3104





XE Login