know-how? know-way!!

PART 1. 기본적인 시스템 테이블과 저장프로시저

많은 RDBMS가 있다. 그중에는 프로그램에 관심없는 사람들도 오라클이란 데이터베이스를 들어봤을 만큼 유명한 RDBMS도 있다. 오라클이 유명할 수 있는 이유는 무엇일까? 강력한 성능과 안정성이 그 이유일까? 필자는 그렇게 생각하지 않는다. 무엇보다 오라클에 많은 관심을 가지고, 오라클을 좋아하는 사람들이 그렇게 만든 것이라 생각한다. SQL Server를 좋아하는 필자로서는, SQL Server 역시, 프로그램에 아무 관심 없는 사람들에게도 유명해 지기를 원한다. 그렇게 만들기 위해서, 필자는 오늘도 글을 적는 것이다. 

필자도 SQL Server의 전부를 알거나, 많이 알지는 못한다. 하지만, 필자가 알고 있는 작은 정보가 누군가에게는 매우 유용하게 사용될 것이라 믿는다. 결코 유명하지 않지만, SQL Server를 좋아하는 한 사람으로서 글을 적어본다.

누군가 SQL Server가 강력한가란 질문을 한다면, 필자는 사용하는 사람에 따라 초강력 일수도 있고, 그렇지 않을 수도 있다고 대답을 하겠다. 또다시, 필자에게 초강력으로 SQL Server를 사용하느냐고 묻는다면, 오늘도 그러기 위해 노력한다고 대답을 하겠다. 이렇게 글을 쓰는 것도 필자의 노력 중 하나이다.

오늘은 SQL Server에서 사용할 만한 시스템 테이블과 저장프로시저를 다루고자 한다. <개발자를 위한 T-SQL> 시리즈를 쓰다가 제목을 바꾼 것은, 시스템 테이블과 저장프로시저는 개발자, 데이터베이스 관리자 모두에게 유용하다고 생각되기 때문이다. 개발자에게만 필요한 내용은 아니라고 생각 되어서 이다.

대부분 우리 나라의 개발환경에서 데이터베이스 관리자, 개발자가 따로 구분되어 있지 않다는 것은 잘 안다. 지금 우리 나라의 개발 환경이 어떠하든, 기본적으로 알고 있어야 할 시스템 테이블과 저장프로시저가 있기에 글을 적어본다.

1절. 컬럼 정보 보기 - syscolumns, sp_columns

데이터베이스를 설계하다가, 또는 개발을 하다 보면, 데이터베이스에 관련 되어서 가장 자주 필요로 하게 되는 정보는 컬럼에 관한 정보들이다. 어떤 테이블의 어떤 컬럼의 자료형이 무엇이었는지, 문자형이라면 크기는 어떻게 되는지 이러한 정보들을 가장 자주 필요로 하게 된다. 이럴 때 사용하는 테이블이 바로 syscolumns테이블이다. 실제로 자신의 데이터베이스에서 syscolumns테이블을 SELECT해보도록 하자. 접근 권한만 가지고 있다면, 누구든지 SELECT 할 수 있을 것이다.

시스템 테이블에 대해 본격적으로 설명 하기전에 알아 두어야 하는 것은 자신의 SQL Server의 버전이다.

자신의 SQL Server에서 SELECT @@VERSION을 실행하면 버전 정보를 알 수 있다. 필자의 버전 정보는 다음과 같다.

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1)



필자를 욕하는 사람들이 있을 것이다. 아직도 Service Pack 1에 Personal Edition을 쓰고 있다고 말이다. 이제 곧 SQL Server 2005가 나올 때인데 말이다. 필자의 부지런하지 못함과 가난함에 돌을 던지기 바란다. 부지런하지 못해서, Service Pack 4가 나왔는데도 적용을 시키지 않고 있고(Personal Edition에도 적용이 되는지는 잘 모르겠다.) 가난해서 Personal Edition을 사용하고 있다. 아무튼, 이렇게 버전을 밝히는 이유는, SQL Server의 버전에 따라 설명하는 내용이 일치하지 않을 수 있기 때문이다. 버전이 올라가면서, 시스템 테이블이 변경될 수 있기 때문이다. 필자가 현재 개발을 하고 있는 곳은 SQL Server Standard Edition에 Service Pack 4이다. 이 환경에서도 필자가 설명하는 내용을 사용하기 때문에 버전이 틀리다고 해서 크게 다르지는 않을 것이다. 하지만, 필자의 버전이 틀리므로 여러분들의 환경과 틀린 부분이 있을 수 있음을 미리 밝혀둔다.

syscolumns테이블은 현재 데이터베이스의 테이블들의 컬럼의 정보를 담고 있다. 테이블들에 어떠한 컬럼들로 구성되었는지 자세히 알고 싶다면, SQL Server에서 F1을 눌러 BOL(Books Online:도움말)을 실행시킨 후 BOL의 색인에서 syscolumns항목을 찾아보기 바란다. Syscolumns의 컬럼들이 어떤 정보를 가지고 있는지 자세히 설명해 주고 있다. 필자는 이 중에 몇 가지만 다룰 것이다. 실제로도 이 중에 몇 가지만 사용한다.

Name이란 컬럼은 말 그대로 테이블의 컬럼명 정보를 담고 있는 컬럼이다. 즉, 해당 데이터베이스에는 어떤 컬럼들이 있는지 정보를 담고 있다.

Name 컬럼 바로 다음에 id라는 컬럼이다. Id는 해당 컬럼을 사용하고 있는 개체id를 뜻한다. 즉, YMD란 컬럼을 어떤 테이블에서 사용한다면, 해당 테이블의 개체 ID를 담고 있다. 그렇다면, 이 id에 대해 어떻게 개체명을 알 수 있을까? SQL Server는 이를 위해서 OBJECT_NAME이라는 함수를 제공하고 있다. 을 실행해 보도록 하자.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT OBJECT_NAME(id), *FROM syscolumns</TEXTAREA>

