SQL Server 모니터링
이제 모든 관련 문서를 확인했습니다. 상세한 답변을 드리겠습니다.
exemONE SQL Server 모니터링 상세 가이드
1. 지원 버전
참고 문서: SQL Server 지원 버전
| 항목 | 내용 |
|---|---|
| 지원 버전 | SQL Server 2012 이상 |
2. 필수 권한
참고 문서: SQL Server 권한
2.1 기본 모니터링 권한 (sysadmin 권한 또는 아래 권한 설정 필요)
master DB 권한
-- SQL Server 2022 이상 / Azure
alter server role ##MS_serverStateReader## ADD MEMBER exemone;
-- 참고: tablesizeinfomessage 수집을 위해선 아래 권한 추가 필요
GRANT VIEW DEFINITION TO exemone;
-- SQL Server 2012~2019
grant view server state to exemone;
각 DB 권한
grant execute on dbo.fn_cdc_hexstrtobin to exemone; -- 변경 데이터 캡처(cdc) 관련 함수 실행 권한 부여
grant view definition to exemone; -- SQL Server 시스템 카탈로그 뷰 접근 권한 부여 (sysindexes, sysobjects)
2.2 Session Kill 권한
grant kill database connection to exemone;
2.3 모니터링 방식(sqlserver.mode)에 따른 권한 차이
sqlserver.mode = procedure (기본 값)
create database권한 필요 (maxgauge DB가 없을 경우 자동 생성)- 사용자가 수동으로 maxgauge DB 생성 시, 아래 권한 필요:
-- 자동 생성 시 (권한 필요)
grant create any database to exemone;
-- 수동 생성 시
use maxgauge;
create user [exemone] for login [exemone]; -- maxgauge db내 사용자 생성
grant create procedure to exemone;
grant control on schema::dbo to exemone;
grant execute on dbo.fn_cdc_hexstrtobin to exemone;
sqlserver.mode = query (Azure 환경)
create database권한 불필요- 단,
fn_cdc_hexstrtobin함수 관련 오류 발생 시 수동 생성 필요:
grant execute on dbo.fn_cdc_hexstrtobin to exemone;
3. 사전 설정
참고 문서에서 SQL Server에 대한 별도의 사전 설정(Pre-Configuration) 전용 문서는 없으나, 권한 문서를 기반으로 정리하면 다음과 같습니다:
3.1 모니터링 계정 생성 및 권한 부여
단계 1. SQL Server에 모니터링 전용 로그인 계정 생성 (예: exemone)
단계 2. master DB에 기본 모니터링 권한 부여
- SQL Server 2012~2019:
grant view server state to exemone; - SQL Server 2022 이상/Azure:
alter server role ##MS_serverStateReader## ADD MEMBER exemone;
단계 3. 각 모니터링 대상 DB에 카탈로그 뷰 접근 권한 및 CDC 함수 실행 권한 부여
단계 4. 모니터링 방식 선택
- procedure 모드 (기본): maxgauge DB 자동 생성을 위해
create any database권한 부여, 또는 수동으로 maxgauge DB 생성 후 관련 권한 설정 - query 모드 (Azure 환경): 별도의 DB 생성 권한 불필요
단계 5. Session Kill 기능이 필요한 경우 kill database connection 권한 추가 부여
3.2 DB Agent 설치
참고 문서: DB Agent 지원 환경
DB Agent를 설치하고, SQL Server 인스턴스를 exemONE에 등록해야 합니다.
4. 주요 모니터링 항목
4.1 Instance Detail Slide (인스턴스 상세)
8개 탭으로 구성됩니다:
| 탭 | 설명 |
|---|---|
| Information | Agent 정보, Machine 정보 (호스트, IP, OS, 커널 버전 등) |
| Metric | 인스턴스 성능 지표 차트 |
| Active Session | 실시간 Active Session 목록 및 관리 (Multi Kill 지원) |
| SQL List | SQL 목록 및 통계 |
| Lock Info | Lock 대기 세션 정보 및 관리 |
| Alert | 등록된 알람 정보 및 실시간 임계값 |
| Parameter | 현재 파라미터 설정 값 |
| Host Process List | 호스트 프로세스 목록 |
4.1.1 Metric (인스턴스 지표)
| 지표 | 설명 |
|---|---|
| Active Sessions | Active 세션 수 |
| Locks Waiting | Lock 대기 세션 수 |
| Open Transactions | 트랜잭션이 열려 있는 세션 수 |
| Page Lookups / Sec | 초당 Logical 읽기 수 |
| Page Reads / Sec | 초당 Physical 읽기 수 |
| Page Life Expectancy | 페이지가 버퍼풀에 남아있는 시간 |
| Batch Requests / Sec | 서버에 의해 수신된 일괄처리 요청 수 |
| Transactions / Sec_Total | 데이터베이스의 초당 트랜잭션 수 |
| User Connections | 시스템에 연결된 사용자 수 |
| Buffer Cache Hit Ratio | 버퍼캐시에서 찾은 페이지 비율 |
| SQL Compilations / Sec | SQL 컴파일 횟수 |
4.1.2 Active Session Grid
| 항목 | 설명 |
|---|---|
| Elapsed Time (sec) | 수행시간 |
| Session ID | 세션 ID |
| SQL ID / SQL Text | SQL 식별 및 쿼리 텍스트 |
| ECID | 실행 컨텍스트 ID |
| Blocking Session ID | 차단 세션 ID |
| Command Type / Status | 실행 명령 / 상태 |
| Database Name / Object Name | DB명 / 오브젝트명 |
| SQL Last Wait Type / SQL Wait Time | 대기 유형 / 대기 시간 |
| CPU Time | CPU 사용 시간 |
| Logical Reads/s / Physical Reads/s | 논리/물리 읽기 수 |
| Program Name / Login Name | 프로그램명 / 로그인명 |
| Tempdb Alloc / Tempdb Dealloc | Tempdb 할당/취소 페이지 수 |
| Open Tran | 열려 있는 트랜잭션 수 |
4.1.3 Lock Info Grid
Session ID, Blocking Session ID, Elapsed Time, ECID, KPID, Command Type, Status, Database Name, Object Name, SQL Last Wait Type, SQL Wait Time, CPU Time, Logical Reads/s, Physical Reads/s, Program Name, Login Name, Open Tran 등의 정보를 제공합니다.
4.2 Single View (싱글 뷰)
참고 문서: Single View - SQL Server
특정 하나의 SQL Server 인스턴스에 대해 다양한 관점에서 실시간 성능 상태를 확인할 수 있습니다.
| 구성 요소 | 설명 |
|---|---|
| 24 Trend | 24시간 성능 지표 운용 추이, 다른 날짜와 비교 가능 |
| TempDB Usage | TempDB 사용량과 여유 공간 비율 |
| Alert Logs | 현재 발생 중인 알람 카운트 |
| Overview | CPU Usage (전체/SQL Server), Session Count, Active Session Elapsed Time (Scatter), SQL Elapsed Time (Scatter) |
| Session Elapsed Time | 수행 시간 별 세션 수 및 최대 수행 시간 |
| DB Metric | 5개 주요 성능 지표 차트 (변경/확장 가능) |
| Session Tab | Session (실행중 세션), Blocked (Lock 세션) 목록 + Session Detail Slide 연계 |
Session Detail Slide에서는 다음 정보를 확인할 수 있습니다:
- 세션 연계 정보 (Instance Name, Database Name, Session ID, Plan Hash)
- 성능 지표 차트 (CPU Time, Reads, Writes, Logical Reads)
- 세션 정보 (Login Time, Host Name, Program Name, Status, Temp Usage, CPU Time, Memory Usage, Total Schedule Time, Total Elapsed Time)
- Full Text, Plan, Event Info
4.3 Performance Analysis (성능 분석)
4.3.1 Trend Analysis
참고 문서: Trend Analysis
날짜별, 시간별 성능 추이를 확인하며, 하단 Grid에서 다음 5가지 탭의 상세 정보를 분석할 수 있습니다:
| 탭 | 설명 |
|---|---|
| Session | 클릭 시점의 세션 정보 (41개 항목: Elapsed Time, Wait Type, CPU Time, Logical/Physical Reads, Tempdb, DOP, SRS 등) |
| Blocked | Lock 세션 정보 (상위/하위 Grid 계층 구조, Lock Resource Type, Request Mode 등) |
| SQL Stat | SQL 통계 (Execution Count, Elapsed Time, Worker Time, Logical/Physical Reads, Rows - Last/Min/Max/Avg 포함) |
| Top SQL | Order By 기준 상위 100 SQL (Elapsed Time, Worker Time, Logical Reads, Physical Reads 등) |
| Wait | Wait 정보 (Pie Chart + Grid, Waiting Tasks Count, Wait Time, Signal Wait Time - Delta 값 포함) |
4.3.2 Parameter History
참고 문서: Parameter History
기준 날짜와 비교 날짜의 파라미터를 비교하여 변경 이력을 확인합니다. 변경된 파라미터는 파란색(True)으로 표시됩니다.
4.3.3 Alert History
참고 문서: Alert History
히트맵 또는 Stack Bar Chart 형태로 알람 발생 이력을 확인하며, Top Alert 및 Alert List를 분석할 수 있습니다.
4.4 SQL Analysis (SQL 분석)
4.4.1 Top-N Analysis
참고 문서: Top-N Analysis
Stacked Area Chart로 특정 기간의 Top SQL을 조회합니다.
- Group By: SQL / Procedure
- Order By: Elapsed Time, Worker Time, Logical Reads, Physical Reads
- Top Procedure: 상위 5건 (최대 200개)
- Top SQL: 상위 20건 (최대 200개)
4.4.2 Search SQL
참고 문서: Search SQL
특정 SQL의 통계 정보, 과거 수행 이력, Trend, History, Full Text, Plan을 확인합니다.
- Filter: Database, Object, SQL Hash
- 수집 항목: Execution Count, Elapsed Time, Worker Time, Logical Reads, Physical Reads, Rows (각 비율(%) 포함)
4.4.3 Plan Analysis
참고 문서: Plan Analysis
SQL 쿼리의 실행 계획을 시각적으로 분석합니다.
- Plan Hash Value 변화가 있는 SQL ID를 조회
- Plan Diff 기능으로 실행 계획 비교 가능
- Plan Grid: Stmt Text, Physical OP, Logical OP, Estimate Rows, Estimate IO, Estimate CPU, Total SubTree Cost 등
4.5 Session Analysis (세션 분석)
Search Session
참고 문서: Search Session
조건 입력으로 5초 단위 세션 정보를 필터링하여 분석합니다.
- Filter: Elapsed Time(s), Session ID, Database Name, Object Name, Blocking Session ID, SQL Hash, Host Name, Program Name, Login Name
- 수집 항목: 43개 항목 (Wait Type, Wait Resource, DOP, Parallel Worker Count, Percent Complete, Deadlock Priority, Tempdb, SRS 등)
4.6 DataSpace Analysis (데이터 공간 분석)
DB Info
참고 문서: DB Info
데이터베이스의 정보, 데이터 파일, 로그 파일 크기 및 사용량을 분석합니다.
| 구성 요소 | 설명 |
|---|---|
| Database Size Info | Data Size, Data Used Size, Log Size, Log Used Size, Last Backup Time, VLF Count, Increment(24h) |
| Database Info | Owner, Create Date, Status, User Access, Compatibility Level, Collation Name, Recovery Model, Updateability |
| Database File Info | File ID, File Name, File Path, File Group, File Size, Max Size, Growth, Usage |
| File Trend (Day) | 일 단위 데이터/로그 파일 Total 및 Used 추이 |
| File Trend (Time) | 시간별 데이터/로그 파일 Total 및 Used 추이 |
| Top 30 Table | Total 내림차순 Top 30 테이블 (Table Name, Total, Used, Increment, Usage%, Rows) |
4.7 SQL Detail Slide / Session Detail Slide
SQL Detail Slide
SQL 상세 정보를 4개 탭으로 제공합니다:
| 탭 | 설명 |
|---|---|
| Full Text | SQL 전체 텍스트 (Formatting, Copy 기능) |
| Trend | SQL 성능 추이 |
| History | 시간별 Elapsed Time, Logical/Physical Reads, Executions |
| Plan | 실행 계획 (Stmt Text, Physical OP, Logical OP, Estimate Rows, Estimate IO, Estimate CPU 등) |
Session Detail Slide
세션 상세 정보를 제공합니다:
- Session Information: Instance Name, Database Name, Session ID, Login Time, Login Name, Host Name, Program Name, Status, Temp Usage, CPU Time, Memory Usage, Total Scheduled Time, Total Elapsed Time
- Active Session History: 과거 이력 (24개 항목)
- SQL Performance Details: Full Text, Plan, Last Statement
5. 모니터링 항목 종합 정리
| 카테고리 | 주요 수집 지표 |
|---|---|
| 인스턴스 성능 | Active Sessions, Locks Waiting, Open Transactions, Page Lookups/Reads/sec, Page Life Expectancy, Batch Requests/sec, Transactions/sec, User Connections, Buffer Cache Hit Ratio, SQL Compilations/sec |
| 세션 모니터링 | Elapsed Time, Blocking Session ID, Command Type, Status, SQL Wait Type/Time, CPU Time, Logical/Physical Reads, Tempdb Alloc/Dealloc, DOP, SRS |
| SQL 분석 | Execution Count, Elapsed Time, Worker Time, Logical/Physical Reads, Rows (Last/Min/Max/Avg), Plan Hash, Creation Time |
| Wait 분석 | Waiting Tasks Count, Wait Time, Signal Wait Time (각 Delta/Delta per sec 포함) |
| 데이터 공간 | Database/File Size, Used Size, VLF Count, Increment(24h), Top 30 Table, Recovery Model |
| Lock 분석 | Lock Resource Type, Request Type/Status/Mode, Wait Resource |
| 파라미터 | 전체 파라미터 현재값, 변경 이력 비교 (Base Date vs Compare Date) |
| 알람 | Metric/System Alert, Warning/Critical 레벨, 히트맵 이력 |
6. 주의사항 및 참고 정보
모니터링 방식 선택: 기본
procedure모드는 maxgauge DB를 생성하며, Azure 환경에서는query모드를 사용하여 별도의 DB 생성 없이 모니터링이 가능합니다.버전별 권한 차이: SQL Server 2022 이상/Azure에서는
##MS_serverStateReader##역할을 사용하고, 2012~2019에서는view server state권한을 직접 부여해야 합니다.Session Kill 기능: Active Session 및 Lock Info 탭에서 Multi Kill 기능을 사용하려면
kill database connection권한이 별도로 필요합니다.Table Size 수집:
tablesizeinfomessage데이터 수집을 위해서는VIEW DEFINITION권한이 추가로 필요합니다.Plan Diff 기능: Plan Analysis에서 서로 다른 Plan Hash Value 간의 차이를 비교할 수 있어, 실행 계획 변경으로 인한 성능 저하 원인 분석에 유용합니다.
SRS (Session Response Score): Search Session과 Trend Analysis의 Session 탭에서 과거 수행 이력 대비 수행 시간 지연 여부를 SRS 값으로 확인할 수 있습니다.