성능베이스라인을 만들기 위해 최모군이 SSIS를 이용해서 자동화를 시키고 있는 중입니다. 해당 SSIS를 이용하여 자동화시키는 강좌는 SQLLEADER.COM에 가면 잘 정리되어 있습니다. 많은 시행착오를 거쳐 SSIS는 거의 완성단계에 왔고, 이제 어떤 성능카운터를 넣을지만 결정하면 되는 이 시점에서 SQL Server 성능 진단을 위해서는 어떤 성능 카운터를 수집을 해야 하는지에 대해서 정리를 해볼까 합니다.

Processor\%Processor Time : % Processor Time

이 성능카운터는 프로세서가 비유휴 스레드를 실행하는데 소비하는 시간의 백분율입니다. 이것은 프로세서가 각 샘플 간격 동안 유휴 스레드를 실행하는데 소비한 시간을 측정하여 간격 기간에서 그 값을 뺀 것입니다. 각 프로세서에는 유휴 스레디가 있는데 이것은 다른 어떤 스레드도 실행되지 않을 때 사이클을 소비하는 스레드입니다. 이 카운터는 프로세서 동작의 주요 표시기이며 샘플 간격 동안 관찰되는 사용 시간의 평균 백분율을 표시합니다. 이것은 서비스가 비활성인 시간을 모니터링하여 100%에서 그 값을 뺀 것입니다. 즉, 쉽게 말해서, 1시간동안 CPU상태를 관찰하였고, 1시간중에 30분간 CPU가 일을 하였다면, %Processor Time은 50% 입니다. 일반적으로 %Processor Time가 80%이상 사용되고 있으면, 관심을 가지고 사용량의 변화를 모니터링 하셔야 합니다.

Processor\%Privileged Time : % Privildged Time

이 성능카운터는 프로세스 스레드가 특권 모드에서 명령을 실행하면서 경과된 시간을 백분율로 표시한 것 입니다. Windows 시스템 서비스가 호출되면, 서비스는 시스템 전용 데이터를 액세스하기 위해 흔히 특권 모드에서 실행됩니다. 그러한 데이터는 사용자 모드에서 실행되는 스레드가 액세스하지 못하도록 보호됩니다. 시스템 호출은 페이지 오류 또는 인터럽트가 발생할 때처럼 명시적이거나 암시적입니다. 일부 초기 운영 체제와는 달리 Windows는 사용자 및 특권모드의 일반적인 보호뿐만 아니라 하위 시스템을 보호하기 위해 프로세스 경계를 사용합니다. 응용 프로그램을 대신하여 Windows에서 수행한 일부 작업은 프로세스의 특권 시간 및 다른 하위 시스템 프로세스에서도 나타납니다. 쉽게 말해서, 운영체제가 사용한 시간의 백분율 값 입니다.

Memory\Available Bytes : Available Bytes

이 성능카운터는 컴퓨터에서 실행되는 프로세스에 사용할 수 있는 실제 메모리의 양(byte)입니다. 이것은 0으로 채워 있거나 비어 있거나 대기 중인 메모리 목록에 있는 공간을 합해 계산합니다. 빈 메모리는 사용할 준비가 된 메모리이고, 0으로 채워진 메모리는 다음 프로세스가 이전 프로세스에서 사용된 데이터를 보지 못하도록 0으로 채운 메모리 페이지로 구성되며, 대기 메모리는 프로세스의 작업 집합(실제 메모리)에서 제거되어 디스크로 라우트되었지만 다시 호출되어 사용될 수 있는 메모리를 말합니다. 이 카운터는 최근에 관찰된 값만 표시하며 평균값은 아닙니다. 사용 가능한 여유 메모리는 많이 있으면 좋겠지만, 그렇지 않다면, 최소한 5Mbyte이상은 남아 있어야 합니다.

Memory\Page Faults/sec : Page Faults/sec

이 성능카운터는 프로세스가 사용하는 메모리 공간(Working set)에 존재하는 않는 코드나, 데이터를 요청할 경우에 발생합니다. 이 때 요청된 코드나 데이터를 다른 메모리 공간에서 찾으면 Soft Page Fault라고 하면, 디스크에서 찾게되면 Hard Page Fault라고 합니다. Page Faults/sec은 초당 발생한 Soft Page Fault와 Hard Page Fault의 합 입니다. Soft Page Fault값은 Page Faults/sec값에서 Pages/sec값을 빼면 됩니다. 따라서, Page Faults/sec의 값을 작을수록 좋다고 말 할 수 있겠으며, 상황에 따라서 그 임계치는 다르므로, 평상시에 모니터하여 적정 값을 파악해 두셔야 합니다.

Memory\Pages/sec : Pages/sec

하드 페이지 부재를 해결하기 위해 디스크에서 읽거나 디스크로 쓴 페이지의 비율입니다. 이것은 Memory\\Pages Input/sec과 Memory\\Pages Output/sec의 합 입니다. 메모리 공간이 부족하게 되면, 디스크의 페이징 파일로 메모리의 내용을 옮겨서 메모리의 여유공간을 확보하여 사용하게 되며, 또 필요시 페이징 파일에서 데이터를 메모리로 로드하여 처리하는 과정을 반복하게 되므로 성능이 저하되게 됩니다. Pages/sec 카운터 값이 20이상 지속되면 메모리 이상을 의심해야 합니다.

PhysicalDisk\%Disk Read Time

디스크가 읽기 작업을 수행한 시간의 백분율 입니다.

PhysicalDisk\%Disk Write Time

디스크가 쓰기 작업을 수행한 시간의 백분율 입니다.

PhysicalDisk\%Disk Time

디스크가 읽기 및 쓰기 작업을 수행한 시간의 백분율이며, 이 값은 Disk Read Time와 Disk Write Time의 합 입니다.

PhysicalDisk\Avg. Disk Queue Length

디스크의 읽기 및 쓰기 작업을 위해 대기중인 실제 디스크 큐 길이 입니다. 이 값은 디스크당 2를 초과하게 되면, 디스크 쪽 부하를 점검해야 합니다.

PhysicalDisk\Current Disk Queue Length

현재 시점의 디스크 읽기 및 쓰기 작업을 위해 대기중인 디스크 큐 길이 입니다. 이 값 역시 2를 초과하면, 디스크 쪽 부하를 점검해야 합니다.

Network Interface\Current Bandwidth

네트워크 인터페이스의 현재 대역폭입니다. 사용하는 네트워크 어댑터 카드의 지원 가능 대역이 100Mbps인 경우에 Current Bandwidth 값이 10Mbps이라면 네트워크 어댑터 카드의 속성 세팅이 잘 못 되었을 가능성이 큽니다.

Network Interface\Packets Outbound Errors

이 항목은 오류로 인해서 외부로 반출할 수 없는 패킷의 수를 나타냅니다.

Network Interface\Packets Received Errors

이 항목은 상위 계층의 프로토콜로 전달되지 못하도록 하는 오류를 포함하고 있는 외부로부터 유입되는 패키의 수를 나타냅니다.

Server\Bytes Total/sec

이 항목은 초당 서버가 네트워크에서 주고 받은 바이트 수 입니다. 동일 네트워크에 존재하는 서버들의 각각의 Bytes/sec 합이 네트워크 대역폭보다 크다면, 네트워크 분리를 고려하셔야 합니다.

SQLServer:Buffer Manager\Buffer cache hit ratio

SQL Server가 데이터를 디스크에서 읽지 않고 버퍼 풀에서 찾은 페이지의 비율입니다. 이 값의 높은 수치는 SQL Server가 데이터를 가져오기 위해서 하드디스크에 아주 가끔 액세스한다는 것이며, 이는 SQL 성능을 극대화 시킵니다.

SQL Server를 모니터하는 다른 카운터들과는 달리, 이 카운터는 SQL Server가 다시 시작한 시점 이후부터의 버퍼 캐시 히트율의 평균값 입니다. 즉, 이 카운터는 현재 시점의 측정 값이 아닌 SQL Server가 시작된 이후의 모든 날들의 평균값입니다. 현재 시점의 버퍼캐시가 어떤일을 하고 있는지에 대한 정확한 자료를 얻기 원한다면, SQL Server를 중지했다가 다시 시작해야만 하고, 정확한 버퍼캐시 히트율을 확인 하기 위해 SQL Server를 여러시간 동안 일반적인 활동을 하게 내버려 둬야 합니다.

만약, 최근에 SQL 서버를 재 시작 하지 않았다면, 여러분이 보고 있는 버퍼 캐시 히트율은 아마도 정확한 정보가 아닐 것 입니다. 또한, 버퍼캐시 히트율이 좋아 보일지라도, 오랜 시간의 평균값으로 계산되기 때문에 실제로는 좋지 않을 지도 모릅니다.

OLTP 응용프로그램 환경에서 이 수치는 통상 90%~95% 이상이어야 합니다. 그렇지 않다면, 여러분은 성능 향상을 위해서 서버에 RAM을 추가할 필요가 있습니다. OLAP 응용프로그램 환경에서는, OLAP작동하는 기본특성 때문에 이 수치는 OLTP보다 더 작을 수 있습니다. 어떤 경우라도, 더 많은 RAM은 SQL Server의 OLAP활동의 성능을 증가 시킬 것 입니다.

SQLServer:Buffer Manager\