을 사용하면, 해당 컬럼을 사용하고 있는 개체 명을 같이 조회할 수 있을 것이다. 여기서 개체에는 테이블이 올 수도 있고 저장프로시저가 올 수도 있다.

그 다음에 살펴볼 컬럼은 xtype이란 컬럼이다. Xtype에 대해서 BOL을 참고해 보면, systypes에서의 물리적인 저장소 유형이라고 나와있다. 그렇다면, systypes라는 시스템테이블을 SELECT해보도록 하자.

systypes라는 시스템 테이블을 SELECT해보면, SQL Server에서 사용 할 수 있는 데이터 자료형에 대한 정보를 알 수 있다. xtypes테이블의 name컬럼과 xtype컬럼을 참고하면, syscolumns에 있는 xtype이 무슨 자료형을 나타내는지 알 수 있을 것이다. 즉, 을 다음과 같이 확장할 수 있다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT OBJECT_NAME(id) ObjectName, T2.name DataType,T1.*FROM syscolumns T1INNER JOIN systypes T2ON T1.xtype = T2.xtype</TEXTAREA>

Syscolumns 테이블에 대해서 그 다음에 볼 것은, length, xprec, xscale 컬럼이다. 이 3개중에 Length는 해당 컬럼이 차지하는 물리적인 최대 공간을 나타낸다. 즉, 바이트 수이다.

개발자를 위한 T-SQL의 SQL_PATTERN 데이터베이스에 대해 다음과 같이 SELECT를 해보자.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT OBJECT_NAME(id) ObjectName, T1.name ColumnName,T2.name DataType,T1.length, T1.xprec, T1.xscaleFROM syscolumns T1INNER JOIN systypes T2ON T1.xtype = T2.xtypeWHERE OBJECT_NAME(id) = 'Sales'AND T1.name = 'SaleYMD'</TEXTAREA>

을 수행하면, Sales테이블의 SaleYMD컬럼에 대한 정보를 볼 수 있게 된다. 이 중에, T1.length란 컬럼을 보면, 16을 나타내고 있다. SaleYMD는 nchar(8)의 자료형을 가지고 있다. 즉, 16은 SaleYMD 컬럼이 가질 수 있는 데이터의 최대 바이트 수이다. Nchar는 UNICODE를 위한 자료형으로서 한 글자에 2바이트의 공간이 필요하다. 그러므로 nchar(8)자른 16바이트의 크기를 가지게 되다. 문자형의 자료형은 xprec과 xscale에 대해서 0이란 값을 가지고 있다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT OBJECT_NAME(id) ObjectName, T1.name ColumnName,T2.name DataType,T1.length, T1.xprec, T1.xscaleFROM syscolumns T1INNER JOIN systypes T2ON T1.xtype = T2.xtypeWHERE OBJECT_NAME(id) = 'Sales'AND T2.name = 'numeric'</TEXTAREA>

는 Sales테이블에 자료형이 numeric인 컬럼 정보만 SELECT를 하고 있다. Numeric 컬럼에 대해서는 xprec과 xscale에 대한 값이 0이 아니다. Xprec은 해당 자료형이 가질 수 있는 전체의 숫자 길이이다. Xscale은 소수점 이하로 가질 수 있는 숫자의 길이이다. 즉, numeric(18,2)로 컬럼을 선언하게 되면, 해당 컬럼의 xprec과 xscale이 각각, 18과 2가 된다. 그리고, 숫자형의 자료형의 바이트 크기는 문자형과 마찬가지로 length를 참고하면 된다.

시스템 테이블이 알고 보면 아무것도 아니란 생각이 들 것이다. 필자도, syscolumns 테이블에서 이 정보 외에는 잘 사용하지 않는다. Syscolumns의 나머지 컬럼들의 정보는 BOL을 통해 언제든지 알 수 있을 것이다.

사실, Microsoft는 시스템테이블의 접근을 권장하지 않는다. 그렇기 때문에, 메타데이터정보를 제공하기 위해서 시스템테이블 말고도 스키마 뷰를 제공하다. syscolumns처럼 컬럼의 유용한 정보를 얻을 수 있는 뷰는 INFORMATION_SCHEMA.COLUMNS이다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT *FROM INFORMATION_SCHEMA.COLUMNS</TEXTAREA>

를 수행하면, syscolumns처럼 현재 데이터베이스의 컬럼들 정보를 보여주는 것을 볼 수 있다. syscolumns보다 정리가 잘 되어 있는 것을 알 수 있다. 어떤 것을 사용하든 자신이 원하는 정보를 빠르게 얻을 수 있으면 되는 것이다. 필자의 경우는 긴 뷰이름을 외우고 다니기도, 타이핑 하기도 귀찮은 관계로 syscolumns를 자주 사용한다. 또한 syscolumns는 저장프로시저의 매개변수에 대한 정보도 제공하기 때문에 유용하게 사용할 수 있다. INFORMATION_SCHEMA뷰 외에도 sp_columns라는 저장 프로시저를 사용해서 컬럼의 정보를 얻을 수 있다. sp_columns 저장프로시저는 지정한 테이블에 대한 컬럼들의 정보를 보여준다. 특정 테이블에 대해서만 컬럼 정보를 얻고 싶다면 sp_columns를 사용하는 것이 syscolumns를 사용하는 것보다 편할 것이다.

sp_columns 'Sales'

syscolumns를 응용할 수 있는 것들에 생각을 해보자.

가장 쉬운 것은, 어떤 컬럼을 어떤 테이블들이 사용하고 있는지 확인해 보는 것이다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT OBJECT_NAME(id) ObjectName,*FROM syscolumnsWHERE name = 'SaleYMD' OR name = '@SaleYMD'</TEXTAREA>

