INF: SQL Server의 올바른 구성 설정

이 문서는 이전에 다음 ID로 출판되었음: KR166967
이 문서가 보관되었습니다. "그대로" 제공되었으며, 업데이트가 되지 않을 것입니다.
요약
SQL Server는 상대적으로 약간의 구성 조정(Tuning) 만으로 매우 높은 성능을 얻을 수 있습니다. 예를 들어, Microsoft의 IS 부서에는 4,000명 이상의 동시 사용자를 지원하는 SQL Server가 있습니다. 메모리, 잠금 및 사용자 연결과 같은 확실한 설정 외에 대부분 기본 구성 설정을 사용합니다. 그래서 산업 표준인 4CPU 프로세서 컴퓨터에서 실행하더라도 매우 높은 성능을 제공합니다. 이러한 높은 성능은 광범위한 구성 조정(Tuning)에 의한 것이 아니라 좋은 응용 프로그램 및 데이터베이스 설계에 의해 구현됩니다.

SQL Server 성능 최적화에 대한 자세한 내용은 Microsoft 기술 자료에 있는 다음 문서를 참고하십시오.
110352Optimizing Microsoft SQL Server Performance
성능 문제를 처리할 때 보통 시스템이 이미 제대로 구성되어 있지 않으면 구성 조정(Tuning)으로 얻을 수 있는 개선 정도는 미비합니다. SQL Server 7.0은 자동 구성 조정(Tuning)을 사용하므로 7.0에서 구성 설정(특히, 고급 설정)을 변경하는 경우는 극히 드뭅니다. 설정은 확실한 이유 없이 그리고 변경의 필요성 및 일단 변경된 이후 얻을 수 있는 이점을 확인하기 위한 신중한 조직적 테스트 없이 변경하지 말아야 합니다.

시스템이 제대로 구성되지 않으면 일부 설정은 서버를 불안전하게 만들거나 오류를 일으킬 수 있습니다. 메모리, 잠금 및 사용자 연결과 같은 확실한 조정을 제외한 기타 설정, 특히 고급 설정을 조정하기 전에 한번 더 생각해 봐야 합니다. 새 설정이 Microsoft 기술 지원부에서 권장한 내용과 충돌하는 경우 잡지 기사, 기술 자료 또는 상담원이 제공한 값대로 임의로 조정하지 않도록 하십시오.
추가 정보
수백 개의 사이트에서 수년 동안 지원하여 얻은 경험으로 비추어 볼 때 적합하지 않은 구성 설정을 사용하는 경우 중립적인 결과에서부터 매우 부정적인 결과까지 나올 수 있습니다. 특히, 아래 설정은 확실하고 구체적인 이유 없이 그 기본값을 변경하지 말아야 합니다. 또한 개선 정도를 평가하기 위해 변경 전후에 이루어지는 세심한 조직적 성능 테스트 없이 이를 조정하지 말아야 합니다. 7.0 버전에서는 더욱 그렇습니다. 여러 실제 지원 경우에서 7.0이 수동으로 구성 조정(Tuning)을 하지 않고도 매우 높은 성능을 제공할 수 있음을 보여 줍니다.
선호도 마스크(Affinity mask)(7.0 전용)
라이트웨이트 풀링(Lightweight pooling)(7.0 전용)
설정 작업 집합 크기(Set working set size)
smp 병행성(smp concurrency)(6.5 전용)
우선 순위 높이기(Priority boost)
최대 작업자 스레드(Max worker threads)
프로시저 캐시(Procedure cache)(6.5 전용)
tempdb in RAM(6.5 전용), free buffers(6.5 전용), ReadAhead 매개 변수(6.5 전용) 및 Max Async I/O와 같은 기타 설정 또한 미칠 수 있는 영향에 대한 충분한 이해 또는 이점을 확인하기 위한 목표 성능 테스트 없이 부주의하게 조정하지 말아야 합니다.

본 문서의 나머지는 이러한 매개 변수를 설명하고 이를 사용하는 데 필요한 몇 가지 사항을 제공합니다.

선호도 마스크(Affinity mask)(7.0 전용)