디스크로 부터 데이터를 읽는 대신에 버퍼 캐시로부터 데이터를 가져온다면 SQL Server는 보다 적은 작원으로 보다 훨씬 더 빠르게 수행합니다. 몇몇 경우에, 메모리 집중적인 명령들로 인해 데이터 페이지들이 이상적으로 플러시 되기 전에 캐시 밖으로 밀려 나기도 합니다. 이는 버퍼캐시가 충분히 크지 않거나 메모리 집중적인 명령의 작업을 위한 더 많은 버퍼 공간 요구에 의해 발생할 수 있습니다. 이런 경우에는 버퍼에 추가 적인 공간을 만들기 위해서 플러시 된 데이터 페이지들은 버퍼가 아닌 디스크로부터 읽혀지게 되며 SQL Server 성능에 안 좋은 영향을 미치게 됩니다. SQL Server가 이러한 문제를 가지고 있는지 확인 하기 위한 3개의 SQL Server 카운터가 있습니다.

SQLServer:Buffer Manager\Page life expectancy

이 성능 카운터는 데이터 페이지가 얼마나 오랫동안 버퍼에 머무르는지를 평균적으로 초 단위로 나타내 줍니다. 만약 이 값이 300초 보다 작은 값을 보인다면, SQL Server의 성능 극대화를 위해서 추가적인 메모리가 필요함을 잠재적으로 나타내는 것 입니다.

SQLServer:Buffer Manager\Lazy Writes/Sec

이 성능 카운터는 버퍼공간을 비우기 위해서 지연기록기(LaxyWriter) 프로세스가 더티페이지들을 버퍼공간에서 디스크로 초당 얼마나 많이 옮겼는지 나타냅니다. 즉, 이 항목은 높은 값(초당 20정도)이어서는 안됩니다. 이성적으로는 0에 가까워야 합니다. 만약 이 값이 0이라면 여러분의 SQL Server는 아주 큰 버퍼 공간을 가지고 있고, 일정한 체크포인트가 발생하여 더티페이지가 반환되기를 기다리는 대신에, 더피페이지 반환을 하지 않아도 됨을 나타냅니다. 만약 이 값이 높다면 보다 많은 메모리가 필요함을 나타냅니다.

SQLServer:Buffer Manager\Checkpoint Pages/Sec

일반적으로 체크포이트가 발생하게 되면 모든 더티페이지들으 디스크에 쓰여지게 됩니다. 이것이 일반적인 절차이며, 체크포인트가 처리되는 동안 이 카운터가 증가하게 됩니다. 여러분은 이 카운터가 오랜 시간에 걸쳐서 카운터가 높아 지는걸 원치 않으실 것 입니다. 이는 SQL Server의 귀중한 자원을 많이 사용할 수 있는 체크포인트 프로세스가 보다 더 자주 실행됨을 나타냅니다. 만약 이 값이 높은 값을 가진다면, 빈번한 체크 포인트 발생을 줄이기 위해서 더 많은 RAM을 추가할 것을 고려하시거나, SQL Server의 구성옵션 중에 ‘복구 간격(recovery interval)’ 옵션 값을 늘려주십시오.
[관련글] Transaction 과 CheckPoint

SQLServer:Buffer Manager\Page reads/sec

모든 데이터베이스에 대해서 발생한 물리적 데이터 페이지의 초당 읽기 수를 나타냅니다. 물리적 I/O는 상대적으로 비용이 많이 발생하므로, 더 큰 데이터 캐시를 사용하거나, 인덱스 및 쿼리를 효율적으로 작성하거나, 데이터베이스 모델링을 다시하여 물리적 I/O비용을 줄여야 합니다.

SQLServer:Buffer Manager\Stolen Page

Windows 시스템이 SQL Server가 아닌 다른 응용프로그램의 요구를 충족시키기 위하여 얼마나 많은 페이지들이 SQL Server 데이터 캐시로부터 제거되었는지를 나타냅니다. Min Server Momory를 지정하여 SQL Server가 지정한 만큼의 SQL Server 전용으로 메모리를 할당하게 할 수 있지만, 그 만큼 다른 프로그램이 적은 메모리로 운영되게 되므로 페이징이 발생하게 됩니다. 따라서 메모리증설을 고려해야 합니다. (역주 : 보통 SQL Server가 운영되고 있는 하드웨어는 SQL Server만을 위해서 존재해야 합니다. 한가지 예를들자면, WEB 서비스를 하기 위해서 한 서버에 IIS 서비스와 SQL Server을 같이 돌리는 것은 좋지 않은 예 입니다. 즉, SQL Server에서 Min Server Momory를 지정하여 윈도우에서 SQL Server 메모리를 사용하지 못하게 하는게 좋습니다.)

SQLServer:Databases\Log Flushes/sec

이 카운터는 초당 플러쉬 된 로그 수를 나타냅니다. 하나의 로그플러쉬는 하나의 트랜잭션이 커밋되어 로그파일에 기록되는 것을 의미 합니다. 이 카운터는 데이터베이스 별로 측정 되거나, 단일 SQL Server의 전체 데이터베이스에 대한 값으로 측정 될 수 있습니다.

여러분은 로그캐시에 대해서 한번도 들어보지 못했을지도 모르겠습니다. 이것은 SQL Server서버가 로그파일에 쓰여질 로그데이터를 기록하는 메모리의 한 영역입니다. 로그캐시의 목적은 트랜잭션이 커밋되기 전에 특정상황이 발생하여 롤백 해야 하는 상황에서 트랜잭션을 롤백 하는 용도로 사용되기 때문에 매우 중요합니다. 그러나, 트랜잭션이 완료되면(완료되면 절대 롤백되지 않음) 로그캐시는 즉시 물리적인 로그파일로 플러시 됩니다. 이것이 정상적인 절차입니다. SELECT쿼리는 데이터를 수정하지도 않고 트랜잭션을 생성하지도 않고 로그플러쉬를 발생하게 하지도 않음을 명심하십시오.

본질적으로, 로그캐시에 있는 데이터가 물리적인 로그파일로 쓰여질 때 하나의 로그플러쉬가 발생합니다. 따라서, 하나의 트랜잭션이 완료될 때마다, 로그플러쉬는 발생을 하며, 많은 수의 로그플러쉬 발생은 SQL Server로부터 수행되는 많은 수의 트랜잭션과 관련이 있습니다. 그리고, 짐작하시는 것처럼 로그플러쉬(얼마나 많은 데이터가 로그캐시로부터 디스크에 기록이 되어졌는가?)의 크기는 트랜잭션에 따라 다릅니다.

우리가 디스크 I/O 병목현상을 격고 있고, 그 원인을 확신하지 못하고 있다고 가정합시다. 디스크 I/O에 대한 병목을 해결하기 위한 하나의 방법은 Log Flushes/sec 카운터 데이터를 수집하고, 이 과정을 처리하는데 얼마나 바쁜지 보는 것 입니다. 여러분의 서버에 많은 트랜잭션이 발생하고 있다면 로그플러쉬의 양은 당연히 많을 것 입니다. 따라서, 이 카운터 항목으로 보는 값은 트랜잭션을 발생하는 활동 형 쿼리가 얼마나 바쁜가에 따라 서버마다 다양할 것 입니다. 이 카운터 정보로써 여러분은 초당 발생하는 로그플러쉬 수가 운영하는 서버에서 예상되는 트랜잭션의 수 보다 확연하게 높은가에 대한 상황 판단에 도움을 줄 것입니다.

예를 들어, 매일 1,000,000행을 한 테이블로 삽입하는 작업을 한다고 가정합시다. 이 행들이 삽입되어질 수 있는 방법은 다양합니다.

첫째, 각 행은 따로따로 삽입되어 질 수 있습니다. 각 INSERT는 단일 트랜잭션 내부에 감싸집니다.
둘째, 모든 INSERTS는 단일 트랜잭션 내에서 수행되어 질 수 있습니다.
마지막으로, INSERTS는 1과 1,000,000사이의 어딘가에 여러 개의 트랜잭션으로 나누어 질 수 있습니다.

각 형태의 처리는 다르며, SQL서버와 초당 플러시 되는 로그 수에 매우 다른 영향을 미칩니다. 더구나, 프로세스가 멀티 트랜잭션으로 처리되고 있는데, 단일 트랜잭션으로 처리되고 있다고 착각할 수 도 있다. 많은 사람들이 단일 프로세스를 단일 트랜잭션으로 생각하고 있는 경향이 있습니다.

첫째의 경우에서, 만일 1,000,000행이 1,000,000개의 트랜잭션으로 삽입되어진다면, 1,000,000번의 로그 플러시가 발생할 것입니다.그러나, 두 번째 경우에는, 단일 트랜잭션에서 1,000,000행이 삽입되어 질 것이고, 단지 하나의 로그 플러시가 발생할 것입니다. 그리고, 세 번째  경우 에는 플러시 되는 로그의 수는 트랜잭션의 수와 같을 것입니다. 명백히, 로그 플러시의 크기는 1,000,000트랜잭션이 1트랜잭션보다 훨씬 클 것입니다, 그러나, 대개의 경우 성능의 관점에서 여기서 언급한 내용은 그다지 중요하지 않습니다.

