안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 4-8. 시스템 카탈로그 엿보기를 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 시스템 데이터베이스와 시스템 카탈로그 뷰, 시스템 저장 프로시저입니다.

TL;DR

네 개의 시스템 데이터베이스(master, model, tempdb, msdb)를 설명하고, 각 데이터베이스의 메타 정보를 제공하는 시스템 카탈로그 뷰와 시스템 저장 프로시저에 대해 살펴봅니다. 

쿼리에서 자주 보였던 master 데이터베이스와 같은 SQL Server 시스템 데이터베이스를 알아보고, 각 데이터베이스에서 데이터베이스 관련 메타 정보를 포함하는 특수 목적의 뷰인 시스템 카탈로그 뷰도 살펴봅니다. 아울러 시스템 테이블을 손쉽게 쿼리 할 수 있는 시스템 저장 프로시저도 살펴보겠습니다.

시스템 데이터베이스

시스템 데이터베이스 확인 방법

시스템 데이터베이스를 확인하려면, SSMS 개체 탐색기에서 시스템 데이터베이스를 확장하면 보입니다.

이미지 - 시스템 데이터베이스

이렇게, master, model, msdb, tempdb 시스템 데이터베이스를 볼 수 있습니다.

시스템 데이터베이스 종류

네 개의 시스템 데이터베이스가 있습니다.

- master
- model
- tempdb
- msdb

이렇게 4개의 시스템 데이터베이스가 있습니다. 먼저 이름부터 포스 있는 master 데이터베이스입니다.

Master database

master 데이터베이스는 SQL Server 전체에 영향을 미치는 시스템 데이터베이스로, 사용자 및 사용자 데이터베이스와 SQL Server의 동작을 관리합니다. 사용자의 직접 접근을 허용하지 않으며, 잘못된 조작은 치명적인 결과를 초래할 수 있습니다. 복구 작업도 어려우므로 정기적으로 백업을 수행하세요.

master 데이터베이스에 기록되는 내용 예시
- 로그인 계정(sys.syslogins)
- 시스템 오류 메시지(sys.sysmessages)
- 저장된 데이터베이스(sys.sysdatabases)

Model database

model database는 새로 생성되는 데이터베이스의 템플릿으로 사용됩니다. 즉, 새로운 사용자 데이터베이스를 생성할 때 SQL 서버는 model 데이터베이스를 복사해 새로운 데이터베이스를 생성합니다. 이렇게 생성되는 사용자 데이터베이스는 model 데이터베이스에 이미 정의된 정보를 포함합니다.

model 데이터베이스에는 시스템 테이블뿐만 아니라 사용자가 정의한 테이블, 뷰, 프로시저 등과 같은 개체도 포함될 수 있습니다. model 데이터베이스에 대한 변경은 이후에 생성되는 모든 데이터베이스에 반영되므로, model 데이터베이스에 새로운 테이블을 추가하거나 기존 테이블을 수정한다면, 이후에 생성되는 모든 데이터베이스에도 해당 변경 사항이 적용됩니다. model 데이터베이스를 활용하면 템플릿처럼 동작해, 생성되는 모든 데이터베이스에 일관적으로 설정을 유지하고 효율적인 데이터베이스 관리가 가능합니다.

그렇다면, model 데이터베이스에 템플릿으로 생성해 두면 좋을 것은 어떤 것들이 있을까요? 일반적으로 model 데이터베이스에 템플릿으로 구성하는 항목들입니다.

- 사용자 정의 자료형(user-defined datatype), 규칙, 디폴트, 저장 프로시저(stored procedure)
- SQL Server 모든 데이터베이스에 접근할 수 있는 사용자
- 디폴트 권한
- 데이터베이스 구성 옵션 설정

위의 정보를 model 데이터베이스에 생성하면 이후 생성하는 모든 데이터베이스에 적용됩니다.

Temp database

tempdb는 SQL Server에서 임시 테이블과 중간 결과 저장에 필요한 임시 저장 공간을 제공하는 데이터베이스입니다. 예를 들어, GROUP BY, ORDER BY, DISTINCT 등과 같은 작업에서 발생하는 임시 결과셋을 저장하기 위해서도 사용됩니다.(tempdb spill이라는 내부 로직이 사용됩니다.)

tempdb는 SQL Server 설치 중에 자동으로 생성되며, 임시 테이블을 관리합니다. 전역 임시 테이블(global temp table)을 제외한 모든 임시 테이블은 생성한 사용자의 SQL Server 연결이 끊어질 때 자동으로 삭제됩니다. 또한, SQL Server가 정지되면 tempdb 데이터베이스에 저장된 모든 테이블이 삭제됩니다.

테이블 변수(Table variable)와 임시테이블(Temp table) 비교

