Henry Lau
Microsoft Corporation
1998년 7월

1 소개

이 성능 조정 요약 참조서는 데이터베이스 관리자가 Microsoft SQL Server™를 최상의 성능으로 구성할 수 있도록 도와주고 SQL Server 환경에서 성능 저하 요인을 확인할 수 있도록 도와주기 위한 것입니다. 또한 SQL Server 응용 프로그램 개발자에게 SQL Server 인덱스와 SQL Server 도구를 사용하여 SQL 쿼리의 입출력 성능 효율을 분석하는 방법을 안내해줍니다.

이 문서는 두 부분으로 구성되어 있습니다. 먼저 2장과 3장에서는 가장 중요한 성능 조정 항목을 중심으로 간략히 소개합니다. 문서의 나머지 부분에서는 SQL Server 입출력 성능 항목에 대해 개괄적으로 다룹니다. 이 문서에서 설명하는 항목에 전문가가 되려면 링크된 여러 정보 소스를 참고하십시오.

2 초기 SQL Server 구성에서 검토해야 할 중요한 성능 항목

입출력 속도를 최대화하는 것은 SQL Server 성능에서 중요한 요소입니다. SQL Server의 메모리 할당을 늘리면 입출력 요구 사항이 줄어듭니다. SQL Server에 제공할 수 있는 인-메모리 데이터 캐싱 용량은 클수록 좋습니다. Windows NT에서 자주 페이징을 하지 않도록 SQL Server에 가능한 많은 RAM을 할당합니다.

하지만 Windows NT용으로 너무 적은 양의 RAM을 남겨 Windows NT가 페이징을 많이 하게 되지 않도록 주의하십시오.SQL Server에 많은 메모리를 할당하는 것이 좋지만 데이터 캐시의 중가에 비해 성능 향상 비율은 대개 작습니다. 예를 들어, 500MB 데이터 캐시가 90%의 캐시 적중 비율을 나타내지만 550MB로 늘려도 91%가 될 뿐입니다. SQL Server 성능은 눈에 띄게 개선되지는 않습니다. 또한 기가바이트 메모리와 수 백 개의 SQL 스레드가 있는 대형 서버의 경우에는 그와 같은 구성을 지원하기 위해 Windows NT에 더 많은 메모리가 필요할 수 있다는 것을 기억해야 합니다. 모토는 Windows NT 페이징이 너무 많아서는 안된다는 것입니다.

Windows NT 페이징은 Windows NT/SQL 성능 모니터 카운터: Memory: Pages/sec > 0으로 나타납니다. 작업 집합 자르기와 거의 사용되지 않는 페이지를 Windows NT 대기 및 사용 가능 목록으로 이동하는 기타 가상 메모리 관리자 작업으로 인해 특정 수준의 페이징 작업을 보게 되는 것은 정상입니다. 작업 집합 조정을 줄이는 것에 대한 자세한 내용은 레이블이 "Process: Page Faults/Sec > 0 (for SQLSERVR process)"인 섹션 3 항목을 참조하십시오.

SQL Server를 위한 메모리를 설정하려면 "sp_configure memory, <값>" 명령을 사용합니다. 여기서, <값>은 2KB 메모리 블록을 나타냅니다. SQL Server만 실행하고 대량의 RAM(500MB 이상)이 있는 Windows NT 기반 전용 서버에서는 50MB를 Windows NT용으로 남겨두고 나머지는 SQL Server에 할당합니다. <값>은 (<바이트 단위로 표현된 Windows NT 기반 서버에서 사용할 수 있는 RAM의 총량> - 50,000,000) / 2000으로 계산합니다. 하드 또는 소프트 페이징의 징후에 대해서는 Windows NT 성능 모니터를 살펴봅니다(페이징에 대한 자세한 내용은 이 문서의 뒷부분 참조). SQL Server 온라인 설명서에 500MB 이하의 RAM이 있는 서버에서 메모리를 설정하는 것에 대한 권장 사항이 있습니다.

SQL Server 메모리 값이 바뀔 때마다 SQL Server는 자동으로 "사용 가능 버퍼" 구성 옵션을 (메모리의 5%까지) 조절합니다. DBA는 SQL Server 메모리를 조정한 후에 사용 가능 버퍼를 필요한 만큼 설정합니다. 아래의 지연 기록기에 대한 장에서 지연 기록기의 작업 내용과 사용 가능 버퍼와의 관계, 사용 가능 버퍼와 최대 lazywrite 입출력에 필요한 조절 등에 대해 더 자세히 다룹니다.

예제: SQL Server 작업 전용인 RAM이 2GB가 있는 Windows NT 기반 서버를 가정합니다. 위의 수식을 사용하고 다음과 같은 명령으로 975,000개의 2KB 페이지 메모리를 설정합니다.

이 구성 옵션을 적용하려면 SQL Server를 중지하고 다시 시작해야 합니다. 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 122부터 124쪽과 743쪽을 참조하십시오.

해시 버킷 설정은 SQL Server에 대한 메모리 설정과 함께 합니다. 해시 버킷은 SQL Server 데이터 캐시에서 주어진 2KB 페이지에 대한 액세스를 가속화하기 위해 SQL Server가 사용합니다. 각 해시 버킷은 8바이트의 RAM을 사용합니다. 해시 버킷을 너무 적게 설정하여 성능이 희생되는 것에 비해 메모리는 싸기 때문에 해시 버킷을 적게 할당하는 것보다는 좀 많이 할당하는 것이 좋습니다.

해시 버킷을 구성하려면 ISQL/W에서 "sp_configure 'hash buckets', <값>" 명령을 실행합니다. 여기서, <값>은 SQL Server에서 사용하기 위해 할당할 해시 버킷의 수를 나타냅니다. 이 옵션을 설정하는 일반적인 경험 상의 규칙은 SQL Server 메모리에 할당된 2 또는 3개의 2KB 메모리 페이지마다 하나의 해시 버킷 비율로 해시 버킷의 수를 설정하는 것입니다. 1GB 이상의 RAM이 있고 SQL Server 6.5 Enterprise Edition을 실행하지 않는 서버의 경우는 ISQL/W에서 다음과 같은 명령을 실행하여 최대값인 265003개의 해시 버킷을 설정해야 합니다.

SQL Server 6.5 Enterprise Edition을 실행하는 Windows NT 기반 서버에서는 최대 천육백만 개의 해시 버킷을 구성할 수 있습니다. 이 경우에는 할당된 2 또는 3개의 SQL Server 2KB 메모리 페이지마다 하나의 해시 버킷을 지정하는 일반 규칙을 이용합니다. 서버에 3GB의 RAM이 있고 Windows NT 4.0 Enterprise Edition과 SQL Server 6.5 Enterprise Edition을 실행한다고 가정합니다. 그리고 2.8GB의 RAM을 SQL Server 메모리로 할당하도록 SQL Server를 구성했다고 가정합니다(1,400,000개의 SQL Server 2KB 페이지에 해당). SQL Server에 할당된 2KB 페이지 수와 위에서 설명한 일반 규칙을 사용하여 적절한 설정은 700,000개의 해시 버킷이 될 것입니다. SQL Server Enterprise Edition에서 이 값을 구성하려면 ISQL/W에서 다음과 같은 명령을 실행합니다.

이 해시 버킷 구성 옵션을 적용하려면 SQL Server를 중지하고 다시 시작해야 합니다.

SQL Server가 프로덕션에서 실행되는 동안 DBCC SQLPERF (HASHSTATS)를 사용하여 해시 버킷 체인을 추적합니다. 특히 SQL Server가 하루 중 가장 많이 사용될 때가 적당합니다. 이 명령으로 'BUFHASH' "Type"인 행의 "Longest" 열에서 값을 찾습니다. 이 값은 가장 긴 해시 버킷 체인을 나타내며 4보다 작아야 합니다. 이 항목에 대한 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 753쪽을 참조하십시오.

필요하다면 버퍼 체인이 4보다 작은 상태를 유지하도록 해시 버킷 값을 늘립니다. 체인이 짧을수록 데이터 페이지 조회가 빨라집니다. 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 753쪽을 참조하십시오.

많은 양의 RAM이 있는 서버의 경우, SQL Server 프로시저 캐시가 필요 이상으로 크다면 프로시저 캐시 백분율을 줄여 SQL Server 데이터 캐시에 이러한 초과분을 제공해야 하기 때문에 SQL Server는 기본적으로 SQL Server 메모리의 30%를 남겨둡니다. 프로시저 캐시를 구성하려면 ISQL/W에서 "sp_configure 'procedure cache',<value>" 명령을 실행합니다. 여기서, <value>는 SQL Server 프로시저 캐시에 할당할 SQL Server 메모리의 백분율입니다.

예제: 900,000개의 2KB 페이지 RAM(1.8GB RAM)이 기본값인 30% 프로시저 캐시 할당으로 SQL Server 메모리에 할당되었다고 가정합니다. Windows NT/SQL 성능 모니터: "SQLServer-Procedure Cache Object: Max Procedure Cache Used %"는 이 카운터가 결코 30%를 넘을 수 없음을 나타냅니다. 이것은 SQL Server에서 1.8GB RAM의 30%의 30%인 162MB RAM(.3 x .3 x 1.8GB)을 SQL Server 프로시저 캐시를 위해 사용하고 나머지(.3 x 1800MB - 162MB = 378MB)는 낭비하는 것을 의미합니다. 이 경우에는 SQL Server에서 약간의 패딩과 함께 필요한 양만 할당하도록 프로시저 캐시 양을 줄일 수 있습니다. 이 경우에는 12%가 더 나은 설정입니다. SQL Server 프로시저 캐시 설정을 구성하려면 다음 명령을 실행합니다.

이 설정을 적용하려면 SQL Server를 중지하고 다시 시작해야 합니다.

백분율을 줄이면서 프로시저 캐시에 충분한 여유를 남겨두어야 하며 프로시저 캐시를 모니터링하여 변경 후의 프로시저 캐시 이용도를 주의 깊게 감시해야 합니다. SQL Server 데이터 캐시 성능은 대개 작은 증가로는 향상되지 않으며 SQL Server 사용자에게 필요한 것보다 더 많은 프로시저 캐시를 사용할 수 있도록 프로시저 캐시를 과도하게 줄이면 프로시저 캐싱 성능이 저하될 수 있기 때문에 SQL Server 데이터 캐시의 바이트를 줄이려고 노력할 필요는 없습니다. 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 745쪽을 참조하십시오.

기본값인 8이 대개의 로우앤드 디스크 하위 시스템에 적당합니다. Windows NT 기반 서버에 연결된 하이앤드 RAID 저장소 하위 시스템은 매우 고속으로 디스크 입출력을 할 수 있어 더 많은 동시 디스크 전송 요청을 완료할 수 있기 때문에 RAID 하위 시스템에는 8의 설정이 적당하지 않을 수 있습니다. 이와 함께 SQL Server에서 더 많은 디스크 전송 용량을 필요로 하는 쓰기 작업을 한다면 이 값을 더 높게 설정합니다.

'max async io'에 적당한 값은 "충분히 빠른" 검사점이 되게 하는 값입니다. 목표는 원하는 복구 특성이 따라 다른 검사점이 필요하기 전에 검사점이 완료될 수 있도록 빠르게 하는 것이지만 너무 빨라 시스템이 이 백서의 뒷부분에서 더 자세히 다룰 디스크 대기와 같은 이벤트에 의해 심각하게 교란되지 않게 해야 합니다.

대형 디스크 하위 시스템에서 실행되는 SQL Server에 대해 'max async io'를 설정하는 경험적인 규칙은 동시 I/O 수행에 사용할 수 있는 실제 드라이브 수에 2나 3을 곱하는 것입니다. 그런 다음 디스크 작업 또는 대기 문제의 징후가 있는지 Windows NT 성능 모니터를 살펴봅니다. 이 구성 옵션을 너무 높게 설정할 때 나타나는 주된 역효과는 검사점이 읽기와 같은 다른 SQL Server I/O 작업에서 필요한 디스크 하위 시스템 대역폭을 점유할 수 있다는 것입니다.

이 값을 설정하려면 ISQL/W에서 "sp_configure 'max async io', <값>" 명령을 실행합니다. 여기서, <값>은 SQL Server 시스템이 검사점 작업 중에 Windows NT에 제출할 수 있는 동시적인 디스크 I/O 요청의 수를 나타냅니다. 이러한 요청은 실제 디스크 하위 시스템에 요청을 제출합니다. 자세한 내용은 아래의 디스크 I/O 조정 절을 참조하십시오. 이 구성 옵션은 동적이기 때문에 적용하기 위해 SQL Server를 중지하고 다시 시작할 필요가 없습니다.

SQL Server 잠금 구조는 28바이트이며 소유자 구조는 32바이트입니다. sp_configure는 범용 레이블인 'locks'로 동시에 이 두 구조 할당을 증가시킵니다. 여러 사용자에게 동일한 잠긴 페이지가 있다면 하나의 잠금 구조가 필요하며 각 사용자에 대한 잠금 소유자 구조가 추가됩니다.

잠금의 내부 메모리 비용이 비교적 낮기 때문에 많이 사용해도 문제가 되지 않습니다. SQL Server에서 잠금을 모두 사용하면 성능이 제대로 발휘되지 않습니다. 그리고 SQL Server에서 잠금 수를 늘릴 필요가 있다는 오류 메시지(SQL Server 오류 메시지 번호 1204,"SQL Server has run out of LOCKS")를 제공합니다. 많이 사용하는 시스템에서는 잠금을 높게 설정하여 오류 메시지를 피하는 것이 가장 좋습니다.

이 값을 설정하려면 ISQL/W에서 "sp_configure locks,<값>" 명령을 실행합니다. 여기서, <값>은 SQL Server가 사용할 수 있는 잠금 수를 나타냅니다. 잠금 설정 위치에 대한 일반적인 지침을 제공하려면 (사용자 연결) x (사용자가 동시에 액세스하는 테이블의 최대 수) x (사용자가 동시에 액세스하는 테이블 당 최대 행 수)의 계산을 사용합니다. 이 추정 값은 잠금 수준 조정이 없고 정상적인 것보다 많은 데이터 페이지 작업을 가정하기 때문에 대략적입니다. 하지만 탐색 위치를 제공할 수 있습니다. 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 749쪽을 참조하십시오.

SQL Server 잠금과 마찬가지로 열린 개체는 많은 메모리를 소모하지 않으며(열린 개체 당 약 70바이트) 너무 낮게 설정하지 않는다면 성능에 영향을 미치지 않습니다. 하지만 SQL Server에서 모두 소모되면 귀찮은 오류 메시지가 응용 프로그램에 표시되기 때문에 약간 많이 사용하는 것이 좋습니다. 이 값을 설정하려면 ISQL/W에서 "sp_configure 'open objects',<값>" 명령을 실행합니다. 여기서, <값>은 동시에 액세스할 수 있는 테이블, 뷰, 규칙, 저장 프로시저, 기본값 및 트리거의 수를 나타냅니다. 개체가 한 번 이상 액세스된다면 다른 열린 개체를 사용할 필요는 없습니다.

SQL Server 6.5의 기본값인 500은 대형 프로덕션 SQL Server 시스템에는 충분하지 않기 때문에 열린 개체에 대한 더 높은 추정 값이 필요합니다. SQL Server에서 한 번에 열릴 테이블, 뷰, 규칙, 저장 프로시저, 기본값 및 트리거의 최대 수를 추정하여 열린 개체를 추정합니다. 예를 들어, SAP/R3와 함께 작동하는 SQL Server 환경에는 SQL Server에 800개 이상의 저장 프로시저가 포함됩니다. 이와 같은 시스템에서는 ISQL/W에서 다음과 같은 명령을 실행하여 최소한 20000개의 열린 개체를 설정하는 것이 안전합니다.

이 경우, 5,000개부터 시작하여 20,000개까지 늘리고 필요하다면 50,000개까지 늘리는 것을 고려합니다. 열린 개체는 모든 SQL Server 임시 개체에 사용됩니다. 따라서 이것은 단순히 사용자 데이터베이스의 지속적인 개체 수가 아니라 tempdb의 개체 수이기도 합니다. 열린 개체를 모든 사용자 데이터베이스 및 tempdb 개체를 조합한 수준으로 설정합니다. 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 750쪽을 참조하십시오.

최상의 성능을 위해 SQL Server의 네트워크 패킷 크기를 네트워크에서 알 수 있는 Windows NT와 기반이 되는 실제 네트워크 패킷 크기에 일치시킵니다. 이 방법으로 회선을 통한 전송이 가장 효율적으로 이루어집니다. 이렇게 하면 SQL Server가 실제 네트워크 패킷 크기를 찾기 위해 데이터 패킷을 분리하지 않아도 되기 때문에 소중한 CPU 리소스를 절약할 수 있습니다. 이 옵션을 설정하려면 ISQL/W에서 "sp_configure 'network packet size', <값>" 명령을 실행합니다. 여기서, <값>은 SQL Server가 네트워크를 통해 보낼 각 데이터 패킷을 채울 바이트 수를 나타냅니다. 기본값인 4096바이트가 대부분의 일반적인 네트워크 구현에 적합합니다. 네트워크 관리자에게 문의하여 지원되는 패킷 크기를 결정합니다. 이 값이 4096과 다르다면 SQL Server의 네트워크 패킷 크기를 네트워크 환경의 패킷 크기에 일치시키는 것이 좋습니다. 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 756쪽을 참조하십시오.

각 사용자가 SQL Server에 연결할 때 27KB의 메모리 할당이 필요합니다. SQL Server가 지정한 사용자 연결 수를 계산하고 27KB를 곱하여 사용자 연결을 위해 남겨둘 메모리 양을 가져오기 때문에 27KB에 사용자 연결 설정을 곱한 값은 어떤 의미에서는 SQL Server 시작 시에 (할당되지 않고) 예약됩니다. 이러한 메모리 예약은 SQL Server 메모리로 구성된 사용 가능한 RAM에서 SQL Server가 데이터 및 프로시저 캐시용으로 할당할 메모리의 양을 가져오게 됩니다. 사용자 연결은 데이터 캐시에서 사용할 수 있는 RAM을 소모하기 때문에 필요한 것 이상으로 많이 구성하지 않는 것이 중요합니다. 하지만 너무 정확하게 설정하려고 하면 프로덕션에서 사용자 연결이 모자르게 될 수도 있습니다.

다음과 같은 시나리오를 고려합니다. SQL Server에서 600MB의 RAM을 사용할 수 있다고 가정합니다. 이것은 전용 SQL Server이며 Windows NT에 50MB를 남기고 나머지는 최상의 성능을 위해 SQL Server에 할당할 계획입니다. ISQL/W에서 다음과 같은 명령을 사용하여 구성합니다.

그런 다음 사용할 수 있는 사용자 연결 수를 설정합니다. 여기서 사용자 연결 수가 2000이라고 합시다. 다음과 같은 명령을 실행합니다.

이것은 SQL Server 데이터 및 프로시저 캐시에 사용할 수 있는 (275,000 * 2KB) - (2,000 x 27KB) = 496,000KB 또는 496MB가 있다는 것을 의미하며 데이터베이스 작업에 충분하거나 또는 충분하지 않을 수도 있습니다.

일반적으로 이 옵션을 설정하려면 ISQL/W에서 "sp_configure 'user connections', <값>" 명령을 실행합니다. 여기서, <값>은 SQL Server 시스템에 동시에 연결할 수 있는 사용자 수를 나타냅니다. 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 748쪽을 참조하십시오.

3 느린 SQL Server 진단 및 SQL Server 성능 최적화 시 살펴봐야 할 최상위 Windows NT/SQL 성능 모니터 개체 및 카운터

이 절에서는 몇 가지 Windows NT 성능 모니터 디스크 카운터를 관찰해야 합니다. 이 카운터를 사용하기 위해서 Windows NT 명령 창에서 'diskperf -y' 명령을 실행하고 Windows NT를 다시 시작합니다.

디스크 대기가 발생하는 실제 하드 드라이브는 I/O 처리를 해결하는 동안 Windows NT 디스크 I/O 요청을 보류하게 됩니다. 이러한 드라이브에 대한 SQL Server 응답 시간은 저하될 것입니다. 이로 인해 쿼리 실행 시간이 소비됩니다.

RAID를 사용한다면 실제 드라이브 당 디스크 대기를 계산하기 위해서 Windows NT에서 하나의 실제 드라이브로 간주하는 각 디스크 배열과 관련된 실제 하드 드라이브 수를 알아야 합니다. 하드웨어 전문가에게 SCSI 채널과 실제 드라이브 분포를 문의하여 각 실제 드라이브에서 SQL Server 데이터를 유지하는 방법과 각 SCSI 채널에서 SQL Server 데이터가 분포된 양을 알아야 합니다.

Windows NT 성능 모니터를 통해 디스크 대기를 조사하는 여러 가지 방법이 있습니다. 논리 디스크 카운터가 Windows NT 디스크 관리자를 통해 할당된 논리 드라이브 문자와 연결되는 반면 실제 디스크 카운터는 Windows NT 디스크 관리자가 하나의 실제 장치로 간주하는 것에 연결됩니다. Windows NT 디스크 관리자가 하나의 실제 장치로 간주하는 것이 하나의 하드 드라이브일 수도 또는 여러 개의 하드 드라이브로 구성된 RAID 배열일 수도 있다는 것에 주의하십시오. Current Disk Queue가 순간적인 디스크 대기 측정값이라면 Average Disk Queue는 성능 모니터 샘플링을 통한 디스크 대기 측정값의 평균입니다. Logical Disk: Average Disk Queue > 2, Physical Disk: Average Disk Queue > 2, Logical Disk: Current Disk Queue > 2 또는 Physical Disk: Average Disk Queue > 2인 모든 카운터를 기록합니다.

이러한 권장 측정값은 실제 하드 드라이브 별로 지정됩니다. 디스크 대기 측정값이 RAID 배열이 관련된다면 실제 하드 드라이브 당 디스크 대기를 결정하려면 측정값을 RAID 배열의 실제 하드 드라이브 수로 나누어야 합니다.

참고 SQL Server 로그 관리자는 SQL Server 로그 파일에 하나 이상의 I/O 요청을 대기시키지 않기 때문에 SQL Server 로그 파일을 유지하는 실제 하드 드라이브나 RAID 배열에서 디스크 대기는 유용한 측정값이 아닙니다. 대신 DBCC SQLPERF(WAITSTATS) 명령을 실행하고 레이블이 "WriteLog waits/tran"인 행의 결과 집합으로 영이 아닌 값이 반환되는지 조사합니다.

자세한 내용은 아래의 디스크 I/O 조정에 대한 절을 참조하십시오.

이것은 Windows NT의 프로세서가 집합적 그룹으로 처리할 수 없을 만큼 많은 요청을 받는다는 것을 의미합니다. 따라서 Windows NT는 이러한 요청을 대기열에 두어야 합니다.

일부 프로세서 대기는 실제로 전체적인 SQL Server I/O 성능의 좋은 지표입니다. 대기 중인 프로세서가 없고 CPU 사용률이 낮다면 시스템 어딘가에 성능 병목 지점이 있다는 것을 나타냅니다. 이러한 병목 지점은 디스크 하위 시스템일 가능성이 많습니다. 프로세서 대기열에 합리적인 양의 작업이 있다면 CPU가 유휴 상태가 아니고 나머지 시스템이 CPU와 보조를 맞추고 있다는 것을 나타냅니다.

경험에 따르면 적당한 프로세서 대기열 수는 시스템에 있는 CPU 수에 2를 곱한 것입니다.