이것은 스레드가 특정 CPU에 얼마나 확실하게 바인드되어 있는지를 나타냅니다. 기본적으로 Windows NT는 "소프트" 선호도를 사용하는데, 이는 스레드가 마지막으로 실행되었던 CPU에서 이를 다시 예약해 놓습니다. 그러나 예약해 놓을 수 없으면 다른 CPU에서 실행할 수도 있습니다. 선호도 마스크(Affinity mask)를 통해 수동으로 조정하여 스레드를 지정된 CPU에 확실하게 바인드할 수 있습니다. 이는 문서화된 Win32 API SetThreadAffinityMas()를 통해 이루어집니다. 이론적으로는 다시 로드하는 작업 수 또는 CPU 캐시 및 파이프라인이 줄므로 성능이 향상됩니다. 그러나 실제 성능을 향상시키는 경우는 드물며 종종 성능을 저하시킵니다.

선호도 마스크(Affinity mask)를 사용하는 두번 째 이유는 SQL Server를 사용 가능한 CPU 하위 집합으로 제한하여 경합하는 기타 서비스에게 더 나은 CPU 액세스를 제공하기 때문입니다. 일반적으로, SQL Server 7.0은 일반 우선 순위(1에서 31의 Windows NT 우선 순위 구성표에서 약 7)에서 실행하므로 이는 별로 필요하지 않습니다. Windows NT 스레드 스케줄러는 경합하는 모든 스레드의 스레드 우선 순위를 동적으로 조정하여 모두 사용 가능한 CPU를 공평하게 사용할 수 있도록 합니다. 매우 이례적인 상황을 제외한 경우에는 선호도 마스크(Affinity mask)를 조정하지 말아야 합니다. 또한 개선의 필요성 및 개선 정도를 검증하기 위해 변경 전후에 세밀한 조직적 테스트를 수행해야 합니다.

라이트웨이트 풀링(Lightweight pooling)(7.0 전용)

기본적으로, SQL Server 7.0은 활성 SPID 또는 사용자 프로세스마다 스레드를 하나씩 사용합니다. 이러한 스레드는 스레드 수를 관리할 수 있도록 풀링된 구성에서 작업합니다."Lightweight pooling" 고급 구성 옵션은 Windows NT "파이버" 지원을 사용하여 단일 스레드에서 여러 사용자 프로세스를 실행합니다. 이론상 이는 스레드 컨텍스트 전환 수를 줄이고 성능을 향상시킬 수 있습니다.

실제적인 프로덕션 환경에서 이는 대부분의 상황에서 필요 없습니다. 아래 조건이 전부 만족되는 경우에만 라이트웨이트 풀링(Lightweight pooling)이 *잠재적으로* 유용해집니다. 실제 유용성 여부는 신중히 조절된 테스트를 통해 결정되어야 합니다.
  • 대규모 다중 CPU 서버
  • 거의 최대 용량으로 실행하는 모든 CPU
  • 수많은 컨텍스트 전환(> 20,000/초)
설정 작업 집합 크기 설정(Set working set size)

이 설정 기본값은 0인데, Windows NT Server 가상 메모리 관리자는 SQL Server의 작업 집합 크기를 확인할 수 있습니다. 대부분의 경우 이 값이 올바른 설정입니다. SQL Server가 설치되면 설치 프로그램은 서버 서비스 등록 정보 하에 있는 네트워크 제어판의 "네트워크 응용 프로그램을 위해 데이터 처리량 최대화"를 자동으로 선택합니다. 이는 Windows NT Server 가상 메모리 관리자에게 작업 집합 자르기를 아주 조금 수행하도록 지시하므로 SQL Server의 작업 집합 간섭을 최소화할 수 있습니다. 이러한 이유로 "설정 작업 집합 크기(Set working set size)" 기본값을 무시하지 말아야 합니다. 그러나, 시작할 때 이를 1로 설정하면 SQL Server는 Win32 API SetProcessWorkingSetSize()를 사용하여 작업 집합을 좀 더 확실하게 수정할 수 있습니다. SQL Server 메모리를 과도하게 사용하면 운영 체제는 보완할 수 있는 기능이 떨어지므로 메모리에 관련된 운영 체제 오류가 더 자주 발생할 수도 있습니다. 자세한 내용은 Microsoft 기술 자료에 있는 다음 문서를 참고하십시오.
110983Recommended SQL Server for NT Memory Configurations
smp 병행성(smp concurrency)(6.5 전용)

