본 글은 Brad McGehee 님이 기고한 글을 번역한 문서입니다.

작성된지는 조금 오래(2005년) 되었지만 DB 서버의 성능을 측정하는 지표로 활용하는데에는 전혀 부족함이 없어 보입니다.

물론 SQL Server 버젼별로 성능 카운터가 더 많이 추가 되어서 버젼별로 상세한 상태를 확인 하는 부분에 대해서는 MSDN 의 On-Line 문서를 확인 하시는 것도 좋을 것 같습니다.

 번역 내용에 오역이 있더라도 널리 양해를 구합니다. (본문도 첨부합니다. )

Tips for Using SQL Server Performance Monitor Counters

SQL Server Access Methods object: Page Splits/sec

SQL Server 에서 I/O 를 초과하는 이유 중 하나는 page splitting이다. Page splitting 은 index나 data page가 꽉 찾을 때 현재 page와 새로 할당 된 page 사이에서 발생한다.  일반적으로 page splitting 이 일어 나는 동안 디스크 I/O를 초과할 수 있고 성능을 저하 시키게 된다.

만약 서버상에서 많은 수의 page splits 를 발생하는 것을 확인하길 원한다면 SQL Server Access Methods object: Page Splits/sec 으로 모니터링 할 수 있습니다. 만약 page splits 가 높다면 index 들의 fill factor 를 증가시키는 것을 고려 할 수 있습니다. Fill factor 의 증가는 데이터를 채우기 전의 방(pages)을 더 많이 만든 다음 page split 이 발생되기 때문에 성능의 향상을 가져올 수 있습니다.

높은 수치의 Page Splits / sec 은 무엇을 의미하는가 ? 거기에 대한 간단한 답은 없습니다. 그것은 시스템상의 I/O subsystem상의 무엇인가로부터 영향을 받는 다는 것입니다. 하지만 일반적인 기본 조건에서 디스크 I/O 성능의 문제를 발견한다면 이 카운터는 보통 100 이상의 수치를 나타낼 것이고 그 때 Fill factor 의 증가가 성능의 향상을 가져올 수 있습니다.

*****

QL Server Buffer Manager Object: Cache Size (pages)

만약 서버상에 얼마나 많은 물리적인 RAM 이 SQL 서버의 data cache 에 할당되었는지 보고 싶다면 SQL Server Buffer Manager Object: Cache Size (pages) 으로 확인 할 수 있습니다. 이 수치는 page들 안에서 표현됩니다. 그렇기 때문에 8K(8,192) 단의로 확인할 수 있으며 이것으로 RAM의 몇 K가 할당되어져 있는지 확인 할 수 있습니다.

일반적으로 이 수치는 서버 전체 RAM 의 사이즈와 유사한 것이 좋고 NT, SQL Server나 기타 다른 유틸리티들의 ram 보다 작습니다.

만약 특정 양의 RAM을 데이터 캐시에 할당하였다면 당신이 예상하는 것보다 그 사이즈는 작을 것입니다. 이럴 때 당신을 그 원인을 찾기 위해 확인을 해 볼 필요가 있습니다. 아마도 당신은 SQL Server가 동적 RAM을 할당하거나 SQL Server가 순간적으로 성능 최적화을 위해 적은 양의 RAM으로 이 사용되는 것을 원하지 않을 것입니다. 어떤 이유에서건 이것에 대한 해결책이 필요하고 데이터 캐시의 양이 SQL Server는 SQL Server의 성능에 영향을 미친다는 사인이 될 수 있습니다.

실제로는 다른 항목으로 SQL Server의 메모리가 부족하지 않는지 확인 하는 더 좋은 방법이 있기 때문에 이 카운터를 보기 위해 많은 시간을 허비하지는 않습니다.

*****

SQLServer: SQL Statistics: Batch Requests/Sec 