프로세서 대기가 이 계산 값보다 많이 크다면 검토를 해보아야 합니다. 과도한 프로세서 대기는 쿼리 실행 시간을 낭비합니다. 여러 다른 작업이 프로세서 대기에 영향을 줄 수 있습니다. 하드 및 소프트 페이징을 제거하는 것이 CPU 리소스를 절약하는 데 도움이 됩니다. SQL 쿼리 조정을 포함한 프로세서 대기를 줄이는 다른 방법은 더 좋은 SQL 인덱스를 선택하여 디스크 I/O(CPU)를 줄이거나 시스템에 더 많은 CPU(프로세서)를 추가하는 것입니다.

이것은 Windows NT가 디스크 페이징을 한다는 의미입니다(페이지 부재). 비용은 디스크 I/O + CPU 리소스입니다. SQL Server에 부여된 메모리를 줄이거나 서버에 더 많은 RAM을 추가할 필요가 있습니다. 자세한 내용은 아래의 메모리 조정에 대한 장을 참조하십시오.

이것은 Windows NT가 소프트 페이징을 한다는 것입니다. 즉, 이 서버에는 아직 RAM 내부에 있지만 Windows NT 작업 집합 외부에 있는 메모리를 요청하는 응용 프로그램이 있다는 것입니다. 이것은 SQL Server에서 사용하는 추가적인 CPU 리소스를 소모합니다. 전용 SQL Server 시스템에서 SQL Server는 이러한 동작의 원인이 될 수 있습니다. 이것은 이 다음 줄의 항목으로 확인하고 해결할 수 있습니다.

SQL Server가 실제로 처음에 모든 데이터 캐시 페이지를 액세스하지 않는 한 각 페이지에 대한 첫 번째 액세스가 소프트 부재의 원인이 될 수 있습니다. 따라서 SQL Server가 처음 시작될 때와 데이터 캐시를 처음 시험할 때 발생하는 초기 소프트 부재에 대해서는 염려하지 않아도 됩니다. 자세한 내용은 아래의 메모리 조정에 대한 장을 참조하십시오.

이것은 소프트 부재를 만든 것이 SQL Server 프로세스라는 것을 나타냅니다. 비용은 CPU 리소스입니다. 이 소프트 페이징이 일어나지 않게 하려면 SQL Server의 작업 집합이 SQL Server 메모리 할당과 정확히 같도록 수정합니다. ISQL/W에서 "sp_configure 'set working set size', 1" 명령을 실행합니다.

4 메모리 튜팅에 대한 자세한 정보

이것은 1.5GB 이상의 RAM이 있는 서버에서 사용할 수 있는 최대 RAM 양에 근접하도록 SQL Server 메모리를 구성하려는 DBA에게 문제가 될 수 있습니다. 그와 같은 작업에는 모든 SQL Server 스레드를 위한 기본 1MB 스레드 스택을 줄일 필요가 있습니다. 이러한 스레드에는 작업, 미리 읽기(RA) 관리자, 백업 및 기타 스레드가 포함됩니다.

이 작업을 하는 것은 쉽습니다. Mssql\Binn 디렉터리에서 "EDITBIN /STACK: 65536 sqlservr.exe" 명령을 사용합니다. 각 SQL Server 연결에 할당된 가상 메모리를 64KB가 되도록 줄입니다(이 이하는 좋지 않음). 자세한 내용은 Microsoft Knowledge Base에서 문서 ID Q160683인 "INF: How to Use Editbin to Adjust SQL Server Thread Stack Size"와 "Inside Microsoft SQL Server 6.5"의 744쪽을 참조하십시오.

Windows NT 4.0 Server Enterprise Edition과 함께 Microsoft SQL Server 6.5 Enterprise Edition을 사용하면 Intel 기반 서버에서 Windows NT가 최대 3GB의 RAM을 주소 지정할 수 있습니다. 이 글을 쓴 시점에서 Windows NT 4.0과 SQL Server 6.5 Enterprise Edition 제품을 실행하는 Alpha 기반 시스템은 최대 2GB의 RAM을 주소 지정할 수 있습니다. 현실적으로 이러한 환경에서는 SQL Server 데이터 캐시에 최대 2.8GB의 RAM을 설정할 수 있으므로 Enterprise Edition 제품이 없을 때보다 더 많은 캐싱 용량을 제공할 수 있습니다. 메모리 증가에 따라 적절히 해시 버킷을 늘려야 합니다.

메모리 설정에 대한 자세한 지침은 SQL Server 온라인 설명서의 "More about Memory"를 참조하십시오. 여기에는 500MB 이하의 RAM이 있을 때 권장 메모리 설정에 대한 차트가 제공됩니다.

현실적으로 2GB RAM 서버에서 1.8GB를 사용하는 전용 Windows NT 4.0/SQL 6.5 서버를 구성하는 것이 가능해야 합니다.

필요한 사용자 연결 수를 물어 그에 따라 SQL Server 사용자 연결 수를 설정합니다. 특정 사용자 응용 프로그램에서 워크스테이션 당 하나 이상의 연결이 필요할 수 있다는 점에 유념하십시오.

SQL Server는 다른 SQL Server 처리에 사용할 수 없는 SQL Server 메모리 풀 중에서 사용자 연결 당 약 27KB를 예약할 필요가 있기 때문에(실제 사용자 연결이 되기 전엔 할당되지 않음), 추가 여분을 최소화하여 사용자 연결을 필요한 수준으로 제한하는 것이 가장 좋습니다. 사용자들에게 가장 좋은 결정을 내리도록 요청하십시오. 도움을 얻으려면 Windows NT 성능 모니터의 페이징 카운터를 살펴봅니다. SQL Server에 전용 SQL Server에서 사용할 수 있는 만큼의 RAM을 부여하는 것이 가장 좋습니다.

SQL Server에서 인덱스 페이지를 데이터 페이지보다 더 오래 데이터 캐시에 유지하도록 지시하는 DBCC 추적 플래그(-T1081)를 사용할 수 있습니다. 일반적인 작동은 SQL Server 버퍼 관리자에서 사용 가능 버퍼를 요구하는 수신 인덱스 또는 데이터 페이지를 위한 2KB 페이지 할당을 해제하기 전에 오래된 인덱스 및 데이터 페이지가 전달되는 것을 허용하지 않는 것입니다. 1081 플래그는 SQL Server 버퍼 관리자에게 버퍼 캐시에서 새로운 수신 페이지를 위한 사용 가능 공간으로 이동할 페이지를 결정할 때 오래된 인덱스 페이지를 한 차례 더 통과시키도록 지시합니다. "오래된 페이지"라는 용어는 SQL Server 버퍼 관리자의 해제할 캐시 페이지 목록에서 해당 데이터 페이지의 차례가 왔다는 것을 의미합니다. 버퍼 관리자 아키텍처에 대한 자세한 내용은 "Inside SQL Server 6.5"의 3장을 참조하십시오.

캐시로 들어오는 데이터 페이지가 일정하지 않으며 일회성인 경향이 있고 데이터 페이지를 가져오기 위해 인덱스를 사용하는 경우에 이 추적 플래그로 성능을 향상시킬 수 있습니다. 이론적 근거는 인덱스 b-트리 구조의 상부는 캐시에 유지되는 경향이 있고 이 인덱스가 다시 필요한 경우에 데이터 캐시에 잎 수준 데이터 페이지만 가져오기 때문에 시간과 I/O를 절약하게 된다는 것입니다. 또한 데이터 페이지 액세스의 무작위성과 일회성 때문에 SQL Server에 대한 캐시 적중 비율이 나쁘고, 나머지 데이터 캐시에 다른 인덱스 b-트리에서 다른 요구 데이터와 인덱스 페이지를 가져올 수 있는 충분한 공간이 있는 경우와 인덱스 b-트리가 순전히 주기적인 데이터 페이지 검색에 사용되는 경우에만 이 추적 플래그 사용으로 성능 향상이 된다는 조건이 있습니다. 또한 버퍼 관리자가 사용량을 추적하기 때문에 대부분의 시스템에서 매우 이용 빈도가 높은 페이지는 캐시에 유지되는 경향이 있다는 것에 유념하십시오. 가장 좋은 방법은 일정 기간 동안 추적 플래그의 사용 여부에 따라 성능 차이가 있는지 모니터링하는 것입니다. 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 764쪽을 참조하십시오.

다음과 같은 절차로 1081 설정을 사용하도록 SQL Server를 설정합니다.

1. Windows NT/SQL Server 시스템에서 SQL Server Setup을 시작합니다. 기본 SQL Server 설치에서 설치를 시작하는 프로그램 그룹은 시작/프로그램/Microsoft SQL Server 6.5/SQL Setup입니다.
2. "Continue" 단추를 두 번 누르면 "Microsoft SQL Server 6.5 - Options" 팝업 화면이 나타납니다.
3. "Set Server Options" 라디오 단추를 선택합니다. "Continue" 단추를 누릅니다.
4. 제목이 "Select Server Options"인 팝업 화면에서 "Parameters" 단추를 선택합니다. 제목이 "Server Parameters"인 대화 상자가 열려야 합니다.
5. Parameters 줄에 "-T1081"을 입력하고 "Add" 단추를 선택한 다음 "Ok" 단추를 누릅니다. "Server Parameters" 대화 상자가 닫힙니다.
6. 제목이 "Select Server Options"인 팝업 화면으로 돌아가 "Change Options" 단추를 누릅니다.
7. 대화 상자에 "The SQL Server options have been successfully updated."라는 메시지가 표시되어야 합니다. "Exit to Windows NT" 단추를 누릅니다.
8. -T1081 플래그가 적용되도록 SQL Server를 중지하고 다시 시작합니다.

Windows NT/SQL 성능 모니터 카운터인 Memory: Pages/Sec는 Windows NT에서 수행하는 페이징 양과 서버의 현재 RAM 구성이 적정한지를 나타내는 좋은 지표입니다. 과도한 페이징은 SQL Server 메모리를 줄여 Windows NT에 더 많은 메모리를 부여하거나 가능하다면 Windows NT 기반 서버에 더 많은 RAM을 추가하여 해결해야 합니다. Memory: Pages/Sec를 영에 가깝게 유지할수록 좋은 성능이 보장됩니다. 이것은 서버의 RAM 양이 충분하기 때문에 Windows NT와 SQL Server를 포함한 모든 응용 프로그램이 메모리 요청을 하는 데이터를 만족시키기 위해 Windows NT에서 파일을 페이징하지 않아도 된다는 것을 의미합니다.

Pages/Sec가 영보다 조금 큰 경우에는 심각하지는 않지만 RAM 대신에 Windows NT 페이징 파일에서 데이터를 검색할 때마다 상당한 성능 손실(디스크 I/O)을 초래할 수 있다는 것에 유념하십시오.

Windows NT/SQL 성능 모니터의 하드 페이징 정보 하위 집합은 Windows NT에서 메모리 참조를 해결하기 위해 Windows NT 페이징 참조를 읽은 초 당 횟수입니다. 이것은 Memory:Page Reads/Sec로 나타납니다. Memory:Page Reads/Sec가 5보다 크면 성능에 좋지 않습니다. Pages/Sec가 0이 될 때까지 SQL Server 메모리를 줄입니다. 이 설정이 필요한 SQL Server 메모리 수준보다 낮다면 서버에 더 많은 RAM을 설치해야 한다는 신호입니다.

Soft Faults per Second라고 하는 카운터는 없습니다. 초 당 발생하는 소프트 부재의 수는 다음과 같이 계산합니다.

"Memory:Page Faults/sec" - "Memory:Page Input/sec" = Soft Page Faults per Second

소프트 부재는 CPU 리소스를 소모하기 때문에 일반적으로 하드 부재만큼 성능에 심각한 영향을 주지는 않습니다. 하드 부재는 디스크 I/O 리소스를 소모합니다. 성능에 있어 최상의 환경은 어떤 종류의 부재도 없는 것입니다.

여기서 잠시 "Memory:Pages Input/sec"와 "Memory:Page Reads/sec" 간의 차이를 이해하는 것이 좋을 것입니다. "Memory:Pages Input/sec"는 페이지 부재를 만족시키기 위해 디스크에서 가져오는 Windows NT 4KB 페이지의 실제 수를 나타냅니다. "Memory:Page Reads/sec"는 페이지 부재를 만족시키기 위해 초 당 발생한 디스크 I/O의 횟수를 나타냅니다. 이것은 발생하는 부재에 대한 관점과는 조금 다른 관점을 제공합니다. 따라서 한 번의 페이지 읽기가 다수의 Windows NT 4KB 페이지를 포함할 수도 있습니다. 데이터 패킷 크기가 64KB 이상으로 증가할수록 디스크 I/O 성능이 나아지기 때문에 이 두 카운터를 동시에 고려하는 것이 좋습니다. 또한 하드 디스크의 경우에 2KB 읽기 또는 쓰기를 한 번 완료하는 데 드는 비용은 64KB 읽기 또는 쓰기 비용과 거의 같다는 것이 중요합니다. 읽기 당 8개의 4KB 페이지로 구성된 200페이지 읽기가 하나의 4KB 페이지로 구성된 300페이지 읽기보다 더 빨린 완료되는 상황을 상상해 보십시오. 지금 300개의 4KB 페이지 읽기보다 더 빨리 완료되는 1600개의 4KB 페이지 읽기를 다루고 있다는 것에 주목해야 합니다. 이것은 모든 디스크 I/O 분석에 적용할 수 있습니다. Disk Bytes/Sec와 Disk Transfers/Sec는 서로 관련되어 있기 때문에 둘 모두를 살펴봐야 합니다. 이것에 대해서는 아래에 있는 디스크 I/O 절에서 자세히 다룰 것입니다.

Windows NT 페이징 파일과 관련된 모든 드라이브에 대해서 "Memory: Page Input/sec"와 "Logical Disk: Disk Reads/sec"를 비교하고 Windows NT 페이징 파일과 관련된 모든 드라이브에 대해서 "Memory: Page Output/sec"와 "Logical Disk:Disk Writes/sec"를 비교하면 SQL Server와 같은 다른 응용 프로그램에 대한 페이징 파일의 정확한 디스크 I/O 양을 알 수 있기 때문에 유용합니다. Windows NT 페이징 파일 I/O 작업을 격리시키는 또 다른 쉬운 방법은 모든 다른 SQL Server 파일과 분리된 별도의 드라이브 집합에 Windows NT 페이징 파일을 두는 것입니다. Windows NT 페이징 파일을 SQL Server 파일과 분리하면 SQL Server와 관련된 디스크 I/O에 병렬로 Windows NT 페이징과 관련된 디스크 I/O를 수행할 수 있기 때문에 디스크 I/O 성능이 향상될 수 있습니다.

5 지연 기록기, 검사점 및 로깅 기능 이해

위의 다이어그램은 SQL Server 디스크 쓰기 작업과 관련된 3가지 작업을 보여줍니다. 따라서 로깅, 지연 기록기 및 검사점의 역할과 차이점, 발생 시기를 이해하는 것이 도움이 될 것입니다. 이것은 디스크 하위 시스템이 SQL Server에서 최상의 성능을 발휘하도록 조정하는 방법을 알기 위한 기초가 되며, 동시에 서버 하드웨어 전문가가 디스크 하위 시스템을 조정하기 위한 지침을 제공합니다.

지연 기록기는 데이터가 전혀 들어 있지 않은 2KB 데이터 캐시 페이지인 사용 가능 버퍼를 생성합니다. SQL Server 데이터 캐시에 새로운 데이터나 인덱스 페이지를 가져올 때 사용 가능 버퍼가 필요합니다. 데이터와 인덱스 페이지가 캐시에 머무르는 기간, 최근에 페이지가 사용된 정보, 이용할 수 있는 사용 가능 버퍼의 현재 수 등에 기준하여 버퍼 관리자가 지연 기록기에게 SQL Server 데이터베이스 파일에 실제 쓰도록 지시할 캐시 페이지를 결정합니다. 지연 기록기는 각 2KB 캐시 버퍼를 디스크에 플러시할 때 다른 데이터를 사용 가능 버퍼에 쓸 수 있도록 캐시 페이지의 ID를 초기화해야 합니다.

SQL Server는 자동으로 사용 가능 버퍼를 구성하여 SQL Server 메모리로 구성된 값의 5%가 되게 합니다. 이 값이 떨어지지 않는지 확인하려면 SQL Server: Cache - Number of Free Buffers를 모니터링합니다. 최적 상태에서는 지연 기록기가 전체 SQL Server 작업을 통해 이 카운터 수준을 유지합니다. 이것은 지연 기록기가 사용 가능 버퍼에 대한 사용자 요구를 충족시킨다는 것입니다. SQL Server: Cache - Number of Free Buffers가 영으로 떨어지는 것은 SQL Server의 지연 기록기가 제공하는 것보다 더 높은 수준의 사용 가능 버퍼를 요구하는 사용자 로드가 있다는 것을 나타내기 때문에 좋지 않습니다.

지연 기록기가 구성된 값에서 사용 가능 버퍼를 유지하는 데(또는 최소한 구성된 사용 가능 버퍼 수준의 50% 이상을 정기적으로 유지하는 데) 문제가 있으면 디스크 하위 시스템이 지연 기록기에 지연 기록기가 필요로 하는 디스크 I/O 성능을 제공할 수 없다는 의미입니다. 이것이 사실인지 확인하려면 사용 가능 버퍼 수준의 삭제를 디스크 대기와 비교합니다. 또는 사용 가능 버퍼를 더 빨리 갱신하기 위해 'max lazywriter io'를 더 높게 설정해야 할 수도 있습니다.

디스크 하위 시스템에서 'max lazywriter io'는 지연 기록기가 Windows NT(차례로, 디스크 I/O 하위 시스템)에 한 번에 제출할 수 있는 동시적인 I/O 요청 수를 제어합니다. 'max lazywriter io'를 더 높게 설정하여 동시에 더 많은 커밋되지 않은 데이터 페이지가 SQL Server 데이터 캐시에서 디스크로 플러시되게 하는 것이 가능하며 사용 가능 버퍼 수준을 최대한 구성된 수준으로 유지하도록 도와주지만 하드 드라이브가 디스크 I/O 요청을 대기시키지 않도록 주의하는 것이 중요합니다.

구성된 사용 가능 버퍼 수준을 기본값인 5% 이상으로 늘리지 않는 것이 좋습니다. 사용 가능 버퍼의 구성된 수준을 늘리는 것은 지연 기록기에서 지속적으로 비워야 하는 데이터 캐시의 페이지 수를 늘리기 때문에 피해야 합니다. 이것은 캐시 적중 비율을 증가시키기 위해서 캐시에 유지할 수 있는 데이터와 인덱스 페이지 수를 줄입니다.

Windows NT/SQL 성능 모니터의 "SQLServer: I/O Lazy Writes/Sec"는 지연 기록기 작업을 나타냅니다. 영이 아닌 값은 실제로 디스크에 쓰여질 2KB 페이지의 수를 나타냅니다. 이 카운터가 영이면 지연 기록기가 꺼져 있는 것을 나타냅니다. CPU를 소비하기 때문에 지연 기록기가 정기적으로 자체적으로 켜지거나 꺼지지 않는 것이 성능에 가장 좋습니다. 사용 가능 버퍼 수준을 유지하도록 지연 기록기를 지속적으로 실행하는 것이 더 좋습니다. 지연 기록기가 자체적으로 켜지거나 꺼지면 지연 기록기가 동시적인 캐시 플러시를 많이 하지 않도록 'max lazywriter io'를 줄이는 것을 고려합니다. 이것은 지연 기록기가 더 오랜 기간 동안 캐시를 플러시하게 하여 지연 기록기가 작업을 지속적으로 유지하는 데 도움이 됩니다. 이러한 감소가 지연 기록기가 구성된 사용 가능 버퍼 수준을 유지할 수 없게 하는 원인이 되지 않도록 주의합니다.

(Logical 또는 Physical) Disk: Average Disk Queue 또는 Current Disk Queue에 대한 카운터 이하를 조사하여 Windows NT/SQL 성능 모니터의 현재 디스크 대기 수준을 모니터링하고 모든 SQL Server 작업과 관련된 각 실제 드라이브에 대해 디스크 대기가 2보다 작게 해야 합니다. 하드웨어 RAID 컨트롤러와 디스크 배열을 적용한 시스템의 경우, Windows NT와 SQL에서 RAID 컨트롤러에 연결된 실제 하드 드라이브의 정확한 개수를 알 수 없기 때문에 Logical/Physical Disk 카운터로 보고된 수를 논리 드라이브 문자와 관련된 실제 하드 드라이브 수나 Windows NT 디스크 관리자가 보고한 실제 하드 드라이브 수로 나누어야 합니다. Windows NT 성능 모니터가 보고하는 디스크 대기 수를 올바르게 해석하기 위해서는 RAID 배열 컨트롤러에 연결된 드라이브의 수를 아는 것이 매우 중요합니다. 그리고 다른 성능에 대해서는 RAID와 디스크 I/O 구성 고려 사항에 대한 논의에서 곧 다루게 될 것입니다.

검사점이 발생하면 전체 데이터 캐시가 삭제되고 커밋되지 않은 모든 데이터 페이지가 SQL Server 데이터 파일에 쓰여집니다. 커밋되지 않은 데이터 페이지는 캐시에 가져온 이후에 수정된 모든 데이터 캐시 페이지입니다. 검사점에 의해 디스크에 쓰여진 버퍼에는 여전히 페이지가 들어 있으며 사용자는 디스크에서 다시 읽지 않고 읽거나 업데이트할 수 있습니다. 이것은 지연 기록기에 의해 작성된 사용 가능 버퍼에는 해당되지 않습니다.

캐시에서 플러시해야 할 페이지가 많다면 SQL Server가 플러시할 데이터 페이지를 디스크에 나타난 페이지 순서로 정렬하지 않기 때문에 검사점을 만드는 것이 더 효율적입니다. 이것은 캐시 플러시 중에 디스크 이동 거리를 최소화하고 잠재적으로 순차적 디스크 I/O의 이점을 이용할 수 있습니다. 또한 검사점은 디스크 하위 시스템에 비동기적으로 2KB 디스크 I/O 요청을 제출합니다. 검사점은 디스크 하위 시스템에서 데이터가 실제로 디스크에 쓰여졌다고 보고하도록 대기하지 않기 때문에, 이렇게 하면 SQL Server가 요구된 디스크 I/O 요청을 더 빨리 완료할 수 있습니다. 검사점이 디스크가 처리할 수 있는 것보다 많은 디스크 I/O 요청을 보내는 것을 알 수 있도록 SQL Server 데이터 파일과 관련된 하드 드라이브에서 디스크 대기를 살펴보는 것이 중요합니다. 그와 같은 경우에는 로드를 처리할 수 있도록 디스크 하위 시스템에 더 많은 디스크 I/O 용량을 추가해야 합니다.

'max async io'의 sp_configure 옵션과 'recovery interval'을 사용하여 검사점의 커밋되지 않은 데이터 페이지 플러시 동작을 조정합니다. 'max async io'는 검사점이 Windows NT(다음에 디스크 I/O 하위 시스템)에 동시에 제출할 수 있는 2KB 캐시 플러시를 제어합니다. 검사점 중에 디스크 대기가 용인할 수 있는 수준으로 발생한다면 'max async IO'를 끕니다. SQL Server가 현재 구성된 'max async io' 수준을 유지해야 한다면 디스크 대기가 적용 가능한 수준으로 떨어질 때까지 디스크 하위 시스템에 디스크를 추가합니다.