이 설정의 기본값은 0인데, SQL Server(시작할 때)는 프로세서의 수를 자동으로 감지하고 그 중 n-1개를 사용합니다. SMP 컴퓨터에서 이는 또한 SQL Server의 프로세스 우선 순위를 15로 높입니다. 대부분의 경우 기본 설정을 사용해야 합니다. 이 설정을 -1로 바꾸면 SQL Server가 사용할 수 있는 프로세서의 수를 제한하지 않지만 이 경우 특정 상황에서 운영 체제에서 사용할 수 있는 CPU가 부족하게 됩니다. 일반적인 증상으로는 네트워크 오류가 발생하거나 콘솔에서 응답을 중지합니다. NET VIEW 명령이 "network session was canceled" 오류로 인해 실패할 수 있습니다. 컴퓨터에서 실행 중인 기타 프로세스는 CPU 부족으로 응답을 중지합니다. 여기에는 기타 서비스, SQL Executive, 백업 프로그램, 일괄 처리 작업 등이 있습니다.

Compaq Proliant 서버에서 ASR(자동 서버 복구) 감시 장치 드라이버에 CPU 부족 현상이 일어날 수 있는데, Windows NT 운영 체제가 실패했다고 생각하고 서버를 다시 시작하여 복구합니다.

SQL Server는 모든 네트워크, I/O 및 메모리 관리 기능에 대해 운영 체제를 호출하므로 "smp 병행성(smp concurrency)"의 기본 설정 값인 0이면 프로세서가 "낭비"되지 않습니다. 운영 체제가 자주 발생하는 호출을 처리하는 경우 CPU 리소스를 사용하며 기본 설정으로 운영 체제에서 SQL Server 요청을 신속하게 처리하는데 필요한 CPU 시간을 확보할 수 있습니다. 따라서 사용 중인 SQL Server 시스템의 모든 프로세서는 0인 "smp 병행성(smp concurrency)" 기본 설정 값인 0이 사용됩니다. "smp 병행성(smp concurrency)"을 1, 2, 3 등으로 설정하지 않아야 합니다.

그러면 -1인 "smp 병행성(smp concurrency)" 설정을 사용해야 하는 시기 및 그 설정이 제공된 이유에 관한 의문이 생길 수 있습니다. 트랜잭션 경로 길이가 엄격하게 제한되고 임의(ad-hoc) 쿼리 또는 의사 결정 지원 쿼리가 허용되지 않는 OLTP(온라인 트랜잭션 처리)에서 그리고 SQL Server 전용 컴퓨터를 사용하는 환경(콘솔이 사용되지 않고 파일, 파일 공유 또는 SQL Executive 등도 사용되지 않음)에서 이 설정은 최소한의 부작용과 함께 성능을 어느 정도 향상시킬 수 있습니다.

그러나 서버에 공급되는 쿼리 스트림의 특성이 위에서 언급한 단순한 상태와 달라지면 이상한 동작이 발생하는 경우도 있습니다. 예를 들어, 일정 기간 동안 제대로 실행하는 것처럼 보이는 시스템이 갑자기 응답을 중지할 수 있습니다. 이러한 오류는 운영 체제 문제처럼 보일 수 있으므로 디버그 작업은 복잡해집니다.

자세한 내용은 Microsoft 기술 자료에 있는 다음 문서를 참고하십시오.
111405SQL Server and Windows NT Thread Scheduling
우선 순위 높이기(Priority boost)

이 설정 기본값은 0인데, SQL Server는 단일 프로세서 컴퓨터에서 프로세스 우선 순위 7로 실행할 수 있으며 SMP 컴퓨터에서는 일반적으로 15로 실행할 수 있습니다(6.5 전용. 7.0은 단일 프로세서 및 SMP 컴퓨터 양쪽에서 기본 우선 순위 7에서 실행).