SQL Server가 얼마나 바쁜지 피부로 느끼기 위해 확인하는 카운터는 SQLServer: SQL Statistics: Batch Requests/Sec  입니다. 이 카운터는 SQL Server 초당 수신한 batch request들의 수를 나타내고 일반적으로 서버의 CPU가 얼마나 busy 한지를 측정하는 용도로 이용합니다. 일반적으로 말하지만 초당 1000 batch request 가 넘는다면 이것은 매우 busy 한 상태의 SQL Server를 나타내고 있고 만약 CPU 병목현상을 이미 경험하지 못하였다면 곧 그러한 상황이 닦칠 수 있다는 의미이기도 합니다. 물론 이것은 상대적은 수치이고 더 좋은 하드웨어를 갖고 있다면 더 많은 수의 batch request 를 처리 할 수 있습니다.

네트웍 병목 현상으로는 일반적으로 100 Mbps 네트웍 카드는 일반적으로 초당 3000 batch request 만 가능합니다. 만약 이 서버가 이 정도로 busy 상태를 유지한다면 네트웍 카드를 하나 더 추가하거나 1 Gbps의 네트웍 카드로 변경하는 것이 필요합니다.

일부 DBA 들을 SQLServer: Databases: Transaction/Sec: _Total  를 SQL Servver 의 전체 활동량을 측정하기 위해 이용합니다.  하지만 이것은 그리 좋은 생각이 아닙니다. Transaction/Sec  은 모든 activity 를 측정하지 않고 Transaction 안에 있는 activity 만을 측정하게 됩니다. 그렇기 때문에 Transaction/Sec  대신에 항상 SQLServer: SQL Statistics: Batch Requests/Sec 카운터를 이용하여 측정하는 것이 좋습니다.

*****

SQLServer: SQL Statistics: SQL Compilations/Sec

Transact-SQL 코드의 SQL 컴파일이 발생하는 것은 SQL Server가 수행하는 정상적인 영역의 일부입니다. 하지만 컴파일은 CPU 와 기타 리소스를 많이 사용하기 때문에 SQL 은 가능하면 캐시 안의 수행 계획 (execution plan)안에서 재수행 할 수 있도록 작성하는 것이 좋습니다. (execution plan은 컴파일이 발생할 때 생성됩니다.)  더 많은 실행 계획들이 이용됐다는 것은 서버상의 더 적은 오버헤드를 유발시키고 성능을 더욱 향상시키게 됩니다.

서버상에서 얼마나 많은 compilation이 발생했는지 확인하기 위해서는 SQLServer: SQL Statistics: SQL Compilations/Sec 카운터를 이용할 수 있는습니다.  예상할 수 있듯이 이 카운터를 초당 얼마나 많은 컴파일이 발생했는지는 나타냅니다.

일반적으로 초당 100 compilation 이상 발생한다면 서버상에 불필요한 오버헤드를 발생하고 있다는 것입니다. 수치가 높다는 것은 서버가 의미없이 바쁘거나 수행중에 불필요한 재컴파일이 발생한다는 것입니다. 예를 들어 object의 스키마가 변경되었거나 병렬 실행 계획이 질렬로 수행된다거나, 통계가 재계산되었거나 다른 일들이 발생하였다면 SQL Server는 강제적으로 재컴파일 할 수 있습니다. 어떤 경우에는 불필요한 컴파일을 숫자를 줄일 수 있습니다. 이 곳에서 page 좀 더 자세한 내용을 확인 할 수 있습니다.

*****

SQLServer: Databases: Log Flushes/sec counter 

SQLServer: Databases: Log Flushes/sec counter  은 초당 로그가 디스크에 쓰여지는 (flush) 되는 수치를 나타냅니다. 이것은 데이터베이스 레벨별로 또는 전체에 대해서 측정할 수 있습니다.