한편 SQL Server가 검사점을 실행하는 속도를 늘려야 하고 디스크 하위 시스템이 이미 디스크 대기 없이 증가된 디스크 I/O를 처리할 정도로 강력하다면 'max async io'를 늘려 SQL Server가 더 많은 디스크 I/O 요청을 동시에 보내 잠재적으로 I/O 성능을 향상시킬 수 있습니다. 'max async IO'를 변경한 후에 디스크 대기 카운터를 주의하여 살펴보십시오. 디스크 쓰기 대기와 함께 디스크 읽기 대기도 살펴봐야 합니다. 'max async io'를 해당 디스크 하위 시스템보다 너무 높게 설정하면 검사점이 많은 수의 디스크 쓰기 I/O 요청을 대기시키는 경향이 있어 SQL Server 읽기 작업이 제한되는 원인이 될 수 있습니다. "SQL Server: I/O - Outstanding Reads"는 대기된 SQL Server 읽기를 조사하기 위해 살펴볼 수 있는 좋은 성능 모니터 카운터입니다. 성능 모니터의 Physical Disk 및 Logical Disk 개체는 대기된 디스크 읽기 I/O 요청을 모니터링하기 위해 사용할 수 있는 Average Disk Read Queue Length 카운터를 제공합니다. 디스크 읽기 대기가 검사점에 의한 것이라면 선택할 수 있는 것은 'max async io'를 줄이거나 더 많은 하드 드라이브를 추가하여 검사점과 읽기 요청이 동시에 처리되게 하는 것입니다.

복구 간격은 SQL Server 검사점과 연관되며 종종 이 항목에 오해가 있었습니다. 일반적인 오해: 1) 매 60초마다 수행되는 검사점 프로세스의 의미와 2) sp_configuration 옵션 "복구 간격"의 실제 의미. SQL Server 검사점 프로세스는 일 분마다 한 번씩 자동으로 데이터베이스를 검색하는 간단한 루프입니다. tempdb와 '검사점에서 로그 자름'으로 표시된 모든 데이터베이스에서는 이것을 덤프 트랜(또는 검사점)이라고 합니다. 그렇지 않고 로그 레코드 임계값이 초과되는 경우에는 해당 데이터베이스의 커밋되지 않은 데이터 페이지를 플러시하는 실제 검사점을 실행합니다. 로그 레코드 임계값에 이른 데이터베이스의 커밋되지 않은 데이터 페이지만 디스크에 쓴다는 것에 주의하십시오. 임계값에 이르지 않은 데이터베이스의 커밋되지 않은 데이터 페이지는 쓰여지지 않습니다. 임계값은 "복구 간격"으로 전역적으로 지정되지만 로그 레코드 횟수는 각 데이터베이스에 별도로 유지됩니다. 검사점은 sysdatabases에서 데이터베이스를 찾을 순서로 데이터베이스를 검사하고 현재 데이터베이스 검사가 끝나면 다음 데이터베이스로 계속 진행합니다. 따라서, 한 데이터베이스의 오랜 검사점이 다른 처리를 지연시킬 수 있습니다. 검사점(또는 덤프 트랜)을 수행하기 위해서 자동 검사점은 단지 사용자에게서 받은 T-SQL 검사점(또는 덤프 트랜)을 처리하기 위해 실행되는 코드와 정확히 같은 코드를 호출합니다.

복구 간격은 SQL Server 검사점이 복구 간격으로 지정된 기간이 지날 때마다 커밋되지 않은 데이터 페이지를 캐시에서 플러시하는 데 사용된다는 것을 의미하지 않습니다. 복구 간격이 기본값인 5분이고 사용 빈도가 매우 적은 데이터베이스를 갖는 SQL Server 시스템은 수일에서 수주 동안 검사점을 통해 커밋되지 않은 데이터 페이지를 플러시하지 않는 경우도 많습니다. 한편으로 과도하게 업데이트된 OLTP 시스템이 있다면 매 60초마다 기록되는 데이터 캐시에 충분한 페이지가 있어 기록되는 로그 레코드의 수가 구성된 복구 간격과 관련된 계산된 로그 레코드 수를 초과하기 때문에 SQL Server에서 매 60초마다 검사점이 필요한지 결정할 수 있습니다.

복구 기본값인 5분은 대부분의 SQL Server 구성에 적당합니다. 복구 간격을 줄이면 검사점이 커밋되지 않은 데이터 페이지를 삭제하기 전에 기록하는 트랜잭션 로그 레코드의 수를 줄일 수 있습니다. 이렇게 하면 검사점이 디스크 하위 시스템에 보낼 디스크 쓰기 I/O 양이 효과적으로 줄어 검사점이 더 빨리 완료됩니다.

검사점에 대한 자세한 내용은 "Inside Microsoft SQL Server 6.5"의 90, 755 및 759쪽을 참조하십시오.

다른 모든 주요 RDBMS 제품과 같이 SQL Server는 데이터베이스에서 수행된 모든 쓰기 작업(삽입, 업데이트 및 삭제)이 SQL Server의 온라인 상태를 중단시키는 어떤 요인(정전, 디스크 드라이브 결함, 데이터 센터 화재 등)으로 인해 손실되지 않도록 보장합니다. 복구성을 보장하는 한 가지 기능은 SQL Server 로깅 프로세스입니다. 모든 암시적(단일 SQL 쿼리) 또는 명시적 트랜잭션(Begin Tran/ COMMIT 또는 ROLLBACK 명령 순서를 발행한 정의된 트랜잭션)을 완료하려면 SQL Server의 로그 관리자가 해당 트랜잭션과 관련된 모든 데이터 변경이 관련 로그 파일에 성공적으로 쓰여졌다는 신호를 디스크 하위 시스템에서 받아야 합니다. 이 규칙은 어떤 이유로 인해 SQL Server가 갑자기 중단되고 데이터 캐시에 기록된 트랜잭션이 데이터 파일에 플러시되지 않은 경우(데이터 버퍼 플러시는 검사점이나 지연 기록기의 책임임을 상기), SQL Server를 켜면 SQL Server에서 트랜잭션 로그를 읽고 다시 적용할 수 있습니다. 서버 중지 후에 트랜잭션 로그를 읽는 것과 SQL Server에 트랜잭션을 적용하는 것을 복구라고 합니다.

SQL Server는 각 트랜잭션이 완료될 때 디스크 하위 시스템에서 SQL Server 로그 파일에 대한 I/O를 완료하도록 대기해야 하므로 중요한 것은 SQL Server 로그 파일이 들어 있는 디스크에 예상되는 트랜잭션 로드에 대한 충분한 디스크 I/O 처리 용량이 있어야 한다는 것입니다.

SQL Server 로그 파일과 관련된 디스크 대기를 살펴보는 방법은 SQL Server 데이터베이스 파일과 다릅니다. SQL Server 로그 관리자는 하나 이상의 I/O 요청을 SQL Server 로그 파일에 대기시키지 않기 때문에 Windows NT 성능 모니터의 Disk Queue 카운터를 사용하는 것은 좋은 방법이 아닙니다. 대신 ISQL/W에서 DBCC SQLPERF(WAITSTATS)를 사용합니다. 다른 무엇보다 이 명령은 SQL Server가 SQL Server 로그 파일에 레코드를 실제로 쓰기 위해 대기해야 했는지 여부를 나타내는 정보를 제공합니다. DBCC SQLPERF(WAITSTATS)는 SQL Server가 로그 파일에 실제로 쓰기 위해 대기해야 했던 기간(밀리초)을 나타내는 "WriteLog millisec /tran"으로 정보를 제공합니다. "WriteLog millisec/tran"이 영이 아닌 값을 반환한다면 SQL Server 로그 파일과 더 많은 실제 하드 드라이브를 연결하는 것을 고려해야 합니다. SQL Server 트랜잭션이 트랜잭션을 완료하기 위해 하드 드라이브에서 대기할 필요가 없도록 돕는 것이 좋은 방법입니다.

컨트롤러가 정전과 같은 경우에도 실제로 보호하고 있는 데이터가 디스크에 기록되도록 보장한다면 SQL Server 로그 파일을 위해 캐싱 컨트롤러를 사용하는 것이 좋습니다. 사실 최상의 성능을 위해서는 필수입니다. 캐싱 컨트롤러에 대한 자세한 내용은 아래에서 제목이 "하드웨어 RAID 컨트롤러의 온보드 캐시 효과"인 장을 참조하십시오.

자세한 내용은 "Inside Microsoft SQL Server 6.5"의 765쪽을 참조하거나 SQL Server 온라인 설명서에서 "dbcc sqlperf" 키워드로 검색한 결과를 참조하십시오.

6 미리 읽기 관리자

SQL Server 미리 읽기 관리자는 테이블 스캔과 같이 SQL 쿼리가 데이터베이스에서 상당한 양의 데이터를 반입하려는 것을 SQL Server가 감지하면 미리 읽기 관리자가 디스크에서 16KB 확장 영역에서 데이터를 반입하고 데이터 캐시로 읽기 시작하는 미리 읽기 스레드를 시작하는 방식으로 SQL Server I/O 성능을 향상시킵니다. 미리 읽기 관리자는 응용 프로그램에서 페이지를 요구하기 전에(순차적으로 처리하는 경우) 페이지를 읽기 위해 페이지 체인을 따릅니다. 이러한 방식으로 쿼리 엔진이 작동할 수 있도록 캐시에 데이터가 있습니다. 미리 읽기 관리자는 16KB 청크에서 작동하기 때문에 성능 이점이 생깁니다. 그렇지 않으면 쿼리 엔진은 2KB 청크에서 디스크를 읽어야 할 것입니다.

살펴보아야 할 중요한 미리 읽기 성능 모니터 카운터에는 SQL Server:RA - Pages Fetched into Cache/sec와 SQL Server: RA - Pages Found in Cache/sec의 두 가지가 있습니다. 미리 읽기는 8페이지 블록을 읽은 다음 그 각각을 해시 테이블(페이지 캐시의 목차)에 추가하려 합니다. 페이지가 이미 해시에 있으면(즉, 응용 프로그램에서 먼저 읽은 것을 미리 읽기에서 읽은 경우) 'page found in cache' 카운터가 증가합니다. 페이지가 아직 해시에 없으면(즉, 미리 읽기에 성공한 경우) 'page placed in cache' 카운터가 증가합니다.

SQL Server에서 미리 읽기 작업을 초기화하는 시기를 결정하는 RA 매개 변수를 조정할 필요는 없습니다. 수 백에서 수 천의 동시적인 쿼리가 있고 그 쿼리 중 다수가 미리 읽기를 이용할 수 있을 때 SQL Server가 관리할 수 있는 동시적인 미리 읽기 수를 조종할 필요는 있습니다. 이 경우, SQL Server는 "RA slots per thread"와 "RA worker threads"를 조정하여 이점을 얻을 수 있습니다. SQL Server가 관리할 수 있는 동시적인 미리 읽기 작업의 수는 "RA slots per thread"와 "RA worker threads"를 곱하여 계산할 수 있습니다. 기본적으로 이것은 SQL Server 미리 읽기 관리자가 동시에 한 번 실행하는 5 x 3 = 15 미리 읽기 작업입니다.

미리 읽기 작업을 조정하려면 디스크 I/O 하위 시스템에서 미리 읽기를 늘려 증가된 I/O 로드를 처리할 수 있는 정도와 특정 SQL Server 사용자 환경에 필요한 미리 읽기 작업의 수를 알아야 합니다. 이 절 다음에 나오는 디스크 I/O에 대한 정보에서 미리 읽기나 다른 작업으로 인한 디스크 I/O 병목 현상을 감지하는 방법을 설명합니다. 미리 읽기 조정을 수행하는 방식은 Windows NT/SQL 성능 모니터에서 디스크 대기에 대한 로드 징후를 살펴보고 조정이 전체적인 쿼리 성능에 미치는 영향을 알아내 "RA slots per thread" 및 "RA worker threads"를 천천히 증가시키는 것이어야 합니다.

미리 읽기 관리자에 대한 한 가지 주의 사항은 지나치게 많은 미리 읽기가 필요하지 않은 페이지로 캐시를 채울 수 있기 때문에 다른 목적으로 사용할 수도 있는 추가적인 I/O와 CPU를 요구하여 전체적인 성능을 저하시킬 수 있다는 것입니다.

이 두 가지 항목에 대해서는 "Inside Microsoft SQL Server 6.5"의 761부터 762쪽에 몇 가지 권장 사항이 제공됩니다. 프로세서가 4개인 시스템에서 "RA worker threads"의 최대 권장 설정은 20입니다. "RA slots per thread"에 대한 최대 권장 설정은 모든 서버에서 10입니다. 프로세서가 8개인 시스템에서는 "RA worker threads"를 이 값 이상으로 증가시키는 것이 가능합니다. 하지만 SQL Server에 더 많은 스레드를 만들 수 있는 능력을 제공하면 시스템 CPU의 부담이 증가할 수 있다는 것에 주의해야 합니다. 따라서 이 설정을 필요한 값 이상으로 높이지 않는 것이 좋습니다.

7 디스크 I/O(입출력) 성능

단지 몇 GB의 데이터만 있고 집중적인 읽기나 쓰기 작업을 하지 않는 SQL Server를 구성할 때는 디스크 I/O와 하드 드라이브 간의 SQL Server I/O 작업 로드 균형 조정에 대해 고려하는 것이 성능에 크게 중요하지 않습니다.

그러나 수 백GB의 데이터를 유지하거나 집중적인 읽기 및 쓰기 작업을 하는 대형 SQL Server 데이터베이스를 구성하기 위해서는 여러 하드 드라이브 간의 로드 균형 조정을 통해 SQL Server 디스크 I/O 성능을 최대화하는 드라이브 구성이 필수적입니다.

일반적으로 데이터베이스 성능 조정에 있어 가장 중요한 측면 중 하나는 I/O 성능 조정입니다. SQL Server 역시 예외는 아닙니다. 전체 데이터베이스를 유지할 만큼의 충분한 RAM이 있는 시스템에서 SQL Server를 실행하지 않는 한 I/O 성능은 디스크 I/O 하위 시스템에서 SQL Server 데이터의 읽기 및 쓰기를 얼마나 빨리 처리할 수 있는 가에 따라 결정됩니다.

기억해야 할 일반적인 법칙은 여러 하드웨어 공급업체에서 사용할 수 있는 전형적인 Wide Ultra SCSI 하드 드라이브를 사용하면 (이 백서를 작성한 시점에서) Windows NT와 SQL Server가 초 당 75개의 비순차적(임의) I/O 작업과 150개의 순차적 I/O 작업을 할 수 있다는 것입니다. 이러한 하드 드라이브에 대해 MB 단위로 표현된 전송 속도는 약 10MB/초 입니다. SQL Server 데이터베이스는 10MB/초 전송 속도보다는 75/150 I/O 전송 속도/초에 의해 제한되는 경우가 많다는 것을 기억해야 합니다. 다음 계산이 이것을 나타낸 것입니다.

(75개의 임의 I/O 작업/초) X (2KB 전송 속도) = 150KB/초

이 계산은 주어진 하드 드라이브에서 엄격하게 임의 읽기 또는 쓰기 SQL Server 작업(단일 페이지 읽기 및 쓰기)을 하면 대개 해당 하드 드라이브에서 150KB/초 I/O 처리 용량을 예상하는 것이 합리적임을 나타냅니다. 이것은 드라이브의 10MB/초 I/O 처리 용량보다 매우 작습니다. SQL Server 검사점 및 지연 기록기는 2KB 전송 크기로 I/O를 수행합니다.

(150개의 임의 I/O 작업/초) X (2KB 전송 속도) = 300/초

이 계산은 주어진 하드 드라이브에서 엄격하게 순차적 읽기 또는 쓰기 SQL Server 작업(단일 페이지 읽기 및 쓰기)을 하면 대개 해당 하드 드라이브에서 300/초 I/O 처리 용량을 예상하는 것이 합리적임을 나타냅니다.

(150개의 순차 I/O 작업/초) X (16KB 전송 속도) = 2400KB(2.4MB)/초

이 계산은 주어진 하드 드라이브에서 엄격하게 순차적 읽기 또는 쓰기 SQL Server 작업을 하면 대개 해당 하드 드라이브에서 2.4MB/초 I/O 처리 용량을 예상하는 것이 합리적임을 나타냅니다. 이 값이 임의 I/O의 경우보다는 훨씬 크지만 10MB의 광고 전송 속도보다는 작습니다. SQL Server 미리 읽기 관리자와 로그 관리자는 16KB 전송 속도로 디스크 I/O를 수행합니다. 로그 관리자는 로그 파일에 순차적으로 씁니다. 미리 읽기 관리자는 이러한 작업을 순차적으로 수행할 수도 있지만 항상 그런 것은 아닙니다. 페이지 분리는 16KB 읽기가 순차적이 되지 못하게 하는 원인이 될 수 있습니다. 그러한 이유로 페이지 분리를 제거하고 방지하는 것이 중요합니다.

(75개의 임의 I/O 작업/초) X (16KB 전송 속도) = 1200KB(1.2MB)/초

위의 계산은 모든 임의 I/O를 가정한 더 열악한 환경의 미리 읽기 시나리오입니다. 완전하게 임의 작업을 하는 상황에서도 16KB 전송 크기가 단일 페이지 전송 속도(150KB/초)보다 디스크에서 더 나은 디스크 I/O 전송 속도(1200KB/초)를 나타낸다는 것에 주의하십시오.

아마 마지막 절이 SQL Server 단일 페이지 I/O의 효율이 SQL Server 확장 영역 I/O의 효율보다 떨어지는 이유를 명확하게 이해하는 데 도움이 되었을 것입니다.

"SQL Server:I/O Page Reads/sec"에 주목해야 합니다. 그 이유는, 이것이 SQL Server가 쿼리를 만족시키기 위해 디스크에서 읽어야 하는 2KB 페이지 수를 나타내기 때문입니다. 디스크에서 2KB 단일 페이지 읽기를 제거하는 것은 매우 어렵습니다. 그 이유는 많은 쿼리가 테이블에서 단일 행을 요청하기 때문입니다. 이것은 OLTP(온라인 트랜잭션 처리) 시스템에서는 일반적인 시나리오입니다. 데이터의 단일 행만 필요할 경우 해당 쿼리를 위해 테이블에 대한 미리 읽기를 초기화하는 것은 SQL Server의 성능 관점에서 보면 합리적이지 않습니다. 의사 결정 지원 시스템(DSS)이나 데이터 웨어하우징(DW)의 경우에는 대량의 데이터를 순차적으로 읽는 일이 많다는 점에서 OLTP 시스템과는 다릅니다. DSS/DW의 경우에서 더 많은 미리 읽기 작업과 "SQL Server:I/O Page Reads/sec" 카운터에 대한 더 적은 작업이 있어야 합니다. "SQL Server:I/O Page Reads/sec"에 대해 지적할 한 가지 사실이 더 있습니다. 시스템에서 읽을 하나의 2KB 페이지가 없는 경우는 거의 없겠지만 단일 페이지 읽기가 많고 SQL Trace를 통해 특정 쿼리로 돌아가는 기간과 관련될 수 있다면 "SQL Server:I/O Page Reads/sec"를 살펴봅니다. 특정 시간에 성능 카운터 변화량을 일치시키는 방법에 대해서는 아래의 "SQLTrace 사용 정보" 및 "Windows NT/SQL 성능 모니터 사용 정보" 절을 참조하십시오. 이것은 SQL 쿼리에서 실제 필요한 것보다 더 많은 데이터를 읽는다는 신호이며 더 세밀한 쿼리 조정이 필요한 쿼리라는 것을 나타냅니다.

하드 디스크 작업을 나타내기 위해 순차 및 비순차(임의)라는 용어를 많이 사용했습니다. 여기서 잠시 이 두 용어가 디스크 드라이브와 관련되어 기본적으로 어떤 의미인지 알아보는 것이 좋을 것 같습니다. 아래의 다이어그램은 일련의 드라이브 판으로 구성되고 각 드라이브 판이 판 위를 움직일 수 있고 판에서 정보를 읽거나 판에 정보를 기록하는 읽기/쓰기 헤드가 있는 일련의 아암을 사용하여 읽기/쓰기 작업을 위한 서비스를 제공하는 단일 하드 드라이브를 보여줍니다. SQL Server와 비교하여 하드 드라이브에는 다음과 같이 기억해야 할 두 가지 중요한 사항이 있습니다.

1. 읽기/쓰기 헤드와 관련 디스크 아암은 SQL Server와 Windows NT에서 요청한 하드 드라이브 플래터 위치를 찾고 작동시키기 위해 이동해야 합니다. 데이터가 하드 드라이브 플래터에서 비순차적인 위치에 있다면 하드 드라이브에서 필요한 모든 하드 드라이브 플래터 위치로 디스크 아암과 읽기/쓰기 헤드를 옮겨야 하기 때문에 상단한 시간이 더 걸릴 것입니다. 이것은 필요한 데이터가 모두 하나의 연속적인 실제 하드 드라이브 플래터 구역에 있어 디스크 아암과 읽기/쓰기 헤드가 최소한의 양만 움직여 필요한 디스크 I/O 작업을 수행할 수 있는 순차 경우와 반대입니다. 순차와 비순차 간의 시간 차이는 상당합니다. 순차 검색에 약 2-3밀리초가 걸리는 데 비해 비순차 검색에는 약 50밀리초가 걸립니다. 이러한 시간은 대략적인 추정이며 비순차 데이터가 디스크에 분산된 정도, 하드 디스크 플래터 회전 속도(RPM) 및 기타 하드 드라이브의 물리적 특성에 따라 다르다는 것에 주의하십시오. 핵심은 순차 I/O가 SQL Server 성능에 좋다는 것입니다.
2. 이미 언급했듯이 대개의 하드 드라이브는 초 당 약 75개의 비순차 I/O와 150개의 순차 I/O를 지원합니다. 또한 2KB 읽기 또는 쓰기에 16KB 읽기 또는 쓰기에 걸리는 시간과 거의 같은 시간이 걸린다는 사실을 기억해야 합니다. 2KB부터 약 16KB의 범위 내에서는 디스크 아암과 읽기/쓰기 헤드 이동이 단일 디스크 I/O 전송 작업에 소비되는 시간을 지배하는 주요 요인이라는 것이 사실입니다. 수학적으로 말하면 16KB 이상의 SQL 데이터를 전송해야 할 경우, 16KB 전송이 2KB 전송만큼 빠르고 각 전송에서 8배의 SQL Server 데이터 양을 처리하기 때문에 가능한 한 16KB 디스크 전송을 수행하는 것이 이롭습니다. SQL Server 확장 영역에서 언급한 것처럼 미리 읽기 관리자와 로그 관리자는 16KB 청크에서 디스크 작업을 한다는 것에 주의합니다. 기억해야 할 요점은 미리 읽기 관리자와 16KB 전송 크기를 잘 사용하는 것이 SQL Server 성능에 좋다는 것입니다.

대개의 하드 디스크는 약 10MB/초 최대 전송 속도 또는 75비순차 및 150 순차 디스크 전송/초를 제공합니다. 광고에 따르면 전형적인 RAID 컨트롤러는 약 40MB/초의 전송 속도나 2000 디스크 전송/초를 제공하며 PCI 버스는 약 133MB/초의 전송 속도를 제공합니다. 장치에서 얻을 수 있는 실제 전송 속도는 광고와는 다를 수 있지만 여기서 설명하고자 하는 것과는 크게 관련이 없습니다. 이해해야 할 중요한 점은 각 RAID 컨트롤러에 연결될 하드 드라이브 수를 결정하기 위한 대략적인 시작 위치로 이러한 전송 속도를 사용하는 방법과 나아가 I/O 병목 문제 없이 PCI 버스에 연결할 수 있는 RAID 컨트롤러와 드라이브 조합의 수를 이해하는 것입니다.

