[MSSQL] 성능 모니터링 용어 & 개념 설명

오늘은 MSSQL 데이터베이스에서 자원 사용률에 대한 성능을 모니터링하는

"성능 모니터링 툴"의 용어들과 개념에 대해서 공부를 좀 해보겠습니다.

 

성능 모니터링을 사용하고 분석하기 위해서는 아래에 나오는 단어와 의미를 다 알고 있어야 합니다.

성능 모니터 실행은 [시작] – [실행] –[pefmon]을 클릭하면 됩니다.

 

 

Access Methods
1. Full Scans/sec
 - 초당 제한되지 않은 전체 검색 수입니다. 기본 테이블이나 전체 인덱스 검색이 될 수 있습니다.
 - 이 카운터는 기본 테이블 또는 인덱스에 대한 전체 스캔 수를 모니터링합니다. 
1 또는 2보다 큰 값은 테이블 / 인덱스 페이지 스캔이 있음을 나타냅니다. 
CPU가 높은 경우가 카운터를 조사해야 합니다. 
그렇지 않으면 전체 스캔이 작은 테이블에 있는 경우가 카운터를 무시할 수 있습니다. 
높은 전체 스캔 / 초의 주요 원인은 다음과 같습니다.
 누락된 인덱스
 요청된 행이 너무 많습니다.
누락된 인덱스가 있거나 요청된 행이 너무 많은 쿼리는 논리적 읽기 수가 많고 CPU 시간이 늘어납니다.

2. Table Lock Escalations/sec
대량의 데이터를 한 번에 처리하려고 하는 경우 발생한다. 이럴 경우 데이터 양을 나눠서 돌리는 것을 권장한다.
그래서 update나 delete 할 자료를 나눠서 처리하는 게 테이블 전체를 잠그는 것을 방지할 수 있다.(테이블 락이 걸리면 select X)
자원이 남는 경우에는 with(tablock) 옵션을 줘서 테이블 전체를 잠그도록 강제로 설정한 다음 빠르게 처리를 하는 경우도 있습니다.(업무시간 이후)

3. Worktables Created/sec 
 - 초당 만들어지는 작업 테이블 수입니다
 - 실제 테이블 말고 프로시저 구문 속에서 #테이블명 생성되는 temp테이블도 해당됩니다.

4. Forwarded Records/sec 
데이터 타입이 varchar 열이 있는 행은 varchar 값이 더 긴 문자열로 업데이트될 때 확장될 수 있습니다. 
행이 기존 페이지에 맞지 않는 경우 행이 마이그레이션 되고 행에 대한 액세스가 포인터를 통과합니다. 
이것은 힙 (클러스터형 인덱스가 없는 테이블)에서만 발생합니다. 
힙 테이블에 대한 클러스터형 인덱스를 평가합니다.

간단히 말하면 데이터를 찾으러 갔다가 거기서 찾기 못하고 또 다른 곳으로 찾으로 가는 시간을 의미합니다.
(힙 조각화 현상으로 인한 찾으로 가는 지연시간을 나타내는데 varchar(XX)로 만들 칼럼이 크기가 커져서 다른 곳으로 이동할 경우 발생)

힙 테이블: insert update 빠르고 select 느리다 (넌 클러스트 인덱스 생성 가능(999))
클러스트 테이블 : insert update 느리고 select 빠르다

5. Mixed page allocation/sec 
 - 혼합 익스텐트에서 초당 할당된 페이지 수입니다. 인덱스 또는 테이블에 할당 된 처음 8 페이지를 저장하는 데 사용됩니다.
이 값이 높으면 임시 테이블이 많이 만들어진다는 것이고(Worktables Created/sec 도 높아집니다.)-> tempDB가 많이 사용된다고 하면 SSD로 tempdb 이동하여 디스크 I/O를 줄이는 방법도 있습니다.
temp 디비만 uniformed extent (단일)로 변경하는 방법도 있음(SQL 2017에서는 DB 옵션 값으로 설정 가능)

데이터 : *. MDF( *. NDF)
로그 : *. LDF

Page : 성능 체크는 Page(8KB) * 수량을 얼마나 읽었느냐로 산정 (기본 값 8KB)
Extent : Page(8KB) * 8개 = 64KB 
Extent 종류는 2가지가 있다.