의 SQL로 SaleYMD 컬럼을 사용하고 있는 테이블 @SaleYMD를 사용하고 있는 저장프로시저 모두를 얻을 수 있다. 이와 같은 작업은 데이터베이스를 관리하는데 큰 도움이 된다. nchar(8)인 SaleYMD컬럼을 datetime형으로 자료형을 변경시, 어는 테이블들과, 어느 프로시저를 변경해야 하는지 쉽게 알 수 있을 것이다.

syscolumns를 유용할게 사용할 수 있는 또 한가지는 동일한 컬럼에 자료형을 동일하게 지키지 못한 컬럼들을 찾아내는 것이다. 한 시스템의 SalesmanID라는 컬럼은 모두 동일한 자료형을 지켜야 할 것이다. 그래야만, 전체적으로 시스템의 성능을 향상시키고 시스템을 이해하는데 도움을 줄 수 있다. 만약에 SalesmanID라는 컬럼을 사용하는 두 개의 테이블이 하나는 int 자료형이고, 하나는 nchar형이라면, 두 테이블의 SalesmanID가 서로 다른 의미를 가지고 있는 것인가에 대해 고민을 하게 될 수도 있고, 두 컬럼이 동일한 것이라면, 어떤 것이 맞는지 찾아내야 한다. 그러므로 같은 컬럼에는 동일한 자료형을 지정해 주어야 한다. 동일한 컬럼에 다른 자료형이 설정되었는지 알아내기 위해 다음과 같은 SQL을 사용할 수 있다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT T1.name ColumnNameFROM syscolumns T1INNER JOIN sysobjects T2ON T1.id = T2.idWHERE T2.xtype = 'U'GROUP BY T1.nameHAVING MIN(T1.xtype) <> MAX(T1.xtype)OR MIN(T1.length) <> MAX(T1.length)OR MIN(T1.xprec) <> MAX(T1.xprec)OR MIN(T1.xscale) <> MAX(T1.xscale)OR MIN(T1.collation) <> MAX(T1.collation)</TEXTAREA>

자신의 시스템에서 의 결과가 하나도 없다면, 동일한 컬럼에 동일한 자료형이 잘 지켜지고 있음을 뜻한다. 에 보면 sysobjects라는 새로운 시스템 테이블이 등장한다. 이 시스템 테이블을 SQL Server의 개체(사용자테이블, 저장프로시저, 사용자정의함수)들의 정보를 가지고 있는 시스템 테이블이다. 사용자테이블에 대해서만 컬럼의 자료형이 동일한지를 검증하기 위해 sysobjects 테이블을 사용한것이다. Sysobjects에 대해서는 2절에서 설명할 것이다.

Syscolumns를 가지고 무엇에 사용할 수 있는지 고민해 보기 바란다. 필자는 syscolumns를 이용해서 주어진 테이블명에 대해서 자동으로 SELECT문과 UPDATE문과 INSERT문을 만들어 내는 저장프로시저를 만들기도 했다. 이 문서와 같이 있는 <개발자에게 syscolumn를>이란 SQL 스크립트를 참고하기 바란다.



2절. 데이터베이스에 어떤 개체들이 생성되어 있는가 ? - sysobjects, sp_tables

이번에는 sysobjects라는 시스템 테이블에 대해 살펴보도록 하자. 개발을 하다 보면, 현재 데이터베이스에 어떤 테이블들이 있는지 궁금해질 때가 있다. 또는 특정 테이블의 컬럼명이 갑자기 기억이 안나는 것처럼 자신이 사용해야 할 테이블이 갑자기 기억이 안날 수도 있을 것이다. 자신이 사용해야 하는 프로시저 명이 무엇인지 찾아야 할 때도 있을 것이다. 이럴 때 사용할 수 있는 시스템 테이블이 sysobjects이다. 이런 시스템 테이블을 사용하지 않고도 SQL 쿼리 분석기에서 개체 브라우져를 연다거나, 엔터프라이즈 메니져를 실행시킨다면, 마우스 클릭을 사용해서 손쉽게 원하는 대부분의 정보를 얻을 수 있을 것이다. 필자가 이야기 해주고 싶은 것은 어떤 방법을 사용하든, 자신이 원하는 목적에 최대한 편하고 빠르게 다가갈 수 있다면 그 방법을 사용하라는 것이다. 개체 브라우져를 통해서 개체의 정보를 얻는 것이 편하다면 그렇게 하는 것이 좋다. 자신의 손에 익지 않은 시스템 테이블을 꼭 사용할 필요는 없는 것이다. 필자의 경우 SQL Server의 대부분의 많은 작업을 쿼리분석기에서 모두 해결하려고 한다. 하지만, 쿼리분석기에서 해결하기에 복잡한 작업들이 있다. 그런 경우는 엔터프라이즈 매니져를 사용한다. 자신의 목적에 최대한 빠르고 효율적으로 다가가는 것이 최선이라 생각하기 때문이다.

직접 자신의 데이터베이스에서 sysobjects를 SELECT 해보도록 하자. 현재 자신이 사용하는 데이터베이스에 어떠한 개체들이 있는지 알 수 있다. Sysobjects도, syscolumns와 마찬가지로, BOL을 통해 각 컬럼에 대한 자세한 정보를 얻을 수 있다.

몇 가지 컬럼에 대해서만 간단하게 설명을 해보겠다. name이라는 컬럼은 말 그대로, 개체의 이름을 뜻한다. Name 바로 다음에 나오는 id는 해당 개체의 id값이다. Syscolumns와 sysobject를 조인 할 때 이 id값으로 조인한 것을 보았을 것이다. OBJECT_NAME(id)를 하게 되면, 개체의 이름이 나오는 것 역시 알고 있을 것이다. 이와는 반대로 OBJECT_ID('개체명')을 사용하게 되면, 해당 개체명에 대한 id값을 알 수 있다.