제목이 "광고 전송 속도와 SQL Server 관계"인 이전 절에서 SQL Server의 초 당 가능한 하드 드라이브 읽기 또는 쓰기 데이터 양이 2.4MB라는 것을 계산했습니다. RAID 컨트롤러에서 초 당 40MB를 처리할 수 있다고 가정하면 40을 2.4로 나누어 하나의 RAID 컨트롤러에 연결해야 하는 하드 드라이브의 수가 약 16개라고 계산하는 것이 가능합니다. 이것은 SQL Server에서 16KB의 순차 I/O 작업만 수행할 때 하나의 컨트롤러에 약 16개의 드라이브를 연결해야 한다는 것을 의미합니다. 비슷하게 16KB의 모든 비순차 I/O로 하드 드라이브에서 컨트롤러로 보낼 최대 데이터가 초 당 1.2MB라는 것을 앞서 계산했습니다. 40MB/초를1.2MB/초로 나누면 33을 얻을 수 있습니다. 이것은 비순차 16KB 시나리오에서 단일 컨트롤러에 약 33개의 하드 드라이브를 연결해야 한다는 것을 의미합니다.

RAID 컨트롤러에 연결해야 하는 드라이브 수를 추정하는 또 다른 방법은 초 당 MB를 조사하는 대신 초 당 디스크 전송 속도를 조사하는 것입니다. 하드 드라이브에서 초 당 75개의 비순차(임의) I/O를 할 수 있다면 이론적으로 함께 작동하는 26개의 하드 드라이브를 사용하면 하나의 RAID 컨트롤러 최대 I/O 처리 용량에 상당하는 초 당 2000개의 비순차 I/O를 생성할 수 있습니다. 한편 하나의 하드 드라이브가 초 당 150개의 순차 I/O를 유지할 수 있기 때문에 초 당 2000개의 순차 I/O를 생성하고 RAID 컨트롤러가 최대 출력으로 동작하게 하려면 약 13개의 하드 드라이브만 함께 작동하면 됩니다.

이제 PCI 버스를 다루겠습니다. RAID 컨트롤러와 PCI 버스 병목 현상은 하드 드라이브와 관련된 I/O 병목 현상처럼 일반적이지는 않습니다. 하지만 설명을 위해서 RAID 컨트롤러와 연결된 일련의 하드 드라이브들을 컨트롤러를 통해 초 당 40MB의 출력을 유지하는 것이 가능하다고 가정하겠습니다. 아마도 "PIC 버스 I/O 병목 현상 없이 PCI 버스에 안전하게 연결할 수 있는 RAID 컨트롤러 수"에 대한 의문이 생길 것입니다. 대략적인 추정을 하려면 PCI 버스의 I/O 처리 용량을 RAID 컨트롤러의 I/O 처리 용량으로 나눕니다. 따라서 133MB/초를 40MB/초로 나누어 하나의 PCI 버스에 약 3개의 RAID 컨트롤러를 연결할 수 있다는 결론을 얻을 수 있습니다. 대부분의 대형 서버는 하나 이상의 PCI 버스가 있기 때문에 이것은 하나의 서버에 설치할 수 있는 RAID 컨트롤러의 수를 늘릴 수 있다는 것을 나타냅니다.

이러한 계산을 통해 디스크 I/O 하위 시스템을 구성하는 다양한 구성 요소(하드 드라이브, RAID 컨트롤러, PCI 버스)의 전송 속도 관계를 이해할 수 있지만 문자 그대로 받아 들여서는 안됩니다. 그 이유는 위의 계산에서 프로덕션 SQL 환경에서는 거의 불가능한 모든 순차 또는 비순차 데이터 액세스를 가정하기 때문입니다. 실제로는 순차, 비순차, 2KB 및 16KB I/O가 혼합되어 일어 납니다. 그리고 또 다른 요소가 하드 드라이브 집합을 통해 한 번에 처리할 수 있는 I/O 작업의 수를 정확히 예측하는 것을 어렵게 만듭니다. RAID 컨트롤러에서 사용할 수 있는 온보드 읽기/쓰기 캐싱은 드라이브 집합이 생성할 수 있는 I/O 양을 늘립니다. SQL Server 환경에서 2KB 및 16KB I/O의 정확한 수를 배치하는 것이 어려운 것처럼 늘어나는 양을 추정하는 것이 어렵습니다. 이 설명서에서는 이 캐싱 형식에 대해 조금 더 자세히 다룰 것입니다.

하지만 이 절에서 광고되고 있는 전송 속도가 SQL Server에 있어서는 실제로 어떤 의미인지 이해할 수 있었으리라 생각합니다.

8 SQL Server의 기본적인 물리적 데이터와 인덱스 레이아웃 및 그 성능 관계

서버에서 하드웨어 장치의 I/O 특성에 대해 논의했습니다. 이제 디스크 드라이브에 SQL Server 데이터와 인덱스 구조가 실제로 배치되는 방법에 대해 설명할 차례입니다. 이 구조에 대해서는 디스크 I/O 성능에 적용할 수 있는 범위 내에서 설명할 것입니다. 더 자세한 내용은 SQL Server 내부 구조를 매우 자세하게 설명하는 Ron Soukup가 작성한 "Inside Microsoft SQL Server 6.5"의 207쪽부터 시작하는 6장, "Internal Storage - The Details"을 참조하십시오. 비슷한 정보를 Ken England의 "The SQL Server 6.5 Performance Optimization and Tuning Handbook"의 2장과 3장인 "SQL Server Storage Structures"와 "Indexing"에서 찾을 수 있습니다.

SQL Server 데이터와 인덱스 페이지는 모두 크기가 2KB입니다. SQL Server 데이터 페이지에는 텍스트와 이미지 데이터를 제외한 테이블 행과 관련된 모든 데이터가 들어 있습니다. 텍스트와 이미지 데이터의 경우, 텍스트/이미지 열과 연결된 행이 있는 SQL Server 데이터 페이지에는 텍스트/이미지 데이터가 들어 있는 더 많은 2KB 페이지 체인에 대한 포인터가 들어 있습니다.

SQL Server 인덱스 페이지에는 특정 인덱스를 구성하는 열의 데이터만 들어 있습니다. 이것은 인덱스 페이지가 2KB 데이터 페이지가 정보를 압축하는 것보다 더 많이 행과 연결된 정보를 2KB 페이지에 효과적으로 압축한다는 것을 의미합니다. 이해해야 할 중요한 I/O 성능 개념은 인덱스의 I/O 성능 이점이 부분적으로 이 정보 압축이라는 점입니다. 인덱스의 일부로 선택한 열이 상대적으로 테이블 행 크기에서 적은 비율을 차지한다면 이것이 사실입니다. SQL 쿼리가 쿼리의 열이 행의 특정 값과 일치하는 테이블 행 집합을 요청할 때, SQL Server에서 필요한 열을 찾기 위해 테이블의 모든 열을 스캔하는 I/O 작업을 수행하는 대신 인덱스 페이지를 읽어 값을 찾고 쿼리를 만족시키는 데 필요한 테이블의 행만 액세스한다면 I/O 작업과 시간을 절약할 수 있습니다. 정의된 인덱스가 적절히 선택된 경우에 이것은 사실이며 이 설명서에서는 곧 설명할 것입니다.

SQL Server 인덱스에는 두 가지 형식이 있으며 둘 모두 2KB 인덱스 페이지로 형성된 이진 트리(b-트리) 구조를 생성합니다. 차이는 b-트리 구조의 아래쪽에 있으며 SQL Server 설명서에서는 이들을 잎 수준이라고 합니다. 인덱스 B-트리 구조의 상위 부분을 인덱스의 잎 수준이라고 하지는 않습니다. SQL Server 테이블에서 정의된 모든 단일 인덱스에 B-트리 구조가 생성됩니다. 이러한 b-트리는 테이블의 행 추가, 삭제 또는 수정이 발생할 때마다 유지 관리됩니다. 따라서 필요 이상의 인덱스는 SQL Server 성능을 해치는 원인이 됩니다.

아래의 다이어그램은 클러스터되지 않은 인덱스와 클러스터된 인덱스의 구조적 차이를 보여줍니다. 기억해야 할 핵심은 클러스터되지 않는 인덱스의 경우에 잎 수준 노드에는 인덱스에 참여하는 데이터와 클러스터되지 않은 인덱스 잎 수준 노드만 있고 인덱스 행에 나머지 관련 데이터 페이지에 대한 행 데이터를 빨리 찾을 수 있는 포인터가 있다는 것입니다. 최악의 시나리오는 각 행에서 행 데이터 검색을 위해 추가적인 비순차 디스크 I/O가 필요한 클러스터되지 않은 인덱스를 액세스하는 것입니다. 최선의 시나리오는 필요한 행이 대부분 같은 데이터 페이지에 있어 각 데이터 페이지 읽기로 여러 개의 요청된 행을 검색할 수 있는 경우입니다. 클러스터된 인덱스의 경우에는 인덱스의 잎 수준 노드가 테이블의 실제 데이터 행입니다. 따라서 테이블 데이터 검색에 포인터 이동이 필요 없습니다. 클러스터된 인덱스에 기반한 범위 스캔은 클러스터된 인덱스의 잎 수준(해당 테이블의 모든 행)이 디스크에서 실제로 클러스터된 인덱스를 구성하는 열 기준으로 정렬되어 있고 이로 인해 16KB 확장 영역에서 I/O를 수행하기 때문에 잘 수행됩니다. 다행히 클러스터된 인덱스 B-트리(잎 수준 및 잎이 아닌 수준)에 페이지 분리가 많지 않다면 이러한 이러한 16KB I/O가 실제로 순차적이 될 것입니다. 점선은 B-트리에 2KB 페이지만 있지만 표시하지 않았다는 것을 나타냅니다.

9 클러스터된 인덱스

테이블 당 하나의 클러스터된 인덱스만 있을 수 있습니다. 여기에는 간단한 물리적 이유가 있습니다. SQL Server 설명서에서 대개 잎이 아닌 수준이라고 하는 클러스터된 인덱스 B-트리 구조의 상위 부분은 클러스터되지 않은 인덱스 B-트리 구조와 동일하게 구성되지만 클러스터된 인덱스 B-트리의 하위 수준은 테이블과 연결된 실제 2KB 데이터 페이지입니다. 여기에는 다음과 같은 두 가지 성능 상의 의미가 있습니다.

1. 클러스터된 인덱스가 있는 키 검색 기반의 SQL 데이터 검색에는 클러스터된 인덱스의 잎 수준이 이미 데이터 페이지와 연결되어 있기 때문에 하드 디스크 위치의 비순차적 변경으로 인한 포인터 점프가 필요 없습니다.
2. 클러스터된 인덱스의 잎 수준은 클러스터된 인덱스를 구성하는 열 기준으로 정렬됩니다. 클러스터된 인덱스의 잎 수준은 테이블의 실제 2KB 데이터 페이지를 포함하기 때문에 전체 테이블의 행 데이터가 실제로 클러스터된 인덱스가 결정하는 순서로 디스크 드라이브에 배치된다는 것을 의미합니다. 이것은 이 절 다음에 나오는 "FILLFACTOR 및 PAD_INDEX의 중요성"에서 설명할 페이지 분리가 테이블에서 발생하지 않는 한 순차적 디스크 I/O을 사용하기 때문에 클러스터된 인덱스 값에 기반한 상당한 양의 테이블 행 반입 시(최소 16KB 이상) 잠재적인 I/O 성능 이점을 제공합니다. 이것이 대량의 행 검색을 위해 범위 스캔을 수행하는 데 사용될 열에 기반한 테이블에서 클러스터된 인덱스를 선택해야 하는 이유입니다.

10 클러스터되지 않은 인덱스

클러스터되지 않은 인덱스는 대개 키 값에 기반한 큰 SQL Server 테이블에서 양호한 선택도로 소수의 행을 반입할 때 유용합니다. 앞서 언급한 것처럼 클러스터되지 않은 인덱스는 2KB 인덱스 페이지로 구성된 이진 트리입니다. 인덱스 페이지의 이진 트리 하위 또는 잎 수준은 잎 수준 2KB 페이지부터 관련 2KB 데이터 페이지까지의 인덱스를 구성하는 열의 모든 데이터를 포함합니다. 클러스터되지 않은 인덱스를 사용하여 키 값 일치에 기반한 테이블 정보 검색을 하면 인덱스의 잎 수준에서 키 일치를 찾을 때까지 인덱스 이진 트리를 검색한 다음 인덱스의 일부를 형성하지 않는 테이블 열이 필요하면 포인터 점프가 만들어집니다. 이 포인터 점프는 대개 디스크에 대한 비순차 I/O 작업을 요구합니다. 테이블과 그에 따른 인덱스 B-트리의 크기가 매우 크다면 다른 디스크에서 데이터를 읽어야 할 수도 있습니다. 여러 포인터가 동일한 2KB 데이터 페이지에 이른다면 2KB 페이지를 데이터 캐시로 한 번만 읽으면 되기 때문에 I/O 성능에 미치는 영향이 작아집니다. 클러스터되지 않은 인덱스 검색을 포함한 SQL 쿼리에서 반환되는 각 행은 한 번의 포인터 점프가 필요합니다. 이러한 포인터 점프가 테이블에서 하나나 또는 소수의 행을 반환하는 SQL 쿼리에 클러스터되지 않은 인덱스가 더 적합한 이유가 됩니다. 더 많은 행을 반환해야 하는 쿼리에는 클러스터된 인덱스가 더 적합합니다.

11 첨부 인덱스

클러스터되지 않은 인덱스의 특별한 경우가 첨부 인덱스입니다. 첨부 인덱스의 정의는 선택 기준과 WHERE 조건자 모두에서 SQL 쿼리를 만족시키기 위해 필요한 모든 열에 대해 생성된 클러스터되지 않은 인덱스입니다. 첨부 인덱스는 대단히 많은 양의 I/O를 절약할 수 있기 때문에 상당한 쿼리 성능 향상을 기대할 수 있습니다. 하지만 새 인덱스를 만드는 비용(행이 기록되거나 업데이트될 때마다 업데이트되어야 하는 다른 B-트리 인덱스 구조)을 첨부 인덱스가 주는 I/O 성능 이점과 비교해야 합니다. 첨부 인덱스가 SQL Server를 매우 자주 실행해야 하는 쿼리나 쿼리 집합에 이익이 된다면 첨부 인덱스를 만들 가치가 있습니다.

이 예제에서 만든 "indexname1"이라는 인덱스는 SELECT 문과 WHERE 조건자에서 모든 열을 포함하기 때문에 첨부 인덱스입니다. 이것은 이 쿼리 실행 중에 SQL Server가 table1과 연결된 데이터 페이지를 액세스할 필요가 없다는 것을 의미합니다. SQL Server는 indexname1이라는 인덱스를 사용하여 쿼리를 만족시키는 데 필요한 모든 정보를 구할 수 있습니다. SQL Server가 indexname1과 연결된 B-트리를 모든 검색하여 col2가 'value'와 같은 키 값 순서를 찾았다면 SQL Server가 첨부 인덱스의 잎 수준(bottomlevel)에서 필요한 모든 데이터(col1,col2,col3)를 반입할 수 있다는 것을 알게 됩니다. 이것은 다음과 같은 두 가지 방법으로 I/O 성능을 제공합니다.

1. SQL Server는 데이터 페이지가 아닌 인덱스 페이지에서 필요한 모든 데이터를 가져오기 때문에 데이터를 더 압축할 수 있고 SQL Server가 디스크 I/O 작업을 줄일 수 있습니다.
2. 첨부 인덱스는 실제 디스크에서 col2 기준으로 필요한 모든 데이터를 구성합니다. 이로써 하드 드라이브가 WHERE 조건자(col2 = '값')와 관련된 모든 인덱스 행을 순차적으로 반환할 수 있으며 더 나은 I/O 성능이 제공됩니다. 디스크 I/O의 관점에서 첨부 인덱스는 첨부 인덱스의 열로 만족시킬 수 있는 쿼리와 다른 모드 쿼리를 위한 클러스터된 인덱스가 됩니다.

CREATE INDEX 문에서 먼저 col2로 인덱스가 만들어졌다는 것에 주의하십시오. 이것은 꼭 기억해야 합니다. SQL Server 6.5 쿼리 최적화 프로그램은 이와 같은 복합 인덱스의 첫 번째 열만 사용합니다. 따라서 복합 인덱스의 첫 번째 열로 다른 열 중 하나를 지정하면 SQL Server에서 위 예제 쿼리의 구문 내에 있는 인덱스를 무시하게 됩니다.

일반적으로 해당 테이블의 한 행에 있는 바이트 수에 비해 인덱스에 있는 모든 열의 바이트 수가 작은 첨부 인덱스라면 첨부 인덱스를 사용하는 것이 좋습니다.

SQL Server 환경에 주어진 테이블에 대한 많은 SQL 쿼리가 포함되고 이 쿼리들이 테이블 열에서 많은 부분을 요구하며 요청된 열 집합을 줄이는 것이 불가능할 때는 도움을 얻기 위해 첨부 인덱스에 의존하는 것이 매우 어렵습니다. 삽입/업데이트/삭제 작업은 인덱스 B-트리 구조의 관련 정보를 업데이트해야 하기 때문에 테이블에 (종류에 관계 없이) 많은 인덱스가 있으면 SQL Server의 쓰기 시간이 느려집니다.

12 인덱스 선택

인덱스를 선택한 방법이 생성된 디스크 I/O 양과 그에 따라 성능에 상당한 영향을 미칩니다. 클러스터되지 않은 인덱스가 소수의 행 검색에 좋은 이유와 클러스터된 인덱스가 범위 스캔에 좋은 이유는 이전 절에서 설명했습니다. 여기서는 추가 정보를 다룹니다.

하나의 열 이상을 포함할 인덱스의 경우에는 가장 많이 선택할 열을 첫 번째에 놓아야 합니다. SQL Server 쿼리 최적화 프로그램에서 인덱스를 효율적으로 사용하도록 돕는 것이 중요합니다. 가능하다면 열의 수와 바이트 수를 줄여 인덱스를 간단히 유지해야 합니다.

고유한 값이 매우 적은 대규모 테이블에서 클러스터되지 않은 인덱스를 만들면 해당 클러스터되지 않은 인덱스의 사용이 데이터 검색 중에 I/O를 줄여주지 않기 때문에 클러스터되지 않은 인덱스에서 선택도가 중요합니다. 사실 인덱스 사용은 테이블의 순차 테이블 스캔보다 더 많은 I/O의 원인이 될 수 있습니다. 클러스터되지 않은 인덱스를 위한 좋은 후보의 예에는 송장 번호, 고유한 고객 번호, 주민 등록 번호, 전화 번호 등입니다.

고유한 값이 많지 않은 열과 일치하는 쿼리의 경우에는 클러스터되지 않은 인덱스보다 클러스터된 인덱스가 훨씬 좋습니다. 이것은 키 값에 대한 순차적 16KB I/O를 허용하는 클러스터된 인덱스의 순서대로 테이블 데이터를 실제로 정렬하기 때문합니다. 클러스터된 인덱스에서 페이지 분리를 제거하여 순차 I/O를 보장하는 것이 중요합니다. 클러스터된 인덱스의 가능한 후보의 몇 가지 예로는 도시명, 회사 지사, 판매 날짜, 우편 번호, 고객 구역 등입니다. 시스템에서 전형적인 쿼리가 고유 값의 큰 순차 범위를 반입하지 않는다면 해당 고유 값만 있는 열에 대한 클러스터된 인덱스를 정의하는 것은 낭비가 될 수 있습니다. 각 테이블에서 클러스터된 인덱스를 만들 최선의 열을 선택할 때 자문해야 할 핵심적인 질문은 "이 열의 순서에 따라 많은 수의 행을 반입할 필요가 있는 쿼리가 많은가"라는 것입니다. 대답은 각 SQL Server 환경에 따라 매우 다릅니다. 어떤 회사에서는 날짜 범위를 기준으로 많은 쿼리를 할 수 있고 다른 회사에서는 은행 지점 범위를 기준으로 많은 쿼리를 할 수 있습니다.

다음은 클러스터된 인덱스가 유용한 WHERE 조건자의 예입니다.

클러스터된 인덱스 선택은 실제로 DBA나 SQL 응용 프로그램 개발자의 책임인 두 가지 주요한 결정을 포함합니다. 먼저 범위 스캔을 위한 순차 I/O를 제공한다는 점에서 클러스터된 인덱스에서 가장 유용한 테이블 열을 결정하는 것입니다. 중요도가 거의 비슷한 그 다음 작업은 테이블이 위치한 하드 드라이브에서 집중 지점을 피할 수 있도록 클러스터된 인덱스를 사용하여 테이블의 실제 배치에 영향을 주는 것입니다. 동시에 많은 쿼리가 해당 디스크에서 데이터를 액세스할 수 밖에 없는 방식으로 데이터가 하드 디스크에 위치하면 집중 지점이 발생합니다. 그로 인해 하드 디스크가 하드 디스크에서 처리할 수 있는 것보다 많은 동시적인 디스크 I/O 요청을 받게 되어 디스크 I/O 병목 상태가 발생합니다. 해결 방법은 디스크에서 많은 데이터를 반입하는 것을 중지하거나 I/O 요구를 지원할 수 있도록 데이터를 많은 디스크에 분산시키는 것입니다. 데이터의 실제 배치에 대한 이러한 종류의 고려는 수 백에서 수 천의 SQL Server 사용자 간에서 데이터를 동시에 액세스할 때 중요합니다.

이러한 두 가지 결정은 종종 서로 충돌하며 가장 좋은 결정은 이 둘 사이의 균형을 유지하는 것입니다. 사용자 로드가 높은 환경에서는 (집중 지점을 피해) 향상된 병행성이 열에 클러스터된 인덱스를 배치하는 성능 이점보다 더 가치가 있는 경우가 많습니다.

클러스터된 인덱스 선택을 설명하기 위한 공통적인 시나리오가 있습니다. 송장 발행 날짜 열, 고유한 송장 번호 열 및 기타 데이터가 있는 테이블을 가정합니다. 매일 이 테이블에 약 10,000개의 새로운 레코드가 삽입되고 SQL 쿼리가 이 테이블에서 일주간의 데이터에 대한 모든 레코드를 자주 검색해야 하며 많은 사용자가 이 테이블을 동시에 액세스해야 한다고 가정합니다. 송장 번호는 여러 가지 이유에서 클러스터된 인덱스를 위한 좋은 후보가 아닙니다. 먼저, 송장 번호는 고유하며 사용자는 송장 번호 범위를 거의 검색하지 않습니다. 따라서 송장 번호를 디스크에서 실제로 순차적인 순서로 배치하는 것은 송자 번호에 대한 범위 스캔을 거의 하지 않기 때문에 대개 도움이 되지 않습니다. 다음으로 송장 번호에 대한 값은 1001, 1002, 1003 등으로 단순하게 증가합니다. 클러스터된 인덱스가 송장 번호에 배치되면 테이블의 새로운 행 삽입은 번호가 높은 송장 번호를 제외하면 동일한 실제 디스크 위치인 테이블 끝에서 발생하기 때문에 집중 지점이 발생합니다.

이제 송장 날짜 열을 고려합니다. 사용자가 일주간의 데이터(약 70,000행)를 자주 검색하기 때문에 순차 I/O를 최대화하는 데 있어 송장 날짜는 좋은 후보입니다. 하지만 병행성의 관점에서는 송장 날짜가 클러스터된 인덱스의 좋은 후보가 되지 못합니다. 클러스터된 인덱스가 송장 날짜에 배치되면 모든 데이터가 날짜의 속성에 따라 테이블의 끝에 삽입되는 경향이 있기 때문에 테이블의 끝을 유지하는 하드 디스크에서 집중 지점이 발생할 수 있습니다.