어떤 옵션이 가장 좋은가요? 모든 경우에서, 많은 디스크 I/O를 유발할 것입니다. 1,000,000행을 핸들링 할 경우에는 I/O양을 줄일 묘안이 없습니다. 그러나, 하나 혹은 적은 수의 트랜잭션을 사용함으로써 로그 플러시 양을 많이 줄일 수 있을 것이고, 이는 디스크I/O양을 줄이게 되어, I/O병목 감소와 성능을 높여줄 것입니다. 우리는 두 가지 포인트를 배웠습니다. 첫째는, 여러분이 플러시 되는 로그 양을 가능한 많이 줄이길 원할 것이라는 것과, 둘째, 여러분의 서버에서 발생하는 트랜잭션의 수를 줄이는 것 입니다.

SQLServer:Databases\Transactions/sec

선택한 데이터베이스에서 발생한 초당 발생하는 트랜잭션 수를 나타냅니다.

SQLServer:General Statistics\User Connections

SQL Server를 사용하는 사용자 수가 많음에 따라 SQL Server 성능에 영향을 미치기 때문에 여러분은 SQL Server General Statistics Object : User Connections 카운터에 관심을 가질것 입니다. 이 카운터는 사용자 수가 아닌, SQL Server에 연결된 사용자 연결 수를 나타냅니다. 이 수치를 해석할 때, 하나의 단일 사용자는 여러 개의 연결들로 열릴 수 있음을 유념하십시오. 그리고 또한, 여러 명의 사람이 하나의 단일 사용자 연결을 공유할 수 도 있습니다. 이 수가 실제 사용자수를 나타낸다고 가정하지 마십시오. 대신에, 서버가 얼마나 바쁜가에 대한 상대적 척도로 사용하십시오. 여러 시간에 걸쳐서 이 수치를 모니터 해보시면, 서버가 많이 사용되고 있는지, 적게 사용되고 있는지 느낄 수 있을 것 입니다.

SQL Server:Latches\

래치는 본질적으로 “경량 잠금” 입니다. 기술적인 관점에서, 래치는 가볍고, 짧은 동기화 개체입니다. 래치는 마치 잠금 처럼 동작하고, 예상치 않은 변화로부터 데이터를 보호하기 위한 목적을 가지고 있습니다. 예를 들면, 하나의 행이 버퍼로부터 SQL서버의 저장소 엔진으로 이동될 때, 이 매우 짧은 시간 동안의 이동 중에 행 내부의 데이터 변형을 방지하기 위해서 SQL서버에 의해서 래치가 사용되어 집니다.마치 잠금과 같이, 래치는 데이터베이스의 행들에 대해 접근하지 못하게 SQL서버를 방해 할 수 있고, 이는 성능에 안 좋은 영향을 줍니다. 이러한 이유 때문에 여러분은 래치 시간을 최소화하길 원하실 것입니다. SQL서버는 래치의 활동을 측정하기 위한 3가지 다른 방법을 제공합니다.

SQL Server:Latches\Average Latch Wait Time(ms)

래치 요청들을 위해 대기해야 하는 시간입니다. 이는 오직 대기해야 하는 래치 요청들에 대한 측정값입니다. 대부분의 경우에 대기가 없습니다. 따라서, 이 값은 모든 래치에 대한 것이 아니라, 대기 해야 하는 래치에 대해서만 적용된 값임을 유념하십시오.

SQL Server:Latches\Latch Waits/sec

이 값은 즉시 승인 받지 못한 래치 요청수입니다. 다시 말해서, 1초 동안에 대기 해야 했던 총 래치의 수입니다. 따라서, 이는 Average Latch Wait Time으로 부터 측정된 래치들 입니다.

SQL Server:Latches\Total Latch Wait Time (ms)

이는 지난 초 동안의 총 래치 대기 시간 (ms) 입니다.

이 값을 읽을 때, 성능카운터에서 배율을 정확히 읽었는지 확인하십시오. 배율은 카운터 값마다 다르게 표시될 수 있습니다. 제 경험에 비추어 볼 때, Average Latch Wait Rime 카운터는 거의 변함이 없습니다. 반면에 다른 두 개의 카운터(Latch Waits/sec , Total Latch Wait Time (ms)) 는 SQL서버가 뭘 하느냐에 따라서 큰 변동폭을 보일 수 있습니다.

각각의 서버가 약간씩 다르기 때문에, 래치 활동도 각 서버마다 다릅니다. 전형적인 작업부하가 있을 때, 이 카운터에 대한 기준 값을 확보해 두시는 것은 아주 좋은 생각입니다. 이는 현재 어떤 일이 발생하고 있는가에 대해서 래치 활동이 평상시 보다 많은지 적은지에 대한 비교자료가 될 것입니다.

래치 활동이 기대치 보다 높다면, 이는 종종 하나 혹은 두 개의 잠재적인 문제점들을 나타냅니다. 첫째, 여러분의 SQL서버가 보다 많은 메모리를 사용할 수 있음을 의미할지도 모릅니다. 래치 활동이 높다면, 버퍼 캐시 히트 비율이 어떤지 확인하십시오. 이 값이99% 이하라면, 보다 더 많은 양의 메모리가 서버의 성능에 도움을 줄 것입니다. 만약 99% 이상이라면, 문제를 유발하는 것은 IO시스템일수도 있습니다. 빠른 IO시스템은 서버 성능에 유리합니다. 래치에 대해서 보다 더 많이 배우고, 실험해보고 싶으시면, 여기 두 개의 명령이 있습니다.

SELECT * FROM SYSPROCESSES WHERE waittime>0 and spid>50

이 쿼리는 현재 대기상태에 있는 waittype, waittime, lastwaittype, waitresource, SPID들을 표시해 줍니다. lastwaittype은 래치 종류를, waitresource는 SPID가 어떤 개체를 위해 대기 중인지를 알려줍니다. 이 쿼리를 실행하게 되면, 실행 시점에 대기가 발생하고 있지 않다면, 아무런 결과도 얻지 못 할 지도 모릅니다. 그러나 계속해서 실행하다 보면, 결국 몇몇 결과를 얻게 될 것입니다.

DBCC SQLPerf (waitstats, clear) --대기 통계초기화
DBCC SQLPerf (waitstats) -- SQL서버 재시작(대기 통계 초기화) 이후의 대기 통계정보

이 쿼리는 대기유형, 대기시간과 함께 현재의 래치들을 나타내줍니다. 여러분은 아마도 통계정보를 초기화하길 원할 겁니다. 그런 다음에는 어떤 래치가 가장 많은 시간을 차지하는지 알기 위하여, DBCC SQLPerf(waitstats)명령을 짧은 시간에 걸쳐서 정기적으로 실행하십시오.

SQL Server: Locks\Average Wait Time(ms)

만약에, 사용자들이 트랜잭션의 완료를 위한 대기시간 때문에 불만을 나타낸다면, 여러분은 개체 잠금이 이 문제가 되고 있는지 찾고 싶을 것 입니다. 문제점을 찾기 위해서, SQL Server Locks Object: Average Wait Time (ms) 카운터를 사용하십시오. 이 카운터는 database, extent, key, paLock Timeouts/secge, RID, table의 다양한 잠금에 대한 평균 대기 시간 정보를 측정합니다. DBA로써, 여러분은 평균 대기 시간이 얼마 정도까지 허용될 수 있는지 결정해야 합니다. 한가지 방법으로써, 개별 잠금 종류에 대해서 장시간 동안 이 카운터 항목을 모니터 하시고, 각 잠금 별 평균을 파악하시는 겁니다. 그리고 그 평균값을 참고 자료로 활용 하시는 거죠. 예를 들어, RID의 평균 잠금 대기시간이 500ms 라면, 500보다 큰 대기시간을 가지는 개체들은 , 잠재적인 문제점을 가지고 있다고 판단할 수 있을 것입니다. 특히 500보다 훨씬 크거나, 장시간 동안 연장되는 개체들은 더 쉽게 판단할 수 있습니다.

여러분이 트랜잭션 지연에 의한 단일 혹은 다양한 종류의 잠금을 확인 할 수 있다면, 어떤 트랜잭션들이 잠금의 원인이 되었는지 확인할 수 있는지 알기 위해서 조사하길 원할 것 입니다.

SQL Server: Locks\Lock Timeouts/sec

리소스에 잠금을 걸기 위해 대기하면서 타임 아웃된 잠금 요청 횟수를 나타냅니다.

SQL Server: Locks\Lock Waits/sec

즉시 충족시킬 수 없고, 잠금을 허가하기 위해서 호출자가 기다려야 하는 잠금 요청의 수

SQL Server: Locks\Number of Deadlocks/sec

만약 여러분들의 데이터베이스들이 데드락 문제로 괴로워하고 있다면, SQL Server Locks Object: Number of Deadlocks/sec 카운터를 통해서 추적할 수 있습니다. 그러나, 이 값이 상대적으로 높지 않다면, 이 값은 초단위로 측정되기 때문에 여러분은 더 많이 보기 원할 것입니다. 그리고, 눈에 띄게 보여지기 위해서는 다량의 데드락이 있어야 합니다. 그러나, 여전히 이 카운터는 여러분이 데드락 문제를 가지고 있는지 확인하기 위해서 가치있는 항목입니다. 차라리, 데드락을 추적하기 위해서 프로필러를 이용하십시오. 이는 보다 상세한 정보를 제공할 것입니다. 데드락 문제를 발견하기 위해서 Number of Deadlocks/sec 카운터를 활용하시고, 좀 더 세부적인 분석을 위해서 프로필러를 사용하십시오.

SQL Server: Memory Manager\Memory Grants Pending

작업 영역 메모리 허가를 위해 대기하고 있는 현재의 프로세스 수