그 다음 살펴볼 컬럼은 xtype이다. Xtype은 해당 개체가 어떤 개체인지의 정보를 나타낸다. 해당 개체가 사용자 테이블인지, 시스템 테이블인지, 저장프로시저인지를 나타낸다. 다음 <표 2-1>을 통해서 알아두도록 하자.

<표 2-1 xtype>

xtype
의미

C
체크 제약조건

D
DEFAULT 제약조건

F
Foreign Key 제약조건

FN
스칼라 함수

P
저장 프로시저

PK
Primary Key 제약조건

S
시스템 테이블

U
사용자 테이블

V
뷰

X
확장 저장 프로시저



필자가 자주 사용하게 되는 몇 가지만 적어 놓았다. 이 외에도 몇 가지가 더 있는데, BOL을 참고하기 바란다. sysobjects의 이와 같은 xtype컬럼을 이용해서 자신이 원하는 개체들만 골라서 볼 수 있을 것이다.

SQL Server는 sysobjects 시스템 테이블보다 더 유용한 저장프로시저들을 제공한다. 먼저 sp_help라는 저장프로시저가 있다. 해당 프로시저를 실행하면 현재 데이터베이스에 등록된 개체들의 정보를 볼 수 있다. sysobjects보다 사용하기 편할 것이다. 이 외에도 현재 데이터베이스의 테이블 개체들만 보고 싶다면 sp_tables라는 저장 프로시저를 실행해 보기 바란다. 현재 데이터베이스가 가지고 있는 테이블 개체들을 전부 볼 수 있을 것이다. 그리고 sysobjects에서 xtype이 F인 데이터는 외래키 제약 조건 개체를 나타내는데, 외래키 제약조건을 보는 것은 sysobjects보다 sp_helpconstraints 저장 프로시저를 사용하는 것이 좋다. SQL_PATTERN데이터베이스의 Sales테이블의 제약 조건을 보고 싶다면 다음과 같이 실행한다.



sp_helpconstraint 'Sales'


이와 같이 실행하면, Sales테이블을 참조하는 테이블과, Sales테이블이 참조하는 테이블의 자세한 정보를 얻을 수 있다. 제약 조건 정보를 보기에는 가장 좋은 방법이다.

2절에서는 간단하게 sysobjects라는 테이블을 소개해 보았다. 필자도 이 시스템 테이블을 자주 사용하지는 않기 때문에 자세히 설명할 만한 것이 없다. 하지만, 이와 같은 테이블이 있고, 이 테이블이 제공하는 정보를 대신 할 수 있는 저장프로시저들이 있음을 기억해 두도록 하자.


3절. 테이블의 크기를 추정해 보자 - sysindexes

데이터베이스 관리와 관련된 업무를 하다 보면, 가끔 보고해야 하는 부분이, 현재 우리 시스템에서 어떤 테이블이 어느 정도의 크기를 가지고 있는가 이다. 보고를 위해서가 아니라도 누구나 한번 쯤은 궁금할 것이다. 우리 시스템의 어떤 테이블이 가장 큰 크기를 가지고 있고, 데이터는 몇 건이나 있는지 말이다. 이와 같은 궁금증을 쉽게 해결할 수 있는 테이블이 sysindexes테이블이다. 테이블의 크기를 어떻게 sysindexes테이블로 알 수 있는지 의아해 하는 사람들이 있을 것이다. 하지만, sysindexes의 정보만이 테이블의 크기를 정확히 제공할 수 있다. 어떤 테이블에 클러스터드 인덱스를 만들었다고 생각해 보자. 해당 테이블은 클러스터드 인덱스의 키값으로 정렬이 이루어지고, 실제 데이터들이 클러스터드 인덱스의 리프 페이지가 된다. 그러므로 클러스터드 인덱스의 리프 페이지는 실제 데이터들이 들어가 있는 페이지가 되므로, 클러스터드 인덱스의 리프 페이지들의 크기를 체크한다면, 실제 테이블의 크기를 알 수 있는 것이다. 뿐만 아니라, 테이블의 크기를 실제 데이터들만이 들어 있는 부분만을 실제 테이블의 크기라고 생각할 수는 없다. 테이블에 대해 인덱스를 만들게 되면, 인덱스는 추가적으로 공간을 필요로 하게 된다. 이와 같이 인덱스를 위해 사용되는 공간도 테이블 크기에 합산해야 할 것이다. 클러스터드 인덱스도 마찬가지로, 리프 페이지 외에 클러스터드 인덱스를 구성하는 루트 페이지와 중간 페이지도 테이블의 크기에 합해야 할 것이다. 이와 같이 인덱스 전체 크기 정보를 제공하는 테이블이 바로 sysindexes이다.

Sysindexs에 대해서도 BOL에서 자세한 정보를 제고하므로 BOL의 내용을 한번쯤 읽어보기 바란다.

여기서도 필자가 사용하는 부분에 대해서만 소개를 해보도록 하겠다. sysindexes테이블을 조회해 보면 가장 먼저 id컬럼이 보일 것이다. Id는 개체의 id를 나타낸다. 즉, OBJECT_ID('테이블명')으로 얻어내는 개체의 id와 동일하다. 즉, sysobjects테이블에서 보여주는 테이블 개체에 대한 id이다. Sales테이블에의 인덱스 정보를 알기 위해서 다음과 같이 할 수 있다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT *FROM sysindexesWHERE id = OBJECT_ID('Sales')</TEXTAREA>

