metadata - blocking 된 이슈

메타데이터가 이제 blocking 이 되는 경우 

시스템 데이터베이스

• master: 데이터베이스의 메타데이터와 시스템 구성 정보를 저장합니다.
  • SQL 서버의 이제 환경정보, 로그인 정보, 관리되는 모든 데이터베이스에 대한 정보들을 관리 
• msdb: SQL Server 에이전트 관련 작업(예: 스케줄러, 경고, 백업 작업 등)을 관리합니다.
  • 자동화에 대한 관련된 정보를 저장
  • 작업. 스케쥴러 
• model: 새 데이터베이스를 생성할 때 기본 템플릿 역할을 합니다.
• tempdb: 임시 데이터와 테이블을 저장하며, SQL Server가 실행될 때마다 초기화됩니다.
• resource: 읽기 전용 데이터베이스로, 시스템 개체 정의를 저장합니다.

 

SQL Server에서 메타데이터(metadata) 관련 blocking 이슈는 주로 메타데이터 액세스 중 발생하는 잠금(Locking) 문제로 인해 다른 쿼리나 트랜잭션이 지연되거나 대기 상태에 빠지는 경우를 의미합니다. 이는 데이터베이스에서 메타데이터를 관리하거나 수정할 때 자주 발생할 수 있는 문제입니다.

 

1. Metadata Locking의 주요 원인

메타데이터는 테이블, 인덱스, 뷰, 저장 프로시저 등의 정의와 같은 시스템 정보를 저장합니다. 메타데이터가 차단되는 주요 원인은 다음과 같습니다:

(1) DDL(데이터 정의 언어) 작업

• CREATE, ALTER, DROP과 같은 DDL 작업은 메타데이터를 변경하기 때문에 메타데이터 잠금을 발생시킵니다.
• 예: 한 사용자가 테이블을 변경(ALTER TABLE) 중이고, 다른 사용자가 해당 테이블에 액세스하려고 하면 blocking이 발생할 수 있습니다.

(2) 대규모 트랜잭션

• 장시간 실행되는 트랜잭션이 메타데이터 잠금을 유지한 상태에서 다른 작업을 차단할 수 있습니다.
• 예: 테이블이나 인덱스의 대규모 업데이트, 삭제, 또는 생성 작업.

(3) 시스템 테이블 액세스

• SQL Server는 메타데이터를 시스템 테이블에 저장합니다. 많은 사용자 또는 작업이 동시에 시스템 테이블에 접근하면 blocking이 발생할 수 있습니다.
• 예: sys.objects, sys.indexes와 같은 시스템 뷰나 테이블에 대한 빈번한 액세스.

(4) 스키마 잠금(Schema Lock, Sch-M)

• DDL 작업이 스키마 잠금을 획득(Sch-M)하면 다른 트랜잭션이 테이블이나 뷰 등의 구조를 액세스하려고 할 때 대기 상태가 됩니다.

---

2. 메타데이터 Blocking 진단 방법

(1) SQL Server Activity Monitor

• SSMS에서 Activity Monitor를 실행하여 Processes 또는 Resource Waits를 확인합니다.

(2) 동적 관리 뷰(DMV) 사용

• 다음 쿼리로 블로킹 트랜잭션을 확인할 수 있습니다:

sql

복사편집

-- 블로킹 세션 확인

SELECT session_id AS BlockingSession, blocking_session_id AS BlockedBySession, wait_type, wait_time, resource_description FROM sys.dm_exec_requests WHERE blocking_session_id <> 0; -- 세션 상세 정보 SELECT session_id, status, login_name, wait_type, wait_time, blocking_session_id, last_request_start_time, last_request_end_time FROM sys.dm_exec_sessions WHERE is_user_process = 1;

(3) Extended Events 또는 SQL Profiler

• 메타데이터 관련 잠금 이벤트를 추적합니다.
  • 이벤트 필터: lock_acquired, lock_released, block_process_report 등.

---

3. 해결 방안

(1) DDL 작업 시간 최적화

• DDL 작업(테이블 변경, 인덱스 생성 등)을 트래픽이 적은 시간대에 실행합니다.
• 가능하면 DDL 작업을 짧고 빠르게 완료하도록 최적화합니다.

(2) 읽기 전용 트랜잭션 격리 수준 설정

• 데이터 읽기 작업에 대해 잠금을 줄이기 위해 격리 수준을 조정합니다:

sql

복사편집

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 또는 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 잠금 없음

• 주의: READ UNCOMMITTED는 더러운 읽기가 발생할 수 있으므로 중요 데이터에는 신중히 사용해야 합니다.

(3) 스키마 변경 시 영향 최소화

• WITH (ONLINE=ON) 옵션을 사용하여 DDL 작업의 잠금을 줄입니다(Enterprise Edition 이상 지원).
  sql

  복사편집

  CREATE INDEX IX_Test ON TestTable (Column1) WITH (ONLINE=ON);

(4) 잠금 시간 초과 제한

• 잠금 대기 시간을 제한하여 블로킹 상황을 방지합니다.

sql

복사편집

SET LOCK_TIMEOUT 5000; -- 5초

(5) Deadlock 모니터링 및 예방

• 시스템에서 교착 상태를 예방하거나 자동 해결하는 Deadlock Monitor를 사용합니다.
• 교착 상태가 발생한 경우 관련 정보를 확인:

sql

복사편집

SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id = 0;

---

4. 장기적인 최적화 전략

• 인덱스 최적화: 올바른 인덱스를 설계하여 검색 성능을 높이고 잠금을 줄입니다.
• 쿼리 리팩토링: DML/DDL 작업을 병렬로 처리하거나 효율적으로 분리합니다.
• 시스템 테이블에 직접 액세스 방지: sys 스키마 뷰 대신 캐싱 메커니즘 사용 고려.
• Extended Events 활용: 메타데이터 잠금 원인을 지속적으로 모니터링합니다.

---