위와 같은 시나리오에 대한 완전한 대답은 없습니다. 송장 날짜 범위를 포함하는 쿼리의 속도를 늘리려면 집중 지점이 발생할 위험을 감수하고 송장 날짜에 클러스터된 인덱스를 배치하도록 선택하는 것이 필요할 것입니다. 이와 같은 경우라면 테이블과 연결된 디스크에서 디스크 대기를 주의 깊게 모니터링하고 삽입으로 인해 테이블 끝을 얻으려는 다른 후속 작업이 대기될 수 있다는 것에 유념합니다. 개인적으로는 이 시나리오에서 집중 지점이 발생할 우려가 있기 때문에 송장 날짜나 송장 번호에 클러스터된 인덱스를 정의하지 않는 것이 좋다고 생각합니다. 대신 테이블에서 송장 날짜나 송장 번호와 같이 순차적인 패턴을 따르지 않도록 데이터를 분산시키고 비교적 균일한 분포를 갖는 열을 찾습니다. 이러한 균일하게 분포된 데이터에서 클러스터된 인덱스를 만들면 송장 날짜와 송장 번호가 디스크에서 균일하게 분산됩니다. 이러한 균일한 분포는 다음 절에서 다루게 될 FILLFACTOR 및 PAD_INDEX와 결합하여 테이블에서 전체적으로 데이터를 삽입할 수 있는 개방된 데이터 페이지 영역을 제공합니다. 이러한 영역은 해당 테이블과 연결된 디스크들 사이에서 실제로 균일하게 분산됩니다. 이러한 분산 형식의 열이 테이블에 없다면 가능한 해결 방법은 테이블에 정수 열을 삽입하고 균일하게 분산된 값을 채워 해당 열에 클러스터된 인덱스를 만드는 것입니다. 클러스터된 인덱스가 정의된 이러한 "보충" 또는 "더미" 열은 쿼리에 사용되지는 않지만 데이터 I/O를 디스크 드라이브 간에 균등하게 분산시켜 테이블 액세스 병행성과 전체적인 I/O 성능을 향상시킵니다. 이것은 크고 액세스 빈도가 높은 SQL 테이블에서 매우 효과적인 방법입니다.

이제 더 즐겁게 작업할 수 있는 다른 예를 다루겠습니다. 송장 번호, 송장 날짜, 송장 수량, 판매를 한 판매소와 다른 데이터로 구성된 테이블을 가정합니다. 매일 이 테이블에 10,000개의 레코드가 삽입된다고 가정합니다. 이 경우, 사용자는 대개 판매소를 기준으로 송장 수량을 쿼리합니다. 따라서 스캔의 기준이 될 범위가 되어야 하기 때문에 클러스터된 인덱스를 만들 열은 판매소가 되어야 합니다. 그리고 새로운 행은 서로 다른 판매소의 혼합으로 삽입되기 쉽기 때문에 삽입은 테이블이 위치한 디스크와 테이블 간에서 균일하게 분산되어야 합니다.

집중 지점에 대해 고려할 또 다른 방법은 select의 컨텍스트 내에 있습니다. 많은 사용자가 매우 유사하지만 그 각각이 실제로 같은 행은 아닌 키 값으로 데이터를 선택한다면 디스크 I/O 작업의 대부분은 디스크 I/O 하위 시스템의 동일한 실제 영역에서 발생하게 될 것입니다. 이러한 키 값이 디스크 간에서 균등하게 분산시키는 열에서 테이블에 대한 클러스터된 인덱스를 정의하여 이 디스크 I/O 작업을 더 균등하게 분산시킬 수 있습니다. 모든 select가 동일한 고유 키 값을 사용한다면 클러스터된 인덱스를 사용하는 것이 테이블의 디스크 I/O 작업 균형 조정에 도움이 되지 않을 것입니다. 하드웨어 또는 소프트웨어 RAID를 사용하는 것이 많은 디스크 드라이브 간의 I/O 분산과 이 문제 해결에 도움이 될 수 있습니다.

여기서 설명한 작동 방식은 디스크 액세스 경합으로 볼 수 있습니다. 이것이 잠금 경합은 아닙니다. 하지만 이 두 가지는 서로 관련되며 동시에 해결할 수 있습니다. SQL Server 공유 읽기 잠금은 서로 호환되기 때문에 충돌 없이 동일한 2KB 페이지에서 많은 잠금을 읽을 수 있습니다. 쓰기 작업이 필요한 다른 SQL Server 잠금은 첫 번째 잠금이 완료되기 전에는 다른 호환되지 않는 쓰기 잠금이 동일한 2KB 페이지를 액세스하지 못하게 차단해야 합니다. 많은 쿼리가 동일한 2KB 페이지에서 행 쓰기를 시도하기 때문에 SQL Server 잠금 경합이 발생한다면 클러스터된 인덱스를 배치할 다른 열을 선택하여 데이터 분포를 다시 조정하면 문제가 발생한 열의 데이터를 균등하게 분산시켜 잠금 경합을 해결할 수 있습니다. 디스크에서 테이블 데이터의 실제 순서를 다시 지정하면 이전에 동일한 2KB 행을 잠그던 쿼리가 이제 동일한 데이터에 대해 다른 2KB 페이지를 잠그게 됩니다. 문제가 발생한 열에 특정 종류의 인덱스가 필요하지 않다면 이 기술이 쿼리 속도 향상에 도움이 됩니다. 문제가 발생한 열에 클러스터되지 않은 인덱스가 배치되면 클러스터되지 않은 인덱스의 잎이 아닌 2KB 페이지에서 잠금 경합이 발생할 것입니다. 삽입만 수행한다면 해당 테이블에서 삽입 행 수준 잠금(IRL)을 사용합니다. ISQL/W에서 "sp_tableoption '테이블이름', 'insert row lock', 'true'" 명령을 사용하여 특정 테이블에 대해 IRL 잠금을 사용합니다. 업데이트를 수행할 때는 IRL이 도움이 되지 않습니다. 유일한 선택은 디스크 액세스나 잠금 경합을 줄이도록 해당 열에 인덱스가 없게 하고 쿼리에 도움이 되는 다른 열에 인덱스를 만드는 것입니다.

SQL Server가 디스크 공간 관리 목적으로 클러스터된 인덱스에 의존하기 때문에 테이블을 액세스하는 쿼리가 범위 스캔을 사용하지 않는 경우에도 각 테이블에서 클러스터된 인덱스를 정의하는 것이 다른 좋은 방법입니다. 클러스터된 인덱스가 없다면 SQL Server 삽입은 항상 테이블 끝에서 일어나며 집중 지점이 발생할 수 있습니다. 테이블에 범위 스캔이 필요한 열이 없다면 바이트 크기가 가장 작은(예: integer) 열을 선택하여 디스크 간에 데이터를 균등하게 분산시킵니다.

SQL Server 데이터베이스에서 대량의 삽입 작업을 처리한다면 페이지 분리를 막을 수 있도록 인덱스와 데이터 페이지에 열린 공간을 제공하고 유지하는 것이 중요합니다. 페이지에 정의된 데이터의 논리적 순서 때문에 인덱스 페이지나 데이터 페이지가 페이지에 삽입되어야 할 행과 모든 새로운 행을 유지할 수 없을 때 페이지 분리는 일어납니다. 페이지 분리가 발생하면 SQL Server는 새로운 2KB 페이지를 만들고 전체 페이지를 연결하며 전체 페이지의 두 페이지 사이에서 데이터를 나누어 두 페이지가 같은 열린 공간에 있게 합니다. 이로 인해 시스템 리소스와 시간이 소모됩니다. 또한 최적의 인덱스 페이지 배치에 해로울 수도 있습니다. 삽입을 수행할 때 페이지를 분리하는 것은 SQL Server가 관련 페이지에 대한 단독 잠금을 요구하기 때문에 대부분의 경우에 IRL과 함께 교착 상태를 유발할 수 있습니다.

초기에 인덱스를 생성할 때 SQL Server는 순차 I/O로 최적의 인덱스 페이지 스캔 I/O 성능이 가능한 연속된 실제 페이지에 인덱스 b-트리 구조를 배치합니다. 페이지 분리가 발생하고 새 페이지를 인덱스의 논리 b-트리 구조에 삽입해야 한다면 SQL Server는 어딘가에 새로운 2KB 인덱스 페이지를 할당해야 합니다. 이러한 작업은 하드 드라이브의 어딘가에서 발생하며 인덱스 페이지의 실제 순차 특성을 해치게 됩니다. 이로 인해 I/O 작업이 순차에서 비순차로 전환되며 성능이 반으로 줄어듭니다. 과도한 양의 페이지 분리는 인덱스 페이지의 실제 순차 순서를 복원하도록 인덱스를 다시 작성하여 해결해야 합니다. 이러한 같은 작동이 테이블의 데이터 페이지에 영향을 줄 수 있는 클러스터된 인덱스의 잎 수준에서 발생할 수 있습니다.

Windows NT/SQL 성능 모니터에서 "SQL Server:I/O Single Page Writes/sec"에 주목해야 합니다. 이 카운터가 영이 아닌 값이면 페이지 분리가 발생하는 것을 나타내는 경향이 있으며 DBCC SHOWCONTIG로 더 자세한 분석을 해야 합니다.

DBCC SHOWCONTIG는 테이블에서 과도한 페이지 분리가 발생하는지 확인하는 데 사용할 수 있는 매우 유용한 명령입니다. Scan Density는 DBCC SHOWCONTIG가 제공하는 주요 지표입니다. 이 값이 100%에 가까울수록 좋습니다. 이 값이 100%보다 많이 작다면 DBCC DBREINDEX로 테이블에서 클러스터된 인덱스를 다시 생성하여 테이블 조각을 모아야 합니다.

SORTED_DATA 옵션과 함께 DBCC DBREINDEX로 클러스터된 인덱스를 다시 생성하는 것이 가장 빠른 방법입니다. 또한 DBCC DBREINDEX는 열에 제약 조건이 있는 테이블에서 클러스터된 인덱스를 다시 생성하는 유일한 방법입니다. 테이블에 제약 조건이 없다면 클러스터된 인덱스를 삭제하고 다시 만들어 인덱스를 다시 생성하고 테이블 조각을 모을 수 있습니다.

SQL Server는 CREATE INDEX와 DBCC REINDEX에서 FillFactor 옵션을 제공하여 인덱스 및 데이터 페이지에 남길 열린 공간의 양을 지정하는 방법을 제공합니다. CREATE INDEX의 PAD_INDEX 옵션은 FillFactor 옵션에 지정된 값을 적용하고 인덱스 페이지의 잎 수준에서 열린 공간의 백분율을 적용합니다. PAD_INDEX 옵션이 없으면 FillFactor는 주로 클러스터된 인덱스의 잎 수준 인덱스 페이지에 영향을 줍니다. FillFactor와 함께 PAD_INDEX 옵션을 사용하는 것이 좋습니다. Create Index와 DBCC REINDEX에서 FillFactor와 Pad_Index에 대한 적절한 구문 정보는 SQL Server 온라인 설명서에서 찾을 수 있습니다. 이 설명서를 SQL Server 클라이언트 유틸리티나 SQL Server 전체 서버 설치와 함께하드 드라이브에 설치하면 간편하게 참조할 수 있습니다.

FillFactor에 지정할 최적 값은 주어진 시간 내에 2KB 인덱스 및 데이터 페이지에 추가될 것으로 예상되는 새 데이터 양에 따라 결정됩니다. 데이터 페이지가 전체 행의 데이터를 유지하는 반면 인덱스 페이지는 해당 인덱스와 연결된 열의 데이터만 포함하기 때문에 SQL Server 인덱스 페이지가 대개 데이터 페이지보다 많은 행을 포함한다는 것을 기억하는 것이 중요합니다. 또한 페이지 분리를 피하기 위해 인덱스를 다시 생성할 수 있는 관리 창이 자주 생길 수 있다는 것도 주의합니다. 필요 없이 인덱스를 다시 생성하면 인덱스 및 데이터 페이지가 대부분 데이터로 채워지게 됩니다. 이러한 경우에 주어진 테이블에 적절한 클러스터된 인덱스를 선택해야 합니다. 클러스터된 인덱스가 새로운 행이 테이블과 연결된 모든 데이터 페이지를 통해 삽입되도록 데이터를 균등하게 분산시킨다면 데이터 페이지는 균등하게 채워집니다. 결과적으로 이렇게 하면 페이지 분리가 시작되기 전에 클러스터된 인덱스를 다시 생성하는 데 필요한 더 많은 시간이 제공됩니다. 다른 결정 사항은 FillFactor입니다. 이것은 부분적으로 주어진 시간 동안 2KB 페이지의 주요 범위 내에 삽입될 행 수와 시스템에서 예약된 인덱스 재생성 작업이 발생하는 빈도를 예상하여 선택해야 합니다.

이것은 페이지를 분리하는 것과 페이지에 많은 열린 공간을 남기는 것 사이의 성능 균형에 따라 결정이 필요한 또 다른 상황입니다. FillFactor에 작은 백분율을 지정하면 인덱스 및 데이터 페이지에 대형 열린 공간이 남게 됩니다. 이렇게 하면 페이지 분리를 피할 수 있지만 페이지에서 데이터를 압축하는 일부 성능 효과에는 해가 될 수 있습니다. SQL Server는 일반적으로 페이지에 데이터가 많이 압축되어 있다면 더 적은 페이지 및 I/O로 더 많은 데이터를 반입할 수 있기 때문에 인덱스 및 데이터 페이지에서 많은 데이터가 압축될수록 성능이 더 빨라집니다. 높은 FillFactor를 지정하면 페이지에 너무 적은 열린 공간이 남고 페이지 유동이 빨라져 페이지 분리가 발생합니다.

FILLFACTOR와 PAD_INDEX를 사용하기 전에 OLTP 시스템에서조차 읽기가 쓰기보다 훨씬 많은 경향이 있다는 것을 기억해야 합니다. FILLFACTOR를 사용하면 테이블을 넓은 영역에 분산시키기 때문에(데이터 압축 감소) 모든 데이터 읽기가 느려집니다. FILLFACTOR를 사용하기 전에 성능 모니터를 사용하여 SQL Server 읽기와 SQL Server 쓰기를 비교하여 쓰기가 읽기의 상당한 비율(말하자면 30% 이상)일 경우에만 이 옵션을 사용하는 것이 좋습니다.

쓰기가 읽기의 상당한 비율이라면 매우 사용량이 많은 OLTP 시스템에서 가장 좋은 접근 방법은 페이지 분리를 방지하면서 SQL Server가 인덱스를 다시 생성하기 위해 사용할 수 있는 다음 시간 창에 도달할 수 있지만 2KB 페이지 당 최소한의 사용 가능한 공간이 남도록 가능한 높은 FillFactor를 지정하는 것입니다. 이 방법은 I/O 성능(가능한 한 페이지를 채움)과 페이지 분리 방지(페이지 오버플로 허용 안함) 사이의 균형을 유지합니다. 테이블에서 다양한 FillFactor로 인덱스를 다시 생성하고 로드 작업 시뮬레이션하는 실험을 수행하여 최적의 FillFactor 값을 확인할 수 있습니다. 최적의 FillFactor 값이 결정되면 SQL Server 작업으로 인덱스를 다시 생성하도록 예약하여 자동화하는 것이 가장 좋습니다. SQL Server 작업을 만드는 방법에 대한 자세한 내용은 SQL Server 온라인 설명서에서 "creating a task" 문자열로 검색한 결과를 참조하십시오.

SQL Server 데이터베이스에 쓰기 작업이 없는 경우에는 최대 I/O 성능을 위해 모든 인덱스 및 데이터 페이지가 완전히 채워지도록 FillFactor를 100%로 설정해야 합니다.

13 RAID(저가 디스크의 중복 배열) "RAID를 알면 효율적으로 사용할 수 있습니다."

데이터베이스가 수 기가바이트 이상으로 확장되면 RAID 기술과 데이터베이스 성능과의 관계에 대한 최소한의 기본 지식을 갖는 것이 중요합니다.

RAID에는 다음과 같은 이점이 있습니다.

성능: 하드웨어 RAID 컨트롤러는 Windows NT 및 Microsoft SQL Server와 같은 응용 프로그램의 모든 데이터 읽기/쓰기를 RAID 배열에 참여하는 모든 디스크 간에 분산되는 조각(대개 32KB부터 64KB)으로 구분합니다. 이와 같이 여러 실제 드라이브에 데이터를 분리하면 RAID 배열에 참여하는 모든 실제 하드 드라이브에 읽기/쓰기 I/O 작업 로드를 분산시키는 효과가 있습니다. 이것은 I/O 요청을 균등하게 분산시키지 못해 특정 디스크가 병목 상태가 되는 대신 RAID 배열에 참여하는 하드 디스크가 전체적으로 균등하게 사용되기 때문에 디스크 I/O 성능이 향상됩니다.

내결함성: RAID는 미러링과 패리티의 두 가지 방법을 사용하여 하드 디스크 오류와 그에 따른 데이터 손실에 대한 보호를 제공합니다.

미러링은 드라이브 미러 쌍의 각 드라이브인 두 개의 드라이브 집합에 정보를 기록하여 구현됩니다. 적절한 미러링이 있는 드라이브가 손실되면 미러 집합의 다른 쪽에서 오류가 발생한 드라이브와 일치하는 드라이브로부터 오류가 발생한 드라이브를 대체하고 데이터를 다시 작성하여 손실된 드라이브에 대한 데이터를 복구할 수 있습니다. 대부분의 RAID 컨트롤러는 Windows NT와 SQL Server를 온라인 상태로 유지하면서 미러 쌍의 다른 쪽에서 이와 같이 오류가 발생한 드라이브를 대체하고 다시 작성하는 기능을 제공합니다(일반적으로 '핫 플러그' 기능이 있는 드라이브라고 함). 미러링의 한 가지 이점은 내결함성이 필요할 때 가장 잘 작동하는 RAID 옵션이라는 것입니다. 미러링을 사용하는 상황에서 각 Windows NT/SQL 쓰기는 미러 집합의 각 드라이브에 한 번씩 두 번의 디스크 I/O 작업을 사용합니다. 미러링의 다른 이점은 패리티 RAID 구현보다 내결함성이 높다는 것입니다. 미러링을 사용하면 최소 한 개의 오류가 발생한 드라이브를 유지할 수 있으며 최대 미러 집합에 있는 드라이브 반이 고장나도 시스템 관리자가 서버를 중단하고 파일 백업에서 복구를 할 필요 없이 서비스를 유지할 수 있습니다. 미러링의 단점은 비용입니다. 미러링의 디스크 비용은 각 드라이브의 데이터 가치에 대해 하나의 드라이브입니다. RAID 1 및 그 하이브리드인 RAID 0+1(종종 RAID 10 또는 0/1로 표현)은 미러링을 통해 구현됩니다.

패리티는 디스크에 쓰여진 데이터에 대한 복구 정보를 계산하여 그 패리티 정보를 RAID 배열을 형성하는 다른 드라이브에 쓰는 것으로 구현됩니다. 드라이브에 오류가 발생하면 새 드라이브가 RAID 배열로 삽입되고 다른 드라이브에 쓰여진 복구 정보(패리티)를 가져오고 이 정보를 사용하여 오류가 발생한 드라이브의 데이터를 다시 생성하여 오류가 발생한 드라이브를 복구합니다. RAID 5 및 그 하이브리드는 패리티를 통해 구현됩니다. 패리티의 이점은 비용입니다. RAID 5로 모든 드라이브를 보호하기 위해서는 단 하나의 추가 드라이브만 필요합니다. 패리티 정보는 RAID 5 배열에 참여하는 모든 드라이브 간에 균일하게 분포됩니다. 패리티의 단점은 성능과 내결함성입니다. 패리티 계산과 쓰기에 관련된 추가 비용으로 인해 RAID 5는 미러링에 2개의 디스크 I/O 작업이 필요한 데 비해 각 Windows NT/SQL 쓰기에 4개의 디스크 I/O 작업이 필요합니다. 읽기 I/O 작업 비용은 미러링과 패리티가 같습니다. Compaq의 "Configuration and Tuning of Microsoft SQL Server 6.5 for Windows NT on Compaq Servers"에서 이 I/O 비용 차이에 대해 자세히 다룹니다. 또한 RAID 5는 하나의 오류가 발생한 드라이브만 유지할 수 있으며 그 이후에는 배열을 오프라인으로 만들고 백업 미디어에서 복구를 수행해 데이터를 복원해야 합니다.

경험적인 일반 규칙: 안정적인 성능을 달성하기 위해서는 필요한 만큼 많은 디스크 사이에서 스트라이프해야 합니다. Windows NT 성능 모니터는 Windows NT 디스크 I/O가 특정 RAID 배열에서 병목 상태인지 나타냅니다. 디스크 I/O 균형을 유지하고 성능을 최대화하기 위해서는 필요할 때 디스크를 즉시 추가하고 데이터를 RAID 배열 및/또는 SCSI 채널을 통해 다시 분산시켜야 합니다.

많은 하드웨어 RAID 컨트롤러는 자체 컨트롤러에 특정 형식의 읽기 및/또는 쓰기 캐싱이 있습니다. 이것은 디스크 하위 시스템의 효과적인 I/O 처리 용량을 상당히 향상시킬 수 있기 때문에 SQL Server로 이러한 사용 가능한 캐싱의 이점을 이용합니다. 이 컨트롤러 기반 캐싱 메커니즘의 원리는 호스트 서버(SQL Server)에서 들어오는 소량의 잠재적으로 비순차적인 I/O 요청을 수집하여 다른 I/O 요청과 함께 몇 밀리초 동안에 함께 배치 처리하는 것입니다. 이러한 배치 처리된 I/O는 더 크고(32부터 129KB) 대개는 순차적인 I/O 요청을 형성하여 하드 드라이브로 보낼 수 있습니다. 이것은 순차 I/O가 성능에 좋다는 원칙과 함께 하드 디스크가 RAID 컨트롤러에 제공할 수 있는 고정된 수의 I/O에 대한 더 많은 디스크 I/O 출력을 생성하도록 돕습니다. RAID 컨트롤러 캐싱은 신비한 힘으로 하드 디스크에서 더 많은 초 당 I/O를 처리하게 하는 것은 아닙니다. RAID 컨트롤러 캐시는 단지 수신 I/O 요청을 배열하는 특정 구성을 사용하여 기반 하드 디스크 I/O 처리 기능의 고정된 양을 최선으로 사용하게 합니다.

이러한 RAID 컨트롤러는 대개 특정 형태의 백업 전원으로 캐싱 메커니즘을 보호합니다. 백업 전원으로 정전의 경우에도 하루정도의 일정 기간동안 캐시에 기록된 데이터를 보존할 수 있습니다. 그리고 프로덕션 환경에서는 데이터베이스 서버에 적절한 UPS 보호를 제공하여 더 많은 보호를 제공하므로 서버 전원이 끊긴 경우에도 RAID 컨트롤러에 더 많은 보호가 제공되고 데이터를 디스크에 플러시할 수 있는 배터리 백업 시간이 제공됩니다.

RAID 1 및 RAID 0+1은 RAID 수준 중에서 가장 좋은 데이터 보호와 가장 좋은 성능을 제공하지만 필요한 디스크가 많아져 비용이 더 들게 됩니다. 하드 디스크 비용이 제한 요인이 아니라면 RAID 1 또는 RAID 0+1이 성능과 내결함성 모두에서 최선의 RAID 선택입니다.

RAID 5는 최선의 비용으로 내결함성을 제공하지만 RAID 5에서는 디스크에서 패리티 정보 읽고 디스크에 패리티 정보를 써야 하는 추가 I/O가 있기 때문에 쓰기 성능이 RAID 1 및 0+1의 반입니다.

최선의 디스크 I/O 성능은 RAID 0(내결함성 보호가 없는 디스크 스트라이프)으로 달성됩니다, 하지만 RAID 0에는 내결함성이 없기 때문에 대개 이 RAID 수준은 개발 서버나 다른 테스트 환경에서만 사용할 수 있습니다.