을 실행해서 결과를 보면, 여러 개의 로우가 나온 것을 볼 수 있을 것이다. 알다시피 한 개의 테이블에 여러 개의 인덱스를 만들 수 있기 때문이다. 이 결과들 중에, first컬럼이 0x000000000000인 데이터는 실제 인덱스로 구성된 개체가 아니므로 무시하도록 하자. 필자가 알기에는 해당 로우들은 SQL 서버가 임의로 만든 통계 정보이다. indid라는 컬럼이 있는데 해당 컬럼은 개체에 생성된 인덱스의 id이다. Indid가 0인 데이터는 해쉬테이블을 나타낸다. 해쉬 테이블이란, 클러스터드 인덱스가 존재하지 않는 테이블이다. 즉, 실제 데이터인 것이다. Indid가 1인 로우는 클러스터드 인덱스를 나타낸다. 그러므로, 하나의 테이블에 indid가 1과 0이 동시에 존재할 수는 없다. Indid가 2부터 254까지는 넌클러스터드 인덱스이다. 여기까지는 sysindexes의 아주 기본적인 내용이다. 우리가 관심있게 볼 내용은 used컬럼이다. Used보다 먼저 나오는 reserved와 dpages라는 컬럼도 관심있게 볼 내용이다. Dpages는 해당 인덱스의 리프 페이지의 수이다. Reserved는 해당 인덱스를 위해 예약되어 있는 페이지의 수이다. Used는 실제 인덱스를 위해 사용된 페이지 수이다. 그러므로 used를 가늠해 보면, 해당 인덱스를 위해 얼만큼의 공간이 사용되고 있는지 알 수 있다. 또는 reserved로 측정해도 될 것이다. 어떤 것으로 측정하든, 한 테이블에 생성된 여러 개의 인덱스들의 페이지를 합해 본다면, 얼만큼의 페이지가 해당 테이블을 위해 사용되는지 알 수 있을 것이다. 그리고, 한 페이지에 8KB정도의 크기를 가지므로 전체 페이지 수에 8을 곱하면 얼만큼의 KB의 공간을 테이블이 사용하고 있는지 알 수 있는 것이다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT SUM(used) * 8FROM sysindexesWHERE id = OBJECT_ID('Sales')</TEXTAREA>

필자는 를 통해 Sales 테이블이 5928KB를 사용하고 있는 것을 알 수 있었다.

used컬럼 외에도 rowcnt컬럼을 유용하게 사용할 수 있다. rowcnt컬럼은 해당 인덱스의 리프 페이지에 몇 건의 데이터가 있는지를 나타낸다. 인덱스 리프 페이지에 있는 데이터 건수는 정확히 실제 테이블의 데이터 건수와 동일하다. 그러므로 한 테이블에 있는 여러 개의 인덱스 중에 하나의 인덱스 또는 힙에 대해서만 rowcnt를 카운트 하면 해당 테이블의 로우수를 정확히 알아 낼 수 있다. 또는 sysindexes의 id컬럼에 대해 GROUP BY를 하고 max(rowcnt)를 한다면, 마찬가지로 정확히 로우수를 알아 낼 수 있다.

지금까지 익힌, used와, rowcnt를 사용해서 각 테이블당 차지하는 공간과 데이터 건수를 뽑아보는 SQL을 만들어 보자.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT T2.name, MAX(rowcnt) rowcnt, SUM(used) * 8 UsedKByteFROM sysindexes T1INNER JOIN sysobjects T2ON T1.id = T2.idWHERE T2.xtype = 'U'GROUP BY T2.nameORDER BY UsedKByte DESC</TEXTAREA>

을 통해서 각 테이블당, 데이터 건수와 사용된 공간의 크기를 알아 낼 수 있을 것이다. 에서 sysobjects테이블과 조인을 한 것은 시스템 테이블을 제외한 사용자 테이블들에 대해서만 정보를 얻고자 이다.

어떠한가? 자신의 테이블들에 얼만큼의 건수가 있는지 알기 위해서 일일이 SELECT를 했던 기억이 나는 사람들이 있을 것이다. 필자도 사실은 처음에 그렇게 했었다. 지금도, 몇 개 안 되는 테이블을 조사할 때는 그냥, 일일이 SELECT해서 COUNT를 세고 있다. 어떤 방법이든, 편하고 빠른 방법을 사용하면 된다. 하지만 현재 시스템의 모든 테이블들의 데이터 건수와 크기를 조사하기 위해서는 sysindexes가 굉장히 유용하다는 생각이 들 것이다.

sysindexes를 마치기 전에 테이블에 대해 인덱스의 정보를 알기 위해서는 어떤 프로시저가 있는지만 알고 넘어가도록 하자. 이전에 글들에서 많이 소개했듯이, sp_helpindex라는 저장 프로시저를 사용해서 특정 테이블에 어떤 인덱스들이 있는지 알 수 있다. sysindexes와는 다르게 sp_helpindex는 현재 테이블에 어떤 인덱스들과 인덱스들이 어떤 컬럼으로 구성되어 있는지의 정보를 제공한다.


sp_helpindex 'Sales'



와 같이 실행하면 된다. sp_helpindex는 무슨 일이 있도록 기억하도록 하자. 현재 SELECT하려는 테이블에 어떤 인덱스들이 설정되어 있는지 알아야 하는 것은 개발자, 관리자 모두에게 필수이다.



4절. 락에 대해 알아보자. - syslockinfo, sp_lock

syslockinfo 란 시스템 테이블을 알아보자. 이름 그대로 데이터베이스에 발생된 lock에 대한 정보를 가지고 있다. syslockinfo는 각 데이터베이스에 존재하지 않는다. syslockinfo테이블은 master데이터베이스에만 존재한다. 시스템 테이블은 각 데이터베이스에 존재하는 것과, master 데이터베이스에만 존재하는 것들이 있다. syslockinfo는 master에만 있으므로, 현재 쿼리분석기의 데이터베이스를 master데이터베이스로 변경하기 바란다.

변경 했다면, syslockinfo를 SELECT해보도록 하자. Lock이 많이 있다면, 현재 운영하고 있는 시스템일 것이다. 필자의 경우는 필자만이 사용하는 테스트 데이터베이스이므로 아무런 lock 정보가 없다. 그러므로 쿼리분석기의 쿼리창을 하나 더 열어서 Lock을 발생시켜 보도록 하겠다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>USE SQL_PATTERNgoBEGIN TRANSELECT *FROM Sales </TEXTAREA>