SQL Server: Memory Manager\Target Server Memory(KB) & Total Server Memory(KB)

이 두 카운터를 관측하는걸 고려하십시요. SQLServer:Memory Manager: Total Server Memory (KB) and SQLServer:Memory Manager: Target Server Memory (KB). 첫번째 카운터 SQLServer:Memory Manager: Total Server Memory (KB) 는mssqlserver서비스가 메모리를 얼마나 사용하고 있는가를 말해줍니다. 이것은 SQL서버 Bpool영역으로 커밋된 전체 버퍼수를 포함하고, ‘OS in Use’ 로 표시되는 OS버퍼들도 포함합니다. 두번째 카운터, SQLServer:Memory Manager: Target Server Memory (KB)는 SQL서버가 얼마나 많은 메모리를 가용할 수 있는가를 나타냅니다. 이는 SQL서버가 시작시에 예약한 버퍼수에 기초합니다. 만약, Total Server Memory (KB)이 Target Server Memory (KB)보다 작다면, 이는 SQL서버가 충분한 메모리를 가졌고, 효율적으로 사용하고 있다는 것을 의미합니다. 반면에Total Server Memory (KB)이 Target Server Memory (KB)보다 크거나 같다면, 이는SQL서버가 메모리 압박을 받고 있고, 더 많은 물리적 메모리에 액세스 하고 있음을 나타냅니다.

SQL Server: SQL Statistics\Batch Requests/sec

SQL서버가 얼마나 바쁜지 알기 위해서, SQLServer: SQL Statistics: Batch Requests/Sec 카운터를 모니터 하십시오. 이 카운터는 초당 SQL서버가 받는 배치 요청 수를 측정하고, 일반적으로 서버의 CPU들이 얼마나 바쁜지 나타냅니다. 말하자면, 초당 1000배치가 넘어서면, SQL서버가 매우 바쁘다는 것을 나타내며, CPU병목 현상이 아직 나타나지 않고 있다면,조만간 CPU병목 현상이 나타날 것임을 알 수 있습니다. 물론 이 수치는 상대적인 것이며, 여러분의 하드웨어가 고 사양이라면, 보다 더 많은 초당 배치요청 수를 커버할 수 있을 것입니다. 네트워크 병목의 관점에서 보자면, 100Mbps 네트워크 카드는 초당 3000 배치 요청을 처리 할 수 있습니다. 만일 네트워크 병목이 심한 서버를 운영하고 계시다면, 네트워크 카드를 2개이상 늘리거나, 1Gbps 네트워크 카드로 교체 할 필요가 있을 것입니다.

몇몇 DBA들은 전체 SQL서버활동량을 측정하기 위해서 SQLServer: Databases: Transaction/Sec: _Total 카운터를 모니터 하는데, 이는 좋은 방법이 아닙니다. Transaction/Sec 카운터는 전체 활동량이 아닌 한 트랜잭션의 내부활동을 측정하며, 왜곡된 값을 나타냅니다. 대신에, SQL서버의 전체 활동량을 측정하는 SQLServer: SQL Statistics: Batch Requests/Sec 카운터를 사용하시기 바랍니다

SQL Server: SQL Statistics\SQL Compilations/sec

TSQL코드의 컴파일은 SQL서버의 일반적인 동작입니다. 그러나, 이 컴파일이 CPU와 다른 리소스들을 많이 잡아 먹기 때문에, SQL서버는 가능한 많은 실행계획을 캐시에 저장해서 실행계획이 컴파일 되지 않고 재사용되도록 시도합니다(실행계획은 컴파일이 발생할 때 생성됩니다). 보다 더 많은 실행계획이 재 사용 되어지면, 서버에 대한 부담은 더 적어지게 되며, 전체적인 성능은 더욱 더 향상 됩니다.

SQL서버가 얼마나 많은 컴파일을 하고 있는지 확인 하려면, SQLServer: SQL Statistics: SQL Compilations/Sec 카운터를 모니터 하십시오. 여러분이 기대하시는 것처럼, 이 카운터는 초당 얼마나 많은 컴파일이 SQL서버에 의해서 실행되었는지를 측정합니다.

말하자면, 이 카운터의 수치가 초당 100을 넘어서면, 불필요한 컴파일 오버헤드를 경험하고 계신 것 입니다. 이러한 높은 수치는 여러분의 서버가 매우 바쁨을 나타내거나, 불필요한 컴파일들이 실행되고 있다고 볼 수 있겠습니다. 예를 들어, 오브젝트의 스키마가 변경되거나, 병렬로 실행계획이 잡혀있던 것이 직렬로 실행되어야 하거나, 통계가 다시 계산되었다거나 하는 등의 이유로 SQL서버로부터 재 컴파일 하라는 지시를 받았을 수도 있습니다. 어떤 경우에는, 불필요한 컴파일을 줄이기 위해서 여러분의 노력이 필요할 수 도 있습니다. 만약, 여러분의 서버가 초당 100회 이상의 컴파일을 수행한다면, 이 원인이 여러분이 조절할 수 있는 것인지 아닌지 찾기 위해 애 쓰셔야 합니다. 너무 많은 컴파일은 SQL서버의 성능에 악영향을 끼칩니다.

SQL Server: SQL Statistics\Re-Compilations/sec

초당 SQL문이 재 컴파일 되는 수 (역주 : 컴파일이 되어서 버퍼에 올라가 있으면 SQL Server는 다시 컴파일을 하지 않고 버퍼에 있는 컴파일된 것을 사용하게 됩니다.. 하지만 어떠한 특정 이유로 그 SQL문들은 재컴파일을 하게 되는데, 앞서 컴파일 카운트를 보셨듯이 컴파일 자체가 Server의 리소스를 사용하는 것 이기 때문에 되도록이면 재 컴파일 이슈가 없는것이 좋습니다. 해당 카운트가 너무 높다면 프로파일러나 DMV로 일일이 살펴볼 필요가 있습니다.)

SQL Server Access Methods Object: Full Scans/sec

그런데 가끔 인덱스 탐색보다 테이블 스캔이 빠른 경우에, 일반적으로 적은 테이블 스캔이 보다 많은 테이블 스캔 보다 좋다. 여러분의 서버에서 얼마나 많은 테이블 스캔이 발생하는지 알아보기 위해서, SQL Server Access Methods Object: Full Scans/sec 카운터를 사용하십시오.이 카운터는 단일 데이터베이스가 아닌 전체 서버에 대한 값이라는 사실을 염두에 두셔야 합니다. 이 카운터 값으로 알게 될 사실 하나는 가끔씩 예측이 가능한 스캔 형태를 나타낸다는 것 입니다. 대부분의 경우에 이 값들은SQL서버가 내부적으로 사용하는 것 들입니다.

여러분의 응용프로그램에서 나타나는 불규칙적인 테이블 스캔들을 파악하길 원하실 것입니다. 과도한 테이블 스캔이 발생될지를 고려하기 위해서 프로필러 데이터를 수집하고 인덱스 튜닝 마법사를 통해서, 어떤 것이 원인이 되는지 결정 할 수 있게 도움을 받을 수 있습니다. 그리고 몇몇 인덱스를 추가함으로써 테이블 스캔을 줄일 수 있을 것 입니다. 물론 SQL서버는 이 작업을 훌륭하게 수행할 것이고, 더 효율적이라면, 인덱스를 사용하는 것 대신에 테이블 스캔을 수행 할 것입니다. 그러나 내부적으로 어떤 일이 발생하는지 찾아 보지 않는 한 여러분은 알지 못 할 것입니다.


해당 정보들은 아래 블로그에서 "펌"하여 정리한 것 입니다.
[출처] http://cid-f08341357ef26d93.spaces.live.com/blog/cns!F08341357EF26D93!166.entry
[출처] http://ceusee.springnote.com/pages/260330
[원문] http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
[번역] 김종균 (jkkim@techdata.co.kr)

Posted by 엘키 엘키
 TAG MSSQL, perfmon, SQL

댓글을 달아 주세요

서버 개발시에 DBMS의 지원을 받지 않는 것은 불가능에 가깝습니다. 

특히나 트랜잭션을 C/C++ 레벨에서 구현하기란 보통 어려운게 아닙니다. DBMS에서는 시스템 단에서 지원해주기 때문에 손쉽게 트랜잭션을 사용 할 수 있죠. 데이터를 저장하고 불러 오는 것 역시 파일 시스템보다 DBMS가 훨씬 더 효율이 좋죠.

물론 그것도 잘 만들고, 잘 사용하고, 잘 관리했을때 이야기지만 말이죠.

다음은 게임 서버에서 DBMS를 이용할 때의 유의 사항입니다.

1. 반드시 측정하라.

쿼리 프로파일러 등을 통해서 DB 부하를 측정하라. 

원하는 목표치를 수립하고, 그 목표치에 달성 할 수 있게끔 노력하라.

목표치가 너무 높거나 낮을 수도 있지만, 목표치가 없이 무작정 빠르게보다 동기부여도 되고, 성취감도 생기기 때문에 반드시 목표치를 두자.


2. 미리 테스트하라.
반드시 테스트 하라. 그리고 미리 테스트하라.

개발 과정에서 데이터를 임의로 생성하고, 측정하라.

테스트할 데이터가, 실제 유저들이 쌓을 데이터와 비슷하다면 금상 첨화다.

하지만 그렇지 않다하더라도 선행 테스트는 반드시 필요하다.