많은 RAID 배열 컨트롤러가 실제 하드 드라이브를 통해 RAID 0+1(또는 RAID 1/0 및 RAID 10으로 표시) 옵션을 제공합니다. RAID 0+1은 하이브리드 RAID 솔루션입니다. 낮은 수준에서는 일반적인 RAID 1처럼 모든 데이터를 미러하지만 높은 수준에서는 컨트롤러가 아래의 모든 컨트롤러 사이에서 데이터를 스트라이프합니다. 따라서 RAID 0+1은 고성능(스트라이프)과 함께 최대한의 보호(미러링)를 제공합니다. 이러한 스트라이프와 미러링 작업은 RAID 컨트롤러에서 관리되기 때문에 Windows NT와 SQL Server에 투명합니다. RAID 1과 RAID 0+1의 차이점은 하드웨어 컨트롤러 수준에 있으며 필요한 실제 하드 드라이브에서는 고객에게 영향을 미치지 않습니다. RAID 1과 RAID 0+1에서는 주어진 저장소 양에 대해 같은 수의 드라이브가 필요합니다. 특정 RAID 컨트롤러의 RAID 0+1 구현에 대한 자세한 내용은 컨트롤러를 생산한 하드웨어 공급업체에게 문의하십시오.

다음 다이어그램은 RAID 0,RAID 1, RAID 5 및 RAID 0+1 간의 차이점을 설명합니다. SQL Server를 실행하는 하드웨어에 특정한 RAID 구현에 대해서 자세히 알고 싶으면 해당 하드웨어 공급업체에 문의해야 합니다.

핫 플러그 슬롯을 사용할 수만 있다면 SQL Server와 Windows NT가 온라인인 동안에 실제 RAID 배열에 동적으로 디스크를 추가할 수 있다는 것은 매우 편리한 기능입니다. 많은 하드웨어 공급업체에서 이와 같은 기능을 제공할 수 있는 RAID 컨트롤러를 제공합니다. 데이터는 새로 추가한 드라이브를 포함한 모든 드라이브 간에 균등하게 다시 스트라이프되며 SQL Server나 Windows NT를 중단할 필요가 없습니다. 따라서 디스크 배열 상자에 핫 플러그 하드 드라이브 슬롯을 남겨두어 이 기능을 이용하는 것이 좋습니다. 그러므로 SQL Server가 정기적으로 RAID 배열에 많은 I/O 요청을 한다면(RAID 배열과 연결된 Windows NT 논리 드라이브 문자에 대한 Disk Queue Length로 표시됨), SQL Server를 계속 실행하면서 하나 이상의 새 하드 드라이브를 핫 플러그 슬롯에 설치하는 것이 가능합니다. RAID 컨트롤러는 SQL 데이터가 RAID 배열의 모든 드라이브에서 균등하게 분산되도록 일부 기존 SQL 데이터를 새 드라이브에 다시 분산시킵니다. 이제 새 드라이브의 I/O 처리 용량(초 당 75 비순차/150 순차 I/O)이 RAID 배열의 전체 I/O 처리 용량에 추가됩니다.

Windows NT/SQL 성능 모니터에서 Logical 및 Physical Disk 개체가 동일한 정보를 효율적으로 제공합니다. 차이점은 성능 모니터의 Logical Disks가 Windows NT에서 논리 드라이브 문자로 보이는 것에 연결되는 것입니다. 하지만 성능 모니터의 Physical Disks는 Windows NT에서 하나의 실제 하드 드라이브로 보이는 것에 연결됩니다.

성능 모니터 카운터를 사용하려면 Windows NT 명령 프롬프트 창의 명령줄에서 diskperf.exe 명령을 사용합니다. 성능 모니터에서 Logical 및 Physical disk 카운터를 보고하게 하려면 'diskperf -y'를 사용합니다. 이것은 Windows NT 소프트웨어 RAID를 사용하지 않고 하드 드라이브나 하드 드라이브 집합과 RAID 컨트롤러를 사용할 때 작동합니다.

Windows NT 소프트웨어 RAID를 이용할 때는 성능 모니터가 Windows NT 스트라이프 세트를 통해 올바로 Physical 카운터를 보고하도록 'diskperf -ye'를 사용합니다. Windows NT 스트라이프 세트와 관련하여 'diskperf -ye'를 사용하면 Logical 카운터는 올바른 정보를 보고하지 않으며 무시해야 합니다. Windows NT 스트라이프 세트와 관련하여 Logical 디스크 카운터 정보가 필요하면 대신 'diskperf -y'를 사용합니다. 'diskperf -y'와 Windows NT 스트라이프 세트를 사용하면 Logical 디스크 카운터는 올바르게 보고되지만 Physical 디스크 카운터는 올바르게 보고되지 않으며 무시해야 합니다.

diskperf 명령은 Windows NT를 다시 시작해야 적용됩니다.

하드웨어 RAID 컨트롤러는 Windows NT에 대해 하나의 RAID 미러 집합이나 스트라이프 세트를 구성하는 여러 실제 하드 드라이브를 하나의 실제 디스크로 나타냅니다. Windows NT는 디스크 관리자와 함께 작업하여 논리 드라이브 문자를 하드 디스크에 연결할 수 있으며 RAID 컨트롤러가 하드 디스크로 나타내는 하나의 하드 디스크에 실제 연결된 하드 디스크 수에 대해서는 염려할 필요가 없습니다.

하지만 RAID 배열에 연결된 실제 하드 드라이브 수를 아는 것이 중요합니다. 그것은 이 정보가 Windows NT 및 SQL Server에서 각 실제 하드 드라이브에 보내는 디스크 I/O 요청 수를 결정할 때 필요하기 때문입니다. 성능 모니터에서 하드 드라이브에 연결된 것으로 보고하는 디스크 I/O 요청 수를 해당 RAID 배열에 있다고 알려진 실제 하드 드라이브 수로 나눕니다.

RAID 배열의 하드 드라이브 당 I/O 작업을 대략적으로 예상하기 위해서는 Windows NT/SQL 성능 모니터에서 보고된 디스크 쓰기 I/O의 수에 2(RAID 1 및 0+1) 또는 4(RAID 5)를 곱하는 것이 중요합니다. 이렇게 하면 실제 수준에서 하드 드라이브 수를 고려한 I/O 용량(드라이브 당 75개의 비순차 및 150개의 순차)이 적용되기 때문에 실제 하드 드라이브에 보내진 실제 I/O 요청에 대해 더 정확한 수가 제공됩니다. 하지만 하드웨어 RAID 컨트롤러가 캐싱을 사용할 때는 위에서 설명한 이유 때문에 하드 드라이브를 실제 사용하는 I/O 양이 상당히 변경될 수 있으므로 이러한 방법으로 하드 드라이브의 정확한 I/O 양을 계산할 수 있다고 기대해서는 안됩니다.

결국 문제를 일으키지 않는 I/O를 걱정하는 대신 디스크 당 실제 I/O에 대한 디스크 대기에 집중하는 것이 가장 좋습니다. Windows NT는 RAID 배열에 있는 실제 드라이브 수를 알 수 없기 때문에 실제 디스크 당 디스크 대기를 정확하게 평가하려면 Disk Queue Length를 관찰 중인 논리 드라이브가 포함된 하드웨어 RAID 디스크 배열에 참여하는 실제 드라이브 수로 나누는 것이 중요합니다. SQL Server 파일이 들어 있는 하드 드라이브에서는 이 값을 2 이하로 유지합니다. SQL Server 로그 파일이 들어 있는 하드 드라이브에서는 이 값을 0으로 유지해야 합니다.

Windows NT는 하드웨어 RAID 컨트롤러 대신 Windows NT 운영 체제를 통해 미러 집합과 (내결함성이 있거나 없는) 스트라이프 세트를 제공하여 하드 디스크 오류에 대한 내결함성을 제공합니다. Windows NT 디스크 관리자를 사용하여 미러 집합(RAID 1)이나 패리티가 있는 스트라이프 세트(RAID 5)를 정의합니다. 또한 Windows NT 디스크 관리자는 내결함성이 없는(RAID 0) 스트라이프 세트 정의도 가능합니다.

하드웨어 RAID 컨트롤러와 달리 Windows NT가 RAID 작업을 관리하는 구성 요소인 소프트웨어 RAID는 더 많은 CPU 리소스를 이용합니다. 따라서 시스템 프로세서를 100% 가까이 이용할 경우에 Windows NT 소프트웨어 RAID와 동일한 수의 디스크 드라이브가 있는 솔루션의 성능은 하드웨어 RAID 솔루션보다 떨어집니다. 하지만 Windows NT 소프트웨어 RAID는 I/O 병목 상태의 가능성을 줄이고 SQL Server의 CPU 사용률이 높아져 출력을 좋아지게 하여 일반적으로 드라이브를 개별적으로 사용하는 것보다 드라이브 집합에서 전체적으로 좋은 SQL Server I/O 서비스를 제공합니다, 그리고 소프트웨어 RAID는 하드 드라이브 집합에 내결함성을 제공하는 비용 효율이 좋은 솔루션을 제공할 수 있습니다.

Windows NT 소프트웨어 RAID를 구성하는 것에 대한 자세한 내용은 Windows NT Server 온라인 도움말에서 4장 "Planning a Reliable Configuration"을 참조하십시오.

14 가능한 최대의 디스크 I/O 병렬 처리 만들기

소수의 디스크 드라이브에 있는 더 작은 SQL Server 데이터베이스를 다룰 때 디스크 I/O 병렬 처리가 제 역할을 못할 수 있습니다. 하지만 많은 디스크 드라이브에 저장된 큰 SQL Server 데이터베이스를 다룰 때는 디스크 하위 시스템의 I/O 처리 기능의 최적 사용을 가능하게 하는 디스크 I/O 병렬 처리를 사용하여 성능을 향상시킬 수 있습니다.

서로 다른 디스크 I/O 채널로 구분하여 성능을 최대화할 수 있는 SQL Server 작업 영역은 다음과 같습니다.

1. 트랜잭션 로깅
2. Tempdb
3. SQL Server 데이터베이스 파일
4. 많은 쿼리나 쓰기 작업과 관련된 테이블
5. 많은 쿼리나 쓰기 작업과 관련된 클러스터되지 않은 인덱스

하드 드라이브는 디스크 I/O 병목 상태의 주요 지점이기 때문에 개별적인 디스크 I/O 채널은 주로 하드 드라이브의 개별 집합이나 개별 RAID 배열을 나타냅니다. 하지만 추가적인 RAID 컨트롤러와 PCI 버스를 사용할 수 있다면 RAID 또는 SCSI 컨트롤러의 개별 집합과 PCI 버스의 개별 집합도 SQL Server 작업을 분리하는 방법으로 고려합니다.

트랜잭션 로깅은 주로 순차적인 쓰기 I/O입니다. 따라서 SQL Server 트랜잭션을 로깅 작업을 다른 비순차 디스크 I/O 작업과 분리하는 것과 관련된 많은 I/O 성능 이점이 있습니다. 이러한 분리는 SQL Server 로그 파일이 들어 있는 하드 드라이브가 순차 I/O에 집중할 수 있게 합니다. 이 방법으로 하드 드라이브가 초 당 75개 대신 150개의 I/O를 제공할 수 있다는 것을 기억하십시오. SQL Server 복제, 롤백 및 지연된 업데이트와 같이 SQL Server 작업의 일부로 트랜잭션 로그를 읽어야 하는 시기가 있습니다.

Tempdb는 임시 테이블, 정렬, GROUP BY나 ORDER BY에 의한 하위 쿼리 및 집계, DISTINCT를 사용한 쿼리(중복 행 제거를 위해 임시 작업 테이블이 작성됨), 커서 및 임시 저장 프로시저를 통한 ODBC SQLPrepare 구현 등을 포함한 다양한 작업을 위한 공유 작업 영역으로 사용하기 위해 SQL Server가 만드는 데이터베이스입니다. SQL Server 환경에서 tempdb를 광범위하게 사용해야 한다면 tempdb를 다른 SQL 파일과 분리된 디스크 집합에 두는 것을 고려합니다. 이렇게 하면 tempdb에 대한 I/O 작업이 트랜잭션과 관련된 다른 SQL Server 파일에 대한 I/O 작업과 병렬로 발생하여 성능이 향상됩니다.

tempdb를 사용자 데이터베이스와 별도로 유지하는 가장 쉬운 방법은 tempdb 확장에 사용되는 마스터 데이터베이스와 모든 SQL 장치를 많이 사용하는 데이터베이스를 유지하는 다른 SQL 데이터베이스와 분리된 디스크 드라이브나 RAID 배열에 유지하는 것입니다. Msdb 마스터 및 모델 데이터베이스는 사용자 데이터베이스에 비하여 프로덕션에 더 많이 사용되기 때문에 대개는 I/O 성능 조정에서 고려하지 않습니다. 마스터 데이터베이스는 일반적으로 새로운 로그인, 데이터베이스, 장치 및 기타 시스템 개체를 추가할 때만 사용됩니다.

SQL Server 테이블과 인덱스를 나머지 연결된 데이터베이스에서 물리적으로 분리하는 것과 관련된 추가 관리 작업이 있기 때문에 매우 활동적인 테이블과 인덱스에만 이 작업을 해야 합니다.

많은 수의 쿼리나 쓰기 작업을 처리하는 대형 테이블을 다른 SQL Server 파일과 분리하면 이점을 얻을 수 있습니다. 원칙은 다른 SQL Server 데이터나 로그 파일을 포함하지 않는 RAID를 통해 디스크 드라이브나 드라이브 집합에서 SQL Server 데이터베이스 장치를 정의하는 것입니다. 그런 다음 sp_addsegment 저장 프로시저를 사용하여 세그먼트를 만들고 데이터베이스 장치에 연결합니다. 그런 다음 CREATE TABLE 문의 SEGMENT 옵션을 사용하여 세그먼트에 테이블을 배치합니다. SQL Server 온라인 설명서에서 "create table statement", segment_name 및 sp_addsegment를 검색하여 더 자세한 정보를 찾을 수 있습니다.

세그먼트를 사용하여 연결된 테이블에서 클러스터되지 않은 인덱스를 실제로 분리할 수도 있습니다. 이것은 대형 테이블에서 사용자 쿼리에 대해 광범위하게 정기적으로 특정 인덱스를 사용해야 한다는 것을 아는 경우에 유용한 디스크 I/O 병렬 처리를 제공합니다. CREATE INDEX 문의 SEGMENT 옵션을 사용하면 RAID를 통해 개별 디스크 드라이브나 드라이브 집합에 실제로 인덱스를 배치할 수 있습니다.

BCP는 세그먼트를 이용하여 로드 시에 테이블을 실제로 분할할 수 있습니다. 분할 키가 변경되면 BCP 일괄 처리가 끝나고 sp_placeobject를 사용한 이후의 모든 할당은 새 세그먼트로 처리된 다음 새로운 세그먼트를 위해 새 BCP 일괄 작업이 시작됩니다. 페이지 분리는 (FILLFACTOR와 마찬가지로) 시간에 대해 분할을 삭제하지만 읽기, 업데이트 및 삭제만 수행된다면 분할을 계속 유지할 수 있습니다.

SQL Server I/O 작업의 분리는 하드웨어 RAID 컨트롤러, RAID 핫 플러그 드라이브 및 온라인 RAID 확장을 사용하면 매우 편리합니다. SQL Server를 실행할 서버를 구성하고 최대 I/O 성능으로 드라이브를 구성할 때 가장 많은 융통성을 제공하는 방법은 위에서 언급한 개별적인 SQL 작업 각각에 별도의 RAID SCSI 채널을 제공하도록 RAID 컨트롤러를 배열하는 것입니다. 많은 RAID 컨트롤러가 하나 이상의 SCSI 채널(일부는 둘 또는 셋 이상)을 제공하고 RAID 컨트롤러 수준에서는 거의 I/O 병목 상태가 발생하지 않기 때문에(대개는 하드 디스크 수준에서 발생), RAID 컨트롤러 SCSI 채널 간의 작업으로 SQL 파일을 분리하는 것부터 시작하는 것이 좋습니다. 하지만 개별 SQL 작업은 실제 하드 드라이브의 개별 집합에서 처리되어야 합니다. 하나 이상의 SCSI 채널이 있는 RAID 컨트롤러는 각 SCSI 채널을 따라 개별 RAID 배열(미러 집합 또는 내결함성 스트라이프 세트)을 정의할 수 있습니다. 이러한 개별 RAID 배열 각각은 Windows NT 디스크 관리자에서 마치 하나의 실제 드라이브처럼 보입니다. 이제 이러한 RAID 배열에 논리 드라이브 문자를 연결하고 작업 종류에 따라 개별 RAID 배열에 SQL Server 파일을 배치하는 것이 가능합니다.

각 RAID SCSI 채널은 온라인 RAID 확장(RAID 컨트롤러를 통해 사용할 수 있는 경우)의 이점을 완전히 이용해야 할 수 있도록 분리된 RAID 핫 플러그 캐비닛에 연결해야 합니다. 이러한 방식으로 시스템을 구성하면 성능 모니터에서 로드 테스트나 과도한 프로덕션 로드 중에 대기 작동을 보고할 때 디스크 대기를 개별 RAID SCSI 채널과 그 드라이브 캐비닛에 관련시키는 것이 가능합니다. 드라이브 캐비닛이 핫 플러그 RAID 캐비닛이고 RAID 컨트롤러가 온라인 RAID 확장을 지원하며 핫 플러그 하드 드라이브를 위한 슬롯을 캐비닛에서 사용할 수 있다면 Windows NT 성능 모니터에서 해당 RAID 배열에 대한 디스크 대기가 허용 가능한 수준(SQL Server 로그 파일의 경우 0, 다른 SQL Server 파일의 경우 2 이하)에 도달할 때까지 해당 RAID 배열에 단순히 더 많은 드라이브를 추가하여 디스크 대기를 해결하는 것이 가능합니다. 이러한 작업은 SQL Server가 온라인일 때 수행할 수 있습니다.

디스크 I/O 병렬 처리 최적화를 위해 위에서 설명한 기술은 다양한 수준의 관리가 필요합니다. 세그먼트를 사용한 분리는 정기적인 관심이 필요합니다. 일부 DBA는 이러한 관리 오버헤드를 피하려고 합니다. I/O 용량을 개별 영역으로 분할하는 대신 DBA는 하나의 대형 I/O "드라이브 풀"을 만들 수도 있습니다(트랜잭션 로그 제외). 드라이브 풀은 Windows NT에서 하나의 논리 드라이브 문자로 나타나는 하나의 RAID 배열이거나 Windows NT 및 SQL Server에 대해 하나의 논리 드라이브 문자를 형성하기 위해 모든 RAID 배열을 통해 내결함성을 정의하지 않은 Windows NT 스트라이프 세트가 있는 다수의 RAID 배열 집합일 수 있습니다. 이렇게 하면 SQL Server 관리가 상당히 간단해지며 SQL Server에 디스크 하위 시스템 성능을 제공하는 데 충분할 수 있습니다. 하나의 드라이브 풀에서 디스크 대기를 살펴볼 수 있으며 필요에 따라 풀에 더 많은 하드 드라이브를 추가하여 디스크 대기를 막을 수 있습니다. 이러한 기술은 데이터베이스에서 가장 많이 사용되는 부분을 알지 못하는 일반적인 경우에 최적화를 도와줍니다. 사용 가능한 I/O 용량의 1/3이나 ½이 일부 다른 디스크 파티션에서 분리되지 않게 하는 것이 좋습니다. 분리가 되면 SQL Server에서 그에 대한 I/O 수행을 위해 5%의 시간을 소모하게 됩니다. 정리하면 이 간단한 방법은 모든 사용 가능한 I/O 용량을 공통적인 SQL Server 작업에서 사용할 수 있게 만드는 데 도움이 됩니다.

15 ShowPlan 사용 정보

SQL Server 6.5 텍스트 기반 ShowPlan은 찾을 주요 단어에 집중하고 거의 연습 없이 사용할 수 있어 SQL 테이블 및 쿼리 문제에 집중할 수 있도록 도와줍니다. ShowPlan 출력에 표시된 용어의 의미에 대해서는 SQL Server 온라인 설명서를 참조하십시오.

특히 주목해야 할 ShowPlan 용어는 다음과 같습니다.

REFORMATTING

SQL Server에서 최적의 성능을 위해 Tempdb에서 작업 테이블을 생성한 다음 해당 작업 테이블에 대해 클러스터된 인덱스를 만드는 것이 좋다고 판단한 것을 의미합니다. 처음부터 테이블에 해당 인덱스가 배치되는 것이 더 좋습니다.

TABLE SCAN

항상 나쁜 의미는 아닙니다. 테이블이 작다면 테이블 스캔은 매우 효율적입니다. 테이블이 아주 크다면 테이블 스캔을 방지하기 위해 테이블에 대한 더 좋은 인덱스를 정의하는 것이 중요합니다.

WORKTABLE

이것은 작업 테이블 생성에 tempdb가 사용된다는 것을 나타냅니다. 많은 작업 테이블을 만들 경우, tempdb에 대한 과도한 디스크 I/O 작업에 주의해야 하며 디스크 대기를 살펴봐야 합니다. tempdb를 I/O 요구에 충분한 드라이브 양이 있는 고유한 디스크 I/O 채널로 이동하는 것을 고려합니다.

ShowPlan에 대한 자세한 내용은 SQL Server 온라인 설명서의 23장 "Understanding ShowPlan Output"을 참조하십시오.

16 Windows NT/SQL 성능 모니터 사용 정보

그래프 모드에서 Max 및 Min 값에 주목합니다. 양극화된 데이터 점은 의미가 없기 때문에 평균에 너무 집착해서는 안됩니다. 그래프 형태를 분석하고 Min/Max를 비교하여 작동에 대한 정확한 느낌을 가집니다.

<백스페이스> 키를 사용하여 흰색 선으로 카운터를 강조 표시합니다.

로깅 기능을 사용합니다. Windows NT 성능 모니터를 사용하면 성능 모니터를 대화식으로 살펴보면서(차트 모드) 모든 사용 가능한 Windows NT 및 SQL Server 성능 모니터 개체/카운터를 로그 파일에 로그할 수 있습니다. 샘플 간격의 설정은 로그 파일 크기가 증가하는 정도를 결정합니다. 로그 파일은 매우 빨리 커질 수 있습니다(예: 모든 카운터를 켜고 샘플 간격이 15초이면 한 시간 안에 100MB). 다행히 테스트 서버에는 수 GB의 사용 가능한 공간이 있어 이러한 종류의 파일을 저장할 수 있습니다. 하지만 공간을 절약하는 것이 중요하다면 성능 모니터가 너무 자주 시스템을 샘플링하지 않도록 긴 로그 간격으로 실행해봅니다. 30초나 60초를 시도해봅니다. 이러한 방법으로 모든 카운터가 합리적인 빈도로 다시 샘플링되지만 로그 파일은 더 작게 유지됩니다.

또한 성능 모니터는 작은 양의 CPU와 디스크 I/O 리소스를 소모합니다. 시스템에 할애할 디스크 I/O 및/또는 CPU가 많지 않다면 다른 시스템에서 성능 모니터를 실행하여 네트워크를 통해 SQL Server를 모니터링하거나(그래프 모드 전용, SQL Server에서 로컬에 성능 정보를 로깅하는 것이 LAN을 통해 정보를 보내는 것보다 효율적) 또는 가장 중요한 카운터만 로깅하도록 줄입니다.