을 수행한 후에 master데이터베이스가 활성화 되어 있는 쿼리창에서 syslockinfo를 조회해 보도록 하자. 한 로우의 lock정보가 추가된 것을 볼 수 있다. syslockinfo를 SELECT 해 놓은 채로 을 실행했던 쿼리 분석기에서 COMMIT TRAN이나, ROLLBACK TRAN을 실행시켜 주도록 하자. 아무리 테스트이지만 Lock을 오래 잡고 있는 것은 마음이 편하지 않다. syslockinfo에 대한 정보는 SQL Server의 BOL에서 자세하게 정보를 제공하고 있다. 여기서는 살펴볼 만한 몇 가지만 살펴보도록 하겠다.

먼저 rsc_dbid라는 컬럼이 있는데, lock발생시킨 데이터베이스의 id이다. DB_NAME(id)와 같은 함수를 사용해서 실제 데이터베이스 명을 알아 낼 수 있다. 그 다음에는 rsc_indid란 컬럼이 있는데, 인덱스에 대해 lock이 발생한 경우 인덱스의 키값을 나타낸다. Sysindexes에 있는 indid와 동일하다. SQL_PATTERN데이터베이스의 Sales테이블의 ModelID컬럼에 UPDATE와 같은 동작을 한다면, ModelID컬럼이 포함된 모든 인덱스에 대해 lock이 발생되는 것을 볼 수 있다. rsc_indid 컬럼 바로 옆에 rsc_objid라는 컬럼이 있는 lock이 발생되는 개체 ID이다. 발생된 경우만 값이 있으며, 테이블 id를 나타낸다. 즉, OBJECT_NAME(id)와 같은 방법으로 lock이 발생된 테이블을 알아 낼 수 있다. 그 다음 중요한 정보는 rsc_type과 req_model이다. rsc_type은 lock이 발생된 개체의 유형을 나타내고, req_mode는 lock의 유형을 나타낸다. syslockinfo에 대한 자세한 내용들은 BOL이 잘 설명하고 있으므로 더 이상 설명하지 않겠다. BOL에 잘 나와 있을 뿐 아니라, 실제 필자가 syslockinfo 테이블을 거의 사용하지 않기 때문이다. 또한, Lock에 대한 것은, 다른 PART로 준비를 해야 할 것 같다. 그만큼 길고 중요한 내용이기 때문이다.

필자의 경우, lock의 정보를 보기 위해서 sp_lock이란 저장프로시저를 많이 사용한다. 여러분들도, 시스템이 갑자기 느려진다면, sp_lock을 실행해서 어떤 세션이 lock을 많이 발생시키고 있는지 찾아낼 수 있을 것이다.

Sp_lock을 실행하게 되면, lock을 발생시킨 세션ID(spid)와, dbid, objid를 먼저 볼 수 있을 것이다. Objid는 sysobjects의 id이다. 그러므로 sysobjects나, OBJECT_NAME(id)를 통해 lock을 발생시킨 개체가 무엇인지 알 수 있다. Type은 lock의 범위에 대한 유형이다. DB에 lock이 발생되었는지, 테이블에 lock이 발생된 것인지에 대해 알려 준다. Resources는 정확히 어느 부분에 lock이 걸렸는지 알려 준다. Mode는 현재 lock이 어떤 형태로 설정되었는지를 보여준다. 이 Mode는 아주 중요하다. X Mode의 lock이 발생되어서 장시간 존재해 있다면, 현재 시스템에 굉장히 느린 프로세스가 있거나, 트랜잭션 처리가 제대로 되지 않은 부분이 있음을 나타낸다.

lock에 대한 자세한 이야기는 나중에 다룰 것을 약속하며, syslockinfo와 sp_lock에 대해서는 여기서 마치겠다.



5절. 어떤 프로세스들이 있는가? - sysprocesses

현재 데이터베이스에 어떤 프로세스들이 있는지 알아보기 위해서 sysprocesses라는 시스템 테이블을 조회해보자.

필자의 경우 데이터베이스가 갑자기 느려 졌을 경우 가장 먼저 확인하는 것이, sp_lock과 바로 이 sysprocesses테이블이다. sp_lock을 2, 3번 실행해서, 어떤 테이블들에 xlock이 걸려서 풀리지 않는지를 확인하고, sysproceeses테이블을 통해서, 어떤 세션이 어떤 세션에 의해 막혀 있는지를 판단한다.

sysprocesses도 syslockinfo 테이블처럼 master데이터베이스에 존재한다. 역시 BOL을 통해 각 컬럼에 대해 자세한 정보를 얻을 수 있다. 

spid는 현재 데이터베이스에 접속해 있는 세션 id들이다. 실제로, 쿼리 분석기나, 프로그램을 통해 접속한 세션들은 spid가 51부터 부여된다. 즉, 50 이하의 세션들은 SQL Server가 운영을 위해 내부적으로 사용하는 spid들이다. 그러므로 실제 사용자들에 대한 세션들은 spid가 51번 이상이다.

blocked라는 컬럼이 있는데 아주 유용한 컬럼이다. 현재 세션을 가로막고 있는 세션을 나타낸다. 즉, 51번의 세션이 트랜잭션 진행 중에 A라는 테이블을 UPDATE를 한 다음에 COMMIT이나, ROLLBACK을 하지 않았다면, A라는 테이블에 xlock이 발생되어 진다. 이때, 52번의 세션이 A테이블에 51번이 UPDATE를 수행해서 Lock이 발생된 부분을 SELECT하거나, 변경하려 한다면, 51번의 세션에 의해 블로킹을 당하게 된다. 즉, 이때, sysprocesses테이블을 SELECT해보면, 52번의 세션에 대해, blocked컬럼에는 51번이 있게 된다. 이것은 굉장히 중요한 정보이다.

