Collation에 따른 DMV 실행 오류

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/

 

 

2013-03-26 / 강성욱 / http://sqlmvp.kr