처음에 테이블은 혼합 테이블로 만들어짐
A타입 Mixed extent 테이블(혼합)
B타입 uniformed extent 테이블 (단일)

6. Page Splits/sec (초당 분할되는 페이지 수)
 - 인덱스 페이지 오버플로의 결과로 발생한 초당 페이지 분할 수입니다.
 - SQL Server가 과도한 페이지 분할을 일으키고 있는지 찾기 위해서 사용하는 항목입니다. 
   (페이지가 꽉 차 있어서 중간에 비집고 들어올 경우가 있는 경우 페이지 분할시켜서 다른 데로 옮겨지는데 이때 I/O가 생기면서 성능 이슈가 발생)
 - 과도한 Page Spilts/sec는 운영하는 시스템의 I/O하부 시스템에 따라 다르므로 이에 대해 간단히 답할 수 없습니다. 그러나 만약 평소에 디스크 I/O의 성능 문제가 발생하였고, 이 카운터 값이 100을 초과한다면 채우기 비율을 높여서 성능이 좋아질 수 있습니다. 만일 과도한 페이지 분할이 발생하고 있다면, 인덱스의 채우기 비율을 높게 설정하시는 걸 고려해야 합니다. 채우기 비율을 높게 설정하면 데이터가 가득 차거나, 페이지 분할이 발생하기 전에 데이터 페이지에 보다 많은  여유 공간이 있으므로, 페이지 분할을 감소시킬 수 있습니다.

 

SQL Statistics
1. Batch Requests/sec : 초당 쿼리 수량 (Go로 잘라서 나오는 처리의 묶음)
2. SQL Compilations / sec
 - 이 카운터는 초당 얼마나 많은 컴파일이 SQL 서버에 의해서 실행되었는지를 측정합니다. 말하자면, 이 카운터의 수치가 초당 100을 넘어서면, 불필요한 컴파일 오버헤드를 경험하고 계신 것입니다. 

이러한 높은 수치는 여러분의 서버가 매우 바쁨을 나타내거나, 불필요한 컴파일들이 실행되고 있다고 볼 수 있겠습니다. 이 수치가 높으면 adhoc 쿼리가 너무 많을 수 있으니, 의심해 봐야 합니다. 병렬 실행 계획이 직렬로 실행되거나, 통계가 다시 계산되거나 할 경우 해당 수치가 튈 수 있습니다.

Locks
1. Average Wait time(ms)
 - 잠금 때문에 평균 기다리는 시간
2. Lock Reqests /sec
 - 초당 잠금 요청 시간
3. number of Deadlocks / sec : 
 - 서로 막고 있어서 잠그고 있는 상황 (그래프가 0 이어야 정상) 
오라클은 롤백이라는 쿼리의 변수성이 있어서 트랜잭션 끝나기 전의 값을 가져오지만 
SQL은 쿼리 실행 시 바로 자동 commit으로 끝나버리기 때문에 변경될 값을 기다리며 락을 발생시킨다.
따라서 SQL 쿼리문 select시에 FROM 절에 NOLOCK 힌트를 쓰면 잠금이 없기 때문에 성능이 30% 상승됨

만약 모니터링 분성중에 데드락이 많이 발생하면 아래의 이유를 확인해보도록 합시다.
 - 내가 찾는 데이터가 어디 있는지 모르니까 테이블 전체를 잠가버림
   (원인은 인덱스가 적절하지 않거나 or 쿼리가 잘못되어 튜닝 필요하거나)


Buffer manager
1. Buffer cache hit ratio : 
 - 디스크에서 읽지 않고 버퍼 풀에서 찾은 페이지 비율입니다. 
   90% 가되어야 하며 낮으면 메모리를 늘려야 함.
2. Lazy writes / esc 
 - 버퍼 캐시에 올릴 메모리가 부족해서 메모리 칸을 를 비우는 액션입니다.
3. Page life expectancy(중요)
 - 데이터가 버터 캐시에 올라가서 머무를 거라고 생각되는 예상시간입니다.
    (높아야 성능이 좋고 낮으면 메모리가 부족하는 현상)

 

=======================================================================================

1. 메모리 사용량

Memory: Available Kbytes

사용 가능한 메모리량: 5000KB 이상 권장

 

Memory: Page Faults / Sec

초당 시스템에서 일어나는 페이지 오류의 평균적인 수