정확히 log flush 란 무엇일까요 ? 이것을 설명하는 가장 좋은 방법은 예를 드는 방법입니다. 예를 들어 당신이 10 개의 INSERT 문을 수행하는 트랜잭션을 시작한다고 가정해 본다면 첫 번째로 INSERT 가 실행되고 새로운 데이터가 데이터 페이지로 삽입됩니다. 이것은 동시에 2가지가 발생됩니다. 이 때 버퍼 캐시 안에 있는 데이터 페이지는 새로운 INSERT Row 데이터로 갱신되는 것과 그리고 로그 파일을 위해 데이터를 책정합니다. 이 것은 트랜잭션이 완료될 때까지 계속됩니다. 동시에 로그 캐시로부터의 이 트랜잭션을 위한 데이터는 즉시 log file 에 기록합니다. 하지만 버퍼 캐시에 머물고 있는 데이터는 다음 번 checkpoint 프로세스가 실행되기 전까지 남아 있게 되고 checkpoint 가 실행될 때 데이터 베이스는 새로운 INSERT row들이 갱신합니다.

그 동안 log cache 라는 부분에 대해서 전혀 들어보지 못 했을 수도 있지만 log cache는 SQL Server 가 데이터를 log file 에 쓰기 위해 기록하는 메모리 위치입니다. Log cache 의 목적은 한 트랜잭션을 commit 하기 전에 roll back 하는데 이용하기 위해 사용 하는 것으로 매우 중요합니다.  하지만 단지 한 트랜잭션이 완료되면 (더 이상 롤백은 할 수 없음) log cache 는 물리적 로그 파일로 즉시 방출(flush) 하게 됩니다. 이것은 일반적인 처리입니다. SELECT 쿼리들은 데이터를 수정할 수 도 없고 트랜잭션을 생성할 수 없고 log 방출 역시 하지 않는 다는 것을 기억하십시요.

본질적으로 로그 방출(Log Flush)은 log cache 로부터 물리적인 log file 로 기록될 때 발생합니다.  그래서 기본 성질은 log flush 는 트랜잭션이 완료될 때마다 발생되게 되고 여러 개의 트랜젼션의 수와 관련되어 SQL Server에 의해서 발생되게 됩니다. 그리고 예상하듯이 log flush 의 사이즈 (얼마나 많은 데이터를 물리적 디스크로 기록할 것인가에 대해) 는 트랜잭션에 따라 매우 다양해 집니다. 이 정보는 어떤 의미를 우리에게 전달할까요 ?

이미 우리는 disk I/O 의 병목에 대해서 경험하고 있다고 가정해 봅니다. 하지만 우리는 그에 대한 원인을 전혀 모르고 있는 상태이구요. 디스크 I/O 병목에 대해서 문제 해결을 할 수 있는 한 가지 방법은  초당 기록되는 log flushed 카운터(Log Flushes/sec counter)를 캡쳐 하고 얼마나 busy 상태인지 확인 하는 것입니다. 이미 예상할 수 있듯이 만약 서버가 많은 수의 트랜잭션을 처리하고 있다면 그것은 또한 많은 수의 log flush 가 발생하고 있다는 의미이고 그 수치는 트랜잭션을 생성하는 action-type 쿼리들이 얼마나 busy 상태인지를 나타내는 서버들간의 수치로 확인 됩니다. 당신이 이 정보로 하고자 하는 것은 당신의 서버에서 예상했던 것보다 더 많은 수치의 log flush 가 발생한다는 상황입니다.

예를 들어 한 테이블에 매일 백만건의 INSERT 가 발생하고 거기에는 insert 하는 여러 가지 다른 방법이 있습니다.  첫 번째로 각각의 row가 개별적으로 insert 될 수 있고 각 INSERT 문장은 sinlgle transaction 으로 wrapped 되어 있습니다. 두 번째는 모든 INSERT 문장은 하나의 transaction 문장 안에 포함되어 있습니다. 그리고 마지막으론 INSERT 문장이 다중 트랜잭션(multiple transaction) 문장안에 나뉘어져 들어가 있고 어디선가 1에서 1,000,000 까지의 INSERT 하게 됩니다. 이 세 가지의 경우는 옵션이 다르고 SQL Server 상에서 작용하는 효과 역시 다르게 나타나고 초당 log flush 의 수 역시 다르게 나타납니다. 덧붙이자면 이것은 그렇게 생각하던 아니던 당신의 프로세스에서 하나의 트랜잭션만 발생한다고 착각하기 쉬운 경우입니다. 대부분의 사람들은 하나의 프로세스는 하나의 트랜잭션만 유발한다고 생각하는 경향이 있습니다.

