SQL Server 전체 성능
SQL Server 전체 상황을 대략적으로 모니터링 할수 있는 카운터들
Object(Instance) Counter
--------------------------- ----------------------------------------------------
SQLServer:Access Methods FreeSpace Scans/sec
Full Scans/sec
Table Lock Escalations/sec
Worktables Created/sec
SQLServer:Latches Total Latch Wait Time (ms)
SQLServer:Locks(_Total) Lock Timeouts/sec
Lock Wait Time (ms)
Number of Deadlocks/sec
SQLServer:SQL Statistics Batch Requests/sec
SQL Re-Compilations/sec
SQLServer:General Statistics Processes Blocked
User Connections
Temp Tables Creation Rate
Temp Tables for Destruction
6.1 인덱스 누락 모니터링
Object(Instance) Counter
--------------------------- -----------------------------------------------------
SQLServer:Access Methods Full Scans/sec
* Full Scans/sec
테이블이나 인덱스의 초당 풀 스캔 수.
데이터량이 적을 경우에는 전체 테이블스캔(풀 테이블스캔)이 유리한 경우도 있지만 대부분의 풀스캔은 문제의 원인이 되며 주요 원인은 다음과 같다.
- 누락된 인덱스
- 과도한 데이터 로우 수 요청. where절에서 적절하게 제한하지 않은 경우 등등
- 선택도가 높지 않은 예측
- 적절하지 않은 T-SQL
- 데이터 분포 또는 수량이 seek 연산을 지원하지 않거나 초과하는 경우.
확장 이벤트와 같은 모니터링 기능을 통해 풀스캔 발생시키는 문제 쿼리를 찾아 내야 한다. 인덱스가 누락, 요청 된 행이 너무 많은 경우, T-SQL 형식이 잘못된 쿼리와 같은 경우들이 테이블 이나 인덱스의 풀 스캔을 유발하는 주요 원인이다. 이렇게 되면 CPU 타임과 논리적 읽기가 지나치게 발생하게 되므로 문제가 된다.
임시 테이블에 인덱스가 없어서 대량으로 풀스캔 하는 저장 프로시저의 경우에도 이 카운터가 도움이 된다.
* DMO(Dynamic Management Objects)
동적 관리 뷰 sys.dm_db_missing_index_details를 이용해 누락된 인덱스를 조사할 수도 있다. 이 DMV는 실행중인 쿼리의 실행 계획을 바탕으로 인덱스 후보를 제안해 주며 보통 "누락 인덱스 기능"이라고 부른다.
이 DMV는 캐시에 저장된 실행 계획을 기반으로 하며 이를 이용해 신규 인덱스를 작성할지 말지 결정할 수 있다. 누락 인덱스는 해당 쿼리에 대한 XML 실행 계획에도 표시 되지만 다음 장에서 더 자세히 다룬다. 이러한 뷰는 누락된 인덱스를 제안하는 데는 유용하지만 어떤 쿼리가 이 신규 인덱스를 사용하는 것이 맞는지에 대한 쿼리정보를 저장하지 않기 때문에 직접적으로 쿼리와 연결할 수 없다. 특정 쿼리와 연결하는 방법도 다음 장에서 설명한다. 다시 한번 말하지만 누락된 인덱스에 대한 제안이 정말로 효과적인지를 판단하기 위해서 신중한 사전 테스트는 꼭 필요하다.
누락 된 인덱스를 해결하기 전에 미사용 인덱스를 먼저 해결하는게 좋다.
sys.dm_db_index_usage_stats DMV를 사용해 인덱스의 사용/미사용 현황을 조사할 수 있는데 이 DVM는 SQL Server 인스턴스를 마지막으로 다시 시작한 이후의 인덱스 사용 통계정보를 보여준다. 안타깝게도 이 DMV 내의 카운터를 재설정하거나 제거하는 방법은 여러 가지가 있기에 인덱스 사용에 대한 100% 정확한 정보라고는 볼 수 없다. 하지만 하위 수준 DMV 인 sys.dm_db_index_operational_stats(파티션에 대한 현재 하위수준 정보)도 같이 이용하여 보다 정확한 미사용 인덱스 정보를 판별 할 수 있고 또한 경합 또는 I/O로 인해 인덱스가 느려지는 위치를 알 수도 있다.
이 두 가지에 대해서는 뒷 장에서 자세히 다룰 것이고 또한 Database Tuning Advisor의 제안이 특정 쿼리에 대한 특정 인덱스를 사용하는 데 도움이 될 수 있다.
6.2 데이터베이스 동시성
블로킹 같은 동시성 문제를 알아내기 위하여 다음 카운터가 중요하다.
Object(Instance) Counter
--------------------------- --------------------------
SQLServer:Latches Total Latch Wait Time (ms)
SQLServer:Locks(_Total) Lock Timeouts/sec
Lock Wait Time (ms)
Number of Deadlocks/sec
* Total Latch Wait Time (ms)
래치는 테이블 행과 같은 데이터 구조의 무결성을 보호하기 위해 SQL Server에서 내부적으로 사용되며 사용자가 직접 제어할 수 없다. 이 카운터는 최근의(마지막 1 초 동안) 대기한 총 래치 대기 시간 (밀리 초)이다. 이 카운터 값이 높으면 SQL Server가 내부 동기화 메커니즘을 기다리는데 너무 많은 시간을 소비하고 있음을 나타낸다.
* Lock Timeouts/Sec, Lock Wait Time (ms)
일반적으로 WAS나 클라이언트 어플리케이션을 운영하는 담당자들한테 "타임아웃이 많이 걸려 문제가 심각해요" 라는 말을 많이 듣는데 이때 같은 용어를 써도 서로가 느끼는 용어의 의미가 달라서 동문서답하는 경우가 많다.
개발자: 클라이언트 어플리케이션에서 쿼리가 중단되며 에러로그에 타임아웃이라고 찍혔기 때문에 DB타임아웃이라고 생각한다.
DBA : DB 타임아웃의 문제인지 아니면 클라이언트 어플리케이션 드라이버의 타임아웃인지 알기 어렵다.
결론적으로 말하면 클라이언트 어플리케이션 드라이버에서 발생하는 타임아웃이 대부분이다. SQL Server에서 타임아웃을 걸어놓는 경우는 거의 없고 나 역시 20년동안 극히 일부분을 제외하고는 걸지 않고 사용해 왔다. 대부분의 SQL Server는 쿼리가 완료될 때까지 몇일이고 돌아가게 놔두며 배치작업 1주일 걸렸다는 얘기도 자주 들리는 것을 보더라도 SQL Server단의 타임아웃 설정하는 경우는 매우 드물다.
어플리케이션 드라이버나 ORM같은 클라이언트 구성요소에는 항상 DB쿼리 타임아웃을 설정하는 부분이 있다. 혹시 본인이 모르는 경우라도 기본값이 30초든 3분이든 존재한다. DB를 잘 모르는 개발자들이 여기서 타임아웃이 발생하면 SQL Server의 타임아웃이라고 생각하는 것이다.
다시 한번 말하지만 SQL Server의 타임아웃은 거의 발생하지 않는다. 그렇다면 여기서 Lock Timeout/sec 은 무엇을 의미하는 것일까? SQL Server의 세션단위로 SET LOCK_TIMEOUT 사용할때 발생하며 기본값은 -1(무기한 대기)이기 때문에 이 값을 수정할때만 Lock Timeout이 발생하는것이다. SSMS같은 쿼리툴에서 SET LOCK_TIMEOUT 20 (20초) 라고 변경해서 지정할수 있고 이 값을 초과할때 성능 카운터에서 Lock Timeout/sec 으로 수집된다. 주로 저장 프로시저나 SQL Agent Job같은데에서 사용된다.
Lock Wait Time (ms)은 항상 매우 낮고 Lock Timeouts/sec는 보통 0이다. Lock Timeouts/sec 값이 0이 아니고 Lock Wait Time (ms) 값이 높을 때 데이터베이스에서 과도한 차단이 발생하고 있음을 나타낸다. 이 경우 두 가지 접근 방식을 채택 할 수 있다.
* 첫번째
SQL 프로필러, xevent, sys.dm_exec_query_stats중 아무거나 사용하여 고 비용 쿼리를 식별 한 다음 적절하게 튜닝해야 함.
* 두번째
차단 분석을 사용하여 과도한 차단의 원인을 진단.
이때도 고비용이 쿼리를 먼저 최적화하는 게 일반적으로 유리한데 다른 사용자에 대한 차단이 줄어들 기 때문이다. 그 이후 차단쿼리를
식별하고 해결하는게 올바른 순서이다. 뒷 장에서 차단을 분석하고 해결하는 방법을 알려준다.
* 기타
확장 이벤트는 차단 정보를 캡처하기 위해 임계 값을 활성화하고 설정할 수있는 blocked_process_report라는 차단 이벤트를 제공한다.
어느 정도의 잠금은 시스템의 필요한 부분이며 특정 수치 이상이 문제를 발생시키는지 기준을 설정하고 계속 모니터링해 가는 것이 좋다.
* Number of Deadlocks/Sec
보통은 이 카운터에는 0이 표시된다. 0이 아닌 값이 발생할 경우 희생된 쿼리를 재 실행하거나 교착 상태를 문제를 해결해야 한다. 뒷 장에서는 데드락을 식별하고 해결하는 방법을 보여준다.
6.3 재사용하지 않는 실행계획
실행 계획을 생성하는 과정은 많은 CPU 파워가 필요하므로 실행 계획을 재사용하면 CPU에 대한 스트레스를 줄일 수 있다. 저장프로시저는 실행계획을 재사용할 수 있는 좋은 방법이며 재 컴파일중인 저장 프로 시저의 수를 분석하려면 다음 카운터를 참조.
Object(Instance) Counter
--------------------------- -----------------------
SQLServer:SQL Statistics SQL Re-Compilations/sec
저장 프로 시저를 다시 컴파일하면 CPU에 오버헤드가 발생한다. 기존 측정 값에서 벗어나거나 일관되게 급증이라고 표시되는 경우 확장 이벤트를 사용하여 재 컴파일중인 저장 프로시저를 찾아내야 한다. 뒷 장에서 재 컴파일의 다양한 원인을 분석하고 해결하는 방법을 설명한다.
6.4 일반적인 활동
Object(Instance) Counter
--------------------------- ----------------------
SQLServer:SQL Statistics Batch Requests/sec
SQLServer:General Statistics User Connections
* User Connections
SQL Server 인스턴스에 현재 연결되어 있는 세션 갯수이며 성능치를 조사하는데 필요한 직관적인 지표중의 하나.
* Batch Requests/sec
SQL Server에 얼마나 많은 배치 쿼리들이 초당 들어오는지를 의미.
얼마나 많은 로드가 걸리는지 알 수 있는 직관적인 지표이며 이 수치가 증가할 수록 전체적인 사용률도 같이 증가하기 때문에 직접적인 관계가 있다. 하지만 빠른 쿼리와 느린 쿼리들이 항상 동시에 존재하기 때문에 이 숫자만으로는 객관적인 부하량을 알수 없으며 다른 카운터과 비교해야 한다. SQL Server의 사양이 다 다르기 때문에 일관된 기준도 없으며 서버별로 베이스라인 수치와 비교하여 판단해야 한다.
이 때 배치와 스테이트먼트(단일 sql 문장)의 정확한 의미를 알아야 한다.
DBMS별로 같지는 않지만 SQL Server에서 스테이트먼트는 단일 sql문을 말하고 배치는 여러개의 스테이트먼트가 GO라는 구분으로 분리되어서
묶음으로 한번에 들어오는 것이다.
만일 클라이언트에서 1개의 단일 sql문과 1개의 배치문(4개의 단일 sql이 묶인) 들어오면 수치는 2이지 5가 아니다.
그래서 이름도 배치 리퀘스트인것이다.