성능 모니터 테스트 실행 중에 사용할 수 있는 모든 카운터를 나중에 분석할 수 있도록 파일에 로그하는 것이 좋습니다. 이러한 방법으로 나중에 모든 카운터를 자세히 검토할 수 있습니다. 성능 모니터가 로그 파일에 모든 카운터를 로그하고 동시에 가장 관심 있는 카운터를 그래프 모드와 같은 다른 모드 중 하나에 모니터링하도록 구성합니다. 이러한 방법으로 성능 실행을 하면서 모든 정보를 기록하고 가장 관심 있는 카운터를 정리된 성능 모니터 그래프에서 볼 수 있습니다.

로깅 기능 시작

1. Windows NT/SQL 성능 모니터를 엽니다.
2. 보기/로그 메뉴 항목을 선택합니다.
3. '+' 기호가 있는 단추를 누릅니다.
4. 모든 카운터를 로그하는 간단한 방법은 "로그에 추가" 대화 상자에서 첫 번째 개체를 왼쪽 마우스 단추로 누르고
5. Shift 키를 누른 상태에서 Page Down 키를 사용하여 모든 카운터를 강조 표시하는 것입니다.
6. "추가" 단추를 누릅니다.
7. "완료" 단추를 누릅니다.
8. 옵션/로그 메뉴 항목을 선택합니다.
9. "파일 이름:" 영역에서 성능 정보를 로그할 파일 이름을 선택하거나 만듭니다.
10. "로그 시작" 단추를 누릅니다.

로깅 기능 중지

1. 옵션/로그 메뉴 항목을 선택합니다.
2. "로그 중지" 단추를 누릅니다.

분석을 위해 성능 모니터로 로그된 정보 로드

1. 보기/로그 메뉴 항목을 선택합니다.
2. 옵션/데이터 가져오기 메뉴 항목을 선택합니다.
3. "로그 파일" 라디오 단추를 누릅니다.
4. "..." 단추를 누르고 "입력 로그 파일 열기" 파일 탐색 창을 사용하여 로그 파일을 찾고 엽니다. 파일 이름을 두 번 누릅니다.
5. "확인" 단추를 누릅니다.
6. '+' 기호가 있는 단추를 누릅니다.
7. "차트에 추가" 대화 상자를 사용하여 그래픽 화면에 원하는 카운터를 추가합니다. 추가할 개체/카운터 조합을 강조 표시한 다음 "추가" 단추를 누릅니다.

서버에서 해당 기간 동안 문제가 발생한 것을 알면 Windows NT 성능 모니터의 이 기능으로 지정한 기간에 Windows NT/SQL 성능 모니터에 기록된 내용을 매우 간편하게 살펴볼 수 있습니다.

1. 위의 지시에 따라 성능 모니터 차트 모드에서 원하는 개체/카운터를 선택하고 표시합니다.
2. "편집/시간 창" 메뉴 항목을 선택합니다.
3. "입력 로그 파일 시간 프레임" 대화 상자가 나타나야 합니다. 제공된 시간 창의 슬라이드 막대 위에서 왼쪽 마우스 단추를 누른 상태로 유지하면 성능 모니터 차트에 표시될 로그된 데이터의 시간 창 시작과 끝을 조정할 수 있습니다.
4. "확인" 단추를 눌러 선택한 시간 창에 로그된 데이터만 표시하도록 차트를 다시 설정합니다.

SQL Server 카운터를 가져오는 데 문제가 있으면 Windows NT 성능 모니터가 나타납니다. Microsoft TechNet 등을 통해 참조할 수 있는 다음과 같은 Microsoft 기술 자료 문서를 참조하십시오.

http://www.microsoft.com\Support

ID: Q127207 Missing Objects and Counters in Performance Monitor [winnt]
ID: Q103109 Changes to Performance Monitor After Installing SQL Server [winnt]
ID: Q112610 PRB: Performance Monitor for SQL Performance Counters Missing [sqlserver]
ID: Q115786 Counters in PerfMon Disappear after Removing SQL Server [winnt]
ID: Q173180 PRB: Cannot Reinstall SQL Server After Deleting Files Manually [sqlserver]

위 문서에서 도움을 얻을 수 없으면 다음과 같은 NT 레지스트리 키가 올바른지 확인합니다. regedt32.exe를 사용하여 "HKEY_LOCAL_MACHINE \System \CurrentControlSet \Services \MSSQLServer \Performance"를 찾습니다. 이 키가 없으면 regedt32.exe를 사용하여 수동으로 키를 추가합니다. 다음과 같은 4가지 기본 성능 모니터 값이 있는지 확인하여 아직 없다면 수동으로 다음과 같은 문자열로 값을 추가합니다.

Close: REG_SZ: CloseSQLPerformanceData
Open: REG_SZ: OpenSQLPerformanceData
Collect: REG_SZ: CollectSQLPerformanceData
Library: REG_SZ: sqlctr60.dll

흔하지는 않지만 발생할 수 있는 또 다른 문제는 \winnt\system32에 있는 성능 모니터 파일이 잘못되어 원래 파일로 갱신할 필요가 있는 경우입니다. 그와 같은 경우의 한 증상은 성능 모니터를 시작하려고 하면 "컴퓨터를 찾을 수 없음"이라는 팝업 메시지 대화 상자가 나타나는 것입니다. 이러한 파일들은 다음과 같습니다.

\winnt\system32\perfh009.dat
\winnt\system32\perfc009.dat

Windows NT Server 설치 CD에서 사용할 수 있는 expand.exe를 사용하여 Windows NT Server 설치 CD에서 원래 perfh009.da_와 perfc009.da_의 압축을 풀어 이 두 파일을 갱신합니다. 먼저 이 두 압축 파일을 \winnt\system32 디렉터리로 복사합니다. expand.exe 명령에 대한 자세한 내용은 명령 프롬프트 창의 명령줄에서 "expand /?"를 입력하십시오. 필요하다면 Windows NT 설치 미디어에서 expand.exe를 \winnt\system32로 복사합니다.

파일의 압축을 푸는 구문은 다음과 같습니다.

17 프로세서 모니터링

SQL Server의 성능 조정에 있어 서버의 모든 프로세서를 사용하여 성능을 최대화하는 것이 좋지만 지나치게 사용하여 병목 상태가 생기지 않게 해야 합니다.

성능 조정에서 해결해야 하는 것은 CPU가 병목 상태가 아니라면 다른 것(대개는 디스크 하위 시스템)이 병목 상태이고 그로 인해 CPU가 소모된다는 것입니다. 일반적으로 CPU는 가장 확장하기 어려운 리소스이기 때문에(많은 현재 시스템에서 4 또는 8과 같이 일부 구성 특정 수준 이상) CPU 사용률이 95% 이상이라면 좋은 상태로 봐야 합니다. 동시에 트랜잭션 응답 시간을 모니터링하여 합리적인지 확인해야 합니다. 그렇지 않다면 95% 이하의 CPU 사용량은 단순히 사용 가능한 CPU 리소스 이상으로 작업 로드가 많은 것을 의미하며 CPU를 늘리거나 또는 작업 로드를 줄이거나 조정해야 한다는 것을 의미합니다.

Windows NT/SQL 성능 모니터 카운터 Processor:Processor Time %를 살펴 각 CPU에서 모든 프로세서가 지속적으로 95% 사용률 이하인지 확인해야 합니다. System:Processor Queue는 Windows NT 시스템에서 모든 CPU에 대한 프로세서 대기열입니다. "System: Processor Queue"가 2보다 크면 CPU 병목 상태를 나타냅니다. CPU 병목 상태가 발견되면 서버에 프로세서를 추가하거나 또는 시스템에서 작업 로드를 줄여야 합니다. 작업 로드를 줄이는 것은 쿼리 조정이나 인덱스를 개선하여 I/O와 그에 따른 CPU 사용을 줄여 달성할 수 있습니다.

CPU 병목 상태가 의심스러울 때 살펴볼 다른 성능 모니터 카운터는 "System: Context Switches/sec"입니다. 이것은 Windows NT와 SQL Server가 한 스레드 실행에서 다른 스레드 실행으로 변경해야 하는 시간(초)을 나타냅니다. 비용은 CPU 리소스입니다. 컨텍스트 전환은 멀티스레드, 멀티프로세서 환경의 일반적인 구성 요소지만 과도한 컨텍스트 전환은 시스템 성능을 저하시킵니다. 프로세서 대기가 있다면 컨텍스트 전환만 고려하는 방법을 선택해야 합니다. 프로세서 대기가 관찰되면 SQL Server 성능 조정 시에 컨텍스트 전환 수준을 측정 단위로 사용합니다. SQL Server에서 사용 중인 스레드 수를 줄이는 것을 고려하고 컨텍스트 전환에서 효과가 있는지 살펴봅니다. 최대 작업자 스레드와 RA 작업자 스레드는 SQL Server에 의한 과도한 스레드 사용을 제거하기 위해 사용해야 하는 기본 설정입니다. RA 작업자 스레드는 위에서 설명했습니다. 최대 작업자 스레드는 sp_configure나 SQL Enterprise를 통해서도 수정됩니다. 최대 작업자 스레드를 줄이고 효과를 모니터링하기 위해 프로세서 대기와 컨텍스트 전환을 살펴봅니다.

DBCC SQLPERF (THREADS)는 I/O, 메모리 및 spid에 매핑되는 CPU 사용 등에 대한 자세한 정보를 제공합니다. SQL Server 온라인 설명서에 이에 대한 더 자세한 내용이 나옵니다. 현재 CPU 시간을 가장 많이 사용하는 항목을 조사하려면 "select * from sysprocesses order by cpu desc" SQL 쿼리를 실행합니다.

18 디스크 I/O 카운터

Disk Write Bytes/Sec 및 Disk Read Bytes/Sec 카운터는 바이트/초/논리 드라이브의 형식으로 데이터 출력을 표현합니다. Disk Reads/Sec 및 Disk Writes/Sec을 따라 주의하여 이 숫자들을 가늠합니다. 초 당 바이트 양이 낮다고 해서 디스크 I/O 하위 시스템이 사용되지 않는다고 확신해서는 안됩니다. 하나의 하드 드라이브가 초 당 총 75개의 비순차 또는 150개의 순차 디스크 읽기와 디스크 쓰기를 지원할 수 있다는 것을 기억하십시오.

SQL Server 로그 파일이 논리 드라이브의 한 집합에 위치하고 SQL Server 데이터 파일이 다른 별도의 논리 드라이브 집합에 위치하는 한 성능 모니터의 Logical Drive 카운터를 사용하여 디스크 병목 상태의 원인 지점을 확인하는 것이 쉽습니다. SQL Server 파일과 관련된 모든 논리 드라이브에 대한 Disk Queue Lengths를 모니터링하고 과도한 디스크 대기와 관련된 파일을 결정합니다.

성능 모니터에서 특정 논리 드라이브가 다른 논리 드라이브보다 사용량이 적은 것으로 나타나면 SQL Server 장치 파일을 병목 상태가 발생한 논리 드라이브에서 그 만큼 사용하고 있지 않은 드라이브로 옮길 수 있습니다. 이렇게 하면 하드 드라이브 간에 더 균등하게 디스크 I/O 작업을 분산시킬 수 있습니다.

디스크 대기가 발생할 때 디스크 I/O의 균형을 조정하는 간단한 방법 중 하나는 sp_movedevice라는 SQL Server 저장 프로시저를 사용하여 SQL Server 데이터베이스 장치 파일의 위치를 변경하는 것입니다. 주의: 실제 장치나 의심스러운 DB를 옮기기에 앞서 sp_movedevice를 사용합니다. sp_movedevice에 대한 자세한 내용은 SQL Server 온라인 설명서를 참조하십시오.

사용할 수 있는 SCSI 채널이 둘 이상이고 여전히 디스크 대기가 발생한다면 SCSI 채널이 I/O 요청으로 포화 상태가 될 수도 있습니다. (10개 이상의) 많은 디스크 드라이브가 SCSI 채널에 연결되고 모두 최고 속도로 I/O를 수행하는 경우에 이러한 일이 자주 발생합니다. 이런 경우에는 디스크 드라이브의 반을 다른 SCSI 채널에 연결하여 I/O의 균형을 조정하는 것이 좋습니다.

SQL Server에 대한 정보: Cache Hit Ratio: 미리 읽기 관리자가 캐시에서 필요한 페이지를 로딩하는 구성 요소라면 이 숫자를 인공적으로 높게 만들 수 있습니다. 응용 프로그램을 미리 읽기 관리자를 끈 상태와 미리 읽기 관리자를 켠 상태에서 벤치마크하고 캐시 적중 비율을 비교하여 이 이론을 테스트하는 것이 가능합니다. 미리 읽기 관리자를 끄려면 sp_configure를 사용하여 "미리 읽기 작업 스레드"를 영으로 설정합니다.

미리 읽기 관리자가 있건 없건 캐시 적중 비율이 일정하게 유지되고 캐시 적중 비율이 높으면(> 95%) 이 SQL Server를 위한 충분한 RAM과 적절한 데이터 캐시 할당이 있다는 의미이므로 축하해도 됩니다. 미리 읽기 관리자를 끈 후에 캐시 적중 비율이 많이 나빠진다면 서버에 더 많은 RAM을 추가해야 한다는 의미이므로 SQL Server 메모리를 더 많이 설정해야 합니다.

19 SQLTrace 사용 정보

SQLTrace는 시스템에서 많은 리소스를 소모하는 SQL 쿼리를 확인할 수 있도록 도와주는 간편한 도구입니다. 사용량이 많은 OLTP 시스템에서 SQLTrace는 눈으로 조사해서는 알기 어려운 대단히 많은 양의 정보를 반환합니다. 이러한 정보 홍수를 극복하고 SQLTrace의 디버그 기능을 이용하는 쉬운 방법은 SQL Trace가 SQL 쿼리와 관련 시스템 리소스 소모를 텍스트 기반 로그 파일에 추적하도록 설정한 다음 SQL Server BCP 유틸리티를 사용하여 이 로그 파일을 SQL 쿼리가 신속히 분석할 수 있도록 SQL Server에 로드합니다. 작업 방법을 다음과 같습니다.

SQLTrace를 파일에 로그

1. SQLTrace를 시작하여 SQL Server에 연결합니다.
2. File/'New Filter' 메뉴 항목을 선택합니다.
3. 필터 이름을 입력합니다.
4. 'Include Performance Information' 확인란을 선택합니다.
5. 'Save as Log File' 확인란을 선택하고 로그된 데이터를 유지할 파일 이름을 선택합니다.
6. 'Add' 단추를 눌러 추적을 시작합니다.
7. 필요한 모든 정보를 수집했으면 File/'Stop FiltersU' 메뉴 항목을 선택합니다. 활성 필터를 강조 표시하고 'OK'라고 표시된 단추를 누릅니다.
8. File/'Disconnect' 메뉴 항목을 선택합니다.
9. File/'Exit' 메뉴 항목을 선택합니다.
1. SQLTrace에서 로그된 정보를 유지할 다음과 같은 SQL Server 테이블을 만듭니다.
/* BCP를 통해 SQL TRACE 데이터를 받을 테이블 만들기 */
CREATE TABLE SQLTrace (
Event char(12) NULL ,
UserName char(30) NULL ,
ID int NULL ,
SPID int NULL ,
StartTime datetime NULL,
EndTime datetime NULL,
Application char(30) NULL,
Data text NULL ,
Duration int NULL,
CPU int NULL ,
Reads int NULL ,
Writes int NULL ,
NT_Domain varchar(30) NULL ,
NT_User varchar(30) NULL ,
HostName varchar(30) NULL ,
HostProcess varchar(13) NULL )
GO
2. BCP를 통해 서버로 갑니다.

다음과 같은 명령을 사용하여 위에서 정의한 SQL 테이블에 SQL Trace 로그 정보를 가져옵니다.

bcp master..sqltrace in c:\<경로 이름>\<sqltracefile>.log /Usa /c /P<sa 암호>

3. 테이블에 많이 행이 있으면 빠른 쿼리 실행을 위해 먼저 테이블에 인덱스를 만듭니다. 다음에 SQL Trace 테이블을 분석하는 데 도움이 될 수 있는 몇 가지 인덱스 예가 있습니다.
CREATE INDEX cpu_index ON SQLTrace(CPU)
GO
CREATE INDEX PhysicalDiskRead_index ON SQLTrace(Reads)
GO
CREATE INDEX PhysicalDiskWrites_index ON SQLTrace(Writes)
GO
CREATE INDEX duration_index ON SQLTrace(Duration)
GO
CREATE INDEX ntuser_index ON SQLTrace(NT_User)
GO
CREATE INDEX starttime_index ON SQLTrace(StartTime)
GO
4. 일단 성능 데이터가 SQLTrace 테이블에 입력되고 인덱스가 만들어지면 SQL Server 리소스를 가장 많이 사용하는 소비자들을 조사하는 것이 가능합니다. 다음은 SQL Server에서 CPU와 디스크 I/O 리소스를 가장 많이 사용하는 사용자와 SQL 쿼리를 결정하는 데 도움이 될 수 있는 몇 가지 쿼리 예제입니다.

select username,data from sqltrace order by CPU desc
select username,data from sqltrace order by reads desc
select username,data from sqltrace order by writes desc

20 RAM의 Tempdb

이 주제는 굉장히 자주 등장합니다. 대개는 이 옵션을 사용하지 않는 것이 좋습니다. SQL Server 데이터 캐시는 tempdb를 포함한 모든 데이터베이스에 사용됩니다. RAM에 tempdb를 두면 RAMD을 tempdb와 다른 데이터베이스가 공유해야 하므로 SQL Server의 융통성이 떨어집니다. tempdb를 RAM에 두고 상당한 tempdb 사용이 있는 상태에서 미리 읽기 작업 스레드를 영으로 설정하여 미리 읽기 관리자를 꺼도 SQL Server의 캐시 적중 비율이 정말 좋다면 tempdb를 RAM에 유지해도 됩니다. 하지만 고유한 디스크 집합에 tempdb를 제공하여 디스크 I/O 균형을 조정하고 최상의 tempdb 성능을 위해 이러한 디스크 집합에서 디스크 대기를 최소화하는 것이 좋다고 생각합니다. tempdb는 임시 데이터를 유지하고 SQL Server를 다시 시작할 때마다 다시 작성되기 때문에 최대 디스크 I/O 성능을 위해서는 tempdb를 RAID 0 배열에 두어야 합니다.

SQL Server 환경에서 ORDER BY와 GROUP BY가 많이 사용되는 것을 알고 있다면 SQL Server가 이러한 작업을 지원하기 위해 많은 작업 테이블을 만든다는 의미입니다. (SQL Server에 실제 테이블에 대해 작업할 좋은 인덱스가 없었기 때문에 작업 테이블에서 클러스터된 인덱스를 만들어) REFORMATTING용으로 작업 테이블이 만들어진다면 이 테이블이 SQL Server에서 그것을 필요로 하는 테이블에 대한 인덱스에 가깝게 배치되었는지 검토하는 것이 중요합니다. 따라서 SQL Server는 처음에 작업 테이블을 만들 필요가 없습니다.

tempdb가 RAM에 있고 SQL Server 데이터 캐시가 다른 데이터베이스에서 빈번히 액세스되는 페이지를 충분히 유지하지 못하면 캐시 적중 비율이 떨어지고 tempdb가 RAM에 있음으로 해서 제공되는 모든 디스크 I/O 절약이 SQL Server 데이터 캐시 대신 디스크에서 읽어와야 하는 다른 데이터 페이지에 필요한 증가된 디스크 I/O기 때문에 무시됩니다.

하지만 서버에서 사용할 수 있는 RAM이 많고 테스트 중에 미리 읽기 관리자를 끄고(미리 읽기 작업자 스레드를 0으로 설정) tempdb가 RAM에 있는 상태에서 최대 쿼리 로드의 캐시 적중 비율이 정말 좋다면(99%) SQL Server가 tempdb에 대한 디스크 쓰기 I/O 성능 이점을 얻고 있는 것이며 데이터 캐시에서 데이터를 읽는 나머지 SQL Server 작업 중에 손실이 발생하지 않는 것이기 때문에 RAM에 tempdb를 유지해도 좋습니다. SQL Server 응용 프로그램 환경의 tempdb 작업 특성이 일회성 쓰기 및 읽기 작업이라면 특히 성능에 이롭습니다.

21 교착 상태

SQL Server 교착 상태와 관련된 몇 가지 팁이 있습니다.

1. SQL Server Service Pack 4를 설치합니다. 이 서비스 팩은 Microsoft TechNet의 SQL Server 디렉터리에서 다운로드할 수 있습니다. SQL Server SP4는 여러 가지 교착 상태 문제를 해결합니다.
2. SQL Server를 액세스하는 응용 프로그램의 아키텍처가 모든 사용자 트랜잭션 간에서 동일한 시간순으로 테이블을 액세스하는 것이라면 교착 상태가 제거됩니다. SQL 응용 프로그램 개발자에게 응용 프로그램 설계 과정에서 가능한 한 초기에 이 시간순 테이블 액세스 개념을 명확하게 설명하는 것이 좋습니다. 이렇게 하면 나중에 교착 상태 문제를 해결하기 위해 더 많은 비용이 드는 것을 방지할 수 있습니다. "Inside Microsoft SQL Server 6.5"의 641쪽에서 시작하는 항목에서 이 내용을 자세히 설명하고 있습니다.
3. 서로 다른 필드에서 클러스터된 인덱스를 배치합니다. 이렇게 하면 디스크에서 실제로 데이터 페이지를 다시 구성합니다. 주의하십시오. 상황을 잘 파악하지 않으면 이로 인해 상황이 더 나빠질 수 있습니다. 클러스터된 인덱스 필드가 현재 교착 상태를 유발하는 쿼리 액세스 경로를 결정하고 클러스터된 인덱스를 다른 위치로 옮겨 효율적으로 쿼리를 수행할 수 있다는 것을 안다면 클러스터된 인덱스를 옮겨야 합니다.
4. SQL 쿼리 I/O를 줄입니다. 이것은 간접적인 방법이며 어떤 방법으로든 모든 쿼리에서 수행해야 하는 작업이지만 쿼리가 더 빨라지고 더 짧은 기간 동안만 리소스를 잠그며 모든 잠금 경합(교착 상태 포함)이 줄어들기 때문에 도움이 될 수 있습니다. ISQL/W의 SHOW I/O를 사용하여 큰 쿼리와 관련된 논리 페이지 반입 수를 결정합니다. ISQL/W의 SHOWPLAN에서 사용하는 인덱스를 고려합니다. 인덱스 배치나 SQL 쿼리를 조사하여 더 효율적이 되어 더 적은 I/O를 사용하도록 다시 설계합니다.
5. 채움 열로 문제가 있는 테이블을 채웁니다. 해당 테이블에 대한 클러스터된 인덱스를 삭제하고 더 낮은 FillFactor로 다시 만듭니다. 이렇게 하면 테이블이 디스크에서 실제로 넓게 퍼지며 해당 테이블에서 데이터 페이지에 대한 동시 요구가 감소합니다. 이것은 더 많은 디스크 공간을 사용하며 인덱스와 데이터 페이지의 압축을 줄여 성능 비용이 증가합니다. 하지만 교착 상태를 줄일 수 있습니다.
6. 교착 상태가 포함된 테이블에 대한 삽입 행 수준 잠금(IRL)을 켭니다. IRL은 삽입 트랜잭션이 모두 동일한 2KB 페이지(예: 테이블 끝)를 대상으로 할 때 도움이 됩니다. IRL을 사용하려면 다음 명령을 실행합니다.

sp_tableoption 테이블이름, 'insert row lock', TRUE

주의 IRL을 교착 상태를 해결하는 기본 방법으로 써서는 안됩니다. 때때로 교착 상태를 증가시키는 것으로 알려져 있습니다.