tempdb는 현재 사용 중인 데이터베이스와 관계없이 모든 임시 테이블을 저장합니다. 즉, 다른 데이터베이스에서 생성된 임시 테이블도 tempdb에 저장됩니다. tempdb에 저장되기 때문에 임시 테이블의 데이터를 다른 데이터베이스 간에 공유하거나, 동일한 연결 세션 안에서 여러 개의 임시 테이블 사용이 가능합니다.

tempdb는 SQL Server의 성능과 안정성에 중요한 역할을 합니다. 따라서 이 데이터베이스의 성능 모니터링, 적절한 크기 조정, 적절한 디스크 구성 등의 관리가 필요합니다.

다음은 임시 테이블과 tempdb 예제 쿼리입니다.

USE AdventureWorks;
GO

--임시 테이블을 생성
CREATE TABLE #A(
a int
);

INSERT INTO #A VALUES(1);
INSERT INTO #A VALUES(2);
SELECT * FROM #A;
GO

결과 
a 
----------- 
1
2

-- 새로운 사용자로 SSMS 세션을 하나를 더 연다. "새 쿼리" 클릭
-- 그후 다시 SELECT를 해 보면?
SELECT * FROM #A;

메시지 208, 수준 16, 상태 0, 줄 1
개체 이름 '#A'이(가) 유효하지 않습니다.

-- 로컬 임시 테이블 #테이블명은 현재 세션 사용자에게만 국한되는 임시 테이블입니다. 같은 사용자라도 다른 세션은 이 로컬 임시 테이블 사용이 불가능합니다.


-- tempdb에서 조회 테스트
USE tempdb;
GO

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE N'#A%';
GO

-- 전역 임시 테이블인 ##테이블명으로 생성하는 임시 테이블 예제
USE AdventureWorks;
--전역 임시 테이블 ##B를 생성
CREATE TABLE ##B(
b int
);
GO

INSERT INTO ##B VALUES(1);
INSERT INTO ##B VALUES(2);
SELECT * FROM ##B;
GO

--새로운 사용자로 SSMS 세션을 열고 쿼리를 수행.
--그 후 다시 SELECT를 해 보면?
USE AdventureWorks;
SELECT * FROM ##B;
GO

-- 전역 임시 테이블의 결과가 잘 보임.
-- tempdb에서 조회
USE tempdb;
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE N'##B%';
GO
-- 전역 임시 테이블 확인

-- 모든 세션을 종료하거나 SQL 서버를 재시작하고 다시 연결해 조회. 
USE tempdb;
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS;
GO
-- #A 로컬 임시 테이블과 ##B 전역 임시 테이블이 없음.

전역 테이블과 임시 테이블은 개발자에 따라 선호하는 분도 많이 계십니다. 주로 긴 Batch 작업이나 summary 정보를 생성하는 복잡한 쿼리를 생성할 경우, 중간 단계 결과셋 저장에 유용하게 사용되므로 잘 알아두면 좋습니다.

Msdb database

msdb는 간단히 SQL Server에서 실행되는 자동화/운영과 관련된 작업을 하는 데이터베이스로 데이터베이스 백업, 복구 진행 및 기록과, 스케줄링, 알림의 기능을 제공합니다. 주기적으로 스케줄링된 작업을 수행하는 SQL Server 에이전트(agent) 프로세스에서 사용하는 작업 스케줄링, 알림, 작업 실행을 msdb에서 관리합니다. 또한, 데이터베이스의 백업 및 복구 작업과 관련된 정보를 msdb에 저장합니다.

☑️ 챗GPT 활용: SQL Server 에이전트 프로세스에 대해서 알려줘

Msdb 주요 테이블

msdb에는 다음 주요한 테이블과 시스템 카탈로그가 있습니다.

시스템 카탈로그 뷰 (System catalog views)

SQL Server 개별 데이터베이스는 모두 시스템 테이블(System table)이 존재하고, 시스템 테이블에 데이터베이스 설정 정보와 다양한 시스템 작업을 기록합니다. 
예를 들어, 데이터베이스 내부 개체 정보가 저장되는 시스템 테이블은 “sysobjects’이고, 아래 쿼리처럼 사용은 가능하지만, 시스템 테이블 직접 참조 및 사용을 권장하지 않습니다.(불가능한 건 아닙니다.)

USE AdventureWorks;
GO

-- sysobjects 시스템 테이블로 데이터베이스의 테이블 정보 출력
SELECT * FROM sysobjects WHERE xtype LIKE 'U';
GO

공식 가이드는 INFORMATION SCHEMA VIEW나 시스템 카탈로그 뷰를 사용해 SQL 서버 정보를 출력하도록 권장합니다.

시스템 카탈로그 뷰와 INFORMATION SCHEMA VIEW 차이