0에 가까울수록 좋다.

초당 2 이상의 페이지 오류가 발생하면 메모리를 추가해야 한다.

 

Memory: Pages / Sec

초당 시스템에 의해 디스크에서 읽거나 디스크로 쓴 페이지 평균값

5보다 작을 것을 권장함.

 

Memory: Cache Fault / Sec

Cache Fault는 Cache Manager가 즉각적인 캐시에서 페이지를 찾지 못할 때 발생

 

Process : Working Set / SQL Server 인스턴스

SQL 서버가 사용하는 메모리량: 5MB 보다 높아야 한다.

 

      

2. CPU 사용량

Processor: %Processor Time

CPU 사용율: 75%가 넘지 않아야 한다. (지수증가)

 

Processor: %User Time

응용프로그램이 사용한 CPU 사용률

 

System: Processor Queue length

프로세서 대기열에 있는 스레드 수: 2보다 작아야 한다.

 

System: Context Switches / Sec

컴퓨터의 모든 프로세서가 한 스레드에서 다른 스레드로 전환한 전체 횟수

CPU 당 5000 이 넘게 되면 Resource Contention Problem.

 

 

3. Physical Disk

Physical Disk: %Disk Time (Physical % Logical)

지속적인 시간 동안 55%를 넘지 않아야 한다.

 

Physical Disk: Avg. Disk Queue Length

대기열의 대기수: 최고 2를 넘지 않을 것을 권장.

 

Physical Disk: Avg. Disk Read Queue Length

대기열의 읽기요청 대기수

 

Physical Disk: Avg. Disk Write Queue Length

대기열의 쓰기요청 대기수

 

 

4. SQL 성능

SQL Server: Cache Manager / Cache Hit Ratio

캐쉬 적중률:90% 이상 권장 (미만시 메모리 추가, OLTP 시스템에서는 99% 권장)

 

SQL Server: Buffer Manager / Buffer Cache Hit Ratio

캐쉬 적중률:90% 이상 권장

 

SQLServer: Databases / Transactions/sec

데이터베이스에있는 모든 데이터 파일의 총 크기

 

SQLServer: Buffer Manager / Checkpoint pages/sec

검사점에의한 플러시된 페이지수

 

SQLServer: Access Methods / Skipped Ghosted Records/sec

고스트 레코드수

 

SQLServer: Access Methods / Page Splits/sec

페이지 스플릿 발생횟수

 

SQLServer: SQL Statistics / SQL Compilations/sec

초당 컴파일수

 

SQL Server General Statistics / User Connections

현재 연결된 사용자수: Maximum Worker Threads =255

 

다음은 계속적으로 모니터링하는 일반적인 성능 카운터입니다. 특정한 값이 한계에 도달하거나 유지되는 것을 관리자에게 알리도록 경보/경고를 설정할 수 있습니다.

Active Server Page, Requests Queued

이것은 대기열에서 서비스를 기다리는 요청 수를 모니터링합니다. 스트레스 상황에서 지연된 요청 수가 상당히 증가할 경우 프로세서 사용률은 비교적 낮게 남아있고 이것은 스크립트가 처리할 수 있는 것보다 많은 호출을 수신하는 COM 개체를 호출하고 있다는 표시입니다. 이러한 경우에 ASP에서 호출된 COM 개체는 일반적으로 장애가 됩니다. 사이트 개발자에게 알려주십시오.

 

Memory: Page Faults/sec.

5초 이상 지속되는 하드 페이지 실패는 RAM이 부족하다는 메시지로 중요한 표시입니다. 메모리 장애를 나타내는 다른 카운터로 Memory:Pages Input/sec, Memory:Page Reads/sec 및 Memory:Pages/sec을 들 수 있습니다.

 

Memory: .

시스템 운영에서 사용 가능한 실제 총 메모리를 측정하고 서버에서 모든 프로세스와 응용 프로그램을 실행하는데 필요한 메모리와 비교하십시오. 적어도 최고 사용 상태에서 사용할 수 있는 메모리의 10%를 유지하십시오. 기본적으로 IIS 5.0은 서버 컴퓨터에서 다른 응용 프로그램을 실행하는데 사용할 수 있는 메모리의 나머지를 남겨두고 파일 캐시에서 사용할 수 있는 메모리의 50%까지 사용한다는 점을 유의하십시오. 이것이 지속적으로 4MB 이하로 떨어지면 더 많은 메모리의 설치를 심각하게 고려해봐야 합니다.

 

