[DB] SSMS(SQL Server Management Studio) 활용한 리소스 점검 방법

 

SSMS Query Store 활용한 리소스 분석 / 점검

SQL Server 2016 버전 이상이고 Query Store가 활성화되어 있다면, 리소스 분석이 용이합니다.

특정 시간대 CPU 사용량 , 대기 시간 등을 보며 원인이 되는 쿼리를 볼 수 있습니다.

• 방법: SSMS(SQL Server Management Studio) → 해당 DB → Query Store(쿼리저장소) → Top Resource Consuming Queries(리소스를 가장 많이 사용하는쿼리) 선택.
• 설정: 우측 상단의 [Configure(구성)] 버튼을 눌러 문제가 발생했던 특정 시간(Time Range)으로 필터링하세요.
• 분석: 해당 시간에 갑자기 실행 횟수(Execution Count)가 늘었거나, 평소보다 논리적 읽기(Logical Reads)가 급증한 쿼리를 바로 확인할 수 있습니다.

 

아래에서는 각 지표별 상세 내역을 정리했습니다.

---

1. 실행 및 시간 관련 지표 (Execution & Time)

가장 먼저 확인해야 할 기본 지표들입니다.

• 실행 수 (Execution Count): 해당 쿼리가 특정 기간 동안 몇 번 실행되었는지 나타냅니다. 실행 횟수가 너무 많으면 개별 쿼리 성능이 좋아도 전체 시스템에 부하를 주는 'Death by a thousand cuts' 현상이 발생합니다.
• 기간 (Duration, MS): 사용자가 체감하는 쿼리 시작부터 종료까지의 전체 시간(Wall-clock time)입니다.
• CPU 시간 (CPU Time, MS): 쿼리를 처리하기 위해 프로세서(CPU)가 실제로 작업한 시간입니다.
  • CPU 시간 > 기간: 병렬 처리(DOP)가 활발하게 일어난 경우입니다.
  • CPU 시간 < 기간: 쿼리가 CPU 작업 외에 I/O나 Lock 대기 등에 시간을 허비했음을 의미합니다.

2. I/O 성능 지표 (Data Access)

디스크와 메모리 사이의 데이터 이동 효율성을 보여줍니다.

• 논리적 읽기 (Logical Reads): 데이터 페이지를 **메모리(Buffer Pool)**에서 읽은 횟수입니다. 인덱스 최적화가 안 되어 있으면 이 수치가 급증합니다.
• 물리적 읽기 (Physical Reads): 데이터가 메모리에 없어 디스크에서 직접 읽어온 횟수입니다. 이 수치가 높으면 메모리 부족이나 'Cold Cache' 상태를 의심해야 하며, 성능 저하의 주범이 됩니다.
• 논리적 쓰기 (Logical Writes): 데이터 변경(Insert, Update, Delete)으로 인해 메모리 내 페이지가 수정된 횟수입니다.

3. 컴퓨팅 및 특수 지표 (Computing)

자원 할당 방식과 외부 연동에 대한 정보입니다.

• CLR 시간 (CLR Time): SQL Server 내부에서 실행되는 .NET 코드(Stored Procedure, Trigger 등)를 처리하는 데 소요된 시간입니다.
• DOP (Degree of Parallelism): 쿼리가 실행될 때 사용된 병렬 CPU 코어의 수입니다. DOP가 너무 높으면 코어 간 통신 오버헤드가 발생하고, 너무 낮으면 대량 조회가 느려집니다.

4. 메모리 및 저장소 지표 (Memory & Storage)

자원 점유 상태를 나타내며, 특히 특정 시간대 부하의 핵심 단서를 제공합니다.

• 메모리 소비 (Memory Consumption): 쿼리 실행을 위해 할당받은 작업 메모리(Grant Memory)의 양입니다. 정렬(Sort)이나 해시 조인(Hash Join) 시 많이 사용됩니다.
• 사용된 로그 메모리 (Log Memory Used): 트랜잭션 로그 파일(LDF)에 기록된 데이터 양입니다. 대량의 데이터 변경 시 이 수치가 치솟으며 시스템을 느리게 할 수 있습니다.
• 사용된 임시 DB 메모리 (TempDB Memory Used): 임시 테이블이나 중간 계산을 위해 TempDB를 사용한 양입니다. TempDB 경합은 SQL Server 전체 성능 저하의 흔한 원인입니다.

5. 병목 현상의 핵심 (Wait)

• 대기 시간 (Wait Time, MS): 쿼리가 자원을 점유하지 못하고 멈춰 서서 기다린 시간입니다.
  • 이 시간이 길다면 무엇을 기다렸는지(Wait Type)를 분석해야 합니다. (예: 네트워크, 디스크 I/O, Lock 등)

---

📊 지표 간의 논리적 연결 (Troubleshooting)

이 지표들을 조합하면 다음과 같은 진단이 가능해집니다.

1. 논리적 읽기는 높은데 CPU 시간은 낮다? → 인덱스를 잘 타고 있지만, 메모리에서 데이터를 찾는 과정이 너무 빈번함 (인덱스 설계 재검토 필요).
2. 물리적 읽기가 특정 시간대에만 튄다? → 해당 시간에 메모리가 부족해지거나, 평소 안 쓰던 대량의 데이터를 건드리는 쿼리가 유입됨.
3. 기간(Duration)은 긴데 CPU 시간은 매우 짧다? → 쿼리 자체는 가벼운데, 대기 시간(Wait Time)이 긴 상태. 즉, 누군가에게 막혀(Blocking) 있거나 네트워크 문제일 가능성 농후.