"Inside Microsoft SQL Server 6.5"에는 685쪽부터 시작하는 교착 상태 문제를 분석하고 해결하는 것에 대한 자세한 절이 있습니다.

22 조인 순서 중요성

SQL Server I/O는 주어진 SQL 쿼리 조인 테이블의 순서에 많은 영향을 받을 수 있습니다. 가장 좋은 성능 시나리오는 SQL 쿼리에서 반입된 페이지를 최소화하는 효율적인 인덱스를 사용하여 오름차순으로 가장 적은 수의 행이 일치하는 테이블부터 많은 행이 일치하는 테이블 순으로 테이블을 조인하는 것입니다. 이것은 중첩된 반복 조인의 특성 때문입니다. 이 항목에 대한 자세한 내용은 "The SQL Server 6.5 Performance Optimization and Tuning Handbook"의 87쪽을 참조하십시오.

SQL Server는 조인을 처리할 때 먼저 SQL 쿼리가 설정한 기준과 일치하는 행을 검색하기 위해 스캔될 외부 테이블을 선택해야 합니다. 이 테이블이 크다면 I/O를 줄일 수 있는 좋은 인덱스가 테이블에 있을 것입니다. 그러면 SQL Server가 내부 테이블을 선택해야 합니다. 내부 테이블을 액세스해야 하는 횟수는 쿼리 조인 조건을 만족시키는 외부 테이블에서 찾은 행 수에 의해 결정됩니다. 이것은 STATISTICS I/O 출력에서 SCAN COUNT로 나타납니다. 내부 테이블 스캔의 I/O 성능은 내부 테이블의 조인 기준을 포함한 열에 좋은 인덱스가 배치되었는지 확인하여 최적화할 수 있습니다.

SHOWPLAN 출력에 나타나는 테이블 순서가 SQL Server 조인 순서를 나타냅니다. STATISTICS I/O 출력은 조인 순서와 같지 않으며 단지 테이블이 쿼리에 나타나는 순서입니다.

조인 순서와 관련된 경험적인 규칙은 N개의 테이블이 있는 각 SQL 쿼리에서 쿼리가 적어도 N-1개의 조인 조건을 포함하는지 확인하는 것입니다. 느린 SQL 쿼리에서 I/O 카운트, SHOWPLAN 조인 순서를 살펴보고 SQL Server 조인 순서에 더 많은 융통성을 부여하는 조인 조건을 추가합니다.

예제: select * from tab1, tab2, tab3 where tab1.c1 = tab2.c1

위의 쿼리에는 조인에 3개의 테이블이 있습니다. N/N-1 규칙에 따라 적어도 2개의 조인 조건이 있어야 합니다. SQL Server 최적화 프로그램에게 조인 순서를 결정하는 더 많은 융통성을 부여하기 때문에 N-1개 대신 N개의 조인 조건을 만들어도 해가 되지 않습니다. 테이블이 작아 테이블 스캔이 효율적인 경우가 아니라면, 위의 쿼리가 느린 경우에 모든 테이블에서 c1 열에 대한 인덱스가 있는지 확인하여 쿼리를 수정합니다.

select * from tab1,tab2,tab3 where tab1.c1 = tab2.c1 and tab2.c1 = tab3.c1 and tab1.c1 = tab3.c1

이 시점에서도 쿼리가 느리다면 DBCC SHOW_STATISTICS로 인덱스를 업데이트한 최종 시간을 확인합니다. 인덱스를 만들거나 UPDATE STATISTICS를 실행한 이후로 많은 행이 추가되었다면 UPDATE STATISTICS 실행합니다.

23 SQL에서 가능한 한 피해야 할 사항

SQL 쿼리에서 부등 연산자를 사용하면 데이터베이스에서 부등 조건을 평가하기 위해 테이블 스캔을 사용하게 됩니다. 이러한 쿼리가 정기적으로 대형 테이블을 다루어야 한다면 대량의 I/O가 발생할 수 있습니다.

예제:

WHERE <열_이름> != 특정_값
WHERE <열_이름> <> 특정_값
NOT이 포함된 모든 WHERE 식

데이터베이스 환경에서 이러한 종류의 쿼리를 실행해야 한다면 NOT 키워드를 제거하도록 쿼리를 다시 구성해 보십시오. 다음 예를 참조하십시오.

다음 쿼리에서

이렇게 하면 SQL Server에서 테이블 스캔에 의존할 필요 없이 col1에서 생성된 인덱스를 사용할 수 있습니다(이 경우 클러스터된 인덱스).

24 ShowPlan을 사용하여 인덱스를 사용하지 않는 쿼리 검색

SQL Trace를 사용하여 많은 수의 리소스를 소모하는 SQL 쿼리를 확인한 후에 ISQL/W와 ShowPlan을 사용하여 문제가 있는 쿼리를 자세히 분석합니다.

select 목록에서 반할할 필요가 없는 열을 제거하거나, 반환될 행의 수를 줄이거나 또는 SQL 쿼리에서 요구하는 전체 I/O 양을 줄이도록 인덱스를 다시 정의하여 반환될 결과 집합의 크기를 줄일 수 있는지 조사합니다.

이러한 방법은 VB/VC 및 MFC/VC에서 ADO/RDO/DAO로 SQL 작업을 하는 SQL 프로그래머에게 특히 유용할 수 있습니다. ADO/RDO/DAO/MFC는 프로그래머에게 풍부한 SQL 행 집합 기능을 이용할 수 있게 하는 뛰어난 데이터베이스 개발 인터페이스를 제공합니다. 이러한 인터페이스 사용에 많은 SQL 프로그램 경험은 필요 없습니다. 하지만 추가 비용이 듭니다. 프로그래머가 자신의 응용 프로그램에서 클라이언트에 반환할 데이터의 양을 주의 깊게 고려하지 않고 SQL Server 인덱스 배치 위치와 SQL Server 데이터 배열 방법에 지속적인 관심을 가지지 않는다면 성능 문제가 발생할 수 있습니다. SQL Trace와 ShowPlan은 이러한 문제가 있는 쿼리를 확인하는 좋은 도구입니다.

25 자동 정규화

액세스 양이 대단히 많은 테이블에서 SQL 응용 프로그램이 정기적으로 필요로 하지 않는 열이 있다면 다른 테이블로 옮기는 것이 좋습니다. 많은 열을 제거할수록 I/O 감소에 좋으며 성능이 향상됩니다.

26 특수한 디스크 I/O 조정 시나리오: EMC Symmetrix 통합 캐시 디스크 배열

EMC Symmetrix Enterprise Storage Systems에서 SQL Server 데이터베이스 시스템을 구현한 경우라면 디스크 I/O 병목 상태를 피하고 성능을 최대화하도록 돕는 EMC Symmetrix 저장소의 고유한 특성 때문에 유념해야 할 몇 가지 디스크 I/O 균형 조정 방법이 있습니다.

Symmetrix 저장소 시스템은 최대 4GB의 RAM 캐시를 포함하며 호스트 서버의 CPU 리소스를 사용할 필요 없이 데이터의 빠른 I/O 처리를 돕도록 디스크 배열 내에 내부 프로세서가 포함됩니다. Symmetrix 장치 안에는 디스크 I/O 균형 조정을 위한 작업 방법을 이해하기 위해 고려해야 할 4가지 주요 구성 요소가 있습니다. 그 중 하나가 Symmetrix 내부에 있는 4GB 캐시입니다. 또한 Windows NT 호스트 서버에서 Symmetrix로 최대 32개의 SCSI 카드를 연결하는 데 사용할 수 있는 최대 32개의 SA 채널이 있으며 모든 SA 채널은 이 4GB 캐시에서 동시에 데이터를 요청할 수 있습니다. 그리고 Symmetrix 장치 안에는 DA 컨트롤러라고 하는 최대 32개의 커넥터가 있습니다. 이것은 Symmetrix 안에 있는 모든 내부 디스크 드라이브를 4GB 내부 캐시에 연결하는 내부 SCSI 컨트롤러입니다. 마지막으로 하드 드라이브가 있습니다.

EMC 하드 드라이브와 관련된 몇 가지 주의 사항이 있습니다. EMC 하드 드라이브에는 이 백서에서 설명한 다른 SCSI 드라이브와 같은 I/O 기능이 있는 SCSI 하드 드라이브가 있습니다(75/150 규칙이 적용됨). EMC 기술과 함께 공통적으로 사용되는 한 가지 기능을 "하이퍼 볼륨"이라고 합니다. 하이퍼 볼륨은 EMC 하드 드라이브의 논리 구역으로 정의되며 Windows NT 디스크 관리자에서 하이퍼 볼륨은 다른 실제 드라이브와 같이 보이기 때문에 다른 모든 디스크 드라이브처럼 Windows NT 디스크 관리자로 조작할 수 있습니다. EMC 드라이브에서 여러 개의 하이퍼 볼륨을 정의할 수 있습니다. 실제 드라이브에 데이터베이스 I/O 로드가 과도하게 걸리는 것을 방지하는 것이 중요하기 때문에 EMC 저장소에서 데이터베이스 성능 조정을 수행할 때 하이퍼 볼륨이 있다면 EMC 실무 엔지니어와의 밀접한 협력 작업을 통해 하이퍼 볼륨이 정의된 방법을 확인해야 합니다. 이러한 과도한 I/O 로드는 두 개 이상의 하이퍼 볼륨이 분리된 실제 드라이브에 있다고 생각했지만 실제로는 두 개 이상의 하이퍼 볼륨이 동일한 실제 드라이브에 있는 경우에 쉽게 발생할 수 있습니다.

SQL Server에 대한 EMC 아키텍처 이점을 살리는 가장 좋은 방법은 SQL Server I/O 작업이 별개의 DA 컨트롤러 사이에 균일하게 분산되게 하는 것입니다. 이것은 DA 컨트롤러가 한정된 하드 드라이브 집합에 할당되기 때문입니다. 이 백서에서 설명한 것처럼 이로 인해 SCSI 컨트롤러에 병목 상태가 발생할 수 있습니다. 이 경우에 염려하는 것은 DA 컨트롤러가 I/O 병목 상태에 처하는 것이 아니라 DA 컨트롤러와 연결된 하드 드라이브 집합이 병목 상태에 처할 수 있다는 것입니다. 이 백서에서 이미 다룬 다른 공급업체의 디스크 드라이브 및 컨트롤러에 대한 방법과 같은 방법으로 DA 컨트롤러의 컨텍스트와 그와 연결된 디스크 드라이브 내에서 SQL Server 디스크 I/O 균형 조정이 달성됩니다. EMC 저장소의 경우, 모든 하이퍼 볼륨의 위치를 아는 것이 중요합니다.

DA 채널이나 분리된 실제 하드 드라이브에서 I/O를 모니터링하는 것에 대해서는 이 I/O 작업이 4GB EMC 캐시 내에서 발생하며 Windows NT/SQL 성능 모니터에서 보이지 않기 때문에 EMC 기술 지원 담당자에게 도움을 얻어야 합니다. EMC 저장소에는 EMC 기술 지원 엔지니어가 EMC 기기 내의 I/O 통계를 모니터링하는 데 사용할 수 있는 내부적인 모니터링 도구가 있습니다. 성능 모니터는 SA 채널에서 들어오는 I/O에 의해 EMC 저장소 기기에서 들어오고 나가는 I/O를 볼 수만 있습니다. 이것은 해당 SA 채널이 Windows NT 디스크 I/O 요청을 대기 중인지 나타내는 데는 충분한 정보이지만 디스크 대기가 발생한 디스크를 알려주지는 않습니다. SA 채널이 대기 중이라고 해도 디스크 I/O 서비스 제공에 지연을 발생시킨 것이 디스크 드라이브일 수도 있기 때문에(실제로 더 많은 경우) SA 채널이 병목 상태의 원인이라고 확신할 수는 없습니다. 드라이브가 관련된 SA 채널과 DA 채널 간의 디스크 I/O 병목 상태를 확인하는 한 가지 방법은 호스트 서버에 다른 SCSI 카드를 추가하여 다른 SA 채널에 연결하는 것입니다. 성능 모니터에서 두 SA 채널 모두를 통과하는 I/O 볼륨이 변경되지 않고 디스크 대기가 계속 발생하는 것으로 나타난다면 병목 상태의 원인이 SA 채널이 아닌 것을 나타냅니다. I/O 병목 상태를 확인하는 또 다른 방법은 EMC 엔지니어가 EMC 모니터링 도구를 통해 EMC 시스템을 모니터링하여 병목 상태가 발생한 드라이브나 DA 채널을 분석하게 하는 것입니다.

SQL Server 작업을 사용할 수 있는 디스크 간에 최대한 균등하게 분산시킵니다. 많은 양의 I/O를 포함하는 작은 데이터베이스로 작업한다면 EMC 기술 지원 엔지니어가 정의한 하이퍼 볼륨 크기를 고려해야 합니다. 예를 들어, SQL Server가 30GB의 데이터를 구성한다고 가정합니다. EMC 하드 드라이브는 최대 23GB 용량을 제공할 수 있습니다. 따라서 전체 데이터베이스가 두 개의 드라이브에 맞습니다. 이것은 관리 효율과 비용 관점에서 보면 문제가 될 수 있지만 I/O 성능 관점에서는 문제가 되지 않습니다. EMC 저장소 기기는 100개 이상의 내부 드라이브를 작동시킬 수 있습니다. 단지 두 개의 드라이브만 SQL Server에 포함시키면 I/O 병목 상태가 발생할 수 있습니다. 대개 각각이 2GB인 더 작은 하이퍼 볼륨을 정의하는 것을 고려합니다. 이것은 거의 12개의 하이퍼 볼륨이 주어진 23GB 하드 드라이브에 관련될 수 있다는 것을 의미합니다. 2GB 하이퍼 볼륨을 가정한다면 데이터베이스 저장에 15개의 하이퍼 볼륨이 필요할 것입니다. 각 하이퍼 볼륨이 별도의 실제 하드 드라이브에 연결되는지 확인합니다. 하나의 실제드라이브에서 12개의 하이퍼 볼륨을 사용하고 또 다른 실제 드라이브에 연결된 나머지 3개의 하이퍼 볼륨을 사용해서는 안됩니다. 이것은 두 개의 실제 드라이브(두 드라이브를 통해 150개의 비순차 I/O / 300개의 순차 I/O)를 사용하는 것과 같습니다. 하지만 각각 별도의 실제 하드 드라이브에 연결된 15개의 하이퍼 볼륨을 사용하면 SQL Server가 15개의 실제 드라이브를 이용하여 I/O(15개의 드라이브를 통해 초 당 1125개의 비순차 / 2250개의 I/O 작업)를 제공합니다.

또한 컨트롤러를 통해 I/O 작업을 분산시킬 수 있도록 호스트 서버에서 여러 개의 EMC SA 채널을 채용하는 것을 고려해야 합니다. 이것은 하나의 PCI 버스가 지원하는 것보다 더 많이 호스트 서버를 지원합니다. 이 경우, 호스트 서버 PCI 버스 당 하나의 SA 채널을 사용하여 SA 채널과 함께 PCI 버스를 통해서도 I/O 작업을 분산시키는 것을 고려합니다. EMC 저장소 시스템에서 각 SA 채널은 특정 DA 채널에 연결되기 때문에 실제 하드 드라이브 특정 집합에 연결됩니다. SA 채널은 EMC 저장소 시스템 내부에서 4GB RAM 캐시를 통해 데이터를 읽고 쓰기 때문에 SA 채널은 거의 I/O 병목 상태를 일으키지 않습니다. 또한 SCSI 컨트롤러 병목 상태가 거의 일어나지 않는다는 것을 고려하면 이용할 SA 채널 수에 대해 걱정하는 대신 실제 드라이브 간의 SQL Server 작업 균형 조정에 시간을 집중하는 것이 가장 좋을 것입니다.

27 SQL Server 성능 조정 시의 일반 정보

이 부분에 가장 적합한 모토는 "준비"입니다. SQL Server 및 기타 Microsoft 제품과 관련된 최신 정보에 대해서는 http://www.microsoft.com\Support를 참조하십시오.

서버에 최신 Windows NT 및 SQL Server 서비스 팩을 설치했는지 확인합니다. 최신 서비스 팩을 다운로드하려면 Microsoft TechNet에서 Windows NT 및 SQL Server 디렉터리를 방문합니다. 일부 서비스 팩은 적절히 압축을 풀고 필요한 하위 디렉터리를 만들기 위해 "-d" 옵션으로 압축을 풀어야 합니다. 서비스 팩의 압축을 올바르게 풀지 않으면 설치가 되지 않습니다.

28 참조

"Inside Microsoft SQL Server 6.5", Ron Soukup 지음. Microsoft Press 출판, ISBN1-57231-331-5. 북아메리카에서는 1-800-MSPRESS로 전화를 걸어 주문합니다. 이 책은 SQL Server 내부 데이터 구조, 구현 세부 정보, 구성 옵션, 커서, SQL Server 응용 프로그램 개발 안내 등의 다양한 내용을 자세히 다룹니다. 저자는 수년간 SQL Server 팀의 개발 팀장이었습니다. 이 책은 SQL Server의 모든 구성 요소 작동 방법에 대해 자세한 정보를 제공합니다. 또한 다음의 권장 도서 목록을 보완합니다.

"The SQL Server 6.5 Performance Optimization and Tuning Handbook", Ken England 지음, Digital Press 출판, ISBN 1-5558-180-3. 이것은 SQL Server 성능 조정과 관련된 중요한 문제를 다루는 훌륭한 책입니다.

http://www.compaq.com/support/techpubs/에는 Microsoft 제품과 관련된 많은 유용한 Compaq 백서가 있습니다. 그 중에 SQL Server 성능 조정과 가장 관련이 많은 3가지는 다음과 같습니다.

Compaq 문서 번호 415A-0696: "Configuration and Tuning of Microsoft SQL Server 6.5 for Windows NT on Compaq Servers - Compaq Corporation"

Compaq의 Windows NT 통합 팀에서 "Disk Subsystem Performance and Scalability"라고 부르는 30쪽짜리 백서. 이것은 Compaq 하드 드라이브의 하드웨어 성능 특성에 대해 자세히 다룹니다. 이 백서는 실제 드라이브 작동에 대해 자세히 다룹니다. 이 백서에 포함된 정보는 대개 Compaq이나 다른 공급업체에서 사용할 수 있는 모든 SISI 하드 드라이브에 적용할 수 있습니다. Compaq 문서 번호는 ECG025.0997입니다.

또 다른 Compaq 백서에서 SQL Server 백업 및 복원 성능을 최대하는 것에 대해 설명합니다. Compaq 문서 번호는 444A-0797이며 제목은 "Compaq Backup and Recovery for Microsoft SQL Server 6.x"입니다. 이것은 Compaq Database Engineering 팀에서 SQL Server 6.5 스트라이프 덤프 기술과 다수의 DLT 테이프 드라이브를 이용하여 시간 당 약 50GB의 백업 및 복원 속도를 달성한 방법을 자세히 설명합니다. 대형 SQL Server 데이터베이스로 작업해야 하는 DBA의 필수 도서입니다.

Microsoft SQL Server 온라인 설명서의 5장 Database Developers Companion에는 매우 유용한 성능 절이 있습니다. 또한, SQL Server 온라인 설명서의 "Understanding ShowPlan Output" 장에는 ShowPlan에 표시되는 용어들의 의미가 제공됩니다. SQL Server 클라이언트 유틸리티나 SQL Server 전체 서버 설치로 하드 디스크에 SQL Server 온라인 설명서를 설치합니다.

"SQL for Smarties", Joe Celko 지음, Morgan Kaufmann Publishers 출판, ISBN 1-55860-323-9, 이 책에는 정말 도움이 되고 실무적인 정보가 들어 있습니다. 계층 데이터를 표현하고 쿼리하는 것과 같은 공통적인 문제에 대한 해결책이 들어 있습니다. 28장에서는 SQL 쿼리 최적화를 자세히 다룹니다.

다음과 같은 Microsoft 기술 자료 문서도 검토할 가치가 있습니다. http://www.microsoft.com\Support를 통해 액세스할 수 있습니다. 또한 이 웹 사이트에는 Microsoft 제품에 대한 최신 기술 지원 정보를 수집할 수 있는 검색 엔진이 있습니다.

ID: Q110352 INF: Optimizing Microsoft SQL Server Performance
ID: Q125770 INF: Locking Behavior of Updates and Deletes in SQL Server
ID: Q162361 INF: Understanding and Resolving SQL Server Blocking Problems
ID: Q167711 INF: Understanding Bufwait and Writelog Timeout Messages
ID: Q102020 How to Monitor Disk Performance with Performance Monitor
ID: Q167610 INF: Assessing Query Performance Degradation
ID: Q89385 INF: Optimizer Index Selection with Stored Procedures
ID: Q166967 Proper SQL Server Configuration Settings
ID: Q117143 INF: When and How to Use dbcancel() or sqlcancel()

Microsoft SQL Server Developer's Resource Kit. Microsoft TechNet(SQL Server Tools and Utilities)에서 확인해 보십시오. 이 키트에서 다양한 SQL Server 항목을 다루는 많은 백서를 다운로드할 수 있습니다.

RAID, RAID 용어 및 디스크 배열에 대한 자세한 내용은 The RAIDbook, A Source Book for Disk Array Technology를 참조하십시오. 이 책은 St. Peter, MN의 The RAID Advisory Board에서 출판했습니다.

다음은 (기울임꼴)로 표시된 위치에서 찾을 수 있는 유용한 정보들입니다.

개발자를 위한 Microsoft SQL Server 성능 조정 및 최적화, 제 1부: 성능 문제 개요 저자: Adam Shapiro
Microsoft TechNet(SQL Server 기술 정보)

개발자를 위한 Microsoft SQL Server 성능 조정 및 최적화, 제 2부: 쿼리 최적화 프로그램 저자: Adam Shapiro
Microsoft TechNet(SQL Server 기술 정보)

개발자를 위한 Microsoft SQL Server 성능 조정 및 최적화, 제 3부: 성능 향상을 위한 SQL Server 구성 저자: Adam Shapiro
Microsoft TechNet(SQL Server 기술 정보)

Microsoft SQL Server Optimization and Tuning. 저자: Tony Scott
Microsoft TechNet(SQL Server 기술 정보)

Designing Efficient Applications for MS SQL Server 백서의 "Database Access Styles" 절
Microsoft TechNet(MS SQL Server Developer's Kit)

"Microsoft SQL Server 6.5 Unleashed, 2^nd edition", 다수의 저자. 이 책은 훌륭한 DBA 및 응용 프로그램 개발자 지침을 제공합니다. 대부분은 성능과 관련된 많은 SQL Server 문제를 다룹니다.

© 1998 Microsoft Corporation. All rights reserved.

이 문서에 포함된 정보는 문서를 발행할 때 논의된 문제들에 대한 Microsoft Corporation의 당시 관점을 나타냅니다. Microsoft는 변화하는 시장 환경에 대처해야 하므로 이를 Microsoft 측의 책임으로 해석해서는 안 되며 발행일 이후 소개된 어떠한 정보에 대해서도 Microsoft는 그 정확성을 보장하지 않습니다.

이 백서는 정보 제공 목적으로만 제공됩니다. Microsoft는 이 설명서에서 어떠한 명시적이거나 묵시적인 보증도 하지 않습니다.

Microsoft, BackOffice 로고, SQL Server, Windows 및 Windows NT는 미국, 대한민국, 및/또는 기타 국가에서의 Microsoft Corporation의 등록 상표 또는 상표입니다.

여기에 인용된 다른 제품이나 회사 이름은 해당 소유자의 상표일 수 있습니다.

Microsoft Corporation • One Microsoft Way • Redmond, WA 98052-6399 • USA