3. 쿼리 호출 횟수를 줄이는 것도 좋은 튜닝 방법이다.

물론 쿼리 자체가 느리다면, 쿼리 호출 횟수가 작아도 문제가 생길 수 있다.

하지만, 쿼리가 아무리 빠르더라도 쿼리 호출 횟수 자체가 많다면 그것이 부하가 될 수도 있다.


4. 같은 동작이 겹치는 상황을 줄여라.

같은 테이블에 동시에 접근하는 것은, 쿼리 수행시간 증가의 요인중 하나다.

단일 큐처리, 테이블 분산 등을 통해서 최대한 병렬 작업의 효율을 높이도록 하자.


5. 커넥션이 많다고 좋은게 아니다.

4번 항목과 연관성이 있는 내용으로, 커넥션이 많다고 능사가 아니다.

같은 테이블에 접근하는 SP가 여러개 커넥션에서 몰리면 블러킹으로 효율이 저하될 가능성도 높아진다.

진정한 병렬수행이 될 수 있도록 설계하고, 사용하라.

잘 사용하는 것은 잘 만든 것 만큼이나 중요한 것이다.


6. 적은량의 데이터만으로 테스트 하지 말아라.

2번 항목에서 언급했듯이, 테스트할 데이터가 실제 유저들이 쌓을 데이터와 비슷하면 비슷할수록 실제 서비스와 유사한 측정과 개선이 가능해진다.

실제 유저들이 쌓을 데이터가 아닌, 개발자와 QA 데이터 몇개만으로 테스트 하지 말아라. 그렇게하면 논리적 구현의 테스트는 될 지언정 효율에 대한 테스트는 불가능하다.


7. 트랜잭션을 적극 활용하라.

4,5번 항목과 조금 엇갈리는 내용이지만, 효율보다 예외처리가 더 중요하다.

트랜잭션을 잡아야 하는 동작이 있다면 반드시 트랜잭션을 잡아라. 효율을 위해 번거로운 예외처리를 서버에서 처리하게 된다면, 그로 인해 손해 보는 것 (예외 상황을 처리하는 개발 과정, 예외 상황 처리를 테스트 하는 과정, 예외 상황을 정상 처리로 수행 가능하게끔 만들기 위한 노력, 만약에 예외 처리에 실패했을 경우에 겪는 피해)들이 너무 크다.


트랜잭션을 잡게 되면 그만큼 효율은 떨어지지만, 논리적 오류로써 겪게 되는 문제들에서 해소 될 수 있다.

좋은 개발자는 단순히 속도만 생각해서 되는 것이 아니다. 무엇이 더 효율적인지 생각하라. 

Posted by 엘키 엘키

댓글을 달아 주세요

1 . 데이터의 결합방법

1) 가로로 연결 : JOIN

     - 의미 있는 연결을 위해서는 로우(ROW)의 공통 요소를 이용하여 연결할 경우 사용

2) 세로로 연결 : UNION

     - 컬럼(Colum)의 공통된 형식을 어기면 연결 불가

2 . JOIN

가로(수평적)로 하나의 결과 집합과 다른 결과 집합을 연결하는 것

(집합 : 테이블, 뷰, 인라인뷰, 테이블변수....)

조인의 종류

 ◎ INNER JOIN

 ◎ OUTER JOIN (LEFT , RIGHT 모두)

 ◎ FULL JOIN

 ◎ CROSS JOIN

 ◎ SELF JOIN

1) INNER JOIN

 두 집합간의 하나나 그 이상의 공통 필드들에 기반해서 레코드들을 일치

 INNER JOIN 은 배타적(exclusive) 결합 이다.

 두 집합(테이블) 모두에서 일치하는 것이 없으면 그 레코드는 반환되지 않는다.

 ▷ 구문

<ANSI>

 SELECT <선택_목록>

 FROM <결과집합1> INNER JOIN <결과집합2>  ON <결과집합1>.<조인조건1>=<결과집합2>.<조인조건2>

 

 SELECT <선택_목록>

 FROM <결과집합1> JOIN <결과집합2>  ON <결과집합1>.<조인조건1>=<결과집합2>.<조인조건2>

 

<T-SQL>

 SELECT <선택_목록>

 FROM <결과집합1> ,<결과집합2>  WHERE <결과집합1>.<조인조건1>=<결과집합2>.<조인조건2>

 ▷ 예제

       USE Pubs

       SELECT discounttype ,discount ,s.stor_name

       FROM discounts d JOIN stores s

                               ON d.stor_id = s.stor_id

1) OUTER JOIN

JOIN 이전에 나오는 테이블은 왼쪽(LEFT) 테이블이 되고 , JOIN 이후에 나오는 테이블이 오른쪽(RIGHT) 테이블이 된다.

LEFT OUTER JOIN 은 왼쪽 테이블로부터 모든 정보를 포함시키며

RIGHT OUTER JOIN 은 오른쪽 테이블로부터 모든 정보를 포함시킨다

 ▷ 구문

 <ANSI>

 SELECT <선택_목록>

 FROM <결과집합1> LEFT OUTER JOIN <결과집합2>  ON <결과집합1>.<조인조건1>=<결과집합2>.<조인조건2>

 

<T-SQL>

 SELECT <선택_목록>

 FROM <결과집합1> ,<결과집합2>  WHERE <결과집합1>.<조인조건1> *= <결과집합2>.<조인조건2>

 <ANSI>

SELECT <선택_목록>

 FROM <결과집합1> RIGHT OUTER JOIN <결과집합2>  ON <결과집합1>.<조인조건1>=<결과집합2>.<조인조건2>

 

<T-SQL>

SELECT <선택_목록>

 FROM <결과집합1> ,<결과집합2>  WHERE <결과집합1>.<조인조건1> =* <결과집합2>.<조인조건2>

 

▷ 예제

       USE Pubs

       SELECT discounttype ,discount ,s.stor_name

       FROM discounts d LEFT OUTER JOIN stores s

                               ON d.stor_id = s.stor_id

       USE Pubs

       SELECT discounttype ,discount ,s.stor_name

       FROM discounts d RIGHT OUTER JOIN stores s

                               ON d.stor_id = s.stor_id

 

※ OUTER JOIN 을 이용하여 일치하지 않는 레코드들 찾기

-- 할인 레코드를 가지지 않은 모든 상점들의 이름 찾기

 

       USE Pubs

       SELECT s.stor_name AS [Store Name]

       FROM discounts d RIGHT OUTER JOIN stores s

                               ON d.stor_id = s.stor_id

       WHERE d.stor_id IS NULL

3) FULL JOIN

JOIN 양쪽의 데이터를 모두 일치 시켜서 양쪽 모두를 포함시키는 것이다.

어느쪽에도 중점을 두지 않고 양쪽 모두의 데이터를 보려 할 때를 위하여 만들어진 것

간단히 FULL JOIN 은 RIGHT JOIN 과 LEFT JOIN 을 동시에 적용하는 것이라 할 수 있다.

 ▷ 구문

<ANSI>

 SELECT <선택_목록>

 FROM <결과집합1> FULL JOIN <결과집합2>  ON <결과집합1>.<조인조건1>=<결과집합2>.<조인조건2>

 

<T-SQL>

 SELECT <선택_목록>

 FROM <결과집합1> ,<결과집합2>  WHERE <결과집합1>.<조인조건1> *= <결과집합2>.<조인조건2>

 UNION

 SELECT <선택_목록>

 FROM <결과집합1> ,<결과집합2>  WHERE <결과집합1>.<조인조건1> =* <결과집합2>.<조인조건2>

▷ 예제

       USE Pubs

       SELECT discounttype ,discount ,s.stor_name

       FROM discounts d FULL JOIN stores s

                               ON d.stor_id = s.stor_id

4) CROSS JOIN

한쪽의 모든 레코드를 그 반대쪽의 모든 레코드들과 결합시킨다.

간단히 CROSS JOIN 은 '데카르트의 곱(Cartesian product)' 이라 할수 있다

CROSS JOIN 의 구문 구조는 ON 연산자가 없다는 점만 빼고는 다른 JOIN 들과 동일하다.

테스트용 데이터를 만드는 용도로 사용한다.

 ▷ 구문

<ANSI>

 SELECT <선택_목록>

 FROM <결과집합1> CROSS JOIN <결과집합2> 

 

<T-SQL>

 SELECT <선택_목록>

 FROM <결과집합1> ,<결과집합2> 

▷ 예제

       USE Pubs

       SELECT discounttype ,discount ,s.stor_name

       FROM discounts d CROSS JOIN stores s

5) SELF JOIN

필요에 의해 JOIN 구문에 같은 테이블이 두번 이상 등장하는 경우이다.

 

자신의 상급자를 매핑하기위해 SELF 조인을 사용한 예제이다.

▷ 예제

       USE Northwind

       SELECT a.EmployeeID, a.LastName, a.FirstName, b.EmployeeID, b.LastName, b.FirstName

       FROM employees a, employees b

       WHERE a.reportsTo = b.employeeid

2 . UNION

2개이상의 데이터 집합을 세로(수직적)로 연결하는 방법으로

결합되는 각 컬럼간의 형식이 일치하면 하나의 집합으로 도출 가능하다.

 