대부분의 경우 기본 설정을 사용하십시오. 이를 1로 설정하면 단일 프로세서 컴퓨터(6.5 및 7.0)에서 SQL Server 프로세스 우선 순위는 15로 올라가고 SMP 컴퓨터에서는 24(6.5만)로 올라갑니다. 이것은 "smp 병행성(smp concurrency)"을 -1로 설정하는 것과 유사한 효과를 가집니다.

실제 지원 경험으로 보아 대부분의 경우 성능을 향상시키기 위해 우선 순위를 높일 필요가 없습니다. "우선 순위 높이기(Priority Boost)"를 설정하면 몇몇 상황에서 매끄러운 서버 기능이 간섭을 받을 수 있으므로 매우 이례적인 경우를 제외하고는 사용하지 말아야 합니다. 예를 들어, 이는 Microsoft 제품 지원 서비스에서 성능을 조사하는 중에 사용될 수 있습니다. 기술 자료 또는 상담자가 권했다고 해도 사용해서는 안됩니다.

최대 작업자 스레드(Max worker threads)

이 설정 기본값은 255인데, 최대 255 작업자 스레드를 만들 수 있습니다. 대부분의 경우 255의 기본 설정을 사용하십시오.이는 255명의 사용자 연결만을 설정할 수 있음을 의미하지는 않습니다. 시스템은 수천 개의 사용자 연결이 있을 수 있으며 특별히 동시에 작업할 수 있는 스레드가 255로 제한된다는 것이고 각 사용자는 일반적으로 지연 현상을 느끼지 못합니다. 이러한 경우 255개의 쿼리만 "동시"에 실행될 수 있지만 이는 사용 가능한 CPU 수만큼 다중 전송되므로 구성되는 작업자 스레드 수에 관계없이 동시에 작업이 이루어집니다.

새 사용자 연결이 설정될 때마다 새 Worker Thread가 Worker Thread 한계까지 만들어집니다. 비활성으로 지정된 시간이 되면 Worker Thread는 자동으로 사용할 수 없게 되고 닫힙니다. 이는 각 스레드가 리소스를 사용하므로 오버헤드를 낮게 유지하기 위한 것입니다. 일반적으로, 많은 수의 Worker Thread를 구성하면 스케줄링과 리소스 오버헤드의 증가로 생산성이 낮아지고 성능이 저하됩니다.

프로시저 캐시

SQL Server 6.5에서 이 설정 기본값은 30인데, SQL Server 캐시 공간의 30%를 프로시저 캐시용으로 예약해 놓았음을 나타냅니다. 메모리가 많은 시스템(256MB 이상)에서 버퍼 캐시에 더 많은 메모리를 할당하기 위해 이 값을 줄일 수도 있습니다. 그러나, 시스템에서 매우 적은 수의 저장 프로시저(Stored Procedure)를 사용해도 이 설정을 너무 낮게(예: 5%) 줄이지 마십시오.프로시저 캐시는 저장 프로시저(Stored Procedure) 캐싱 뿐만 아니라 다양한 내부 서버 할당 기능에 사용되는 범용 메모리이기 때문입니다. 이 풀에 메모리가 충분하지 않으면 오류가 발생하거나 기타 이상한 동작이 발생할 수도 있습니다. SQL Server 6.5에서 성능 모니터를 사용하여 프로시저 캐시의 사용을 모니터하고 프로시저 캐시 설정을 줄일 것인지 결정할 수 있습니다.

많은 경우 프로시저 캐시 설정을 줄여서 늘어난 만큼 사용 가능해진 버퍼 캐시의 양은 캐시 크기가 상대적으로 조금 변할 때(백분율로 말하면)는 비교적 일정한 버퍼 캐시 적중률에 인지할 수 있을 만큼 영향을 미치지 않습니다.
속성

문서 ID: 166967 - 마지막 검토: 02/13/2014 14:13:25 - 수정: 3.0

  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbenv kbusage ssrvgen KB166967
피드백