52번이 51번에 막혀 있고, 51번은 또 다시 53번에 막혀 있다면, 53번 세션이 작업하고 있는 내용을 처리해 준다면, 52번과 51번 모두 처리 가능할 것이다.

blocked외에 유용한 정보를 가지고 있는 컬럼은 waittime이다. waittime은 해당 세션이 작업을 위해 기다리고 있는 시간이다. 즉, waittime이 길다면, 어떤 세션에 블로킹 당하고 있을 가능성이 있다.

sysprocesses는 어떤 세션이 어떤 세션에 블로킹 당했는지에 대한 정보를 제공해 준다. 하지만, 실제 블로킹에 관한 튜닝을 할때는 이와 같은 정보만으로 한번에 어떤 부분을 처리해야 할지를 찾기가 쉽지 않다. 계속해서 sysprocesses와 sp_lock을 실행하고, 블로킹을 오래 하고 있는 세션이 있다면, 해당 세션에서 마지막으로 사용한 SQL을 찾아서(DBCC INPUTBUFFER(세션id)를 사용할 수 있다.) 해당 SQL이 포함된 프로그램과, SQL을 관찰해야만 문제가 무엇인지를 찾을 수 있을 것이다. 더 쉽게 찾을 수 있는 방법은 SQL 프로필러를 사용하는 것이다. SQL 프로필러 역시 하나의 큰 주제이다. 언젠가 다룰 수 있는 시간을 만들어 보겠다.

SQL 프로필러 외에도 요즘에는 튜닝 툴들이 많이 있으므로 툴들을 사용하면 손쉽게 찾을 수 있지 않을까 생각이 들기도 한다. 하지만, 필자는 튜닝 툴을 사용해 본적은 없어 잘은 모르겠다.

Sysprocesses는 블로킹에 관한 정보 외에도 현재 접속되어 있는 세션들의 현재 데이터베이스에 접속되어 있는 호스트와 프로그램명등의 정보를 보여 주므로 아주 유용하게 사용할 수 있을 것이다.

응용은 여러분들의 몫이다. Lock에 대한 문제를 해결하기 위해 sp_lock과 sysprocesses 테이블을 유용하게 사용할 수 있음을 기억하기 바란다.



6절. 현재 데이터베이스의 캐시를 훔쳐보자 - sp_cacheobjects

데이터베이스는 시스템에서 가장 많은 메모리를 사용한다. 그만큼 처리해야 할 일들이 많기 때문이다. 그렇기 때문에 최대한 메모리를 절약해서 사용할 수 있도록 해 주어야 한다. 시스템에서 쓸데 없이 많은 메모리를 차지하고, 불필요하게 시스템에 부하를 주는 부분은 SQL문의 컴파일에 대한 내용이다. 을 예로 들어보자.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT *FROM SalesWHERE SaleYMD = '20050610'</TEXTAREA>

을 실행하면 해당 SQL을 위한 새로운 실행계획을 만들고 이 실행계획을 캐시에 올려 놓게 된다. 즉, 의 실행계획을 위해 메모리를 사용하게 된다. 메모리에 실행계획을 올려놓는 이유는 과 동일한 SQL이 들어왔을 경우, 새로이 실행계획을 만들지 않고, 기존에 실행계획을 사용하기 위해서이다. 하지만, 과 동일하지만, WHERE절의 SaleYMD만 20050611로 변경하게 되면, 을 실행하면서 만들어 놓은 실행계획을 재사용하지 못한다. 이 경우 20050611에 대한 새로운 실행계획이 만들어 지게 된다. 이런 경우 SQL을 실행계획을 사용할 수 있도록 시스템을 구성해 주어야 한다. 이 부분에 대해서는 개발환경에 따라 많이 다르다. SQL Server의 경우 과 같이 실행하면, 변수만 변경되어도 실행계획을 재사용하지 못한다. 실행계획을 재사용하게 하기 위해서는 sp_executesql을 사용해서 SaleYMD값을 변수로 넘겨야 한다. 즉, 와 같이 구성해야 한다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>EXEC sp_executesql N'SELECT * FROM Sales WHERE SaleYMD = @P1',N'@P1 nvarchar(8)',@P1 = '20050620'</TEXTAREA>

와 같이 구성되면, 동일한 SQL에 변수만 변경되는 것에 대해 동일한 실행계획을 사용하므로 메모리에 이득과 더불어 실행계획이 불필요하게 재컴파일되는 비용을 줄일 수 있다.

재컴파일을 없애는 방법을 봤는데, 현재 어떤 SQL이 재컴파일을 심하게 발생시키고 있는지 확인하기 위한 시스템 테이블이 바로 syscacheobjects이다. 이 시스템 테이블은 master 데이터베이스에 있으며, 과 SQL순으로 정렬을 해서 조회해 보기 바란다.

<TEXTAREA class=sql style="display：none" name=CodeHighLighterCode rows=10 readOnly cols=60>SELECT SQL, *FROM syscacheobjectsORDER BY SQL</TEXTAREA>

과 같이 조회하게 되면, 동일한 SQL의 실행계획이 얼만큼이나 많이 캐시에 올라와 있는지 알 수 있을 것이다. Syscacheobjects를 통해 조회한 내용으로, 캐시의 많은 부분을 차지하고 있는 실행계획을 재사용하도록 구성한다면, 시스템의 남는 메모리들을 좀 더 유용하게 사용할 수 있을 것이다.

실제 개발에서 될 수 있다면, 거의 모든 SQL이 실행계획이 재사용되도록 구현해야 한다. 하지만, 튜닝을 목적으로 하거나, 또는, 실제 실행 횟수가 다른 프로세스에 비해 적은 프로세스의 SQL은 재컴파일을 일부러 유발시킬 수도 있다.



7절. 기타 저장프로시저