▷ 주의점

 ◎ UNION 으로 쿼리들을 결합할 때, 모든 쿼리들의 SELECT 목록에 있는 열들의 개수는 같아야 한다.

 ◎ 결합된 결과의 제일 처음에 나타날 헤더들은 오직 첫번째 쿼리에 의해서만 결합된다.

 ◎ 결합될 쿼리들의 각 열들은 동일한 데이터 형식이거나 적어도 묵시적으로 변환될 수 있는 것들이어야 한다.

 ◎ UNION 의 경우 기본적으로 DISTINCT 가 적용된다. 중복된 행을 표시하고 싶들떄는 UNION ALL 을 사용한다.

 

Northwind 에 관련된 모든사람에게 우편물을 보내고자 할 때의 UNION 을 통해 모든 사람의 주소를 가져오는 쿼리

▷ 예제

       USE Northwind

 

       SELECT CompanyName as Name, Address, City, Region, PostalCode, Country

       FROM Customers

       UNION

       SELECT CompanyName , Address, City, Region, PostalCode, Country

       FROM Suppliers

       UNION

       SELECT FirstName + ' ' + LastName , Address, City, Region, PostalCode, Country

       FROM Employees

 
3 . 요약

RDBMS 에서는 데이터를 둘 이상의 테이블에 분산시켜야 하는 경우가 많다.

따라서 JOIN 및 UNION 을 이용하여 여러 테이블들의 있는 데이터를 결합시켜 사용 할 수 있다.

 ◎ 일치하지 않는 열들을 제외하고자 할 경우에는 INNER JOIN 을 사용한다.

 ◎ 일치하는 것들을 뽑아야 할 뿐만 아니라, JOIN의 한 쪽에 있는 테이블의 모든 것들을 포함시키려 할 때는 OUTER JOIN 을 사용한다.

 ◎ 일치하는 것들을 뽑아야 할 뿐만 아니라, 양쪽에 있는 테이블의 모든 것들을 포함시키려 할 때는 FULL JOIN 을 사용한다.

 ◎ 두 테이블의 대해 행들에 기반한 데카르트 곱을 수행하려 할 때에는 CROSS JOIN 을 사용한다. 과학계산이 필요하거나 테스트 데이터를 생성할때 유용하다.

 ◎ 여러 쿼리들의 결과를 수직적으로 결합할 때에는 UNION 을 사용한다.

Posted by 엘키 엘키

댓글을 달아 주세요

1. Clustered Index만 존재했을 때
CI의 정렬 기준으로 실제 데이터를 정렬해 둡니다. 데이터를 찾을 때 CI를 타느냐, 테이블 스캔 하느냐의 차이만 존재합니다. 

1-2. 구조
루트페이지와 리프페이지의 2중 구조

루트페이지에는  검색기준이  리프페이지에는 실제 데이터가 검색기준에 맞쳐 분류되어있음

쿼리시  루트 페이지에서 해당 검색기준을 통해 리프페이지(실제데이터)를 검색하여 결과처리

데이터 변경작업시 루트페이지에 맞쳐서  리프페이지를 다시 분류작업함

2.
Non-Clustered Index만 존재했을 때

이 경우 NI의 키 값 대로 정렬된 데이터가 존재하고, 키 값에 대응하는 실제 데이터의 주소 (파일, 페이지, 행번호) 를 가집니다.

NI에서의 키 값에 매칭되는 값을 찾은 후, 실제 데이터를 찾아가는 과정만큼의 비용이 필요합니다.
이 비용이 크기 때문에, 일반적으로 NI를 이용해서 찾는 데이터가 전체 데이터의 3~5%이내 일 때만 NI를 이용하고, 그렇지 아닐 경우 테이블 스캔을 합니다.

2-1. 구조
루트페이지와 리프페이지. 실제데이터의 3중구조.

루트페이지에는 검색기준, 리프레이지에는 실제데이터를 참조한 분류 ,실제데이터에는 데이터

select 시에는 3중구조임으로 클러스터형 인텍스보다 비효율적임

그러나. 데이터 변경작업시에는 실제데이터에는 변경이 없음으로 클러스터형 보다 효율적.

3. Non-Clustered Index와 Clustered Index 공존시

이 경우 NI가 실제 데이터의 행번호를 가리키는 것이 아니라, CI의 키 값을 가리킵니다.

장점은 CI가 변경될 때, NI에 적은 영향을 줍니다. (CI로 정렬 되어 있는 만큼, 테이블 중간에 데이터가 끼어들거나 삭제되면 페이지 분할등이 일어나 행번호가 바뀔여지가 있습니다. 행번호가 바뀌어도 행번호가 아니라, CI의 키 값을 가리키기 때문에 NI에 적은 영향만을 주고 데이터를 변경할 수 있게 됩니다.)

3-1. 구조
루트페이지(비클러스터형)  -> 리프페이지 (비클러스터형) ->  루트페이지 (클러스트형) -> 리프페이지 (클러스트형) 의 4중구조 (실제데이터는 리프페이지 - 클러스트형)

Posted by 엘키 엘키

댓글을 달아 주세요

  1. Favicon of http://passman.tistory.com BlogIcon DLTKDGO 2011.12.13 10:38  댓글주소  수정/삭제  댓글쓰기

    엘키님 공부하고 퍼갈게요!! ^^;;

2009. 3. 26. 15:44 Database/General

인덱스 정리

데이터를 찾는 과정이 필요한 모든 일 (Select, Update, delete, Insert 모두) 에 영향을 준다.

데이터를 빠르게 찾기 위해 필요하다.

인덱스 추가시 인덱스 관리 비용(처리하는 일, 인덱스 관리용 공간 필요)

인덱스는 항상 타는게 아니다. 인덱스를 탈 때 통계를 참고하는데, 이 통계가 최적 수행 방법을 산출하려면, 통계가 최신에 가까워야 좋다.

하지만, 통계 갱신에는 비용이 존재하므로, 적절한 수위를 유지하는 것이 좋다.

인덱스가 걸려 있는 경우에는 정렬이 필요하다.  1Page가 꽉 찬 상태에서, 데이터가 중간 삽입 될 경우, 들어갈 데이터를 포함해 데이터를 반으로 쪼개서 두개의 페이지에 넣는다. 이 것을 페이지 분할이라 부른다. 

페이지 분할이 자주 일어날꺼라 생각되면 인덱스 생성시 채우기 비율 설정으로, 미리 페이지 분할 해두는 것이 가능하다.

이렇게 할 경우, 페이지를 여러개로 나누는 만큼 페이지를 읽어오는 양이 늘어나는 부담이 생긴다.
검색에 사용된 인덱스가 유니크 인덱스일 경우에는, 데이터를 찾자마자 검색 과정을 중단하면 되기에 검색시에 더 빠르다.

인덱스 검사하는 법
explain select * from Table_Name where A='a' and B='b' order by C,D,E ;
해당 쿼리문이 인덱스를 타는지 안타는지 알기 위해서는 쿼리문 앞에 explain을 붙여주면 인덱스를 타는지 안타는지 알 수 있습니다.
type의 결과값이 ALL일 경우 인덱스를 타지 않고 있습니다. range,index등일 때 인덱스를 타고 있습니다.(system,const,eq_ref,range,index,ALL,fulltext)
key의 값이 해당 쿼리문이 타고 있는 인덱스입니다.


인덱스 관련 용어 정리
-
Table Scan
인덱스를 사용하지 않고 테이블 전체를 읽는것.

- Index Seek
인덱스를 사용해서 데이터를 찾은 것.

- Random Access
 여러 데이터를 찾을 때, 순차적으로 다음행을 읽지 못하고, 데이터 하나당 검색을 수행해야 하는 경우를 말함.

- Clustered Index (CI) 
실제 데이터를 키에 따라 정렬 하는 것이다.
페이지 분할시 실제 데이터도 분할해야 하는 것이 단점이다.
범위 처리에 일반적으로 유용하다. (쿼리에 따라 다르지만)

- Non-Clustered Index (NI) 
키 + 주소로 설정된 별도의 저장소를 가진다.
일반적으로 CI보다 크기가 작으므로, 한 페이지에 많이 들어간다. 데이터 검색시 페이지 간 이동이 CI보다 적다는 장점이 있다.
키에 따라 검색이 끝나도 실제 주소를 찾으러 가야하기 때문에, 범위 처리가 CI보다 느리다.

- Covered Query : Index에 포함된 값만 필요로 해서 (a, b컬럼이 복합 인덱스로 걸려 있을 때 select a, b from table), 인덱스만 읽어서 결과를 보여줄 수 있는 쿼리를 말합니다.

Posted by 엘키 엘키
 TAG 인덱스

댓글을 달아 주세요

1. 인덱스  컬럼의 변형
select * from table  where LOWER(name)  ='word';
select * from table  where idx - 1 = 5;
이 처럼 인덱스에 변형을 가하게 되면, DBMS가 인덱스를 이용하지 않는다.

2. NOT 또는 IN 연산자 사용
NOT일 경우 무조건 인덱스를 안타는 것이 아니다.
NOT일 경우에도 인덱스를 타긴 타지만, 일반적으로, NOT에 사용된 값이 아닌 데이터의 비율이 높은 경우가 많기 때문에 인덱스를 타지 않는 경우가 많다.
마찬가지로 IN일 경우에도, IN에 포함된 데이터들의 비율이 매우 높다면 FULL SCAN을 하는 것이 낫다고 DBMS가 판단하면 인덱스를 타지 않는다.