시스템 카탈로그 뷰는 일반적으로 줄여서 “sys테이블”이라고 부릅니다. 그리고 유사한 INFORMATION SCHEMA VIEW(정보 스키마 뷰)가 있습니다.

둘 다, 데이터베이스 메타 정보를 제공하는 점에서 같습니다. 하지만, INFORMATION SCHEMA VIEW는 ANSI SQL 표준에 맞춰 제작된 뷰입니다. 즉, 모든 데이터베이스 시스템의 정보를 포함하지 않고, ANSI SQL과 호환되는 정보만 표시합니다. 

현재 개발하는 앱이나 서비스가 SQL Server 뿐만 아니라 다른 데이터베이스 시스템을 지원하는 높은 이식성이(portability) 필요하다면, ANSI 표준 INFORMATION SCHEMA VIEW를 사용하는 것이 최선입니다.

반면, 시스템 카탈로그 뷰는 SQL Server 엔진의 거의 모든 정보를 표시합니다. 개발하는 앱이나 서비스가 SQL Server에 강하게 연동된다면 시스템 카탈로그 뷰를 사용하는 것이 편리합니다.

이 강좌에서는 가능한 다른 데이터베이스와 공유 가능한 ANSI 표준인 INFORMATION SCHEMA VIEW를 최대한 이용하고, 필요할 경우에만 시스템 카탈로그 뷰를 이용합니다.

USE AdventureWorks;
GO

-- INFORMATION_SCHEMA.TABLES 사용
SELECT * FROM AdventureWorks.INFORMATION_SCHEMA.TABLES  
WHERE TABLE_NAME LIKE 'Product%';
GO  
-- sys.tables 시스템 카탈로그 뷰 사용 - 더 많은 정보가 출력됨
SELECT * FROM sys.tables
WHERE type LIKE 'U' AND name LIKE 'Product%';
GO

-- INFORMATION_SCHEMA.COLUMNS 사용
SELECT * FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME LIKE 'Person'; 
GO  
-- sys.columns 시스템 카탈로그 뷰 사용 - 더 많은 정보가 출력됨
SELECT * FROM sys.columns
WHERE object_id = (SELECT object_id from sys.objects WHERE name LIKE 'Person')
ORDER BY column_id ASC;
GO

시스템 카탈로그 뷰 용도와 종류

시스템 카탈로그 뷰는 데이터베이스 엔진의 구조와 정보를 제공합니다. 시스템 카탈로그 뷰는 sys 스키마로 제공됩니다. master 데이터베이스는 모든 시스템 테이블을 포함하고, 사용자 데이터 베이스는 시스템 카탈로그 중 일부만 포함합니다.
시스템 카탈로그 뷰 종류는 개체 카탈로그 뷰(Object catalog view)나 스키마 카탈로그 뷰(Schema catalog view) 같은 개체 정보를 제공하는 것부터, 시스템 정보를 제공하는 카탈로그 뷰까지 다양한 종류가 있습니다.

시스템 카탈로그 상세 정보는 아래 링크를 참조하세요.
- 시스템 카탈로그 뷰(Transact-SQL) 

아래는 자주 사용하는 개체 카탈로그 뷰 정보입니다.

개체 카탈로그 뷰 종류와 설명

- 개체 카탈로그 뷰(Transact-SQL)

시스템 저장 프로시저

다음은 시스템 저장 프로시저입니다. 이전 강좌 쿼리에서 몇 번 사용했지요. sp_who와 같은 “sp_”로 시작하는 SQL Server 개체가 시스템 저장 프로시저입니다.

시스템 저장 프로시저란?

시스템 저장 프로시저는 SQL Server의 관리 작업을 위해 시스템 테이블을 쿼리 하는 간단한 방법을 제공합니다. SQL Server를 관리하고 데이터베이스와 사용자들에 대한 정보를 보기 위해 시스템 저장 프로시저를 사용합니다. 시스템 카탈로그 뷰가 데이터베이스 개체 단위 정보라면, 시스템 저장 프로시저는 sp_who 또는 sp_spaceused처럼, 작업 단위 정보도 제공합니다.

시스템 저장 프로시저 구성 및 특징

시스템 저장 프로시저는 미리 컴파일된 SQL 쿼리문의 집합입니다. sp_who처럼 그냥 실행하면 동작합니다. 이런 시스템 저장 프로시저들은 모두  master 데이터베이스에 존재합니다. 시스템 관리자가 소유하지만 모든 데이터베이스에서 실행이 가능한 특수 데이터베이스 개체입니다.

대표적인 시스템 저장 프로시저

이미 몇 번 사용한 시스템 저장 프로시저도 있습니다. 아래는 여러 번 사용하게 될 프로시저들을 몇 개 정리했습니다. 다양한 시스템 저장 프로시저를 강좌를 진행하면서 계속 소개하고 사용하게 됩니다.

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022