마지막으로 사용할 만한 시스템 저장프로시저들을 간단하게 정리해 보겠다. 다행히도 필자가 기존에 적었던 글에 이와 같은 내용이 있어 별다른 고생 없이 그대로 옮겨 붙였다.^^

<표 7-1>

저장 프로시저
내 용

Sp_help
데이터베이스 테이블, 뷰, 저장 프로시저 등의 개체와 사용자 정의 데이터 형식 또는 시스템이 제공하는 데이터형식 등에 관한 종합 정보를 제공한다.

Sp_helpindex
테이블 또는 뷰의 인덱스에 관한 정보를 조회한다.

Sp_helptext
암호화되지 않은 저장 프로시저, 사용자 정의 함수, 트리거 또는 뷰, 규칙의 텍스트,기본값등의 정의 문자열을 조회한다.

Sp_helpdb
지정된 데이터베이스 또는 모든 데이터베이스에 관한 정보를 보고한다.

Sp_renamedb
데이터베이스의 이름을 변경한다.

Sp_spaceused
테이블이나 인덱스된 뷰가 가지고 있는 행의 수, 예약된 디스크 공간 및 현재 데이터베이스의 테이블이 사용하는 디스크 공간을 표시하거나 전체 데이터베이스가 예약하였거나 사용하는 디스크 공간을 조회한다.

Sp_attach_db
물리적으로 저장해둔 데이터베이스의 파이를 서버에 데이터베이스로 첨부한다.

Sp_attach_single_file_db
데이터 파일이 하나인 데이터베이스를 현재 서버에 첨부한다.

Sp_configure
현재 서버에 대한 전역 구성 설정을 표시하거나 변경한다.

Sp_dboption
데이터베이스 옵션을 표시하거나 변경한다.

Sp_who
현재 서버의 사용자 및 프로세스에 관한 정보를 제공한다. 유휴 상태가 아닌 프로세스만 반환하려면 반환되는 정보를 필터링하면 된다.

Sp_lock
잠금 관련 정보를 조회한다.

Sp_dbcmptlevel
SQL Server의 지정된 이전 버전과 호환될 특정 데이터베이스 동작을 설정한다. 업그레이드 한 후 프로그램 변동없이 이전 버전에서 지원하였던 내용을 계속 사용하고 싶다면 설정할 수 있다.

Sp_depends
테이블 또는 뷰에 종속된 뷰 및 프로시저, 뷰 및 프로시저에 종속된 테이블 또는 뷰등과 같은 데이터베이스 개체 종속 관계에 관한 정보를 표시한다.

Sp_rename
현재 데이터베이스에서 테이블, 열 또는 사용자 정의 데이터 형식 등 사용자 작성 개체의 이름을 변경한다.

Sp_addmessage
Sysmessage테이블에 새 오류 메시지를 추가한다.

Sp_tables
해당 데이터베이스의 모든 테이블과 뷰에대한 리스트 정보를 보여준다.

Sp_databases
각 인스턴스에 있는 데이터베이스의 리스트를 보여준다.

Sp_statistics
지정한 테이블 또는 인덱스된 뷰에 관한 모든 인덱스및 통계 목록을 보여준다.

Sp_fkeys
지정한 테이블에 대한 외부 키 정보를 보여준다.

Sp_stored_procedures
현재 데이터베이스에 있는 사용자 정의 저장 프로시저의 목록과 정보를 반환한다.

Sp_pkeys
지정한 테이블에 있는 기본키 정보를 보여준다.

Sp_table_privileges
지정한 테이블에 대한 권한 정보를 사용자별로 보여준다.

Sp_server_info
현재 서버에 대한 자세한 정보를 보여준다.

Sp_helpconstraint
지정된 개체에 있는 제약의 정보를 보여준다.

Sp_helpdb
지정한 데이터베이스에 대한 자세한 정보를 보여준다.

Sp_helpextendedproc
지정한 확장 프로시저에 대한 간단한 DLL 매핑정보를 반환한다..

Sp_helpfile
현재 데이터베이스에서 지정한 파일에 대한 정보를 반환한다.

Sp_helpfilegroup
현재 데이터베이스에서 지정한 파일그룹에 대한 정보를 반환한다.

Sp_helplanguage
지정한 특정 언어에 대한 정보를 반환한다.

Sp_helpserer
지정한 서버에 관한 정보를 보여준다.

Sp_helpsort
현재 서버의 문자셋 정보, 즉 정렬 순서 및 문자 집합을 표시한다.

Sp_helpstats
지정한 테이블에 대해 컬럼과 인덱스에 관한 통계 정보를 반환한다.

Sp_helptrigger
지정한 트리거에 관한 정보를 보여준다.

Sp_datatype_info
현재 환경에서 지원되는 데이터 형식에 관한 정보를 표시한다.



마치며..

여기까지가 필자가 자주 사용하는 시스템 테이블들 전부이다. 내용도 너무 적고, 새로운 내용도 아니지만, 이 정도 시스템 테이블과 저장프로시저는 알고 있어야 한다는 생각에서 글을 적어 보았다.

마지막으로 참고할만한 내용은 1절에서 INFORMATION_SCHEMA에 대해 약간 살펴 보았는데, INFORMATION_SCHEMA는 시스템 테이블만큼 유용한 뷰들이다. 이것 역시 BOL에 자세히 나와 있으니, BOL을 참고해서 필요한 내용들을 습득하기 바란다.

BOL을 참고하란 말이 PART-1에 걸쳐 전체적으로 이야기 한 것 같다. 그만큼 BOL이 잘 정리되어 있고, BOL에 있는 내용을 중복해서 글을 적고 싶지는 않기 때문이다.(하지만, 필자의 글이, BOL의 내용을 풀어 쓴 것에 불과할지도 모른다.)

-> 이 포스트는 다른분이 작성한걸 예전부터 가지고 있었는데, 누구껀지 몰라 출처를 못 적었네요.

출처확인되는데로 추가해 놓을게요. ^^*