자.. 첫 번째 경우 백만건의 row 를 백만번 트랜잭션으로 INSERT 하게 되면 거기에는 또한 백만번의 log flush 가 발생합니다. 하지만 두 번째의 경우에는 백만건의 row 가 단 하나의 트랜잭션안에서 INSERT 하게 될 것입니다. 그리고 이 경우에 log flush 는 한 번만 발생하게 됩니다. 그리고 세 번째의 경우에는 log flush 의 수치는 트랜잭션의 수와 동일하게 됩니다.  사실 log flush 의 크기는 한 트랜젹션으로 처리하는 것보다는 백만 건의 트랜젼션으로 처리하는 것 보다 커질 것이다. 하지만 대부분의 경우에 이것은 여기에서 설명하듯이 성능을 측정하는 것에는 중요하지 않다.

자.. 어느 옵션이 가장 좋을까 ? 모든 경우에서 당신은 여전히 많은 disk I/O 를 유발시킬 것입니다. 백만건의 row를 처리 할 것이라면 이 상황을 개선시키는 확실한 방법은 없을 것이다. 하지만 하나 또는 몇 개의 트랜잭션으로만 처리한다면 log flush 의 수치를 줄일 수 있고 이로 인해 disk I/O 를 상당 부분 줄임으로 인해 I/O 병목에 대해 개선할 수 있을 것입니다.

 이것으로 우리는 이것을 통해 2가지를 확인 할 수 있었습니다.. 첫 번째는 최대한 log flush 를 줄이기를 원한다는 것이고 이는 결국 당신의 서버상에서 트랜잭션의 수를 줄이는 것이 성능 개선의 방법이라는 것이다.

*****

SQL Server General Statistics Object: User Connections

SQL Server를 여러 명의 사용자들이 이용하기 시작한 이후부터 성능을 확인 하기 위해 SQL Server General Statistics Object: User Connections 수치를 눈여겨 볼 것이다. 이 것은 현재 SQL Server에 연결된 사용자의 수를 나타낸다. ( 전체 유저들의 수를 의미하는 것이 아니다. )

이 수치를 관찰할 때 한 유저가 여러 개의 연결(connection) 을 유지할 수 있고 여러 명의 사람들이 한 사용자의 connection 을 공유할 수 있다는 것을 주의해라. 이 수치가 실제 유저의 수치를 나타낸다고 착각하자 말아라 !! 대신 연관되어 측정할 수 있는 얼마나 “busy” 인가를 확인해라.  이 수치는 과거에 비해 서버가 얼마나 더 많이 이용되고 있는지 또는 얼마나 더 적게 이용되고 있는지만을 느끼는 정도로 이용해라.

만약 당신의 데이터베이스가 데드락(deadlocks) 으로 어려움이 있다면 SQL Server Locks Object: Number of Deadlocks/sec 값으로 측정할 수 있다. 하지만 이 수치가 상대적으로 높지 않다면 측정은 초 단위로 진행되고 인지할 수 있는 단지 몇 개의 데드락만을 측정 할 수 있기 때문에 더 많은 정보를 보기를 원할 것이다.

하지만 여전히 이 것은 데드락 문제를 갖고 있는지 확인 하는 데 유용하다. 더 구체적으로 확인하기 위해서는 SQL 프로필러의 데드락 추적 능력을 이용해라. 프로필러는 더 많은 상세한 정보를 제공할 것이다. Deadlocks/sec counter 의 수치를 확인해서 일반적인 큰 그림을 그리는데 사용하고 그 정보를 기초로 어떤 부분에 문제가 있는지 확인하기 위해서는 프로필러의 좀 더 상세한 분석을 통해 문제점의 “drill” down 할 수 있도록 이용해라.