3. 와일드 카드 LIKE문장에서 범위를 전체를 지정시
select * from table  where name like '%word'; 
문자열로 이루어진 값을 인덱스로 잡았을 때, %가 앞쪽에 사용되면 정렬 순서를 사용할 수 없으므로 테이블 FULL SCAN이 이루어진다.

select * from table  where name like 'word%'; 
당연한 얘기지만 쿼리가 이런 경우 인덱스를 탄다. 문자열 정렬 순서를 그대로 이용할 수 있기 때문이다.

4. 복합 컬럼 index에서 조건이 잘못되여 index 가 적용 되지 못하는경우
select * from table where name = 'word' or idx = 5

name과 idx가 둘다 인덱스가 걸려있는 경우라해도, DBMS가 최적의 OR 조건을 뽑기 힘들어, FULL SCAN 하는 경우가 많다.

5. Optimizer 의 선택
select * from table  where name ='word' and  id ='elky'; 

인덱스가 name 과   id로 2개가 있을 경우 id나 name 인덱스 중 하나가 선택될수도 있고, 둘다 선택 될 수도있다.
어떤 방식으로 선택하는냐가 속도에 중요할수도있다. 즉 실행 계획을 추적해서 원하는 결과가 나오도록 관리가 필요하다

Posted by 엘키 엘키

댓글을 달아 주세요

CString strQuery;  
CString strOut;

/////////////////////////////////////////////////////////////////////////////////////////////
// Command  : CTable
// Accessor : CAccessor
// Rowset   : CRowset
// 예제목적 : CTable 클래스를 이용해서 한 테이블의 모든 데이터를 읽어오는 방법.

class CUserInfoTable

{

public:

    int m_nID;

    char m_szName[20];

    char m_szTel[20];


BEGIN_COLUMN_MAP(CUserInfoTable)

   COLUMN_ENTRY(1, m_nID)

   COLUMN_ENTRY(2, m_szName)

   COLUMN_ENTRY(3, m_szTel)

END_COLUMN_MAP()

};


CTable<CAccessor<CUserInfoTable>, CRowset> AllContents;

AllContents.Open(m_OleDB.GetSession(), "UserInfo");

   

while(AllContents.MoveNext() == S_OK)

{

    strOut.Format("ID=%d, Name=%s, Tel=%s", AllContents.m_nID, AllContents.m_szName, AllContents.m_szTel);

    m_List.AddString(strOut);

}


AllContents.Close();


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


//////////////////////////////////////////////////////////////////////////////////////////////////////////

// Command :     CCommand

// Accessor  :     CNoAccessor(데이터를 받기위한 처리를 할 필요가 없다.)

// Rowset     :     CNoRowset(데이터를 받기 위한 버퍼작업을 할 필요가 없다.)

// 예제목적    :    반환값이 필요없는 쿼리를 실행시킬 경우의 가장 단순한 형태를 보여줌.

// 부연설명    :    반환값 없는 퀴리를 실행할 경우. 일반적으로 어떤 Accessor를 사용하더라도

//                      Open()함수에서 bBind값을 false로 설정하면 INSERT 명령어나 UPDATE명령어를

//                      사용하는데 이상이 없을 것이다.


CCommand<CNoAccessor, CNoRowset, CNoMultipleResults> Cmd;


    //strQuery = "INSERT INTO UserInfo (Name, Tel, FirstDate) VALUES ('이상팔', '000-0000-0000', GetDate())";

strQuery = "UPDATE UserInfo SET Tel = '111-111-1111' WHERE id = 1";

Cmd.Open(*(m_OleDB.GetSession()), strQuery, NULL, NULL, DBGUID_DBSQL, false);

Cmd.Close();


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

///////////////////////////////////////////////////////////////////////////////////////////////////////////

// Command  : CCommand

// Accessor : CAccessor

// Rowset   : CRowset

// 예제목적 : CAccessor를 이용하여 사용자정의 레코드셑인 CUserInfoTable로 반환값을 받는 방법.


class CUserInfoTable

{

public:

    int m_nID;

    char m_szName[20];

    char m_szTel[20];


BEGIN_COLUMN_MAP(CUserInfoTable)

   COLUMN_ENTRY(1, m_nID)

   COLUMN_ENTRY(2, m_szName)

   COLUMN_ENTRY(3, m_szTel)

END_COLUMN_MAP()

};


CCommand<CAccessor<CUserInfoTable>, CRowset, CNoMultipleResults> Cmd;

Cmd.Open(m_OleDB.GetSession(), "select * from UserInfo", NULL, NULL, DBGUID_DEFAULT, true);

   

while(Cmd.MoveNext() == S_OK)

{

    strOut.Format("ID=%d, Name=%s, Tel=%s", Cmd.m_nID, Cmd.m_szName, Cmd.m_szTel);

    m_List.AddString(strOut);

}


Cmd.Close();


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


///////////////////////////////////////////////////////////////////////////////////////////////////////////

// Command  : CCommand

// Accessor : CAccessor

// Rowset   : CArrayRowset

// 예제목적 : CAccessor를 이용해 배열의 형태로 사용자정의 레코드셑을 사용하는 방법.


class CUserInfoTable

{

public:

    int m_nID;

    char m_szName[20];

    char m_szTel[20];


BEGIN_COLUMN_MAP(CUserInfoTable)

   COLUMN_ENTRY(1, m_nID)

   COLUMN_ENTRY(2, m_szName)

   COLUMN_ENTRY(3, m_szTel)

END_COLUMN_MAP()

};


CCommand<CAccessor<CUserInfoTable>, CArrayRowset<CUserInfoTable>, CNoMultipleResults> Cmd;

strQuery = "select * from UserInfo";

Cmd.Open(m_OleDB.GetSession(), strQuery, NULL, NULL, DBGUID_DBSQL, true);


for(int i = 0; i < 3; i++)  // i 의 값은 반환된 Rowset의 갯수임.

{                           // 배열형으로 사용될 경우 제로베이스로 시작한다는 것에 주의.

    strOut.Format("ID = %d, Name = %s, Tel = %s", Cmd[i].m_nID, Cmd[i].m_szName, Cmd[i].m_szTel);

    m_List.AddString(strOut);

}


Cmd.Close();


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


///////////////////////////////////////////////////////////////////////////////////////////////////////////

// Command  : CCommand

// Accessor   : CAccessor

// Rowset      : CBulkRowset

// 예제목적     : 일반적으로 CRowset의 경우 한번에 하나의 Rowset을 읽어 오고 GetNextResult()가

//                    호출될때 새로운 Rowset을 읽어오는 방법이지만, CBulkRowset 을 이용할 경우에는

//                    한번에 읽어올 Rowset의 갯수를 지정할 수 있으며 그 사용방법을 보여준다.


class CTestOne

{

public:

    int m_nID;

    int m_naaaa;

    int m_nbbbb;

    int m_ncccc;

    int m_ndddd;


BEGIN_COLUMN_MAP(CTestOne)

   COLUMN_ENTRY(1, m_nID)

   COLUMN_ENTRY(2, m_naaaa)

   COLUMN_ENTRY(3, m_nbbbb)

   COLUMN_ENTRY(4, m_ncccc)

   COLUMN_ENTRY(5, m_ndddd)

END_COLUMN_MAP()


CCommand<CAccessor<CTestOne>, CBulkRowset, CNoMultipleResults> Cmd;

strQuery = "select * from Test1";

   

Cmd.SetRows(20); // 기본적으로는 한번에 10개씩 읽어오는 것으로 설정돼 있음.

Cmd.Open(m_OleDB.GetSession(), strQuery, NULL, NULL, DBGUID_DBSQL, true);

   

Cmd.MoveToRatio(20, 40);


while(Cmd.MoveNext() == S_OK)

{

    strOut.Format("ID = %d, aaaa = %d, bbbb = %d, cccc = %d, dddd = %d",

                    Cmd.m_nID, Cmd.m_naaaa, Cmd.m_nbbbb, Cmd.m_ncccc, Cmd.m_ndddd);

    m_List.AddString(strOut);

}


Cmd.Close();


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


///////////////////////////////////////////////////////////////////////////////////////////////////////////

// CDynamicAccessor를 사용하는 방법

// oledb.h 에 DBTYPE에 대한 정보가 있습니다.

// 반환되는 각 레코드의 형과 갯수를 알고 있을 경우.

CCommand<CDynamicAccessor, CRowset, CNoMultipleResults> Cmd;

strQuery = "select * from UserInfo";

Cmd.Open(m_OleDB.GetSession(), strQuery, NULL, NULL, DBGUID_DBSQL, true);


int nID = 0;

TCHAR szName[20];

TCHAR szTel[21];

   

while(Cmd.MoveNext() == S_OK)

{

    nID = *(int*)(Cmd.GetValue(1));

    ::sprintf(szName, (TCHAR*)Cmd.GetValue(2));

    ::sprintf(szTel, (TCHAR*)Cmd.GetValue(3));

       

    strOut.Format("ID = %d, Name = %s, Tel = %s", nID, szName, szTel);

    m_List.AddString(strOut);

}


Cmd.Close();



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


///////////////////////////////////////////////////////////////////////////////////////////////////////////

// Command  : CCommand

// Accessor   : CAccessor

// Rowset      : CRowset

// 예제목적    : 스토어프로시져가 하나의 입력파라미터를 가지며, 반환값을 가지고 있을때 사용하는 방법.

//                   실질적으로 중요한 부분은 CParamInput 유저레코드셑 임.



// 목적: 입력파라미터와 반환값을 얻는데 사용되는 형태.

class CParamInput

{

public:

    int     m_nParamInput;          // 입력파라미터값으로 사용되는 변수.

    TCHAR   m_szName[20];           // 반환값으로 사용되는 변수.

    TCHAR   m_szTel[20];            // 반환값으로 사용되는 변수.

   

BEGIN_COLUMN_MAP(CParamInput)       // 각각의 레코드(컬럼)의 값을 연결합니다.

    COLUMN_ENTRY(1, m_szName)       // 방법론적인것으로 이런 형태라고 이해하면됨.

    COLUMN_ENTRY(2, m_szTel)

END_COLUMN_MAP()


BEGIN_PARAM_MAP(CParamInput)        // 파라미터의 값을 설정.

    SET_PARAM_TYPE(DBPARAMIO_INPUT) // 입력파라미터의 형태임.

    COLUMN_ENTRY(1, m_nParamInput)  // 첫번재 파라미터에 연결할 변수.

END_PARAM_MAP()

};


/*++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE PROCEDURE sp_ParamInput

@num int

AS

    select [Name], Tel from UserInfo where ID=@num

GO

****************************************************/


CCommand<CAccessor<CParamInput>, CRowset, CNoMultipleResults> Cmd;

strQuery = "sp_ParamInput ?";   // storeprocedure 호출방법.

                                // 두개의 입력파라미터가 존재한다면 ? -> ?, ?로 바뀜.

Cmd.m_nParamInput = 2;          // 입력파라미터에 값 설정.

HRESULT hr = Cmd.Open(m_OleDB.GetSession(), strQuery, NULL, NULL, DBGUID_DBSQL, true);

while(Cmd.MoveNext() == S_OK)

{

    strOut.Format("Name = %s, Tel = %s", Cmd.m_szName, Cmd.m_szTel);

    m_List.AddString(strOut);

}


Cmd.Close();


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


///////////////////////////////////////////////////////////////////////////////////////////////////////////

// Command: CCommand

// Accessor : CAccessor

// Rowset    : CRowset

// 예제목적 : 스토어프로시져가 하나의 출력파라미터를 가지며, 반환값이 없을때 사용되는 경우.

//                입력파라미터만 있는 경우도 이 예제와 유사함.

//                실질적으로 중요한 부분은 CParamOut 유저레코드셑임.


// 목적 : 출력파라미터를 얻는데 사용되는 형태.

//        입력파라미터의 경우도 동일한 형태임.

class CParamOut

{

public:

    int m_nParamOut;                // 출력파라미터를 얻느데 사용되는 변수.


BEGIN_PARAM_MAP(CParamOut)          // 파라미터의 값을 연결.

    SET_PARAM_TYPE(DBPARAMIO_OUTPUT)// 출력파라미터의 형태임.

    COLUMN_ENTRY(1, m_nParamOut)    // 첫번째 파라미터에 연결할 변수.

END_PARAM_MAP()

};


/*********************************

CREATE PROCEDURE sp_ParamOut

@Ret int OUTPUT

AS

BEGIN

    SET @Ret = 99

END

**********************************/


CCommand<CAccessor<CParamOut>, CRowset, CNoMultipleResults> Cmd;

strQuery = "sp_ParamOut ? OUT"; // 출력파라미터를 설정할 경우 반드시 ?뒤에 OUT을 붙여야 함.

Cmd.Open(*(m_OleDB.GetSession()), strQuery, NULL, NULL, DBGUID_DBSQL, true);


strOut.Format("Out Parameter = %d", Cmd.m_nParamOut);

m_List.AddString(strOut);


Cmd.Close();


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


///////////////////////////////////////////////////////////////////////////////////////////////////////////

// Command : CCommand

// Accessor   : CAccessor

// Rowset      : CRowset

// 예제목적    : 스토어프로시져가 입력파라미터와 출력파라미터를 가지고 반환값이 없을 경우.

//                    실질적으로 중요한 부분은 CParamInputOutput 유저레코드셑임.


// 목적 : 입력파라미터와 출력파라미터가 있는 경우 사용되는 형태.

class CParamInputOutput

{

public:

    int m_nParamInput;

    int m_nParamOutput;


BEGIN_PARAM_MAP(CParamInputOutput)  // 파라미터의 값을 연결.

    SET_PARAM_TYPE(DBPARAMIO_INPUT) // 입력파라미터의 형태임.

    COLUMN_ENTRY(1, m_nParamInput)  // 첫번째 파라미터에 연결할 변수.

    SET_PARAM_TYPE(DBPARAMIO_OUTPUT)// 출력파라미터의 형태임.

    COLUMN_ENTRY(2, m_nParamOutput) // 두번째 파라미터에 연결할 변수.

END_PARAM_MAP()

};


/****************************************

CREATE PROCEDURE sp_ParamInputOutput

@num int,               입력값을 읽어서 버리는 구조.

@Ret int OUTPUT

AS

BEGIN

    Set @Ret = @num

END

*****************************************/


CCommand<CAccessor<CParamInputOutput>, CRowset, CNoMultipleResults> Cmd;

strQuery = "sp_ParamInputOutput ?, ? OUT";  // 프로시져에 입출력 파라미터가 있다고 알림.

Cmd.m_nParamInput = 22; // 입력파라미터의 값을 22로 설정.

Cmd.Open(m_OleDB.GetSession(), strQuery, NULL, NULL, DBGUID_DBSQL, true);


// 반환값이 없을 경우, MoveNext()나 GetNextResult()를 호출할 필요가 없음.

strOut.Format("Input Param = %d, Output Param = %d", Cmd.m_nParamInput, Cmd.m_nParamOutput);

m_List.AddString(strOut);


Cmd.Close();


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


///////////////////////////////////////////////////////////////////////////////////////////////////////////

// Command :  CCommand

// Accessor  :  CAccessor

// Rowset     :  CRowset

// 예제목적    :  스토어프로시져가 입출력 파라미터를 가지며, 반환값이 있을 경우.

//                    이 예제는 특히 CMultipleResults클래스가 사용되었다는 점에 유의.

//                    출력파라미터는 반환값을 모두 받은 후에 GetNextResult()를 호출한 다음에

//                    얻을 수 있음.


// 목적 : 입출력 파라미터가 있고 반환값이 있는 경우 사용되는 형태.

class CResultParamIO

{

public:

    int m_nParamInput;  // 입력파라미터를 위한 변수.

    int m_nParamOutput; // 출력파라미터를 위한 변수.


    // 여기부터는 각가의 레코드에 사용되는 변수임.

    int m_nID;          

    TCHAR m_szName[20];

    TCHAR m_szTel[20];


BEGIN_PARAM_MAP(CResultParamIO)    

    SET_PARAM_TYPE(DBPARAMIO_INPUT)

    COLUMN_ENTRY(1, m_nParamInput)

    SET_PARAM_TYPE(DBPARAMIO_OUTPUT)

    COLUMN_ENTRY(2, m_nParamOutput)

END_PARAM_MAP()


BEGIN_COLUMN_MAP(CResultParamIO)

    COLUMN_ENTRY(1, m_nID)

    COLUMN_ENTRY(2, m_szName)

    COLUMN_ENTRY(3, m_szTel)

END_COLUMN_MAP()


/*************************************************

CREATE PROCEDURE sp_ResultParamIO

@num int,

@Ret int OUTPUT

AS

BEGIN

    select * from UserInfo where ID = @num

    Set @Ret = 99

END

*************************************************/


CCommand<CAccessor<CResultParamIO>, CRowset, CMultipleResults> Cmd;

strQuery = "sp_ResultParamIO ?,? OUT";

Cmd.m_nParamInput = 2;  // 입력파라미터의 값을 2로 설정.

Cmd.Open(m_OleDB.GetSession(), strQuery, NULL, NULL, DBGUID_DBSQL, true);


// 반환값을 읽어온다.

while(Cmd.MoveNext() == S_OK)

{

    strOut.Format("ID = %d, Name = %s, Tel = %s, ParamInput = %d",

                   Cmd.m_nID,

                   Cmd.m_szName,

                   Cmd.m_szTel,

                   Cmd.m_nParamInput);

    m_List.AddString(strOut);

}


// 출력파라미터를 얻기 위해 호출한다.

Cmd.GetNextResult(NULL, true);

int nResultOut = Cmd.m_nParamOutput;    // 출력파라미터의 값을 얻는다.

strOut.Format("Out Param = %d", nResultOut);

m_List.AddString(strOut);

Cmd.Close();

Posted by 엘키 엘키
 TAG OLEDB

댓글을 달아 주세요

  1. Favicon of http://namoda.springnote.com/pages/2050462?edit=1# BlogIcon namo 2008.11.10 17:30  댓글주소  수정/삭제  댓글쓰기

    CCommand 클래스에 대해 어떻게 써야할지 막막했는데 도움이 되었습니다. 감사합니다.

  2. Favicon of https://sysenter.tistory.com BlogIcon Ring0 2009.02.27 16:37 신고  댓글주소  수정/삭제  댓글쓰기

    교보문고에서 찾다 포기한 지식이 여기에 있었군요,
    큰 도움이 되었습니다. 감사합니다.

이전버튼 1 이전버튼

블로그 이미지
Software Engineer
엘키

공지사항

Yesterday31
Today27
Total1,605,481

달력

 « |  » 2020.8
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          

글 보관함