Memory: Committed Bytes.

최고 작업 기간 동안 허용하는 비교치를 특정 시간 동안 추적해야 합니다. 적어도 4MB의 메모리 또는 커밋된 메모리가 사용할 수 있는 메모리의 5% 이상이 항상 있어야 합니다.

 

SQLServer: Cache Hit Ratio.

이것은 SQL 서버가 디스크에 액세스하는 것에 대한 캐시에서 데이터를 찾는 시간에 대한 비율입니다. 80%보다 적은 캐시 적중률은 SQL Server에 RAM이 부족함을 나타냅니다. 시스템에 RAM이 많이 있다고 해도 SQL Server에 충분한 RAM이 할당되지 않았다면 이러한 문제가 발생할 수 있습니다. SQL Server에 보다 많은 RAM을 제공하려면 sp_configure 저장된 프로시저 및 SQL Server Enterprise Manager(Sqlew.exe)를 사용하십시오.

 

Physical Disk: >% Disk Time.

선택한 디스크가 읽기 및 쓰기 요청을 제공하는데 사용되는 경과 시간 비율입니다. Physical Disk와 함께 Avg. Disk Queue Length는 디스크 드라이브 장애를 나타내는 중요한 표시입니다. 명령줄 유틸리티 Diskperf ?y를 실행한 후에 디스크 카운터를 추적해야 합니다.

 

Physical Disk: Avg. Disk Queue Length.

디스크가 읽기와 쓰기 요청을 수용할 정도로 빠르지 않으면 해당 요청은 대기열에 넣게 됩니다. Physical Disk: % Disk Time은 85% 이상이고, Avg. Disk Queue Length는 둘 이상이고, RAM의 부족으로 디스크 작업이 이루어질 수 없는 경우 디스크에 병목 현상이 발생할 수 있습니다. Physical Disk에 포함된 디스크 트래픽을 관찰할 수 있는 다른 카운터로 Disk Reads/sec, Physical Disk, Disk Writes/sec, and SQLServer, Log Writes/sec 등을 들 수 있습니다.

RAID 시스템과 같은 디스크 시스템에 보다 많은 물리적 드라이브의 추가를 고려해보십시오. 이것은 읽고 쓸 수 있는 스핀들 수뿐만 아니라 데이터 전송률 속도도 향상시켜 줍니다.

 

System: >% Total Processor Time.

이것은 프로세서가 사용 중인 시간에 대한 비율입니다. 이 카운터가 지속적으로 80%와 100% 사이에서 실행되고 있을 때 CPU 병목 현상의 중요한 표시가 됩니다. 보다 많은 프로세서의 설치를 고려하십시오.

 

System: Processor Queue Length.

이것은 프로세서 주기를 기다리며 대기하는 스레드 수의 순간적인(평균이 아닌) 계산입니다. 둘 이상으로 지속되는 프로세서 대기열 길이는 일반적으로 프로세서 정체를 나타냅니다. 보다 많은 프로세서의 설치를 고려하십시오.

 

SQLServer -Locks: Total Blocking Locks.

차단 잠금 수가 높으면 데이터베이스에서 핫스폿을 나타냅니다. 사이트 개발자에게 알려주십시오.

 

Process: Private Bytes.

이 프로세스가 할당한 현재 바이트 수는 다른 프로세스와 공유할 수 없습니다. 시스템이 특정 시간 동안 성능이 떨어지는 경우에 이 카운터는 메모리 누출의 좋은 표시가 될 수 있습니다. 사이트 개발자에게 알려주십시오.

 

Thread: Context Switches: sec: Inetinfo =>Thread#.

프로세서 당 스레드 또는 스레드 풀의 최대 수를 측정합니다. 너무 많은 컨텍스트 전환을 하지 않았는지 확인하려면 이 카운터를 모니터링해야 합니다. 컨텍스트 전환에서 손실한 메모리는 성능이 향상되기 보다는 감소하는 위치에 추가되는 스레드의 이점을 허용합니다. 초 당 15,000개 이상의 컨텍스트 전환에 대해서는 심각하게 고려해야 합니다.