데이터 웨어하우징용
Microsoft SQL Server 2000 RDBMS
성능 조정 가이드
John H. Miller 및 Henry Lau Microsoft Corporation 요약: 데이터베이스 관리자와 개발자에게 Microsoft® SQL Server™ 2000 성능 및 조정 개념에 대한 정보와 비즈니스 인텔리전스 개발자를 위한 정보를 제공합니다(92페이지의 인쇄된 문서).
 이 성능 조정 가이드는 데이터베이스 관리자 및 개발자가 Microsoft® SQL Server™ 2000이 최대 성능을 내도록 구성하고 데이터 웨어하우징을 포함한 관계형 데이터베이스의 성능 저하 원인을 확인할 때 참조할 수 있도록 설계되었습니다. 또한 SQL Server에 저장된 데이터에 액세스하는 쿼리 로드, 인덱싱 및 작성에 필요한 지침과 적절한 사례를 제공합니다. 성능 특성을 분석하는 데 사용할 수 있는 다양한 SQL Server 도구에 대해서도 설명합니다. SQL Server 2000 성능 및 조정 원리 Microsoft SQL Server 7.0의 주요 개선 사항은 자체 구성, 자체 조정 및 자체 관리 기능을 크게 개선한 데이터베이스 엔진을 도입한 것입니다. SQL Server 7.0 이전에는 데이터베이스 관리자가 대부분의 데이터베이스 서버에 상당한 시간과 노력을 들여야 했습니다. 즉, 최적 성능을 유지하기 위해 관리자가 서버 구성을 수동으로 조정해야 했습니다. 사실 아직까지도 비교적 많은 수의 데이터베이스 관련 제품이 관리자에 의한 데이터베이스 서버의 수동 구성 및 조정을 필요로 합니다. 이것이 바로 많은 고객들이 SQL Server를 찾는 주된 이유입니다. SQL Server 2000은 SQL Server 7.0의 확고한 기반 위에 구축됩니다. SQL Server의 목표는 더 이상 데이터베이스의 수동 구성 및 조정이 필요치 않도록 만드는 것입니다. SQL Server 2000을 사용할 경우, 데이터베이스 환경의 구성 및 조정에 필요한 시간이 절약되므로 보다 생산적인 작업에 집중할 수 있습니다. 이전 버전 문서인 "MS SQL Server 7.0 성능 조정 가이드"를 사용해 보았으면 SQL Server 2000에서는 성능 조정을 위해 수동 조정이 필요한 옵션의 수가 줄어들었다는 사실을 깨닫게 될 것입니다. 일부 sp_configure 옵션은 아직도 수동으로 구성 및 조정할 수 있지만 데이터베이스 관리자에 의한 수동 작업을 피하고 그 대신 SQL Server에서 자동으로 구성 및 조정하도록 하는 것이 좋습니다. SQL Server 7.0에는 자동 조정을 가능하게 하는 추적 레코드가 기본 제공됩니다. SQL Server 2000에서는 이 방법이 크게 개선되었습니다. SQL Server가 자체 조정하도록 하면 사용자 환경에서 데이터베이스 성능에 악영향을 줄 수 있는 조건을 변경하도록 데이터베이스 서버가 동적으로 조정됩니다.
데이터베이스의 성능을 관리하기 위해 여러 가지 작업을 수행할 수 있습니다. SQL Server 2000은 이러한 작업을 지원하는 다양한 도구를 제공합니다. 성능 관리
SQL Server 성능 도구 이용
성능에 영향을 주는 구성 옵션max async IOSQL Server 7.0에서 수동 구성 옵션으로 제공되었던 max async IO가 SQL Server 2000에서는 자동화되었습니다. 이전에는 max async IO를 사용하여 SQL Server 7.0이 검사점 작업 도중 Microsoft Windows® 2000 또는 Windows NT® 4.0에 동시에 제출할 수 있는 디스크 입/출력 요청의 수를 지정했습니다. 그 다음 Windows가 이러한 요청을 물리 디스크 하위 시스템으로 보냈습니다. 이러한 구성 설정의 자동화로 인해 SQL Server 2000에서 최적의 입/출력 처리량의 관리를 동적으로 처리하고 자동화할 수 있습니다. 참고 Windows 98에서는 비동기 입/출력을 지원하지 않으므로 Windows 98 플랫폼에서 max async IO 옵션도 지원되지 않습니다. 데이터베이스 복구 모델SQL Server 2000에서는 트랜잭션을 데이터베이스 수준에서 기록하는 방법을 사용자가 지정할 수 있게 되었습니다. 선택된 모델은 성능, 특히 데이터 로드 도중 상당한 영향을 줄 수 있습니다. 복구 모델에는 단순, 전체, 대량 로그의 세 가지가 있습니다. 새 데이터베이스의 복구 모델은 새 데이터베이스를 만들 때 모델 데이터베이스에서 상속됩니다. 데이터베이스 모델은 데이터베이스를 만든 후에 변경할 수 있습니다.
전문 지식이 있는 관리자는 복구 모델 기능을 사용하여 데이터 로드 및 대용량 작업 속도를 빠르게 할 수 있습니다. 그러나 데이터 손실 위험에 노출될 가능성은 선택한 모델에 따라 달라집니다. 중요 복구 모델을 선택하기 전에 반드시 관련 위험을 완전히 알고 있어야 합니다. 복구 모델마다 각기 다른 요구를 처리하며 선택하는 모델에 따라 제공되는 장단점이 달라집니다. 성능, 공간 활용률(디스크 또는 테이프) 및 데이터 손실로부터의 보호 기능 등이 모델에 따라 다를 수 있습니다. 복구 모델을 선택할 때는 다음과 같은 비즈니스 요구 사항 중에서 결정합니다.
수행하는 작업에 따라 어떤 모델이 적합한지 결정될 수 있습니다. 복구 모델을 선택하기 전에 그로 인해 받게 되는 영향을 고려해야 합니다. 다음 표에 유용한 정보가 요약되어 있습니다.
복수 인스턴스 고려 사항SQL Server 2000에는 하나의 컴퓨터에서 여러 개의 SQL Server 인스턴스를 실행하는 기능도 추가되었습니다. 기본적으로, 각 SQL Server 인스턴스는 동적으로 메모리를 확보 및 해제하면서 인스터스의 작업 로드 변경에 맞춰 메모리를 조정합니다. SQL Server 2000의 여러 인스턴스가 독립적으로 메모리 활용률을 자동 조정하는 경우, 성능 조정 작업이 복잡해질 수 있습니다. 일반적으로, 각 컴퓨터에 하나의 SQL Server 인스턴스만 설치하는 고급 비즈니스 인텔리전스 고객의 경우 이 기능은 고려 대상이 아닙니다. 그러나 개별 시스템이 점점 대형화되면서(Windows 2000 Datacenter Server는 최대 64GB RAM과 32개의 CPU를 지원함) 프로덕션 환경에서조차 여러 인스턴스에 대한 요구가 생길 수 있습니다. 확장 메모리 지원을 사용하는 인스턴스에는 특별한 고려 사항이 적용됩니다. 확장 메모리 지원일반적으로 SQL Server 2000은 필요에 따라 동적으로 메모리를 확보 및 해제하므로 보통은 관리자가 SQL Server에 할당할 메모리 크기를 지정할 필요가 없습니다. 그러나 SQL Server 2000 Enterprise Edition과 SQL Server 2000 Developer Edition에는 Microsoft Windows 2000 AWE(Address Windowing Extensions) 지원 기능이 도입되었습니다. 이 기능을 사용해 SQL Server 2000에서 상당량의 추가 메모리 주소 지정이 가능합니다(Windows 2000 Advanced Server의 경우 최대 8GB, Windows 2000 Datacenter Server의 경우 최대 64GB). 확장 메모리를 구성할 때 확장 메모리에 액세스하는 각 인스턴스가 사용할 메모리를 정적으로 할당하도록 구성되어야 합니다. 참고 이 기능은 Windows 2000 Advanced Server 또는 Windows 2000 Datacenter Server를 실행할 때만 사용할 수 있습니다. Windows 2000 사용 시 고려 사항AWE 메모리를 이용하려면, 메모리 권한에 Windows 2000 잠금 페이지가 할당된 Windows 2000 계정에서 SQL Server 2000 데이터베이스 엔진을 실행해야 합니다. SQL Server 설정 권한이 MSSQLServer 서비스 계정 권한에 자동으로 부여되므로 메모리의 페이지 잠그기 사용할 수 있습니다. Sqlservr.exe를 사용하여 명령줄 프롬프트에서 SQL Server 2000 인스턴스를 시작하는 경우, Windows 2000 그룹 정책 유틸리티(Gpedit.msc)를 사용하여 이 권한을 수동으로 대화식 사용자 계정에 할당해야 합니다. 그렇지 않으면 SQL Server가 서비스로 실행 중이 아닐 때는 AWE 메모리를 사용할 수 없습니다. 메모리 옵션에서 페이지 잠금을 활성화하려면
Windows 2000 Advanced Server 또는 Windows 2000 Datacenter Server가 4GB 이상의 실제 메모리를 지원하도록 하려면 Boot.ini 파일에 /pae 매개 변수를 추가해야 합니다. 16GB 이하의 컴퓨터에서는 Boot.ini 파일에 /3gb 매개 변수를 사용할 수 있습니다. 그러면 Windows 2000 Advanced Server 및 Windows 2000 Datacenter Server에서 사용자 응용 프로그램이 확장 메모리를 3GB의 가상 메모리를 통해 주소를 지정하도록 허용하고, 순수한 운영 체제용으로 1GB의 가상 메모리를 예약합니다. 컴퓨터에서 사용할 수 있는 실제 메모리가 16GB를 초과할 경우, Windows 2000 운영 체제는 시스템 용도로 2GB의 가상 메모리 주소 공간을 필요로 합니다. 따라서 응용 프로그램 용도로 2GB 가상 주소 공간만 지원할 수 있습니다. 실제 메모리가 16GB를 넘는 시스템에서는 반드시 Boot.ini 파일에 /2gb 매개 변수를 사용하도록 하십시오. 참고 실수로 /3gb 매개 변수를 사용하면 Windows 2000에서 16GB를 넘는 메모리의 주소를 지정할 수 없습니다. SQL Server 2000 사용 시 고려 사항SQL Server 2000 인스턴스에서 AWE 메모리를 사용할 수 있도록 하려면 sp_configure를 사용하여 awe enabled 옵션을 설정하십시오. 그런 다음 SQL Server를 다시 시작하여 AWE를 활성화합니다. AWE 지원은 SQL Server를 시작한 후부터 SQL Server가 종료될 때까지 사용 가능하므로, SQL Server가 사용자에게 awe enabled이 설정 중이면 "Address Windowing Extension 사용" 메시지를 SQL Server 오류 로그에 보내 알립니다. AWE 메모리가 사용되는 경우, SQL Server 2000 인스턴스는 주소 공간의 크기를 동적으로 관리합니다. 따라서 AWE 메모리를 사용하면서 SQL Server 2000 인스턴스를 시작하면 max server memory의 설정 결과에 따라 다음 중 하나가 발생합니다.
32GB 시스템에서 SQL Server AWE 메모리를 할당할 때 Windows 2000에서 AWE를 관리하는 데 1GB 이상의 여유 메모리가 필요할 수 있습니다. 따라서 AWE를 사용하면서 SQL Server 인스턴스를 시작할 때는 기본 max server memory 설정을 사용하지 말고 값을 31GB 이하로 제한하는 것이 좋습니다. 장애 조치 클러스터링 및 복수 인스턴스 고려 사항AWE 메모리를 사용하면서 SQL Server 2000 장애 조치 클러스터링을 사용하거나 여러 인스턴스를 실행하는 경우, 실행되는 모든 SQL Server 인스턴스에 대한 max server memory 설정 합계를 사용 가능한 물리적 RAM 크기 미만으로 유지해야 합니다. 장애 조치의 경우, 참여하는 모든 존속 노드에서 가장 작은 물리 RAM 크기를 고려해야 합니다. 장애 조치 노드가 원래 노드보다 적은 실제 메모리를 갖는 경우, SQL Server 2000 인스턴스가 시작될 수 없거나 원래 노드에 있던 것보다 적은 메모리로 시작될 수 있습니다. sp_configure 옵션cost threshold for parallelism 옵션 cost threshold for parallelism 옵션은 SQL Server가 병렬 계획을 작성 및 실행하는 임계값을 지정하는 데 사용합니다. SQL Server는 쿼리에 대한 순차 계획을 실행하는 데 드는 예상 비용이 cost threshold for parallelism에 설정된 값보다 클 때만 같은 쿼리에 대해 병렬 계획을 작성하여 실행합니다. 비용은 특정 하드웨어 구성에서 순차 계획을 실행하는 데 필요한 예상 경과 시간(초 단위)을 뜻합니다. SMP(Symmetric Multiprocessors)에서만 cost threshold for parallelism을 설정하십시오. 병렬 계획은 보통 긴 쿼리에 효과적입니다. 계획의 초기화, 동기화 및 종료에 필요한 추가 시간을 무시할 수 있을 정도의 성능 향상 효과가 있습니다. cost threshold for parallelism 옵션은 짧은 쿼리와 긴 쿼리를 혼합하여 실행할 때 자주 사용됩니다. 짧은 쿼리는 순차 계획을 실행하지만 긴 쿼리는 병렬 계획을 사용합니다. cost threshold for parallelism의 값은 짧은 것으로 간주할 쿼리를 결정하여 순차 계획만 실행되도록 합니다. 특수한 경우에, 쿼리의 비용 계획이 현재 cost threshold for parallelism 값보다 작더라도 병렬 계획을 선택할 수 있습니다. 그 이유는 cost threshold for parallelism 측면에서, 병렬 또는 순차 계획의 사용 결정은 완전 최적화가 완료되기 전에 제공된 비용 예상 값의 영향을 받기 때문입니다. cost threshold for parallelism 옵션은 0 ? 32767 사이의 값으로 설정할 수 있습니다. 기본값은 5(밀리초 단위)입니다. 프로세서가 하나뿐인 컴퓨터에서 affinity mask 구성 옵션의 값 때문에 SQL Server에 하나의 CPU만 사용되는 경우나 병렬 처리의 최대 수준 옵션이 1로 설정된 경우, SQL Server는 cost threshold for parallelism의 값을 무시합니다. 병렬 처리의 최대 수준 옵션 병렬 처리의 최대 수준 옵션은 병렬 계획 실행에 사용할 프로세서 수를 제한(최대 32개)하는 데 사용됩니다. 기본값은 0으로, 이 경우 사용 가능한 실제 CPU 수를 사용합니다. 병렬 처리의 최대 수준 옵션을 1로 설정하면 병렬 계획 생성이 억제됩니다. 단일 쿼리 실행에 사용되는 프로세서 수를 제한하려면 값을 2 이상으로 설정하십시오. 이 값을 사용 가능한 CPU 수보다 크게 설정하면 실제 사용 가능한 CPU 수가 사용됩니다. 참고 If the affinity mask 옵션이 기본값으로 설정되지 않으면 SMP 시스템의 SQL Server에서 사용할 수 있는 CPU 수가 제한될 수 있습니다. SMP 컴퓨터에서 실행 중인 서버에서 max degree of parallelism을 바꾸는 경우도 간혹 있습니다(아주 드문 경우임). 컴퓨터에 프로세서가 하나뿐이면 max degree of parallelism 값이 무시됩니다. priority boost 옵션 priority boost 옵션은 SQL Server가 같은 컴퓨터 내의 다른 프로세스보다 높은 스케줄링 우선 순위로 실행되어야 하는지 여부를 지정하는 데 사용됩니다. 이 옵션을 1로 설정하면 SQL Server가 Windows 스케줄러에서 13의 우선 순위로 실행됩니다. 기본값은 0으로, 이 경우 우선 순위 기준은 7입니다. priority boost 옵션은 SQL Server 전용이며 SMP 구성을 적용한 컴퓨터에서만 사용해야 합니다. 경고 우선 순위를 너무 올리면 필수 운영 체제 및 네트워크 기능에서 리소스를 너무 많이 빼앗아 결과적으로 SQL Server 종료 문제가 발생하거나 서버에서 다른 Windows 작업이 사용될 수 있습니다. 일부 환경에서 priority boost의 기본값을 변경하면 다음과 같은 통신 오류가 SQL Server 오류 로그에 기록될 수 있습니다. Error: 17824, Severity: 10, State: 0 Unable to write to ListenOn Error 17824는 SQL Server가 클라이언트에 기록을 시도하는 중 연결 문제가 발생했음을 나타냅니다. 이러한 통신 문제는 클라이언트가 응답을 중지한 때나 클라이언트가 다시 시작된 때의 네트워크 문제가 원인일 수 있습니다. 그러나 오류 17824가 반드시 네트워크 문제를 나타내는 것은 아닙니다. 단순히 priority boost 옵션을 설정한 결과일 수도 있습니다. 작업 집합 크기 설정 옵션 작업 집합 크기 설정 옵션은 서버 메모리 설정과 동일한 SQL Server의 실제 메모리 공간을 예약할 때 사용합니다. 서버 메모리 설정은 SQL Server에서 작업 로드와 사용 가능한 리소스를 기준으로 자동으로 구성하며, mix server memory와 max server memory 사이에서 동적으로 변합니다. 작업 집합 크기 설정을 설정하면 SQL Server가 유휴 상태로 바뀔 때 SQL Server 페이지가 다른 프로세스에 의해 사용될 가능성이 높아져도 운영 체제가 서버 페이지의 스왑을 시도하지 않습니다. SQL Server에서 메모리를 동적으로 재사용할 수 있도록 하려면 작업 집합 크기 설정를 설정하지 마십시오. 작업 집합 크기 설정를 1로 설정하기 전에 mix server memory와 max server memory를 같은 값으로 설정하고 SQL Server에 할당할 메모리 크기를 설정하십시오. lightweight pooling과 affinity mask 옵션에 대해서는 뒤에 나오는 "감시할 주요 성능 카운터" 절에서 설명합니다.
단지 몇 GB의 데이터만 있고 과도한 읽기 또는 쓰기 작업이 지속적으로 유지되지 않는 SQL Server를 구성할 때는 디스크 입/출력과 하드 드라이브 간의 SQL Server 입/출력 작업 로드 균형 조정에 대해 고려하는 것이 최적의 성능을 위해 그다지 중요하지 않습니다. 그러나 수백 기가바이트, 심지어 테라바이트 단위의 데이터를 포함하며 과도한 읽기 및 쓰기 작업이 지속되는 대용량 SQL Server 데이터베이스를 구축할 때는 여러 하드 드라이브 사이에서 로드 균형을 조절하여 SQL Server 디스크 입/출력 성능이 극대화되도록 구성해야 합니다. 전송 속도 최적화데이터베이스 성능 조정에 있어 가장 중요한 것 중 하나는 입/출력 성능 조정입니다. SQL Server도 예외는 아닙니다. 전체 데이터베이스를 유지할 만큼의 충분한 RAM이 있는 시스템에서 SQL Server를 실행하지 않는 한 입/출력 성능은 디스크 입/출력 하위 시스템에서 SQL Server 데이터의 읽기 및 쓰기를 얼마나 빨리 처리할 수 있는가에 따라 결정됩니다. 전송 속도와 입/출력 처리량, 입/출력 성능에 영향을 주는 기타 요소가 꾸준히 개선되고 있으므로 사용자 저장 시스템에서 기대할 수 있는 속도에 대한 수치를 제시하지는 않겠습니다. 예상되는 기능을 제대로 파악하기 위해서는 주로 사용하는 하드웨어 공급업체에 문의하여 예상되는 최적의 성능을 확인하십시오. 여기서 강조하고 싶은 점은 순차적 입/출력 작업(흔히 “연속” 또는 “디스크 순서”라고도 함)과 비순차적 입/출력 작업 사이의 차이점입니다. 또한 미리 읽기 처리가 입/출력 작업에 미칠 수 있는 영향에 대해서도 중점적으로 살펴보겠습니다. 순차적 디스크 입/출력 작업과 비순차적 디스크 입/출력 작업 여기서 순차적 및 비순차적 디스크 입/출력 작업이 의미하는 것을 디스크 드라이브와 관련지어 살펴보는 것이 좋습니다. 하드 드라이브 하나에는 드라이브 플래터가 여러 개 있습니다. 각 플래터는 읽기/쓰기 작업에 필요한 표면을 제공합니다. 읽기/쓰기 헤드가 있는 일련의 암 세트를 사용하여 플래터 사이를 이동하면서 플래터의 데이터를 읽거나 플래터에 데이터를 기록합니다. SQL Server의 하드 드라이브에 대해 다음과 같은 사항을 반드시 기억해 두어야 합니다. 읽기/쓰기 헤드와 관련 디스크 암이 SQL Server가 요청하는 하드 드라이브 위치를 찾아 그 위치에서 동작하기 위해 헤드와 디스크 암이 이동해야 합니다. 데이터가 비순차적 방식으로 하드 드라이브 플래터에 분산되어 있는 경우, 하드 드라이브가 디스크 암을 이동시키고(검색 시간) 읽기/쓰기 헤드를 회전시키면서 데이터의 위치를 찾기 때문에 시간이 훨씬 더 많이 걸립니다. 이는 필요한 데이터가 모두 하나의 연속적인 물리 하드 드라이브 플래터 구역에 있어 디스크 암과 읽기/쓰기 헤드가 최소한으로 움직여 필요한 디스크 입/출력 작업을 수행할 수 있는 순차 경우와 반대입니다. 비순차 방식과 순차 방식에서의 시간 차이는 아주 큽니다. 즉, 비순차 검색에 약 50밀리초가 걸리는 반면, 순차 검색에는 약 2 ? 3밀리초가 걸립니다. 이 시간은 대략적인 값으로서, 디스크 상에서 비순차적 데이터의 확산 범위, 하드 디스크 플래터의 회전 속도(RPM 단위) 그리고 하드 드라이브의 기타 물리적 속성에 따라 달라집니다. 기억할 점은 순차적 입/출력은 성능 향상에 좋고 비순차적 입/출력은 그렇지 못하다는 것입니다. 두번째로, 8KB를 읽거나 쓰는 작업에 64KB를 읽거나 쓰는 작업과 거의 같은 시간이 걸린다는 점을 기억해 두시기 바랍니다. 8KB에서 약 64KB까지의 범위일 때는 디스크 암과 읽기/쓰기 헤드가 움직이는 시간이 단일 디스크 입/출력 전송 작업에 소요되는 대부분의 시간을 차지합니다. 따라서 수학적으로 볼 때, 64KB 전송이 실제로 8KB 전송만큼 빠르고 각 전송마다 SQL Server 데이터를 여덟 배나 더 처리할 수 있으므로 64KB 이상의 SQL 데이터를 전송하는 경우에는 가능한 한 64KB 디스크 전송을 수행하는 것이 좋습니다. 미리 읽기 관리자는 SQL Server 확장이라고 하는 64KB 단위의 디스크 작업을 수행한다는 점에 유의하십시오. 로그 관리자는 이보다 더 큰 입/출력 크기로도 순차 쓰기를 수행합니다. 중요한 점은, 미리 읽기 관리자를 합리적으로 사용하고 비순차적으로 액세스한 다른 파일과 SQL Server 로그 파일을 분리해서 처리하는 것이 SQL Server 성능을 위해 좋다는 사실입니다. 일반적으로 대부분의 하드 드라이브는 비순차적 입/출력 작업 처리와 비교해 순차적 입/출력 작업을 처리할 때 2배의 성능을 제공할 수 있습니다. 즉, 비순차적 입/출력이 필요한 작업에 걸리는 시간은 순차적 입/출력 작업의 두 배입니다. 이것은 데이터베이스 내에서 임의의 입/출력 처리를 유발하는 상황이 되지 않도록 해야 한다는 것을 의미합니다. 입/출력 작업을 항상 순차적으로 수행하는 것이 중요하기는 하지만 패키지 분할이나 데이터 순서를 벗어나는 등의 상황에서는 비순차적 입/출력이 일어날 수 있습니다. 순차적 입/출력을 유도하기 위해서는 페이지 분할을 일으키지 않도록 해야 합니다. 이는 데이터 로드 전략을 세우는 데 있어서도 중요합니다. 데이터와 인덱스를 분리하는 분할 전략을 적용하여 데이터가 디스크에 순차적으로 배치되도록 할 수 있습니다. 데이터와 인덱스의 조각화 여부를 주기적으로 검사하는 작업을 설정하고, 지나치게 조각화된 경우에는 SQL Server에 제공된 유틸리티를 사용하여 데이터의 순서를 다시 지정하는 것이 중요합니다. 이러한 작업에 대해서는 뒤에서 자세히 설명합니다. 참고 일반적으로 기록은 중요한 문제가 아닙니다. 그 이유는 트랜잭션 로그 데이터는 최대 32KB 크기의 로그 파일에 항상 순차적으로 기록되기 때문입니다. RAID RAIDRAID(Redundant Array of Inexpensive Disks)는 3 - 5GB 이상의 데이터베이스에 주로 사용되는 저장 기술입니다. RAID는 성능과 내결함성 측면 모두에서 이점을 제공합니다. RAID 컨트롤러와 디스크 구성이 다양하기 때문에 원가, 성능 및 내결함성 사이에서 하나에 대해 이점이 있으면 다른 것에 대해서는 손해를 보게 됩니다. 여기서는 SQL Server 데이터베이스에 RAID를 사용하는 방법을 소개하고 여러 가지 구성 및 장단점에 대해 설명합니다.
미러링은 정보를 보조(미러링된) 드라이브 세트에 기록함으로써 구현됩니다. 정상 미러링 상태에서 드라이브 손실이 있는 경우, 손실된 드라이브의 데이터는 실패한 드라이브의 교체 및 미러 세트 재구성을 통해 다시 작성할 수 있습니다. 대부분의 RAID 컨트롤러가 Windows 및 SQL Server를 온라인 상태로 유지하면서 실패한 드라이브의 교체 및 재미러링 수행 기능을 제공합니다. 이러한 RAID 시스템을 흔히 "핫 플러그" 가능 드라이브라고 합니다. 미러링의 장점 중 하나는 내결함성이 요구되는 경우에 최고 성능을 발휘하는 RAID 옵션이라는 점입니다. 미러 세트에 SQL Server가 쓰기 작업을 할 때마다 두 번, 즉 미러 세트의 각 면에 대해 한 번씩 디스크 입/출력 작업이 발생한다는 점을 명심하십시오. 다른 장점으로는 미러링이 패리티 RAID 구현보다 더 나은 내결함성을 제공한다는 점이 있습니다. 미러링 기능을 이용하면 하나 이상의 드라이브에 장애가 발생하더라도 정상 작업이 가능하며, 시스템 관리자가 서버를 종료하고 파일 백업을 사용하여 복구하지 않아도 미러 세트에 속하는 드라이브의 절반까지 정상 상태로 작동될 수 있습니다. 미러링의 단점은 비용입니다. 한 드라이브 분량의 데이터에 추가로 하나의 드라이브가 필요합니다. 따라서 데이터 웨어하우스에 있어 종종 필요한 가장 고가의 구성 요소 중 하나인 저장 비용이 두 배로 늘어나게 됩니다. RAID 1과 해당 하이브리드인 RAID 0+1(RAID 10 또는 0/1이라고도 함) 모두 미러링을 통해 구현됩니다. 패리티는 디스크에 쓰여지는 데이터에 대한 복구 정보를 계산하고 이 패리티 정보를 RAID 어레이를 구성하는 다른 드라이브에 기록하는 것입니다. 드라이브에 오류가 발생하면 새 드라이브가 RAID 어레이로 삽입되고 다른 드라이브에 쓰여진 복구 정보(패리티)를 가져오고 이 정보를 사용하여 오류가 발생한 드라이브의 데이터를 다시 생성하여 오류가 발생한 드라이브를 복구합니다. RAID 5와 해당 하이브리드는 패리티를 통해 구현됩니다. 패리티의 장점은 비용 측면에서 찾을 수 있습니다. RAID 5로 모든 드라이브를 보호하기 위해서는 단 하나의 추가 드라이브만 필요합니다. 패리티 정보는 RAID 5 어레이에 참여하는 모든 드라이브 간에 균일하게 분포됩니다. 패리티의 단점은 성능과 내결함성입니다. 미러링이 디스크 입/출력 작업을 두 번 하는 데 비해, RAID 5는 패리티를 계산하고 기록하는 추가 작업으로 인해 각 쓰기 작업마다 디스크 입/출력을 네 번 수행해야 합니다. 읽기 입/출력 작업 비용은 미러링과 패리티 모두에서 동일합니다. 그러나 보통 읽기 작업 시 오류가 발생한 드라이브가 둘 이상이면 어레이가 오프라인 상태가 되어야 하며, 백업 미디어에서 복구를 수행해 데이터를 복원해야 합니다. 일반적으로 바람직한 방법은 안정된 디스크 입/출력 성능을 얻는 데 필요한 수의 디스크에 스트라이프하는 것입니다. 시스템 모니터는 특정 RAID 어레이에 디스크 입/출력 병목 현상이 발생하는 경우 이를 표시합니다. 이러한 경우에는 바로 디스크를 추가하고 데이터를 RAID 어레이에 다시 분산시키거나, 소형 컴퓨터 시스템 인터페이스(SCSI) 채널을 필요한 만큼 추가하여 디스크 입/출력 균형을 조정하고 성능을 극대화합니다. 하드웨어 RAID 컨트롤러에 내장된 캐시의 효과 하드웨어 RAID 컨트롤러들은 대부분 어떤 형태로든 읽기 및 쓰기 캐시를 갖추고 있습니다. SQL Server에서 사용할 수 있는 이러한 캐시 기능으로 디스크 하위 시스템의 효과적인 입/출력 처리 용량을 크게 늘릴 수 있습니다. 이 컨트롤러 기반 캐시 메커니즘의 원리는, 호스트 서버(즉, SQL Server)에서 들어오는 작고 어쩌면 비순차적인 입/출력 요청들을 모은 다음 다른 입/출력 요청과 함께 수 밀리초 이내에 일괄 처리함으로써 더 크고(32-128KB) 아마 순차적일 입/출력 요청으로 만들어 하드 드라이브에 전달되도록 하는 것입니다. 순차적이고 더 큰 입/출력일수록 성능이 좋으므로, 하드 디스크가 RAID 컨트롤러에 제공할 수 있는 입/출력 수가 정해져 있는 경우 디스크 입/출력 처리량은 더 늘어날 것입니다. RAID 컨트롤러 캐시가 하드 디스크에서 초당 더 많은 입/출력을 처리하도록 해주는 것은 아닙니다. 그보다 RAID 컨트롤러 캐시는 일부 구성을 이용하여 입/출력 처리 용량을 결정하는 고정된 크기의 하드 디스크를 최대한 사용할 수 있도록 들어오는 입/출력 요청을 배열합니다. 이러한 RAID 컨트롤러는 대개 특정 형태의 백업 전원으로 캐싱 메커니즘을 보호합니다. 백업 전원을 사용하면 정전된 경우에도 일정 기간(예: 하루) 동안 캐시에 기록된 데이터를 보존할 수 있습니다. 데이터베이스 서버에서 무정전 전원 공급 장치(UPS)도 지원하는 경우, RAID 컨트롤러는 정전이 일어날 경우에 데이터를 디스크로 플러시할 시간과 기회를 더 많이 갖게 됩니다. 서버의 UPS가 성능에 직접적으로 영향을 주지는 않지만 RAID 컨트롤러 캐시를 통해 얻게 되는 성능 개선 효과가 지속되게 해줍니다. RAID 수준 RAID 1 및 RAID 0+1은 RAID 수준 중에서 가장 나은 데이터 보호 기능과 성능을 제공하지만 필요한 디스크가 많아져 비용이 늘어나게 됩니다. 하드 디스크 비용이 제한 요인이 아니라면 RAID 1 또는 RAID 0+1이 성능과 내결함성 측면 모두에서 최선의 선택입니다. RAID 5의 비용은 RAID 1 또는 RAID 0+1보다 저렴하지만 내결함성과 쓰기 성능이 떨어집니다. RAID 5의 쓰기 성능은 RAID 1 또는 RAID 0+1의 절반 정도밖에 안됩니다. 그 이유는 패리티 정보를 읽고 쓰는 데 필요한 추가 입/출력 때문입니다. 디스크 입/출력 성능은 RAID 0(내결함성 보호가 없는 디스크 스트라이핑)이 가장 좋습니다. RAID 0은 내결함성 보호를 전혀 제공하지 않기 때문에 프로덕션 환경에서 사용하면 안되며 개발 환경에서도 권장할 만한 수준은 아닙니다. RAID 0은 주로 벤치마킹이나 테스트에만 사용됩니다. 많은 RAID 어레이 컨트롤러가 물리 하드 드라이브에 RAID 0+1(RAID 1/0 및 RAID 10이라고도 함) 옵션을 제공합니다. RAID 0+1은 하이브리드 RAID 솔루션입니다. 이 컨트롤러는 하위 수준에서는 정상적인 RAID 1처럼 모든 데이터를 미러링하고, 상위 수준에서는 RAID 0처럼 컨트롤러가 모든 드라이브의 데이터를 스트라이핑합니다. 따라서 RAID 0+1은 최대의 보호 수준(미러링)과 높은 성능(스트라이핑)을 제공합니다. 이 스트라이핑과 미러링 작업은 RAID 컨트롤러가 제어하므로 Windows와 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 사이의 차이점을 보여줍니다.
참고 위 그림에서, 디스크 네 개 분량의 데이터를 보관하기 위해서는 RAID 1(및 RAID 0+1)에 8개의 디스크가 필요한 반면, Raid 5에는 5개의 디스크만 있으면 됩니다. 저장 장치 공급업체별 고유 RAID 구현에 대한 자세한 내용은 반드시 관련 업체에 문의하십시오. 수준 0 스트라이프 세트라는 디스크 파일 시스템을 사용하기 때문에 이 수준은 디스크 스트라이핑이라고도 합니다. 데이터가 블록으로 나뉘어 어레이의 모든 디스크에 지정된 순서로 분산됩니다. RAID 0은 여러 디스크로 작업을 분산시켜 여러 작업이 동시에 개별적으로 수행될 수 있도록 함으로써 읽기/쓰기 성능을 개선합니다. RAID 0은 RAID 5와 비슷합니다. 단, RAID 5는 내결함성도 제공한다는 것이 다른 점입니다. 다음 그림에서는 RAID 0을 보여줍니다.
수준 1 미러 세트라는 디스크 파일 시스템을 사용하기 때문에 이 수준은 디스크 미러링이라고도 합니다. 디스크 미러링은 선택된 디스크에 대한 동일한 중복 사본을 제공합니다. 기본 디스크에 기록된 모든 데이터는 미러 디스크에도 기록됩니다. RAID 1은 내결함성을 제공하며 일반적으로 읽기 성능을 개선합니다(반면 쓰기 성능은 저하될 수 있음). 다음 그림에서는 RAID 1을 보여줍니다.
수준 2 이 수준에서는 디스크 전체에 패리티를 분산시키는 오류 수정 방법을 사용하여 중복성을 추가합니다. 또한 파일을 바이트로 쪼개서 여러 디스크에 분산시키는 디스크 스트라이핑 전략도 이 수준에 적용됩니다. 이 전략은 미러링 상에서 디스크 이용 및 읽기/쓰기 성능을 약간 개선할 뿐입니다(RAID 1). RAID 2는 기타 RAID 수준만큼 효과적이지 못하여 일반적으로 사용되지 않습니다. 수준 3 RAID 2와 동일한 스트라이핑을 사용하지만 오류 수정 방법에 패리티 데이터용으로 하나의 디스크만 필요로 하는 수준입니다. 디스크 공간의 사용은 데이터 디스크의 수에 따라 달라집니다. RAID 3은 읽기/쓰기 성능을 약간 개선합니다. RAID 3은 그다지 자주 사용되지 않습니다. v 4 이 수준에서는 RAID 2 또는 RAID 3보다 훨씬 큰 대형 블록이나 세그먼트에 스트라이프된 데이터를 사용하며, RAID 3과 같이 오류 수정 방법에 패리티 데이터용으로 하나의 디스크만 필요로 합니다. 이 수준은 사용자 데이터를 오류 수정 데이터와 별도로 보관합니다. RAID 4는 기타 RAID 수준만큼 효과적이지 못하며 일반적으로 사용되지 않습니다. 수준 5 패리티가 있는 스트라이핑이라고도 하는 이 수준은 최근 가장 널리 사용되는 방식입니다. 어레이의 여러 디스크에 걸쳐 데이터를 대형 블록으로 스트라이프하는 점에서 RAID 4와 비슷합니다. 그러나 모든 디스크에 패리티를 기록하는 방법에 있어서는 차이가 있습니다. 데이터 중복성은 패리티 정보에 의해 제공됩니다. 데이터와 패리티 정보는 항상 서로 다른 디스크에 놓이도록 배열됩니다. 패리티가 있는 스트라이핑은 디스크 미러링보다 더 나은 성능을 제공합니다(RAID 1). 그러나 스트라이프 구성원이 유실될 경우(예: 디스크 결함이 있는 경우) 읽기 성능이 떨어집니다. RAID 5는 가장 널리 사용되는 RAID 구성 중 하나입니다. 다음 그림에서는 RAID 5를 보여줍니다.
수준 10 (1+0) 스트라이핑이 있는 미러링이라고도 합니다. 이 수준은 스트라이프된 디스크 어레이를 사용합니다. 여기서 어레이는 또 하나의 스트라이프된 동일한 디스크 세트에 미러링됩니다. 예를 들어, 네 개의 디스크를 사용하여 스트라이프된 어레이를 만들 수 있습니다. 그런 다음 스트라이프된 디스크 어레이가 다른 네 개의 스트라이프된 디스크 세트를 사용해 미러링됩니다. RAID 10은 미러링의 디스크 중복성을 통해 디스크 스트라이핑 이점을 제공합니다. RAID 10은 두 배로 많은 디스크를 사용하는 대신 그 어느 RAID 수준보다 높은 읽기/쓰기 성능을 제공합니다. 다음 그림에서는 RAID 10을 보여줍니다.
온라인 RAID 확장 이 기능을 사용하면 SQL Server를 온라인 상태로 유지하면서 물리적 RAID 어레이에 디스크를 동적으로 추가할 수 있습니다. 추가 디스크 드라이브가 자동으로 RAID 저장소에 통합됩니다. 디스크 드라이브를 추가할 때 핫 플러그 드라이브 슬롯이나 핫 플러그 슬롯이라는 물리적 위치에 설치하는 방법을 사용합니다. 많은 하드웨어 공급업체들은 이 기능을 갖춘 하드웨어 RAID 컨트롤러를 제공합니다. 데이터는 새로 추가한 드라이브를 포함한 모든 드라이브에 자동으로 균등하게 스트라이프되므로 SQL Server나 Windows를 종료할 필요가 없습니다. 디스크 어레이 케이지에 빈 핫 플러그 슬롯을 남겨두면 이 기능을 이용할 수 있습니다. 따라서 SQL Server가 정기적으로 RAID 어레이에 과도한 입/출력 요청을 하는 경우에는(해당 RAID 어레이와 연관된 Windows 논리 드라이브 문자에 해당하는 디스크 대기열의 길이로 확인), SQL Server가 실행되는 상태에서 핫 플러그 슬롯에 새 하드 드라이브를 하나 또는 여러 개 장착할 수 있습니다. RAID 컨트롤러는 데이터가 RAID 어레이의 모든 드라이브에서 균등하게 분산되도록 일부 기존 SQL Server 데이터를 이러한 새 드라이브로 옮깁니다. 그런 다음 RAID 어레이의 전반적인 입/출력 처리 성능에 새 드라이브의 입/출력 처리 성능(드라이브마다 1초에 75번의 비순차/150번의 순차 입/출력)이 더해집니다. 시스템 모니터 및 RAID 시스템 모니터(Microsoft Windows NT® 4.0의 경우 성능 모니터)를 사용하면 논리적 및 물리적 디스크 드라이브 모두에 대한 정보를 얻을 수 있습니다. 차이점은 시스템 모니터의 논리 디스크는 Windows가 무엇을 논리 드라이브 문자로 인식하는지에 있습니다. 시스템 모니터의 물리 디스크는 Windows에서 하나의 물리 하드 드라이브로 인식하는 것과 연관됩니다. Windows NT 4.0에서 성능 모니터의 모든 디스크 카운터는 성능에 약간의 영향을 줄 수 있기 때문에 기본적으로 해제되어 있습니다. Windows 2000에서는 기본적으로 물리 디스크 카운터는 설정되어 있고, 논리 디스크 카운터는 해제되어 있습니다. Diskperf.exe는 시스템 모니터에 표시되는 카운터 형식을 제어하는 Windows 명령입니다. Windows 2000에서 논리 드라이브나 저장소 볼륨에 대한 성능 카운터 데이터를 구하려면 명령 프롬프트에서 diskperf -yv를 입력하고 ENTER를 눌러야 합니다. 그러면 디스크 성능 데이터 수집에 사용된 디스크 성능 통계가 논리 드라이브나 저장소 볼륨에 대한 데이터를 보고합니다. 기본적으로 운영 체제는 diskperf -yd 명령을 사용하여 물리 드라이브 데이터를 얻습니다. Windows 2000에서 Diskperf.exe 명령의 구문은 다음과 같습니다. diskperf [-y[d|v] | -n[d|v]] [\\computername] 매개 변수 (none)
-y
-yd
-yv
-n
-nd
-nv
\\computername
Windows NT 4.0 이하에서는 diskperf ?y를 사용하여 하드 드라이브나 하드 드라이브 및 RAID 컨트롤러 세트를 모니터하였습니다(Windows NT 소프트웨어 RAID를 사용하지 않음). Windows 소프트웨어 RAID를 이용할 때는 시스템 모니터가 Windows NT 스트라이프 세트를 통해 올바르게 물리 카운터를 보고하도록 diskperf ?ye를 사용합니다. Windows NT 스트라이프 세트와 함께 diskperf ?ye를 사용하면 논리 카운터가 올바른 정보를 보고하지 않으며 그러한 카운터는 무시되어야 합니다. Windows NT 스트라이프 세트와 함께 논리 디스크 카운터 정보가 필요한 경우에는 diskperf ?y를 사용합니다. diskperf ?y를 사용할 경우 Windows NT 스트라이프 세트에 대해 논리 디스크 카운터가 올바르게 보고되지만 물리 디스크 카운터는 올바르게 보고되지 않으며 이를 무시해야 합니다. 참고 diskperf 명령의 효과는 Windows를 다시 시작할 때까지 계속 적용됩니다(Windows 2000과 초기 Windows NT 버전 모두에 해당). 하드웨어 RAID 모니터링 고려 사항 RAID 컨트롤러는 여러 개의 물리 하드 드라이브를 하나의 RAID 미러 세트 또는 스트라이프 세트로 Windows에 표시하므로 Windows는 여러 개의 하드 드라이버가 마치 하나의 물리 디스크에 있는 것처럼 그룹 단위로 읽습니다. 그 결과, 실제 기초 하드 드라이브 동작의 추출된 뷰에 성능 카운터가 잘못된 정보를 보고할 수도 있습니다. 성능 조정 측면에서 얼마나 많은 물리 하드 드라이브가 RAID 어레이와 연관되는지 아는 것이 매우 중요합니다. 이는 Windows 및 SQL Server가 물리 하드 드라이브 각각으로 보내는 디스크 입/출력 요청 횟수를 결정할 때 필요한 정보입니다. 시스템 모니터가 하드 드라이브에 연관되는 것으로 보고하는 디스크 입/출력 요청 횟수를 해당 RAID 어레이에 있는 것으로 알려진 실제 물리 하드 드라이브 수로 나눕니다. RAID 어레이에 있는 각 하드 드라이브의 입/출력 동작을 대략적으로 예측하려면 시스템 모니터가 보고하는 디스크 쓰기 입/출력 횟수에 2(RAID 1 및 0+1의 경우) 또는 4(RAID 5의 경우)를 곱해야 합니다. 하드 드라이브의 입/출력 용량을 나타내는 숫자가 적용되는 것은 이 물리적 수준이므로, 이 방법으로 물리 하드 드라이브로 보내지는 물리 입/출력 요청 횟수에 대한 더 정확한 값을 구할 수 있습니다. 그러나 이 방법은 하드웨어 RAID 컨트롤러가 캐시를 사용할 때 하드 드라이브 입/출력을 정확히 계산하지 않습니다. 왜냐하면 캐시 사용이 하드 드라이브에 대한 직접 입/출력에 상당한 영향을 줄 수 있기 때문입니다. 디스크 동작을 모니터할 때 각 디스크의 물리 입/출력이 아닌 디스크 대기열에 주목하는 것이 좋습니다. 디스크 입/출력 속도는 드라이브의 전송 속도에 따라 결정되며 사용자가 조정할 수 없습니다. 속도가 빠른 드라이브나 더 많은 드라이브를 구입하는 것 외에는 달리 시도할 수 있는 것이 없기 때문에 실제 일어나는 입/출력 처리량에 관심을 가질 이유가 거의 없습니다. 그러나 디스크 대기열이 너무 많이 사용되지 않도록 하는 것이 좋습니다. 디스크 대기열이 너무 많으면 입/출력에 문제가 있음을 나타냅니다. Windows는 RAID 어레이에 있는 물리 드라이브 수를 읽을 수 없기 때문에 각 물리 디스크의 디스크 대기열을 정확히 평가하기 어렵습니다. 디스크 대기열의 길이를 관찰 대상 논리 드라이브의 하드웨어 RAID 디스크 어레이에 참여하는 물리 드라이브 수로 나눠서 대략적인 값만 구할 수 있습니다. SQL Server 파일을 포함하는 하드 드라이브에 대해서는 디스크 대기열 값을 2 미만으로 유지하는 것이 가장 좋습니다. 소프트웨어 RAID Windows 2000은 하드웨어 RAID 컨트롤러가 사용되지 않을 때 운영 체제를 통해 미러 세트와 스트라이프 세트(내결함성과 무관)를 제공함으로써 내결함성을 처리하도록 소프트웨어 RAID를 지원합니다. 운영 체제 프로시저를 사용하여 RAID 0, RAID 1 또는 RAID 5 기능을 설정할 수 있습니다. 대부분의 대용량 데이터 웨어하우스가 하드웨어 RAID를 사용하지만 비교적 적은 규모의 설치이거나 하드웨어 RAID 구현을 선택하지 않은 경우, 소프트웨어 RAID가 약간의 데이터 액세스와 내결함성 이점을 제공할 수 있습니다. 소프트웨어 RAID는 일부 CPU 리소스를 활용합니다. 이는 일반적으로 하드웨어 RAID 컨트롤러가 관리하는 RAID 작업을 Windows가 관리해야 하기 때문입니다. 따라서 시스템 프로세서가 다른 용도로 거의 100% 활용된다고 가정할 때 디스크 드라이브 수가 동일하고 Windows 소프트웨어 RAID가 하드웨어 RAID 솔루션에 비해 성능 면에서 몇 % 정도 낮을 수 있습니다. Windows 소프트웨어 RAID는 일반적으로 입/출력 병목 현상을 줄임으로써 드라이브 세트가 소프트웨어 RAID 없이 사용할 때보다 SQL Server 입/출력을 보다 잘 지원하도록 도와줍니다. 소프트웨어 RAID를 사용할 경우, 종종 SQL Server가 입/출력 요청을 완료하기 위해 대기하는 시간이 줄어들기 때문에 서버의 CPU 활용률이 향상됩니다. 디스크 입/출력 병렬 처리 여러 개의 디스크 드라이브에 저장된 대용량 SQL Server 데이터베이스의 성능을 향상시키는 효과적인 방법은 디스크 입/출력 병렬화를 구현하는 것으로, 이는 여러 디스크 드라이브에서 동시에 읽기 및 쓰기를 수행하는 방식입니다. RAID는 하드웨어 및 소프트웨어를 통해 디스크 입/출력 병렬화를 구현합니다. 다음 절에서 분할을 통해 디스크 입/출력 병렬화의 강도를 높이도록 SQL Server 데이터를 구성하는 방법에 대해 설명합니다.
여러 개의 디스크 드라이브에 저장된 SQL Server 데이터베이스에서는 데이터 분할을 통해 디스크 입/출력 병렬화 정도를 증가시키는 방법으로 성능을 개선할 수 있습니다. 분할은 다양한 방법으로 수행할 수 있습니다. 파티션을 생성 및 관리하는 방법에는 저장소 하위 시스템(디스크, RAID 파티션 분할)을 구성하고 SQL Server에서 다양한 데이터 구성(예: 파일, 파일 그룹, 테이블 및 뷰) 메커니즘을 적용하는 것이 포함됩니다. 여기서는 성능과 관련된 일부 분할 기능에 대해서만 중점적으로 설명하고, "Using Partitions in a SQL Server 2000 Data Warehouse"라는 제목의 백서에서 특별히 분할을 주제로 설명합니다. 디스크 입/출력 병렬화를 구축하는 가장 간단한 방법은 하드웨어 분할을 사용하고 트랜잭션 로그 파일을 제외한 모든 SQL Server 데이터베이스 파일을 지원하는 하나의 "드라이브 풀"을 생성하는 것입니다. 트랜잭션 로그 파일은 항상 로그 파일 전용으로 사용되는 별도의 물리 디스크 드라이브에 저장되어야 합니다. 이 풀은 Windows에서 단일 물리 드라이브로 표현되는 단일 RAID 어레이일 수 있습니다. 이보다 큰 풀인 경우에는 여러 개의 RAID 어레이와 SQL Server 파일/파일 그룹을 사용하여 설정할 수 있습니다. SQL Server 파일 하나를 각 RAID 어레이와 연관시킬 수 있으며, 파일 여러 개를 SQL Server 파일 그룹으로 결합할 수 있습니다. 그런 다음 파일 그룹에 데이터베이스를 구축하면 데이터가 모든 드라이브와 RAID 컨트롤러에 골고루 분산됩니다. "드라이브 풀" 방식은 RAID에 따라 데이터를 모든 물리 드라이브에 분산하여 데이터베이스 서버가 동작하는 동안 그 데이터에 병렬 액세스할 수 있도록 합니다. 데이터베이스 관리자는 데이터베이스 개체를 만들 단 하나의 물리적 장소를 알고 있으므로 이 드라이브 풀 방법을 사용하면 SQL Server 입/출력 성능 조정이 쉬워집니다. 단일 드라이브 풀을 감시하여 디스크 대기열을 확인하고, 필요한 경우에는 풀에 하드 드라이브를 추가하여 디스크 대기를 방지할 수 있습니다. 이 방법은 데이터베이스에서 가장 많이 사용되는 부분을 알지 못하는 일반적인 경우에 최적화를 도와줍니다. 사용 가능한 전체 입/출력 용량의 일부가 다른 디스크 파티션에서 격리되지 않도록 하는 것이 좋은데, 이는 SQL Server 작동 시간의 5%가 이 디스크로의 입/출력에 사용될 수 있기 때문입니다. "단일 드라이브 풀" 방법을 사용하면 사용 가능한 모든 입/출력 용량을 "항상" SQL Server 작업에 사용할 수 있습니다. 또한 입/출력 작업을 사용 가능한 최대 수의 디스크에 모두 분산시키는 것도 가능합니다. SQL Server 로그 파일은 항상 다른 모든 SQL Server 데이터베이스 파일과 물리적으로 분리된 별도의 하드 드라이브에 저장되어야 합니다. 활용률이 많은 여러 데이터베이스를 관리하는 SQL Server에서 각 데이터베이스에 대한 트랜잭션 로그 파일은 물리적으로 서로 분리시켜 경합을 줄여야 합니다. 트랜잭션 로그는 주로 순차적 쓰기 입/출력이므로 로그 파일을 분리하면 엄청난 입/출력 성능 향상 효과를 얻을 수 있습니다. 로그 파일을 포함하는 디스크 드라이브는 다른 입/출력 요청에 의해 중단되지 않을 때 이러한 순차 쓰기 작업을 매우 효과적으로 수행합니다. 트랜잭션 로그를 복제, 롤백 및 지연 업데이트 같은 SQL Server 작업의 일부분으로 읽어야 할 때가 있습니다. 일부 구현에서는 새 데이터를 거의 실시간에 데이터 웨어하우스로 로드하는 수단으로 데이터 변환 유틸리티에 대한 프런트 엔드로써 복제를 사용합니다. 복제에 참여하는 SQL Server의 관리자는 트랜잭션 로그 파일에 사용된 모든 디스크에 대한 정상적인 로그 트랜잭션 쓰기에 추가로 필요한 읽기를 수용할 만큼 충분한 입/출력 처리 능력이 있어야 합니다. 실제 세그먼트 파일 및 파일 그룹에 대한 별도의 관리도 필요합니다. 활용률이 많은 테이블이나 인덱스 분리 및 액세스 향상을 목적으로 한 세그먼트 분할을 통해서도 좋은 효과를 얻을 수 있습니다. 몇 가지 이점이 아래 나와 있습니다.
분할 대상 개체 고려 사항여러 하드 드라이브, RAID 컨트롤러, PCI 채널(또는 이 세 가지의 조합) 사이에서 SQL Server 작업 영역을 아래와 같이 분리할 수 있습니다.
하드웨어 RAID 컨트롤러, RAID 핫 플러그 드라이브 및 온라인 RAID 확장을 사용하면 SQL Server 입/출력 작업을 매우 편리하게 물리적으로 분리할 수 있습니다. 최대 융통성을 제공하는 접근 방법은 RAID 컨트롤러를 정렬하여 별도의 RAID 채널을 위에 설명된 각기 다른 작업 영역과 연관됩니다. 각 RAID 채널을 별도의 RAID 핫 플러그 캐비닛에 부착하여, RAID 컨트롤러를 통해 사용 가능한 경우 온라인 RAID 확장을 최대한 활용할 수 있도록 해야 합니다. Windows 논리 드라이브 문자는 각 RAID 어레이와 연관되며, SQL Server 파일은 알려진 입/출력 활용률 패턴을 기반으로 별개의 RAID 어레이들 사이에서 분리될 수도 있습니다. 이렇게 구성하면 각 작업과 연관된 디스크 대기열을 다시 고유한 RAID 채널 및 관련 드라이브 캐비닛과 관련지을 수 있습니다. RAID 컨트롤러와 해당 드라이브 어레이 캐비닛이 모두 온라인 RAID 확장을 지원하고 사용 가능한 핫 플러그 하드 드라이브용 슬롯이 캐비닛에 있는 경우, 해당 RAID 어레이에 대한 디스크 대기열 문제는 시스템 모니터가 그 RAID 어레이에 대한 디스크 대기열이 허용 가능한 수준(SQL Server 파일의 경우 2 미만)에 도달했다고 보고할 때까지 그 RAID 어레이에 드라이브를 추가하는 것으로 간단히 해결됩니다. 이러한 작업은 SQL Server가 온라인 상태일 때 수행할 수 있습니다. 트랜잭션 로그 분리 트랜잭션 로그 파일은 데이터 파일이 포함된 장치와 물리적으로 분리된 저장 장치에서 유지 관리해야 합니다. 사용자의 데이터베이스 복구 모델 설정에 따라 대부분의 업데이트 동작이 데이터 장치 동작과 로그 동작을 모두 생성합니다. 두 가지가 같은 장치를 공유하도록 설정되어 있으면 수행될 작업이 같은 제한된 리소스에 대해 완료됩니다. 이러한 입/출력 작업을 분리하면 대부분의 설치에 이롭습니다. tempdb 분리 SQL Server는 다양한 작업의 공유 작업 영역으로, 서버에 사용될 모든 서버 인스턴스에 tempdb 데이터베이스를 만듭니다. 여기에는 GROUP BY나 ORDER BY 절을 지원하거나 DISTINCT(중복 행을 제거하기 위해 임시 작업 테이블을 작성해야 함), 커서 및 해시 조인을 사용한 쿼리 지원을 위한 임시 테이블, 정렬, 하위 쿼리 처리, 집합 생성 작업 등이 포함됩니다. tempdb를 자체 RAID 채널로 분할하여 tempdb 입/출력 작업이 관련 트랜잭션의 입/출력 작업과 병렬로 수행되도록 할 수 있습니다. tempdb는 본질적으로 스크래치 영역이며 업데이트 작업이 많기 때문에 tempdb의 경우 RAID 5를 선택하는 것은 좋지 않습니다. RAID 1 또는 0+1이 더 나은 성능을 제공합니다. Raid 0은 내결함성을 제공하지는 않지만 tempdb에 대해 사용을 고려할 수 있는데, 그 이유는 데이터베이스 서버가 재시작될 때마다 tempdb가 다시 구축되기 때문입니다. RAID 0은 가장 적은 수의 물리 드라이브를 사용하면서 tempdb에 대해 최대의 RAID 성능을 제공합니다. 그러나 프로덕션 환경에서 tempdb에 대해 RAID 0을 사용할 때 tempdb에 사용된 드라이브를 포함하여 물리 드라이브 장애가 발생할 때 SQL Server 가용성이 떨어질 수 있다는 것이 문제가 될 수 있습니다. tempdb를 내결함성을 제공하는 RAID 구성에 포함시키면 이 문제를 피할 수 있습니다. tempdb 데이터베이스를 옮기려면 ALTER DATABASE 명령을 사용하여 tempdb와 연관된 SQL Server 논리 파일 이름의 실제 파일 위치를 변경하십시오. 예를 들어, tempdb 및 관련 로그를 새 파일 위치인 E:\mssql7과 C:\temp로 옮기려면 아래 명령을 사용하십시오. alter database tempdb modify file (name='tempdev',filename= 'e:\mssql7\tempnew_location.mDF') 생산 도중에는 사용자 데이터베이스에 비해 마스터 데이터베이스인 msdb와 모델 데이터베이스가 그리 자주 사용되지 않으므로 입/출력 성능 조정을 고려할 때 이를 염두에 둘 필요가 없습니다. 마스터 데이터베이스는 보통 새로운 로그인, 데이터베이스, 장치 및 기타 시스템 개체를 추가할 때만 사용됩니다. 데이터베이스 분할 데이터베이스는 파일이나 파일 그룹을 사용해 분할할 수 있습니다. 파일 그룹은 단순히 관리 목적으로 함께 그룹으로 묶인 개별 파일들의 집합입니다. 한 파일이 둘 이상 파일 그룹의 구성원이 될 수 없습니다. 테이블, 인덱스, text, ntext 및 image 데이터를 모두 특정 파일 그룹과 연관될 수 있습니다. 즉, 모든 관련 페이지가 해당 파일 그룹의 파일로부터 할당됩니다. 아래에서는 세 종류의 파일 그룹에 대해 설명합니다. 주 파일 그룹
사용자 지정 파일 그룹
기본 파일 그룹
파일 및 파일 그룹은 데이터와 인덱스의 배치를 제어하고 장치 경합을 제거하는 데 유용합니다. 제법 많은 설치에서 파일 및 파일 그룹을 데이터베이스보다 더 세분화된 메커니즘으로 사용하여 해당 데이터베이스 백업/복구 전략을 보다 정확하게 제어합니다. 수평 분할(테이블) 수평 분할은 한 개의 테이블을 열 수는 같고 행 수는 적은 테이블 여러 개로 분할합니다. 테이블을 수평으로 분할하는 방법은 데이터의 분석 방법에 따라 달라집니다. 기본 원칙은 쿼리가 가능한 한 적은 수의 테이블을 참조하도록 테이블을 분할하는 것입니다. 그렇지 않으면 쿼리 시 테이블을 논리적으로 병합하는 데 사용되는 과도한 UNION 쿼리가 성능을 저하시킬 수 있습니다. 예를 들어, 업무 상 데이터 웨어하우스의 중앙 정보 테이블에 10년치 트랜잭션 데이터를 저장해야 한다고 가정하십시오. 10년간 회사의 트랜잭션 데이터는 10억 개가 넘는 행을 차지합니다. 10억 개 단위를 관리한다는 것은 어려운 일입니다. 그리고 매년 10번째 년도의 데이터를 삭제하고 최근 년도 데이터를 로드해야 합니다. 관리자가 취할 가장 일반적인 방법은 서로 별개인 동시에 같은 방식으로 구조화된 테이블을 10개 만들어 각각에 1년치 데이터를 저장하는 것입니다. 그런 다음 10개의 테이블 맨 위에 하나의 결합된 뷰를 정의하여 일반 사용자에게 모든 데이터가 하나의 테이블에 있는 것처럼 나타내 줍니다. 그러나 실제로는 그렇지 않습니다. 이 뷰에 대한 모든 쿼리는 지정된 연도(및 해당 테이블)만 검색하도록 최적화됩니다. 관리 권한은 여전히 관리자에게 있습니다. 이제 관리자는 각 연도별 데이터를 집합적으로 관리할 수 있습니다. 연도별 데이터를 로드하고 인덱스를 생성하고 유지 관리할 수 있습니다. 새로운 연도의 데이터를 추가하는 것은 아주 간단하여, 먼저 뷰를 삭제하고 10번째 연도의 테이블을 삭제한 다음, 새로운 연도의 데이터를 로드 및 인덱스화하고 새 데이터를 포함하는 새 뷰를 재정의하면 됩니다. 데이터를 여러 테이블이나 서버로 분할할 경우, 데이터 스캔 시간이 감소하기 때문에 분할된 데이터 단편만 액세스하는 쿼리는 빠르게 실행됩니다. 테이블이 여러 개의 서버에 있거나 여러 프로세서가 장착된 컴퓨터에 있는 경우, 쿼리에 포함된 각 테이블을 병렬로 스캔할 수 있으므로 쿼리 성능이 향상됩니다. 또한 인덱스 재작성이나 테이블 백업 등과 같은 유지 관리 작업도 더 빠르게 실행할 수 있습니다. 분할된 뷰를 통해 데이터는 계속 하나의 테이블로 표시되며 올바른 기본 테이블을 수동으로 참조하지 않고도 쿼리될 수 있습니다. 다음 조건 중 하나가 만족될 경우 분할된 뷰를 업데이트할 수 있습니다. 분할된 뷰 및 관련 제한 사항에 대한 자세한 내용은 온라인 SQL Server 문서를 참조하십시오.
클러스터링되지 않은 인덱스 분리 인덱스는 B-트리 구조체에 있습니다. 이 구조체는 ALTER DATABASE 명령을 사용하여 별도의 파일 그룹을 설정함으로써 관련 데이터베이스 테이블(클러스터링된 인덱스는 제외)과 분리시킬 수 있습니다. 아래 예제에서 첫째 ALTER DATABASE는 파일 그룹을 만듭니다. 둘째 ALTER DATABASE는 파일을 새로 만들어진 파일 그룹에 추가합니다. alter database testdb add filegroup testgroup1 파일 그룹 및 관련 파일을 만든 다음, 인덱스를 만들면서 파일 그룹을 지정하여 인덱스를 저장하는 데 사용할 수 있습니다. create table test1(col1 char(8)) SP_HELPFILE은 파일 및 파일 그룹에 대한 정보를 지정된 데이터베이스로 다시 보고합니다. SP_HELP sp_helpfile 프로세서가 여러 개인 컴퓨터에서 실행되는 SQL Server는 데이터를 병렬로 스캔할 수 있습니다. 여러 개의 파일로 구성된 파일 그룹에 들어 있는 테이블에 대해 여러 병렬 스캔을 동시에 실행할 수 있습니다. 테이블이 순차적으로 액세스될 때마다 각 파일을 병렬로 읽기 위한 별도의 스레드가 생성됩니다. 예를 들어, 네 개의 파일로 구성된 파일 그룹에 작성된 테이블 전체 스캔에서는 네 개의 별도 스레드를 사용하여 데이터를 병렬로 읽어옵니다. 따라서 각 파일 그룹에 더 많은 파일을 만들면 별도 스레드를 통해 각 파일이 병렬로 스캔되므로 성능을 향상시킬 수 있습니다. 마찬가지로 쿼리가 서로 다른 파일 그룹에 있는 테이블을 연결할 때, 테이블이 서로 병렬로 읽혀지므로 쿼리 성능이 향상됩니다.
또한 테이블 내의 text, ntext 또는 image 열이 기본 테이블을 포함하는 파일 그룹이 아닌 다른 파일 그룹에 작성될 수 있습니다.
결국, 파일이 너무 많고 비례적으로 병렬 스레드도 너무 많아져 디스크 입/출력 하위 시스템에 병목 현상이 일어나는 포화점에 도달하게 됩니다. 병목 현상은 Windows 시스템 모니터(Windows NT 4.0의 경우 성능 모니터)에서 PhysicalDisk 개체 및 Disk Queue Length 카운터를 관찰하면 확인할 수 있습니다. Disk Queue Length 카운터가 3을 넘으면 파일 수를 줄이십시오.
여러 개의 파일을 사용한 병렬 데이터 액세스를 통해 처리량을 개선하려면 가능한 한 많은 물리 드라이브에 가능한 한 많은 데이터를 분산시키는 것이 좋습니다. 데이터를 모든 디스크에 고르게 분산시키려면 먼저 하드웨어 기반 디스크 스트라이핑을 설정한 다음, 파일 그룹을 사용하여 필요한 만큼의 하드웨어 스트라이프 세트에 데이터를 분산시킵니다.
병렬 쿼리 권장 사항
SQL Server는 자동으로 쿼리를 병렬로 실행합니다. 이 기능으로 복수 프로세서 컴퓨터에서의 쿼리 실행이 최적화됩니다. 하나의 OS 스레드를 사용하여 한 가지 쿼리를 실행하기보다는 여러 개의 스레드로 작업을 나누면(스레드 및 메모리 가능성에 따라) 복잡한 쿼리를 보다 빠르고 효율적으로 완료할 수 있습니다.
SQL Server의 최적화 프로그램이 쿼리에 대한 계획을 세우고 쿼리를 병렬로 실행할 시기를 결정합니다. 다음과 같은 사항을 기준으로 결정을 내립니다.
SQL Server에서 DBCC 및 인덱스 작성 등의 병렬 작업이 허용되면 서버 리소스에 대한 로드가 증가하여, 과도한 병렬 작업이 일어날 때 경고 메시지가 나타날 수 있습니다. 서버 오류 로그에 리소스 부족에 대한 경고 메시지가 자주 나타나면, 시스템 모니터(Windows NT 4.0의 경우 성능 모니터)를 사용하여 메모리, CPU 활용률, 입/출력 활용률 등과 같은 사용 가능한 리소스를 조사해 보십시오.
서버에 활성 사용자가 여러 명 있을 때 병렬로 실행되는 쿼리를 너무 많이 실행하지 마십시오. 오프로드 시간에는 DBCC 및 INDEX 생성과 같은 유지 관리 작업을 시도하십시오. 이러한 작업은 병렬로 실행할 수 있습니다. 디스크 입/출력 성능을 모니터하십시오. 시스템 모니터(Windows NT 4.0의 경우 성능 모니터)에서 디스크 대기열의 길이를 관찰하여 하드 디스크를 업그레이드할 것인지 여부나 여러 디스크에 데이터베이스를 다시 분산시킬지 여부를 결정합니다. CPU 활용률이 너무 높으면 업그레이드하거나 프로세서를 추가하십시오.
다음의 서버 구성 옵션이 쿼리의 병렬 실행에 영향을 줄 수 있습니다.
데이터베이스 로드 작업 속도를 높일 때 몇 가지 팁과 기술에 유의해야 합니다. 기술은 초기 데이터 로드인지 혹은 증분 데이터 로드인지에 따라 변할 수 있습니다. 일반적으로 증분 로드가 더 복잡하고 제한적입니다. 선택하는 방법은 사용자 제어와 무관한 요소의 영향도 받습니다. 사용자가 선택한 저장소 구성, 서버 하드웨어의 제한 사항 등과 같은 창 요구 사항 처리는 모두 사용자에게 허용되는 옵션에 영향을 줄 수 있습니다.
초기 데이터 로드와 증분 데이터 로드 모두에서 유의해야 할 많은 일반적인 사항이 있습니다. 이에 대해 다음과 같은 주제별로 자세히 설명합니다.
"성능에 영향을 주는 구성 옵션" 절에서 데이터베이스 복구 모델에 대해 설명했습니다. 사용자가 선택하는 복구 모델이 데이터를 로드할 때 필요한 시간에 상당한 영향을 줄 수 있다는 점을 명심하십시오. 기본적으로 복구 모델이 트랜잭션 로그에 기록될 데이터의 양을 제어합니다. 트랜잭션 로그에 쓰기 작업을 수행하면 필연적으로 작업 로드가 두 배로 증가하기 때문에 이는 중요한 사항입니다.
로그 및 최소 로그 대량 복사 작업
전체 복구 모델을 사용할 경우, 대량 데이터 로드 메커니즘(아래 설명됨) 중 하나를 통해 수행된 모든 행 삽입 작업이 트랜잭션 로그에 기록됩니다. 데이터 로드 양이 많으면 트랜잭션 로그가 너무 빨리 채워질 수 있습니다. 이 경우에 minimally logged bulk copy 작업을 수행하여 트랜잭션 로그 공간이 부족하게 되는 것을 막을 수 있습니다. 대량 복사 작업의 일부로 대량 복사 수행 모드(기록과 무기록 중 하나)를 지정하지 않으면, 데이터베이스의 상태 및 대량 복사에 포함된 테이블에 따라 결정됩니다. 다음 조건이 모두 만족되면 기록 없이 대량 복사가 수행됩니다.
이러한 조건을 만족하지 않는 모든 대량 복사(SQL Server로 복사)가 완벽하게 기록됩니다.
초기 데이터 로드 시 항상 대량 로그 또는 단순 복구 모델에서 작업해야 합니다. 증분 데이터 로드에서 데이터 손실 가능성이 적으면 되도록 대량 로그 모델을 사용하십시오. 많은 데이터 웨어하우스는 주로 읽기 전용이거나 최소한의 트랜잭션 작업이 있으므로 데이터베이스 복구 모델을 대량 로그로 설정해도 거의 문제가 발생하지 않습니다.
SQL Server에 데이터의 대량 이동 요구를 처리하는 두 가지 메커니즘이 있습니다. 첫째 메커니즘은 bcp 유틸리티이고 둘째 메커니즘은 BULK INSERT 문입니다. bcp는 SQL Server로 또는 SQL Server로부터 데이터를 복사하는 명령 프롬프트 유틸리티입니다. SQL Server 2000에서는 ODBC 대량 복사 응용 프로그래밍 인터페이스(API)를 사용하여 bcp 유틸리티를 다시 작성했습니다. 초기 버전의 bcp 유틸리티는 DB 라이브러리 대량 복사 API를 사용하여 작성했습니다.
BULK INSERT는 데이터베이스 환경에서 실행될 수 있는 SQL Server에 포함된 Transact-SQL 문입니다. bcp와 달리, BULK INSERT는 데이터만 SQL Server로 끌어올 수 있습니다. 데이터를 내보낼 수는 없습니다. BULK INSERT를 사용할 경우, 명령 프롬프트로 나오지 않고 Transact-SQL 문을 사용하여 데이터를 SQL Server 인스턴스로 복사할 수 있다는 이점이 있습니다.
프로그래머가 많이 사용하는 옵션 중 하나인 셋째 옵션은 대량 복사 API입니다. 프로그래머는 대량 복사 API를 사용하여 ODBC, OLE DB, SQL-DMO 또는 DB 라이브러리 기반 응용 프로그램을 통해 SQL Server 안팎으로 데이터를 이동할 수 있습니다.
세 옵션 모두 일괄처리 크기를 정확하게 제어하도록 지원합니다. 작은 용량의 데이터로 작업할 때를 제외하고, 복구 용도로 일괄처리 크기를 지정하는 습관을 들이는 것이 좋습니다. 크기를 지정하지 않으면, SQL Server가 한 번의 일괄처리로 모든 행의 로드를 커밋합니다. 가령 1,000,000개의 새로운 데이터 행을 하나의 테이블에 로드한다고 가정하십시오. 그리고 999,999번째 행을 막 처리했는데 갑자기 전원이 꺼져 서버가 종료되었습니다. 서버가 재가동되고 데이터를 다시 로드하려면 먼저 데이터베이스로부터 총 999,999개의 행을 롤백해야 합니다. 일괄처리 크기를 10,000으로 지정했다면 999,999개의 행이 아닌 9,999개의 행만 롤백하면 되므로 복구 시간이 상당히 줄어들 것입니다. 1 - 990,000개의 행은 이미 데이터베이스에 커밋되었기 때문입니다. 일괄처리 크기를 지정하지 않으면 또한 첫째 행부터 로드 처리를 다시 시작해야 합니다. 일괄처리 크기를 10,000개의 행으로 지정하면 990,001번째 행부터 로드가 다시 시작되고 이미 커밋된 990,000개의 행은 효과적으로 무시할 수 있습니다.
bcp 유틸리티와 BULK INSERT 문은 TABLOCK 힌트를 승인합니다. 이 힌트는 사용할 잠금 동작을 지정하는 데 사용됩니다. TABLOCK을 지정하면 대량 복사 작업 기간 동안 대량 업데이트 테이블 수준 잠금이 적용됩니다. TABLOCK을 사용하면 테이블에 대한 잠금 경합이 줄어들기 때문에 대량 복사 작업 성능을 개선할 수 있습니다. 하나의 테이블에 대해 병렬 로드를 수행할 경우 이 설정은 매우 중요한 의미를 가집니다(다음 절 참조).
예를 들어, 테이블 수준 잠금을 지정하고 Authors.txt 데이터 파일에서 pubs 데이터베이스의 authors2 테이블로 데이터를 대량 복사하려면 다음 명령 프롬프트에서 실행하십시오.
bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "TABLOCK"
또는 다음 예에서와 같이 SQL 쿼리 분석기 등의 쿼리 도구에서 BULK INSERT 문을 사용하여 데이터를 대량 복사할 수도 있습니다.
BULK INSERT pubs..authors2 FROM 'c:\authors.txt' TABLOCK을 지정하지 않을 경우, 행 수준 잠금이 기본 잠금 설정으로 사용됩니다. 단, 테이블에 대한 table lock on bulk load 옵션을 on으로 설정한 경우는 예외입니다. table lock on bulk load 옵션과 함께 sp_tableoption 명령을 사용하는 것도 대량 로드 작업 중 테이블에 대한 잠금 동작을 설정하는 방법입니다.
참고 TABLOCK 힌트가 지정된 경우, 이 힌트는 대량 로드 기간에 대해 sp_tableoption을 사용하여 지정한 설정에 우선합니다.
병렬 로드 - 분할되지 않은 테이블
SQL Server의 대량 데이터 로드 메커니즘을 사용하여 하나의 분할되지 않은 테이블로 병렬 데이터 로드를 수행할 수 있습니다. 이는 여러 데이터 로드를 동시에 실행하는 방법입니다. 병렬 로드를 시작하기 전에 로드할 데이터를 여러 개의 별도 파일(대량 삽입 API에 대한 데이터 소스)로 쪼개야 합니다. 그런 다음 별도의 여러 로드 작업을 동시에 시작하여 데이터를 병렬로 로드할 수 있습니다.
예를 들어, 전세계 네 지역에서 활동하며 월 단위로 클라이언트에 부과된 시간 수를 보고받는 서비스 회사를 위한 통합 데이터베이스를 로드해야 한다고 가정하십시오. 대규모 서비스 업체의 경우, 통합해야 할 트랜잭션 데이터 용량이 엄청나게 클 수 있습니다. 네 보고 지역 각각에서 별도의 파일을 제출하는 경우, 앞에 이미 설명한 방법으로 네 파일을 동시에 하나의 테이블로 로드할 수 있습니다.
참고 병렬로 처리하는 병렬 스레드(로드) 수가 SQL Server에서 사용할 수 있는 프로세서 수를 넘을 수 없습니다.
다음 그림에서는 분할되지 않은 테이블에서 병렬로 로드하는 작업을 보여줍니다.
병렬 로드 - 수평 분할(테이블)
여기서는 수평 분할된 테이블을 사용하여 데이터 로드 속도를 개선하는 방법에 대해 중점적으로 설명합니다. 앞 절에서는 여러 개 파일의 데이터를 하나의(분할되지 않은) 테이블로 로드하는 것에 대해 설명하였습니다. 테이블을 수평 분할하면 장치 경합을 줄여 로드 프로세스를 가속시킬 뿐 아니라 데이터의 인접성을 개선할 수도 있습니다. 위의 그림에서는 테이터가 테이블의 여러 부분으로 로드되고 있지만 정확한 결과가 아닐 수도 있습니다. 위 로드에서 세 스레드가 모두 동시에 처리될 경우, 테이블에 대한 익스텐트가 뒤섞일 수 있습니다. 데이터의 무질서한 혼합은 데이터 검색 시 성능을 저하시킬 수 있습니다. 이는 데이터가 물리적으로 인접되는 순서로 저장되지 않기 때문이며, 시스템이 비순차적 입/출력을 통해 데이터에 액세스하는 결과를 초래할 수 있습니다.
테이블에 대해 클러스터링된 인덱스를 만드는 것이 문제를 해결해 줄 수 있는데 그 이유는 데이터가 키순으로 읽혀지고 정렬되며 다시 서로 인접되도록 기록되기 때문입니다. 그러나 오래된 데이터 읽기, 정렬, 삭제 및 새로 정렬된 데이터 재기록 등에는 시간이 많이 걸릴 수 있습니다(아래의 '미리 정렬된 데이터 로드' 참조). 데이터 뒤섞임을 피하려면 파일 그룹을 사용하여 큰 테이블을 저장할 수 있는 인접한 여유 공간을 예약해 보십시오. 많은 설치 프로세스에서도 파일 그룹을 사용하여 인덱스 데이터를 테이블 데이터와 분리합니다.
설명을 위해 하나의 커다란 물리 파티션에 할당된 데이터 웨어하우스를 가상하십시오. 이 데이터베이스에 병렬로 수행한 로드 작업 결과 대상 데이터/인덱스 페이지가 서로 인접되지 않은(뒤섞인) 상태로 저장되기 쉽습니다. 어떤 종류의 작업일까요? 또한 데이터 수정 작업으로 이러한 데이터 상태가 더 심해지게 됩니다. 초기 데이터 로드, 증분 데이터 로드, 인덱스 작성, 인덱스 유지 관리, 삽입, 업데이트, 삭제 등은 모두 처리 창 요구 사항을 만족시키기 위해 병렬로 수행되기 쉬운 작업입니다.
다음 그림에서는 테이블을 여러 파일 그룹으로 분할하는 작업을 보여줍니다.
초기 버전의 SQL Server에 인덱스를 생성할 때 SORTED_DATA 옵션을 지정할 수 있는 옵션이 있었습니다. SQL Server 2000에서는 이 옵션이 제거되었습니다. 초기 버전에서 CREATE INDEX 문의 일부로 이 옵션을 지정하는 이유는 인덱스 작성 과정에서 정렬 단계를 피할 수 있었기 때문입니다. 기본적으로 SQL Server에서 클러스터링된 인덱스를 생성하는 도중 테이블의 데이터가 정렬됩니다. SQL Server 2000에서 같은 효과를 보려면 데이터를 대량 로드하기 전에 클러스터링된 인덱스를 만들어 보십시오. SQL Server 2000에서 대량 작업에는 고급 인덱스 유지 관리 전략이 사용되는데, 기존의 클러스터링된 인덱스가 있는 테이블로의 데이터 이동 성능을 개선하고 데이터를 가져온 후 정렬할 필요성을 없애는 전략입니다.
FILLFACTOR와 PAD_INDEX에 대해서는 "인덱스와 인덱스의 유지 관리"에서 자세히 설명합니다. FILLFACTOR와 PAD_INDEX에 대해 명심할 중요한 점은 인덱스를 생성할 때 기본 설정을 그대로 두면 SQL Server가 데이터 저장에 필요한 것보다 많은 쓰기 및 읽기 입/출력 작업을 수행할 수 있다는 점입니다. 쓰기 작업은 극히 적지만 읽기 작업량은 매우 많은 데이터 웨어하우스에서 특히 그렇습니다. SQL Server가 더 많은 데이터를 하나의 데이터 페이지나 인덱스 페이지들로 묶도록 하려는 경우 인덱스를 생성할 때 특정 FILLFACTOR를 지정할 수 있습니다. 대체 FILLFACTOR 값을 제공할 때 PAD_INDEX를 지정하는 것도 좋은 방법입니다.
데이터 로드 중
데이터 로드 후
서버에서 하드웨어 장치의 입/출력 특성에 대해 논의했습니다. 이제 디스크 드라이브에 SQL Server 데이터와 인덱스 구조가 실제로 배치되는 방법에 대해 설명할 차례입니다. 인덱스 배치는 데이터 웨어하우스 설계를 마친 후 성능 개선에 가장 큰 영향을 줄 수 있는 요소입니다.
SQL Server 2000에 새로 등장하는 몇 가지 새로운 인덱스 유형은 모두 두 가지 주요한 형식을 기반으로 합니다. 바로 클러스터링된 인덱스와 클러스터링되지 않은 인덱스 형식입니다. SQL Server에서 데이터베이스 설계자가 사용할 수 있는 두 가지 기본 인덱스는 다음과 같습니다.
기본 유형을 변형한 추가 유형은 다음과 같습니다.
계속해서 앞에 나왔던 인덱스 유형에 대해 자세히 설명합니다. 단, 전체 텍스트 인덱스는 제외합니다. 전체 텍스트 인덱싱은 기타 데이터베이스 인덱스와 다른 특별한 경우로, 이 책에서는 다루지 않습니다. 인덱싱된 뷰는 SQL Server 2000에 처음 소개된 새로운 유형의 인덱스로 특히 데이터 웨어하우징 사용자가 관심을 가질만한 기능입니다. SQL Server 2000에서 또 하나의 새로운 기능은 오름차순 또는 내림차순으로 인덱스를 만드는 기능입니다.
데이터베이스의 인덱스는 책의 색인과 비슷합니다. 책에서 색인을 이용하면 전체를 읽지 않고도 정보를 빠르게 찾을 수 있습니다. 데이터베이스의 인덱스는 데이터베이스 프로그램이 전체 테이블을 스캔하지 않고 테이블에서 데이터를 찾을 때 사용됩니다. 책의 색인은 특정 단어들을 해당 단어가 있는 페이지 번호와 함께 나열해 놓은 목록입니다. 데이터베이스의 인덱스는 테이블에 있는 값들을 테이블에서의 저장소 위치를 나타내는 행과 함께 나열해 놓은 목록입니다.
인덱스는 하나의 열에 만들 수도 있고 테이블의 여러 열 집합에 만들 수도 있으며 B-트리 형태로 구현됩니다. 인덱스에는 테이블의 각 행에 있는 하나 이상의 열(검색 키)로 구성된 항목이 있습니다. B-트리는 검색 키에 오름차순 또는 내림차순으로 정렬된 순서로 저장되므로(정렬 순서는 인덱스를 만들 때 선택한 옵션에 따라 결정됨) 검색 키의 어떠한 하위 집합으로도 효율적 검색이 가능합니다. 예를 들어, A, B, C 열의 인덱스는 A 또는 A 및 B 또는 A, B, C의 세 대상에서 모두 효율적으로 검색할 수 있습니다.
데이터베이스를 만들고 성능을 위해 조정할 때, 데이터 찾기에 사용할 열에 대해 인덱스를 만들어야 합니다. SQL Server가 제공하는 pubs 샘플 데이터베이스에서 employee 테이블은 emp_id 열에 인덱스가 있습니다. 누군가 지정된 emp_id 값을 이용하여 employee 테이블에서 데이터를 찾는 명령문을 실행하면 SQL Server 쿼리 프로세서가 emp_id 열에 대한 인덱스를 인식하여 데이터 찾기를 위한 인덱스로 사용합니다. 다음 그림에서는 인덱스가 각 emp_id 값을 저장하고 테이블에서 해당 값이 있는 데이터 행을 가리키는 방법을 보여줍니다.
인덱스를 사용하면 성능이 개선되는 이점이 있지만, 여기에는 대가가 따릅니다. 인덱스가 있는 테이블은 데이터베이스에 더 많은 저장 공간을 필요로 합니다. 또한 데이터를 삽입, 업데이트 또는 삭제하는 명령을 수행하는 시간이 길어지고 인덱스를 유지 관리하는 데 시간이 더 많이 걸릴 수 있습니다. 인덱스를 설계하고 생성할 때 저장소 공간 및 처리 리소스에 필요한 추가 비용을 충분히 보상할 만한 성능이 제공되는지 확인해야 합니다.
SQL Server 쿼리 프로세서 내부에서 있는 고유한 기능으로 인덱스 교차 수행 기능이 있습니다. 이는 인덱스를 다루는 특별한 형태로, 뒤에서 자세히 설명하겠지만 다음 두 가지 이유로 여기에서도 언급합니다. 첫째, 인덱스 설계 전략에 영향을 줄 수 있습니다. 둘째, 필요한 인덱스 수를 줄임으로써 대용량 데이터베이스의 디스크 공간을 크게 절약할 수 있게 합니다.
쿼리 프로세서는 인덱스 교차를 통해 여러 개의 인덱스를 사용하여 쿼리를 해결할 수 있습니다. 대부분의 데이터베이스 쿼리 프로세서가 쿼리 해결을 시도할 때 하나의 인덱스만 사용합니다. SQL Server는 지정된 테이블이나 뷰에서 여러 개의 인덱스를 결합하여 결합된 인덱스를 기반으로 해시 테이블을 만든 다음, 해시 테이블을 이용함으로써 요청된 쿼리에 대한 입/출력을 줄일 수 있습니다. 인덱스 교차를 통해 만든 해시 테이블은 본질적으로 포함 인덱스가 되어, 포함 인덱스와 동일한 입/출력 성능 상의 이점을 제공합니다. 인덱스 교차는 데이터베이스에 수행할 모든 쿼리를 사전에 결정하기 어려운 데이터베이스 사용자 환경에 많은 융통성을 제공합니다. 이 경우에는 자주 쿼리되는 모든 열에 단일 열의 클러스터링되지 않은 인덱스를 정의하고, 포함된 인덱스에 필요한 상황은 인덱스 교차가 처리하도록 하는 것이 좋습니다.
다음은 인덱스 교차를 이용한 예입니다.
Create index Indexname1 on Table1(col2) 이전 쿼리가 수행될 때, 인덱스가 결합되어 쿼리를 빠르고 효율적으로 해결할 수 있습니다.
SQL Server의 모든 인덱스는 8KB 인덱스 페이지에 저장되는 B-트리 인덱스 구조 위에 만들어집니다. 각 인덱스 페이지에 페이지 헤더가 있고, 그 뒤에 인덱스 행이 옵니다. 각 인덱스 행에는 키 값과 하위 수준 인덱스 페이지 또는 실제 데이터 행을 가리키는 포인터가 있습니다. 인덱스의 각 페이지를 인덱스 노드라고도 합니다. B-트리의 최상위 노드는 루트 노드라고 하고 인덱스의 노드의 맨 아래 층을 잎 노드라고 합니다. 루트와 잎 사이의 모든 인덱스 수준은 묶어서 중간 수준 노드라고 합니다. 각 인덱스 수준의 페이지들은 함께 이중 링크 목록에 연결됩니다.
SQL Server 데이터와 인덱스 페이지 크기는 모두 8KB입니다. SQL Server 데이터 페이지에는 텍스트와 이미지 데이터를 제외한 테이블 행과 연관된 모든 데이터가 들어 있습니다. 텍스트 및 이미지 데이터의 경우, 텍스트/이미지 열과 연관된 행을 포함하는 SQL Server 데이터 페이지는 텍스트 또는 이미지 데이터를 포함하는 한두 개의 8KB 페이지로 이루어진 바이너리 트리(또는 B-트리) 구조를 가리키는 포인터를 포함합니다. SQL Server 2000의 새로운 기능 중 하나는 작은 크기의 텍스트 및 이미지 값을 연속해서 저장하는 기능입니다. 즉, 작은 크기의 텍스트와 이미지 값이 데이터 페이지에 저장되는 것입니다. 이 기능을 사용하면 해당 이미지나 텍스트 데이터를 가져오는 데 필요한 추가 입/출력 작업을 피할 수 있기 때문에 입/출력 작업이 줄어듭니다. 텍스트나 이미지를 연속해서 저장할 테이블을 설정하는 방법에 대해서는 SQL Server 온라인 문서를 참조하십시오.
클러스터링된 인덱스는 테이블에서 광범위한 데이터 값을 검색할 때 매우 유용합니다. 클러스터링되지 않은 인덱스는 특정 행을 대상으로 하는 검색에 아주 적합한 반면, 클러스터링된 인덱스는 광범위한 행을 검색하는 데 적합합니다. 그러나 이 단순한 논리만으로 어떤 유형의 인덱스를 만들지 결정하는 것이 항상 옳은 것은 아닙니다. 왜냐하면 한 테이블에 하나의 클러스터링된 인덱스만 허용되기 때문입니다. 여기에는 단순한 물리적인 이유가 있습니다. 클러스터링된 인덱스 B-트리 구조의 윗부분(비잎 수준)은 대응 부분의 클러스터링되지 않은 인덱스처럼 구성되지만 클러스터링된 인덱스의 아래 부분은 테이블에서 가져온 실제 8KB 데이터 페이지로 구성됩니다. 단, 뷰의 최상위에 클러스터링된 인덱스를 만들 경우는 예외입니다. 인덱싱된 뷰는 나중에 설명하고, 실제 테이블에 만들 클러스터링된 인덱스에 대해 논의해 보겠습니다. 테이블에 클러스터링된 인덱스를 만들 때 테이블과 연관된 데이터가 읽혀져 정렬되고, 다시 데이터베이스에 물리적으로 저장됩니다(인덱스 검색 키와 같은 순서로 저장됨). 테이블의 데이터는 복제 없이 한 가지 순서로 저장소에 유지될 수만 있으므로, 클러스터링된 인덱스는 하나뿐이라는 제한이 생깁니다. 다음 그림에서는 클러스터링된 인덱스의 저장소를 보여줍니다.
클러스터링된 인덱스 및 성능
클러스터링된 인덱스의 상속 특성 중 일부는 성능에 영향을 줍니다.
클러스터링된 인덱스를 적용한 키 검색으로 SQL Server 데이터를 검색할 때, 관련 데이터 페이지를 검색하는 데 포인터 점프(하드 디스크 위치의 비순차적 변경이 사용될 가능성이 있음)가 필요하지 않습니다. 잎 수준의 클러스터링된 인덱스가 사실상 연관된 데이터 페이지이기 때문입니다.
앞에서 설명했듯이 잎 수준(또한 결과적으로 테이블이나 인덱싱된 뷰의 데이터)은 검색 키와 동일한 순서로 실제로 정렬되어 저장됩니다. 클러스터링된 인덱스의 잎 수준에 테이블의 실제 8KB 데이터 페이지가 포함되어 있으며, 이는 전체 테이블의 행 데이터가 클러스터링된 인덱스에 의해 결정된 순서로 디스크 드라이브에 물리적으로 정렬되어 있다는 뜻입니다. 순차 디스크 입/출력이 사용되므로, 이 방법은 클러스터링된 인덱스의 값을 기준으로 이 테이블에서 최소 64KB가 넘는 많은 행을 반입할 때 뛰어난 입/출력 성능을 제공할 가능성이 있습니다. 이 테이블에서 페이지 분할이 이루어지는 경우는 제외되는데, 이에 대해서는 뒤의 "FILFACTOR 및 PAD_INDEX의 중요성" 절에서 설명합니다. 이 때문에 범위 스캔을 수행하여 대량의 행 검색을 하는 데 사용할 열 기반 테이블에서 클러스터링된 인덱스를 선택하는 것입니다.
클러스터링된 인덱스과 연관되는 테이블의 행이 인덱스 검색 키와 같은 순서로 정렬 및 저장되어야 한다는 사실에는 다음과 같은 의미가 내포되어 있습니다.
참고 아주 큰 테이블에 인덱스를 만드는 좋은 방법은 먼저 클러스터링된 인덱스로 시작하고, 다음에 클러스터링되지 않은 인덱스를 만드는 것입니다. 여기서 클러스터링되지 않은 인덱스는 데이터 이동 때문에 다시 작성해야 합니다. 모든 인덱스를 삭제할 때, 먼저 클러스터링되지 않은 인덱스가 삭제되고 클러스터링된 인덱스가 마지막에 삭제됩니다. 따라서 어떤 인덱스도 다시 만들 필요가 없습니다. 클러스터링되지 않은 인덱스는 키 값을 기반으로 하여 대형 SQL Server에서 선택이 용이한 적은 수의 열만 반입하는 데 유용합니다. 앞에서 설명한 것과 같이 클러스터링되지 않은 인덱스는 8KB 인덱스 페이지로 형성된 바이너리 트리입니다. 인덱스 페이지의 바이너리 트리 아래 부분 또는 잎 수준에는 이 인덱스를 구성하는 열에 있는 모든 데이터가 포함되어 있습니다. 키 값에 일치하는 정보를 테이블에서 검색하기 위하여 클러스터링되지 않은 인덱스를 사용하는 경우, 인덱스의 잎 수준에서 키와 일치하는 값을 찾을 때까지 B-트리가 검색됩니다. 인덱스를 구성하지 않는 테이블의 열이 필요한 경우에는 포인터 점프가 이루어집니다. 포인터 점프에는 디스크에 대한 비순차 입/출력 작업이 필요할 것입니다. 테이블 및 해당 인덱스 B-트리가 큰 경우에는 다른 디스크에서 데이터를 읽어야 할 수도 있습니다. 동일한 8KB 데이터 페이지에 포인터가 여러 개 연결되어 있으면 해당 페이지를 데이터 캐시로 읽기만 하면 되므로 입/출력 성능에 미치는 부정적인 영향이 줄어들 것입니다. 클러스터링되지 않은 인덱스를 사용하는 검색에 관련된 SQL 쿼리에 대해 반환되는 각 행에 한 번 이상의 포인터 점프가 필요합니다.
참고 이것이 각 포인터 점프와 연관된 오버헤드가 테이블에서 하나 또는 몇 개의 행만 반환하는 쿼리를 처리하는 데 있어 클러스터링되지 않은 인덱스가 더 적합한 이유입니다. 많은 행을 필요로 하는 쿼리에는 클러스터링된 인덱스가 더 적합합니다.
다음 그림에서는 클러스터링되지 않은 인덱스의 저장소를 보여줍니다. 해당 데이터 페이지를 가리키는 추가된 잎 수준에 주목하십시오. 이는 클러스터링된 인덱스를 사용할 때와는 반대로 클러스터링되지 않은 인덱스를 사용하여 테이블 데이터에 액세스할 때 추가된 포인터 점프가 일어나는 위치입니다. 클러스터링되지 않은 인덱스에 대한 자세한 내용은 SQL Server 온라인 문서를 참조하십시오.
기존 테이블에 인덱스를 만들 때 UNIQUE 키워드를 지정하는 방법으로 클러스터링된 인덱스와 클러스터링되지 않은 인덱스 모두에 고유성을 부여할 수 있습니다. UNIQUE 제약 조건을 사용하는 것은 테이블 내에서 고유성을 유지하는 또 다른 방법입니다. 고유 인덱스와 마찬가지로 UNIQUE 제약 조건은 일련의 열 집합 값의 고유성을 유지합니다. 실제로 UNIQUE 제약 조건을 지정하면 기본이 되는 고유 인덱스가 자동으로 만들어져 해당 제약 조건을 적용하기 쉬워집니다. 고유성은 CREATE TABLE 문의 요소로 정의되어 문서화되기 때문에 종종 별도의 고유 인덱스를 만드는 것보다 UNIQUE 제약 조건이 더 많이 사용됩니다.
SQL Server 2000에 계산된 열에 대한 인덱스를 만드는 기능이 새로 추가되었습니다. 이 기능은 쿼리가 흔하게 제출되고 계산된 열이 일상적으로 제공되지만 관리자가 단순히 인덱스 작성을 허용하기 위해 테이블의 실제 열에 데이터를 보존하지는 않으려는 경우에 편리합니다. 이 경우, 계산된 열이 인덱싱에 필요한 모든 조건을 만족하는 한 계산된 열을 참조하여 인덱스를 만들 수 있습니다. 다른 제한 사항 중에서 계산된 열 식은 결정적이고 정확해야 하며 text, ntext 또는 image 데이터 형식으로 평가되어서는 안됩니다.
결정적
결정적이지 않은 함수는 뷰나 계산된 열에 인덱스를 만들려고 할 때 뷰나 계산된 열을 사용해 호출할 수 없습니다. 함수는 결정적인 함수와 그렇지 않은 함수로 구분됩니다.
예를 들어, DATEADD 기본 제공 함수는 세 가지 입력 매개 변수를 통해 전달되는 일련의 주어진 인수 값에 대해 항상 예측 가능한 값을 반환하기 때문에 결정적 함수입니다. GETDATE는 결정적이지 않은 함수입니다. 왜냐하면 항상 같은 인수 값을 사용하여 호출되지만 반환하는 값은 매번 다릅니다.
정확성
계산된 열 식은 다음과 같은 경우에 정확합니다.
CREATE TABLE t2 (a int, b int, c int, x float, COLUMNPROPERTY 함수의 IsPrecise 특성은 computed_column_expression의 정확성 여부를 보고합니다.
참고 float 식은 정확성이 없는 것으로 간주되고 인덱스의 키가 될 수 없습니다. float 식을 인덱싱된 뷰에 사용할 수는 있지만 키로서 사용되지는 않습니다. 계산된 열에 대해서도 마찬가지입니다. 함수, 식, 사용자 지정 함수 또는 뷰 정의가 논리식(비교)을 비롯해 float 식을 포함하는 경우, 모두 결정적이지 않는 것으로 간주됩니다.
계산된 열이나 뷰에 인덱스를 만들면 이전에 제대로 실행되던 INSERT 또는 UPDATE 작업에서 오류가 발생할 수 있습니다. 이러한 오류는 계산된 열이 산술 오류를 초래할 때 일어날 수 있습니다. 예를 들어, 다음 테이블에서 계산된 열이 산술 오류를 일으키지만 INSERT 문은 제대로 실행됩니다.
CREATE TABLE t1 (a int, b int, c AS a/b) 예에서 테이블을 만들고 나서 계산된 열 c에 인덱스를 만들면 같은 INSERT 문도 실행되지 않습니다.
CREATE TABLE t1 (a int, b int, c AS a/b) 인덱싱된 뷰는 결과가 데이터베이스에 보존되며 빠른 액세스를 위해 인덱싱되는 뷰입니다. 다른 뷰와 마찬가지로 인덱싱된 뷰도 기본 테이블의 데이터를 기초로 합니다. 이러한 종속성은 인덱싱된 뷰와 연관된 기본 테이블을 변경하면 인덱싱된 뷰가 올바르지 않을 수 있다는 것을 의미합니다. 예를 들어, 뷰가 종속된 테이블의 열 이름을 바꾸면 뷰를 더 이상 사용할 수 없습니다. 이러한 문제를 방지하기 위해 SQL Server는 스키마 바인딩을 사용한 뷰 작성을 지원합니다. 스키마 바인딩은 뷰를 무효화하는 모든 테이블이나 열 수정을 금지합니다. SQL Server에서 인덱싱된 뷰는 스키마 바인딩을 가져야 하기 때문에, 디자이너 보기를 사용하여 만든 모든 인덱싱된 뷰에 자동으로 스키마 바인딩이 적용됩니다. 스키마 바인딩이 뷰를 수정할 수 없음을 뜻하지는 않습니다. 다만 기본 테이블이나 뷰를 인덱싱된 뷰의 결과 세트를 변경하는 방식으로는 수정할 수 없다는 것을 의미합니다. 또한 계산된 열에 대한 인덱스와 마찬가지로 인덱싱된 뷰도 결정적이고 정확성이 있어야 하며, text, ntext 또는 image 열을 포함할 수 없습니다.
인덱싱된 뷰는 기초가 되는 데이터가 자주 업데이트되지 않을 경우에 가장 적합합니다. 인덱싱된 뷰의 유지 관리 비용은 테이블 인덱스의 유지 관리 비용보다 높습니다. 인덱싱된 뷰의 상위 데이터가 자주 업데이트되면 뷰 유지 관리 비용이 인덱스화 뷰의 사용으로 얻을 수 있는 성능 상의 이점을 능가할 수 있습니다.
인덱싱된 뷰는 다음과 같은 종류의 쿼리 성능을 개선합니다.
예를 들어, 인벤터리를 기록하는 OLTP 데이터베이스에 Parts, PartSupplier 및 Suppliers 테이블을 조인하는 쿼리가 많이 수행될 것으로 예상되는 경우가 있습니다. 이러한 조인을 수행하는 각 쿼리가 아주 많은 행을 처리하지 않더라도 그러한 쿼리의 전체 조인 수가 수십만 번을 넘으면 문제가 심각해질 수 있습니다. 이런 관계는 자주 업데이트되는 편이 아니므로 조인된 결과를 저장할 인덱싱된 뷰를 정의하여 전체 시스템의 전반적인 성능을 개선할 수 있습니다. 인덱싱된 뷰는 보통 다음과 같은 종류의 쿼리 성능을 개선하지 않습니다.
SELECT PriKey, SUM(SalesCol) 테이블 키의 카디널리티가 100인 경우, 이 쿼리를 사용하여 만든 인덱싱된 뷰에는 100개의 행만 있습니다. 이 뷰를 사용하는 쿼리에 필요한 읽기 작업 수는 평균적으로 기본 테이블에 필요한 작업의 1/10 정도입니다. 고유 키인 경우 키의 카디널리티는 1000이고 뷰 결과 세트는 1000개의 행을 반환합니다. 기본 테이블을 직접 읽는 대신 이 인덱싱된 뷰를 사용하는 것으로 쿼리에 발생하는 성능 상의 이점은 없습니다. 다양한 작업이 만족되도록 인덱싱된 뷰를 설계하십시오. 최적화 프로그램에서 뷰 자체가 FROM 절에 지정되지 않는 경우에도 인덱싱된 뷰를 사용하기 때문에 인덱싱된 뷰를 잘 설계하면 많은 쿼리의 처리 속도가 빨라질 수 있습니다. 예를 들어, 이 뷰에 인덱스를 만들어 보십시오.
CREATE VIEW ExampleView (PriKey, SumColx, CountColx) 이 뷰는 뷰 열을 직접 참조하는 쿼리를 만족시킬 뿐 아니라 기본 테이블을 쿼리하며 SUM(Colx), COUNT_BIG(Colx), COUNT(Colx) 및 AVG(Colx) 등과 같은 식을 포함하는 쿼리에도 사용됩니다. 이러한 모든 쿼리는 기본 테이블의 전체 행을 읽지 않고 뷰에 있는 적은 수의 행만 검색하기 때문에 빠르게 수행됩니다.
뷰에 생성된 첫 인덱스는 고유한 클러스터 인덱스여야 합니다. 고유한 클러스터링된 인덱스를 만든 후 추가로 클러스터링되지 않은 인덱스를 만들 수 있습니다. 명명 규칙은 뷰의 인덱스와 테이블의 인덱스에서 동일합니다. 차이점은 테이블 이름이 뷰 이름으로 바뀐다는 점 뿐입니다.
뷰를 삭제하면 뷰의 모든 인덱스도 삭제됩니다. 클러스터링된 인덱스를 삭제하면 뷰의 모든 클러스터링되지 않은 인덱스도 삭제됩니다. 클러스터링되지 않은 인덱스는 개별적인 삭제도 가능합니다. 뷰에서 클러스터링된 인덱스를 삭제하면 저장 결과 세트가 제거되고 최적화 프로그램이 표준 뷰와 같은 뷰 처리로 돌아갑니다.
클러스터 인덱스를 구성하는 모든 열은 CREATE UNIQUE CLUSTERED INDEX 문에 지정되지만, 뷰의 전체 결과 세트는 데이터베이스에 저장됩니다. 기본 테이블의 클러스터 인덱스에서와 같이 클러스터링된 인덱스의 B-트리 구조에는 키 열만 포함되지만 데이터 행에는 뷰 결과 세트의 모든 행이 포함됩니다.
참고 SQL Server 2000의 모든 버전에서 인덱싱된 뷰를 만들 수 있습니다. SQL Server 2000 Enterprise Edition의 경우, 쿼리 최적화 프로그램에서 인덱싱된 뷰를 자동으로 고려합니다. 기타 모든 버전에서 인덱싱된 뷰를 사용하려면 반드시 NOEXPAND 힌트를 사용하십시오.
포함 인덱스는 선택 기준 및 WHERE 조건자 모두에 있서 SQL 쿼리를 만족하는 데 필요한 모든 열 위에 구축된 클러스터링되지 않은 인덱스를 말합니다. 포함 인덱스는 입/출력의 작업을 크게 줄일 수 있으므로 쿼리 성능을 크게 향상시킬 수 있습니다. 그러나 관련 B-트리 인덱스 구조 유지 관리를 포함하여 새 인덱스를 만드는 비용과 포함 인덱스가 제공할 입/출력 성능 이득 사이에 균형을 유지해야 합니다. 포함 인덱스가 SQL Server에서 자주 실행되는 쿼리나 쿼리 집합에 많은 이익을 준다면 포함 인덱스는 만들 만한 가치가 있습니다.
다음 예에서는 포함 인덱스 삽입 방법을 보여줍니다.
Create index indexname1 on table1(col2,col1,col3). 위의 쿼리를 수행할 때, 축소된 인덱스 페이지를 읽는 것만으로 기본 테이블에서 필요한 값을 검색할 수 있기 때문에 쿼리가 아주 효과적으로 해결됩니다. 일반적으로 해당 테이블에 있는 단일 열의 바이트 수와 비교할 때 포함 인덱스에 있는 모든 열의 바이트 수가 적고 쿼리가 포함 인덱스의 장점을 활용하는 것이 확실한 경우에는 포함 인덱스를 사용하는 것이 좋습니다.
인덱스를 선택한 방법이 생성된 디스크 입/출력 크기 및 그에 따른 성능에 상당한 영향을 미칩니다. 앞 부분에서는 클러스터링되지 않은 인덱스가 왜 적은 수의 행을 검색하는 데 좋으며 클러스터링된 인덱스는 영역 스캔에 좋은지에 대한 이유를 설명했습니다. 다음은 사용할 인덱스의 종류를 선택하는 데 도움이 되는 정보입니다.
인덱스를 만들기 위해 세운 쿼리 계획은 SQL Server Enterprise 및 Developer용 제품을 실행하는 여러 마이크로프로세서 컴퓨터에서 병렬의 복수 스레드 인덱스 작성 작업을 허용합니다.
SQL Server는 기타 Transact-SQL 문에서와 동일한 알고리즘을 사용하여 인덱스 작성 작업에 대한 병렬 정도(실행할 총 별도 스레드 수)를 결정합니다. 유일한 차이점은 인덱스를 만드는 CREATE INDEX, CREATE TABLE 또는 ALTER TABLE 문이 MAXDOP 쿼리 힌트를 지원하지 않는다는 점입니다. 인덱스 작성에서 최대 병렬 정도는 max degree of parallelism 서버 구성 옵션에 따라 결정되며, 개별 인덱스 작성 작업에 대해 다른 MAXDOP 값을 설정할 수 없습니다.
SQL Server가 인덱스 작성 쿼리 계획을 세울 때 병렬 작업의 수는 다음 중 가장 낮은 값으로 설정됩니다.
예를 들어, CPU가 8개인 컴퓨터에서 max degree of parallelism 옵션을 6으로 설정하면 인덱스를 작성할 때 생성되는 병렬 스레드 수가 6개를 넘지 못합니다. 인덱스 작성 계획을 세울 때 컴퓨터에서 5개의 CPU가 SQL Server 작업의 임계값을 초과할 경우, 실행 계획은 세 개의 병렬 스레드만 지정합니다.
병렬 인덱스 작성 과정은 다음과 같습니다.
병렬 스레드를 모두 마친 후, 통합 스레드가 여러 인덱스 하위 단위를 하나의 인덱스에 연결합니다. 각 CREATE TABLE 또는 ALTER TABLE 문은 인덱스 작성을 필요로 하는 여러 제약 조건을 가질 수 있습니다. 각 인덱스 작성 작업이 CPU가 여러 개인 컴퓨터에서는 병렬 작업으로 실행될 수 있지만, 이렇게 여러 인덱스 작성 작업은 연속해서 순차적으로 실행됩니다.
데이터베이스에서 인덱스를 만들 때 쿼리에 사용된 인덱스 정보는 인덱스 페이지에 저장됩니다. 순차 인덱스 페이지들은 포인터를 통해 서로 연결됩니다. 인덱스에 영향을 주는 데이터를 변경할 경우 인덱스의 정보가 데이터베이스에 분산될 수 있습니다. 인덱스를 다시 만들면 인덱스 데이터(그리고 클러스터링된 인덱스인 경우 테이블 데이터)의 저장소가 재구성되어 조각을 제거합니다. 따라서 요청한 데이터를 구하기 위한 필요한 페이지 읽기 횟수가 줄어들어 성능이 개선됩니다.
조각화는 클러스터링된 인덱스의 검색 키 값을 수정하는 삽입 동작이나 업데이트가 아주 많이 수행될 때 발생합니다. 이러한 이유로 인덱스 및 데이터 페이지에 어느 정도의 개방 공간을 유지함으로써 페이지 분할을 막는 것이 중요합니다. 페이지 분리는 페이지에 정의된 데이터의 논리적 순서 때문에 인덱스 페이지나 데이터 페이지가 페이지에 삽입되어야 할 행과 모든 새로운 행을 유지할 수 없을 때 일어납니다. 이러한 경우 SQL Server는 가득 찬 페이지에서 데이터를 나누어 데이터의 약 절반을 새로운 페이지로 옮겨 두 페이지 모두에 어느 정도 개방 공간이 있도록 합니다. 이 작업은 시스템 리소스를 소모하므로 자주 시도하는 것은 바람직하지 않습니다.
초기에 인덱스를 만들 때 SQL Server는 순차 입/출력으로 최적의 인덱스 페이지 스캔 입/출력 성능이 가능한 물리적으로 연속된 페이지에 인덱스 B-트리 구조를 사용합니다. 페이지 분리가 발생하고 새 페이지를 인덱스의 논리 B-트리 구조에 삽입해야 한다면 SQL Server는 새로운 8KB 인덱스 페이지를 할당해야 합니다. 이러한 작업은 하드 드라이브의 어딘가에서 발생하며 인덱스 페이지의 물리적 순차 특성을 해치게 됩니다. 그 결과 입/출력 작업이 순차적 수행에서 비순차적 수행으로 전환될 수 있습니다. 또한 성능도 현저하게 저하될 수 있습니다. 과도한 양의 페이지 분리 문제는 인덱스를 다시 만들어 인덱스 페이지의 물리적 순차 순서를 복원하는 방식으로 해결해야 합니다. 이와 같은 동작이 테이블의 데이터 페이지에 영향을 줄 수 있는 클러스터링된 인덱스의 잎 수준에서 발생할 수 있습니다.
시스템 모니터에서 "SQL Server: Access Methods: - Page Splits/sec"에 특히 주목하십시오. 이 카운터의 값이 0이 아니면 이는 페이지 분할이 일어나고 있으며 DBCC SHOWCDONTIG을 사용하여 추가 분석을 수행해야 한다는 것을 의미합니다.
DBCC SHOWCONTIG 명령으로도 테이블에 과도한 페이지 분할이 발생하는지 여부를 확인할 수 있습니다. 스캔 밀도는 DBCC SHOWCONTIG가 제공하는 주요 지표입니다. 이 값은 가능한 한 100%에 가깝도록 하는 것이 좋습니다. 이 값이 100%보다 지나치게 낮으면 문제가 있는 인덱스에 대한 유지 관리가 필요합니다.
DBCC INDEXDEFRAG
하나의 인덱스 유지 관리 방법은 SQL Server 2000에 새로 추가된 새 명령문(DBCC INDEXDEFRAG)을 사용하는 것입니다. DBCC INDEXDEFRAG는 테이블과 뷰에서 클러스터링된 인덱스와 클러스터링되지 않은 인덱스에 대해 조각 모음을 수행합니다. DBCC INDEXDEFRAG는 잎 수준 인덱스에 대해 조각 모음을 수행하므로 페이지의 물리적 순서가 잎 노드의 왼쪽에서 오른쪽으로 진행하는 순서와 일치하고, 그 결과 인덱스 스캔 성능이 좋아집니다.
DBCC INDEXDEFRAG는 또한 인덱스를 만들 때 지정한 FILLFACTOR를 고려하여 여러 인덱스 페이지를 압축합니다. 압축 결과 생성된 빈 페이지는 모두 제거됩니다.
인덱스가 두 개 이상의 파일에 걸쳐 있는 경우 DBCC INDEXDEFRAG는 한 번에 한 파일의 조각을 모읍니다. 페이지는 파일 사이에서 마이그레이션하지 않습니다. 그리고 DBCC INDEXDEFRAG는 5분마다 예상 완료도를 사용자에게 보고합니다. DBCC INDEXDEFRAG는 언제든지 종료할 수 있으며, 이 때 완료된 작업은 그대로 유지됩니다.
DBCC DBREINDEX(또는 일반적인 인덱스 구축 작업)와 달리 DBCC INDEXDEFRAG는 온라인 작업입니다. 잠금을 오래 유지하지 않으므로 쿼리나 업데이트 실행을 차단하지 않습니다. 조각 모음하는 시간은 조각화 정도에 상대적이기 때문에 비교적 조각화되지 않은 인덱스 조각을 모으는 것이 새 인덱스를 만드는 것보다 빠를 수 있습니다. 조각화가 심한 인덱스의 조각 모음에는 다시 만드는 것보다 훨씬 오랜 시간이 걸릴 수 있습니다. 또한 데이터베이스 복구 모델 설정에 관계없이 조각 모음은 완벽히 기록됩니다(ALTER DATABASE 참조). 너무 많이 조각난 인덱스는 심지어 완전 기록된 인덱스 작성보다 더 긴 로그를 생성할 수도 있습니다. 그러나 조각 모음은 일련의 짧은 트랜잭션 모음으로 실행되므로 로그 백업을 자주 수행하거나 복구 모델 설정이 텍스트인 경우에는 큰 로그가 필요하지 않습니다.
두 개의 인덱스가 디스크에 삽입된 경우에는 INDEXDEFRAG가 페이지를 섞어 제자리에 놓기 때문에 DBCC INDEXDEFRAG가 도움이 되지 않습니다. 페이지 클러스터링을 개선하려면 인덱스를 다시 만드십시오. DBCC INDEXDEFRAG도 같은 이유로 페이지 분할을 수정할 수 없습니다. INDEXDEFRAG는 기본적으로 검색 키의 순차적 순서를 반영하여 이미 할당된 인덱스 페이지의 순서를 재정렬합니다. 인덱스 페이지 순서는 정렬되지 않은 데이터 로드, 지나친 삽입, 업데이트, 삭제 작업 등의 다양한 이유 때문에 뒤섞일 수 있습니다.
SQL Server 온라인 문서에 몇 가지 수정을 통해 다양한 인덱스 유지 관리 작업을 자동화하는 데 사용할 수 있는 편리한 샘플 코드가 제공됩니다. 이 예에는 데이터베이스에서 선언된 임계값 이상으로 조각화된 모든 인덱스 조각을 모으는 간단한 방법이 나와 있습니다. SQL Server 온라인 문서의 "DBCC SHOWCONTIG" 항목에 있는 섹션 E, "DBCCSHOWCONTIG 및 DBCC INDEXDEFRAG를 사용하여 데이터베이스의 인덱스 조각 모음"을 참조하십시오.
DBCC DBREINDEX
DBCC DBREINDEX는 테이블의 모든 인덱스 또는 지정된 하나의 인덱스만(사용한 명령 구문에 따라 결정됨) 다시 만들 수 있습니다. 개별 인덱스 삭제 및 재작성 방법과 유사하게 DBCC DBREINDEX 문도 하나의 명령문으로 테이블의 모든 인덱스를 다시 만들 수 있다는 이점이 있습니다. 이 과정은 개별 DROP INDEX 및 CREATE INDEX 문을 코딩하는 것보다 쉬우며, 테이블 구조나 지정된 제약 조건을 모르고도 테이블의 인덱스를 다시 만들 수 있습니다. DBCC REINDEX 문은 또한 본래 원자성의 특성을 갖습니다. 개별 DROP INDEX 문과 CREATE INDEX 문을 코딩할 때 동등한 원자성을 유지하려면 한 트랜잭션 내에 별도의 명령 여러 개를 자동 줄바꿈하여 사용해야 합니다.
DBCC DBREINDEX는 자동으로 개별 DROP INDEX 및 CREATE INDEX 문보다 더 나은 최적화를 이용하는데, 특히 클러스터링되지 않은 인덱스 여러 개가 한 개의 클러스터링된 인덱스를 갖는 테이블을 참조할 때 그렇습니다. DBCC DBREINDEX는 제약 조건 삭제 후 재작성할 필요 없이 PRIMARY KEY 또는 UNIQUE 제약 조건을 적용시키는 인덱스를 다시 만들 때도 유용합니다. 그 이유는 PRIMARY KEY 또는 UNIQUE 제약 조건을 강제로 적용하기 위해 작성한 인덱스를 삭제하려면 먼저 해당 제약 조건을 삭제해야 하기 때문입니다. 예를 들어, PRIMARY KEY 제약 조건에 대한 인덱스를 다시 만들어 인덱스에 대해 해당 채움 요소를 다시 설정할 수 있습니다.
DROP_EXISTING
인덱스를 다시 만들거나 조각 모음하는 또 다른 방법은 인덱스를 삭제한 후 다시 작성하는 것입니다. 기존의 인덱스를 삭제하고 같은 인덱스를 다시 작성하는 방법으로 클러스터링된 인덱스를 다시 만드는 것은 비용이 많이 드는 작업입니다. 모든 보조 인덱스가 해당 데이터 행을 가리키는 클러스터 키의 영향을 받기 때문입니다. 단순히 클러스터링된 인덱스를 삭제하고 다시 작성할 경우, 참조하는 모든 클러스터링되지 않은 인덱스가 실수로 두 번 삭제되고 재작성될 수 있습니다. 처음 삭제/재작성은 클러스터링된 인덱스를 삭제할 때 일어납니다. 다음 삭제/재작성은 클러스터링된 인덱스를 다시 작성할 때 일어납니다.
이러한 현상을 피하기 위해 CREATE_INDEX 절의 DROP_EXISTING을 사용해 한 번의 단계로 재작성 작업이 수행되도록 할 수 있습니다. 한 단계 작업으로 인덱스를 다시 작성하면 SQL Server에서 기존의 인덱스를 재구성하고 연관된 클러스터링되지 않은 인덱스에 대한 불필요한 삭제 및 재작성 작업을 피할 수 있습니다. 이 방법은 기존의 인덱스에서 미리 정의된 데이터를 사용할 수 있다는 이점이 있으므로 데이터를 정렬할 필요가 없어집니다. 그 결과 클러스터링된 인덱스를 다시 만드는 시간과 비용을 상당히 줄일 수 있습니다.
DROP INDEX / CREATE INDEX
인덱스 유지 관리를 수행하는 마지막 방법은 단순히 인덱스를 삭제하고 다시 작성하는 것입니다. 이 옵션은 여전히 널리 사용되며, 익숙한 사용자나 테이블에 있는 모든 인덱스의 완전한 재작성을 처리할 능력이 사용자가 즐겨 사용하는 방법입니다. 이 방법의 단점은 이벤트를 올바른 순서로 발생하도록 수동으로 제어해야 한다는 점입니다. 인덱스를 수동으로 삭제 후 재작성할 때, 클러스터링되지 않은 인덱스가 모두 삭제되었는지 확인한 다음에 클러스터링된 인덱스를 삭제하고 다시 작성하십시오. 그렇지 않으면 클러스터링된 인덱스를 작성하려고 할 때 자동으로 클러스터링되지 않은 인덱스가 다시 작성됩니다.
수동으로 클러스터링되지 않은 인덱스를 만들 때의 이점은 클러스터링되지 않은 인덱스들을 각각 병렬로 재작성할 수 있다는 점입니다. 그러나 분할 전략은 결과적인 인덱스의 실제 레이아웃에 영향을 줄 수 있습니다. 클러스터링되지 않은 인덱스 두 개를 동시에 같은 파일(파일 그룹)에 다시 만드는 경우, 디스크에서 두 인덱스의 페이지가 서로 뒤섞일 수 있습니다. 그러면 데이터가 비순차적 순서로 저장될 수 있습니다. 여러 파일(파일 그룹)이 여러 개의 디스크에 있는 경우, 별도 파일(파일 그룹)을 지정하여 작성 시 인덱스를 보유함으로써 인덱스 페이지의 순차적 인접성을 유지할 수 있습니다.
미리 정의된 데이터에 대한 인덱스 구축에서 설명한 것과 같은 경고가 여기에도 적용됩니다. 정렬된 데이터에 대해 만든 클러스터링된 인덱스는 추가 정렬 단계를 수행할 필요가 없기 때문에 인덱스 구축에 필요한 시간과 처리 리소스를 상당히 절약할 수 있습니다.
FILLFACTOR and PAD_INDEX
FILLFACTOR 옵션은 색인 및 데이터 페이지에 남길 개방 공간 백분율을 지정하는 방법을 제공합니다. CREATE INDEX 메뉴의 PAD_INDEX 옵션은 비잎 수준 인덱스 페이지에서 FILLFACTOR 옵션으로 지정한 값을 적용합니다. PAD_INDEX 옵션을 사용하지 않으면 FILLFACTOR는 주로 클러스터링된 인덱스의 잎 수준 인덱스 페이지에만 영향을 미칩니다. FILLFACTROR와 함께 PAD_INDEX 옵션을 사용하는 것이 좋습니다.
PAD_INDEX 및 FILLFACTOR 옵션은 페이지 분할을 제어하는 데 사용됩니다. FILLFACTOR에 지정할 최적의 값은 해당 시간 범위 내에 새로운 데이터가 얼마나 8KB 인덱스와 데이터 페이지에 삽입될 것인가에 따라 달라집니다. 인덱스 페이지에는 단지 이 인덱스에 관련된 열에 대한 데이터만 포함되어 있는 반면, 데이터 페이지에는 전체 행에 대한 데이터가 포함되어 있으므로 SQL Server 인덱스 페이지에 보통 더 많은 열이 포함되어 있음을 염두에 두어야 합니다. 또한 페이지 분할을 방지하기 위해 인덱스를 재구축할 수 있는 유지 관리 창을 사용할 빈도도 염두에 두어야 합니다. 다수의 인덱스 및 데이터 페이지가 데이터로 채워지는 경우에만 인덱스를 다시 만들도록 하십시오. 테이블에 적합한 클러스터링된 인덱스를 선택하면 인덱스를 다시 만들 필요가 거의 없습니다. 클러스터링된 인덱스가 데이터를 균등하게 분산시켜 테이블에 삽입하는 새로운 열이 테이블과 연관된 모든 데이터 페이지 전체에 걸쳐 발생하면 데이터 페이지의 양이 균등해질 것입니다. 전반적으로, 페이지 분할이 일어나고 클러스터링된 인덱스의 재구축이 필요하게 되기까지 더 많은 시간을 가지게 됩니다.
PAD_INDEX 및 FILLFACTOR에 사용할 올바른 값을 결정하려면 필요한 정보를 문의해야 합니다. 페이지에 많은 양의 개방 공간을 두는 것과 일어날 수 있는 페이지 분할 크기 사이에서 최적의 성능을 유지하기 위한 절충점을 기준으로 결정해야 합니다. FILLFACTOR의 백분율 값을 너무 작게 지정하면 인덱스 및 데이터 페이지에 개방 공간이 너무 많아지기 때문에 SQL Server가 쿼리에 응답하기 위해 읽어야 하는 부분적으로 채워진 페이지 수가 너무 많아집니다. 읽기 작업량이 많은 경우, 당연히 인덱스 및 데이터 페이지에 압축된 데이터가 많을수록 SQL Server가 읽기 작업을 더 빨리 수행합니다. FILLFACTOR를 너무 높게 지정하면 페이지의 개방 공간이 너무 작아지므로 페이지가 더 빠르게 채워져 페이지 분할의 원인이 됩니다.
많은 데이터 웨어하우스 환경에서 FILLFACTOR 또는 PAD_INDEX 값에 도달하기 전 읽기 작업 수는 쓰기 작업 수를 훨씬 능가하는 경향이 있음에 유의하십시오. 그러나 주기적으로 데이터를 로드할 경우에는 달라질 수 있습니다. 많은 데이터 웨어하우스 관리자가 예상되는 주기적 데이터 로드를 수용하기 위해 테이블/인덱스의 분할과 구조화를 시도합니다.
경험에 비추어 볼 때 쓰기 작업이 읽기의 상당한 부분일 것으로 예상되는 경우, 최상의 접근 방법은 FILLFACTOR를 합당한 최대 값으로 지정하는 동시에 각 8KB 페이지에 빈 공간을 충분히 유지하여 잦은 페이지 분할을 피하는 것입니다. 적어도 SQL Server가 인덱스 재작성에 필요한 다음 번 사용 가능한 시간에 도달할 수 있을 때까지 페이지 분할이 되지 않도록 합니다. 이 방법은 입/출력 성능(가능한 한 페이지 성능을 최대로 유지)의 균형을 조정하고 페이지 분할을 방지(페이지 오버플로 허용 안함)합니다. SQL Server 데이터베이스로 쓰기 작업이 전혀 없는 상황에서는 FILLFACTOR를 100%로 설정하여 인덱스와 데이터 페이지가 모두 완전히 채워져서 최대의 입/출력 성능을 내도록 해야 합니다.
여기서는 데이터가 있는 테이블을 로드한 다음 SQL 프로필러 및 SQL 쿼리 분석기를 사용하여 성능을 분석 및 조정하는 예제 코드를 살펴봅니다.
SQL Server 성능 도구 사용법을 설명하기 위해 다음과 같은 예를 사용합니다. 첫째, 아래와 같이 테이블을 만듭니다.
create table testtable 다음 테이블에 테스트 데이터 20,000행을 넣습니다. nkey1 열에 들어가는 데이터는 클러스터링되지 않은 인덱스가 됩니다. ckey1 열의 데이터는 클러스터링된 인덱스가 되며 col2의 데이터는 단순히 각 열의 크기를 300바이트 늘리기 위해 채워지는 것입니다.
declare @counter int 아래 쿼리는 데이터베이스 서버 작업 로드를 만듭니다.
select ckey1 from testtable where ckey1 = 'a' 일반적인 성능 조정 접근 방식 중 하나로 표시 및 측정(Mark and Measure)이라는 것이 있습니다. 성능 향상을 위해 수행한 변경 내용이 실제로 성능을 개선하는지 확인하기 위해서는 먼저 기존의 저조한 성능에 대한 기준선 또는 표시를 설정해야 합니다. 측정이란 성능이 개선됨을 양적으로 증명할 방법을 설정하는 것을 뜻합니다.
SQL 프로필러는 편리한 표시 및 측정 도구로, 이 도구는 성능 분석을 위해 서버 내에서 발생하는 동작을 캡처할 수 있을 뿐 아니라 나중에 동작을 다시 재생할 수도 있습니다. SQL Server의 재생 기능은 유용한 회귀 테스트 도구를 제공합니다. 성능 개선을 위해 진행될 동작이 원하는 효과를 갖는지 여부는 재생을 통해 편리하게 결정할 수 있습니다.
재생 기능은 로드 또는 스트레스 테스트를 시뮬레이션할 수도 있습니다. 여러 프로필러 클라이언트 세션이 동시에 재생되도록 설정할 수 있습니다. 이 기능을 통해 관리자는 한 예로 5명의 동시 사용자로부터 쉽게 동작을 캡처한 다음 동시 재생을 시작하여 동시 사용자가 50명이 될 경우의 예상 시스템 성능을 에뮬레이션할 수 있습니다. 또한 데이터베이스 작업 중 일부를 개발 중인 데이터베이스 수정이나 테스트할 새 하드웨어 구성에 대해 다시 재현할 수 있습니다.
명심할 점은 프로필러를 사용할 경우 SQL Server 데이터베이스에서 일어나는 동작을 기록할 수 있다는 점입니다. SQL Server에 대한 쿼리를 실행하는 하나 이상의 사용자를 계속 관찰하고 기록하도록 프로필러를 구성할 수 있습니다. SQL 문 외에도 도구를 사용한 캡처에 광범위하고 다양한 성능 정보가 이용됩니다. 프로필러를 사용하여 기록할 수 있는 성능 정보에는 입/출력 통계, CPU 통계, 잠금 요청, Transact-SQL 및 RPC 통계, 인덱스 및 테이블 스캔, 제기된 경고 및 오류, 데이터베이스 개체 만들기/삭제, 연결/연결 해제, 저장된 프로시저 작업, 커서 작업 등이 있습니다.
인덱스 튜닝 마법사와 함께 사용할 프로필러 정보 캡처
SQL 프로필러와 인덱스 튜닝 마법사를 함께 사용할 경우, 데이터베이스 관리자가 테이블 및 뷰에 적절한 인덱스를 구성하는 데 도움이 되는 매우 강력한 도구 조합이 제공됩니다. SQL 프로필러는 쿼리에 의한 리소스 사용 정보를 세 위치 중 한 곳에 기록합니다. 출력은 .trc 파일, SQL Server 테이블 또는 모니터 중 한 곳으로 보내질 수 있습니다. 그러면 인덱스 튜닝 마법사가 캡처된 데이터를 .trc 파일이나 SQL Server 테이블에서 읽어올 수 있습니다. 인덱스 튜닝 마법사는 캡처된 작업 로드의 정보와 테이블 구조에 대한 정보를 분석한 다음, 성능 개선을 위해 어떤 종류의 인덱스를 만들어야 하는지에 대한 권장 사항을 제시합니다. 인덱스 튜닝 마법사에서는 데이터베이스에 대한 적합한 인덱스를 자동으로 만들거나, 나중에 자동으로 인덱스를 만들도록 일정을 잡거나, 나중에 검토하고 실행할 수 있는 Transact-SQL 스크립트를 생성할 수 있는 선택 사항을 제공합니다.
쿼리 로드를 분석하려면 다음 절차를 따라야 합니다.
프로필러 설정
여러 번(3-4회) 작업 로드 실행
select ckey1 from testtable where ckey1 = 'a' SQL 프로필러 중지
인덱스 튜닝 마법사로 추적 파일 또는 테이블 로드
인덱스 튜닝 마법사에서 생성한 데이터베이스 예제 및 작업 로드용 Transact-SQL
/* Created by: Index Tuning Wizard */ 인덱스 튜닝 마법사에서 예제 테이블 및 데이터로 권장하는 인덱스는 ckey1에 클러스터링된 인덱스와 nkey1에 클러스터링되지 않은 인덱스로 예상하는 것입니다. ckey1에는 단지 다섯 개의 고유한 값이 있으며 각 값에는 4000개의 행이 있습니다. 예제 쿼리 중 하나(select ckey1, col2 from testtable where ckey = 'a')가 ckey1의 값들 중 하나를 기준으로 한 테이블에서의 검색을 요구하는 경우, ckey1 열에 클러스터링된 인덱스를 만드는 것이 좋습니다. 둘째 쿼리(select nkey1, col2 from testtable where nkey1 = 5000)는 nkey1 열의 값을 기준으로 하나의 행을 반입합니다. nkey1은 고유하며 20,000개의 행이 있으므로 이 열에는 클러스터링되지 않은 인덱스를 만드는 것이 좋습니다.
SQL 프로필러와 인덱스 튜닝 마법사의 결합은 많은 테이블이 사용되고 또 많은 쿼리가 처리되는 실제 데이터베이스 서버 환경에서 매우 강력한 기능을 제공합니다. 데이터베이스 서버에 대표적인 쿼리 집합이 발생하는 경우에는 프로필러를 사용하여 .trc 파일을 기록하거나 테이블을 추적합니다. 그런 다음 추적을 인덱스 튜닝 마법사에 로드하여 구축할 적절한 인덱스를 결정합니다. 인덱스 튜닝 마법사의 안내에 따라 자동으로 인덱스를 생성하고 인덱스 작성 작업이 바쁘지 않은 시간에 수행되도록 일정을 잡습니다. SQL 프로필러 및 인덱스 튜닝 마법사의 결합을 정기적으로(예: 매주 또는 매달) 실행하여 데이터베이스 서버에서 실행 중인 쿼리가 상당 부분 변경되고 다른 인덱스가 필요한지 여부를 확인하는 것도 좋습니다. SQL 프로필러/인덱스 튜닝 마법사를 정기적으로 사용하면 시간이 지나면서 쿼리 작업 로드가 변하고 데이터베이스의 크기가 증가함에 따라 데이터베이스 관리자가 SQL Server를 최적의 상태로 유지하는 데 도움이 됩니다.
쿼리 분석기로 프로필러에 기록된 정보 분석
정보가 SQL Server 테이블에 기록되면 쿼리 분석기를 사용하여 시스템의 어떤 쿼리가 가장 많은 리소스를 소비하는지 알 수 있습니다. 이러한 방식으로 데이터베이스 관리자는 가장 도움이 많이 필요한 쿼리를 개선하는 데 집중할 수 있습니다. 추적 데이터를 테이블에 저장하면 추적 데이터의 하위 집합을 쉽게 선택 및 필터링할 수 있으므로 최악의 성능을 보이는 쿼리를 쉽게 식별할 수 있습니다. 예를 들어, 위 예에서 SQLProfiler Tuning 템플릿을 사용할 때 자동으로 캡처되는 기간 열을 이용하여 실행에 가장 많은 시간이 걸린(밀리초 값이 가장 큰) 쿼리를 식별할 수 있습니다. 실행 시간이 상위 10%에 해당하는 쿼리를 찾으려면 다음과 같은 쿼리를 실행하십시오.
SELECT TOP 10 PERCENT * 실행 시간 순서에서 상위 5개의 쿼리를 찾으려면 다음과 같은 쿼리를 실행하십시오.
SELECT TOP 5 * 조정에 사용할 행만 별도의 테이블에 넣으려면 다음 SELECT/INTO 문을 사용하십시오.
SELECT TOP 10 PERCENT * 위에서 설명한 SQLProfiler Tuning 템플릿은 단순히 성능 조정 목적으로 권장하는 사전 선택된 열 및 필터 설정 집합입니다. 추가 정보를 캡처해야 할 수도 있을 것입니다. 간단히 기본으로 제공되는 템플릿을 열어서 다른 이름으로 저장한 뒤 사용자 지정 조정 템플릿을 만들 수도 있습니다. 그리고 입/출력 통계, 잠금 정보 등을 포함한 많은 이벤트를 캡처할 수 있습니다.
SQL 쿼리 분석기를 사용하여 쿼리를 조정할 수 있습니다. SQL 쿼리 분석기는 통계 입/출력이나 쿼리의 문제점 해결에 사용할 수 있는 실행 계획과 같은 여러 가지 메커니즘을 제공하는 도구입니다.
통계 입/출력
SQL 쿼리 분석기는 쿼리 분석기에서 실행되는 쿼리의 입/출력 소모량에 대한 정보를 구하는 데 사용할 수 있는 옵션을 제공합니다. 이 옵션을 설정하려면, 쿼리 분석기의 쿼리 메뉴에서 현재 연결 속성을 선택하여 현재 연결 속성 대화 상자를 엽니다. 그리고 Set statistics IO 확인란을 선택한 다음 대화 상자를 닫습니다. 다음에 쿼리를 실행하고 결과 창에서 메시지 탭을 선택하여 입/출력 통계를 검토합니다.
예를 들어, 앞의 SQL 프로필러 절에서 작성된 예제 데이터에 대해 다음 쿼리를 실행할 경우, Set statistics IO 옵션이 선택되어 있으면 메시지 탭에 다음과 같은 입/출력 정보가 표시됩니다.
select ckey1, col2 from testtable where ckey1 = 'a' 통계 입/출력을 사용하는 것은 쿼리 조정 효과를 모니터하는 데 아주 좋은 방법입니다. 예를 들면 인덱스 튜닝 마법사가 예제 데이터로 권장하는 인덱스를 만든 다음 쿼리를 다시 실행하십시오.
select ckey1, col2 from testtable where ckey1 = 'a' 인덱스를 사용할 수 있으면 논리적 및 물리적 읽기 횟수가 현저하게 줄어든다는 점을 잊지 마십시오.
실행 계획
그래픽 방식 실행 계획은 쿼리 최적화 프로그램이 수행하는 작업에 대한 정보를 자세히 표시하여 문제가 있는 SQL 쿼리에 주의를 집중하는 데 사용할 수 있습니다.
쿼리에 대해 평가한 실행 계획을 쿼리 분석기의 결과 창에 표시할 수 있습니다. CTRL+L을 눌러 SQL 쿼리를 실행하거나 쿼리 메뉴에서 예상 실행 계획 표시를 선택하면 됩니다. 아이콘은 쿼리 최적화 프로그램이 쿼리를 실행한 경우 수행했을 작업을 표시합니다. 화살표는 쿼리에 대한 데이터 흐름 방향을 표시합니다. 마우스 포인터를 작업 아이콘 위에 놓으면 각 작업에 대한 자세한 정보가 표시됩니다. 각 작업 단계에 대한 대략적 비용도 해당 작업 아이콘 아래 표시됩니다. 이 레이블을 통해 쿼리에서 가장 비용이 많이 드는 작업을 쉽게 구분하여 그 작업에 집중할 수 있습니다.
또한 쿼리 메뉴에서 실행 계획 표시를 선택하고 쿼리를 실행하는 방식으로 쿼리에 대한 실제 실행 계획도 볼 수 있습니다. 예상 실행 계획 표시 옵션과 반대로 실행 계획 표시 옵션은 쿼리에 사용된 실제 실행 계획을 표시하기 전에 쿼리를 실행합니다.
쿼리 메뉴에서 현재 연결 속성을 선택한 다음, 표시되는 대화 상자에서 Set showplan_text 옵션을 선택하여 실행 계획의 텍스트 버전을 생성할 수 있습니다. 그러면 쿼리를 실행할 때 결과 탭에 실행 계획이 텍스트 형식으로 표시됩니다.
다음 명령 중 하나를 실행하면 쿼리 내에서도 실행 계획 옵션은 설정할 수 있습니다.
set showplan_all on SET SHOWPLAN_ALL 명령은 출력을 읽도록 개발된 응용 프로그램에서 사용하기 위한 것입니다. osql 유틸리티와 같은 Microsoft MS-DOS® 응용 프로그램용으로 읽기 가능한 출력을 작성하려면 SET SHOWPLAN_TEXT 명령을 사용하십시오.
SET SHOWPLAN_TEXT 및 SET SHOWPLAN_ALL은 계층 구조 트리를 형성하는 텍스트 행 세트로 정보를 반환하며, 이러한 트리에는 SQL Server 쿼리 프로세서가 각 명령문을 실행하면서 수행하는 단계가 표시됩니다. 출력에 반영된 각 내용에는 명령문 텍스트가 있는 하나의 행과 여러 실행 단계에 대한 자세한 정보가 있는 여러 개의 행이 들어 있습니다.
실행 계획 출력 예
앞에서 정의한 쿼리 예제와 쿼리 분석기에서 실행한 "set shoplan_text on"을 사용합니다.
쿼리 1
select ckey1,col2 from testtable where ckey1 = 'a'
텍스트 형식의 실행 계획 출력
|--Clustered Index Seek (OBJECT:([TraceDB].[dbo].[testtable].[testtable1]), SEEK:([testtable].[ckey1]='a') ORDERED FORWARD)
등가의 그래픽 형태 실행 계획 출력
다음 그림에서는 쿼리 1에 대한 그래픽 실행 계획을 보여줍니다.
실행 계획은 ckey1 열의 클러스터링된 인덱스를 이용하여 Clustered Index Seek에 따라 쿼리를 해결합니다.
테이블에서 클러스터링된 인덱스를 제거한 다음 같은 쿼리를 다시 실행하면 쿼리가 테이블 스캔을 사용하는 상태로 돌아갑니다. 다음 그래픽 실행 계획은 동작 변경을 지시합니다.
텍스트 형식의 실행 계획 출력
|--Table Scan(OBJECT:([TraceDB].[dbo].[testtable]), WHERE:([testtable].[ckey1]=[@1]))
등가의 그래픽 형태 실행 계획 출력
다음 그림에서는 쿼리 1에 대한 그래픽 실행 계획을 보여줍니다.
이 실행 계획을 테이블 스캔을 사용하여 쿼리 1을 해결합니다. 테이블 스캔은 작은 테이블에서 정보를 검색하는 데 가장 효과적인 방법입니다. 그러나 큰 테이블에서, 실행 계획에 나타난 테이블 스캔은 테이블에 보다 적절한 인덱스가 필요할 수 있다는 경고이거나 기존 인덱스 통계의 업데이트가 필요하다는 경고입니다. 통계는 UPDATE STATISTICS 명령을 사용하여 테이블 또는 인덱스에서 업데이트될 수 있습니다. 발견하는 페이지가 기본이 되는 인덱스 값과 동기화에서 너무 멀어지면 SQL Server가 인덱스를 자동으로 업데이트합니다. 한 가지 예로 ckey1 value = "b"를 포함하는 모든 열을 testtable에서 삭제한 다음 통계의 첫 업데이트 없이 쿼리를 실행하는 경우를 들 수 있습니다. SQL Server가 인덱스 통계를 자동으로 유지 관리하도록 하는 것이 좋은데, 이를 통해 쿼리가 항상 양호한 인덱스 통계를 사용할 수 있기 때문입니다. ALTER DATABASE 문을 사용하여 AUTO_UPDATE_STATISTICS 데이터베이스 옵션을 OFF로 설정한 경우에는 SQL Server가 통계를 자동으로 업데이트하지 않습니다.
쿼리 2
select nkey1,col2 from testtable where nkey1 = 5000
텍스트 형식의 실행 계획 출력
--Bookmark Lookup(BOOKMARK:([Bmk1000]), 등가의 그래픽 형태 실행 계획 출력
다음 그림에서는 쿼리 2에 대한 그래픽 실행 계획을 보여줍니다.
쿼리 2의 실행 계획에서는 nkey1 열의 클러스터링되지 않은 인덱스를 사용합니다. 이는 nkey1 열에 대한 "Index Seek" 작업으로 표시됩니다. "Bookmark Lookup" 작업은 SQL Server가 인덱스 페이지에서 데이터 페이지로 포인터 점프하여 요청된 데이터를 검색해야 했다는 사실을 나타냅니다. 쿼리가 col2 열을 요청했으며 이는 클러스터링되지 않은 인덱스에 포함된 열이 아니기 때문에 포인터 점프가 필요했습니다.
쿼리 3
select nkey1 from testtable where nkey1 = 5000
텍스트 형식의 실행 계획 출력
|--Index Seek(OBJECT:([TraceDB].[dbo].[testtable].[testtable2]), SEEK:([testtable].[nkey1]=Convert([@1])) ORDERED FORWARD)
등가의 그래픽 형태 실행 계획 출력
다음 그림에서는 쿼리 3에 대한 그래픽 실행 계획을 보여줍니다.
쿼리 3의 실행 계획은 nkey1에서 클러스터링되지 않은 인덱스를 포함 인덱스로 사용합니다. 이 쿼리에는 "Bookmark Lookup" 작업이 필요했다는 점에 주목하십시오. 이는 쿼리에 필요한 모든 정보(SELECT 및 WHERE 절 모두)가 클러스터링되지 않은 인덱스에 의해 제공되기 때문입니다. 다시 말하면, 데이터 페이지로의 포인터 점프가 클러스터링되지 않은 페이지에서 필요하지 않았다는 의미입니다. 책갈피 조회가 필요한 경우에 비해 입/출력은 줄어듭니다.
시스템 모니터(Windows NT 4에서는 성능 모니터)는 데이터베이스 서버 실행 도중 일어나는 Windows 및 SQL Server 작업에 대한 풍부한 정보를 제공합니다.
시스템 모니터의 그래프 모드에서는 최소 및 최대 값에 주목하십시오. 평균값은 그다지 중요하게 생각하지 마십시오. 과도하게 극화된 데이터 포인터에서는 평균값이 왜곡될 수 있기 때문에 평균값에 지나치게 의미를 주지 않도록 주의해야 합니다. 그래프 모양을 조사하고 최소 및 최대를 비교하여 동작을 정확히 파악하십시오. BACKSPACE 키를 사용하여 카운터를 흰선으로 강조 표시합니다.
시스템 모니터를 대화식으로 살펴 보면서 동시에 시스템 모니터를 사용하여 사용 가능한 모든 Windows 및 SQL Server 시스템 모니터 개체/카운터를 로그 파일에 기록할 수 있습니다. 샘플링 간격 설정에 따라 로그 파일 크기가 증가하는 정도가 결정됩니다. 로그 파일은 매우 빠른 속도로 상당히 커질 수 있습니다. 예를 들어, 모든 카운터를 사용하고 샘플링 간격이 15초인 경우 한 시간만에 100MB가 될 수 있습니다. 테스트 서버에 이러한 파일 형식을 저장하기에 충분한 사용 가능한 공간(GB)이 있는 것이 좋습니다. 그러나 공간을 절약하는 것이 중요하다면 시스템 모니터가 너무 자주 시스템을 샘플링하지 않도록 긴 로그 간격으로 실행해 봅니다. 30초나 60초를 시도해 봅니다. 이렇게 하면 모든 카운터가 적당한 빈도로 다시 샘플링되지만 로그 파일의 크기는 작게 유지됩니다.
시스템 모니터는 또한 적은 양의 CPU와 디스크 입/출력 리소스를 소비합니다. 시스템에 여유 디스크 입/출력 및 CPU가 많지 않으면 다른 시스템에서 시스템 모니터를 실행하여 네트워크 상에서 SQL Server를 모니터하는 것도 하나의 방법일 수 있습니다. 네트워크 상에서 모니터할 때는 그래프 모드만 사용하십시오. 그래프 모드는 LAN을 통해 정보를 보내는 대신 SQL Server에 로컬로 성능 모니터링 정보를 기록하는 데 있어 더 효율적입니다. 네트워크 상에서 기록을 피할 수 없는 경우 가장 중요한 카운터만으로 기록 범위를 줄이십시오.
성능 테스트 실행 중에 사용할 수 있는 모든 카운터를 나중에 분석할 수 있도록 파일에 로그하는 것이 좋습니다. 이러한 방법으로 나중에 모든 카운터를 자세히 검토할 수 있습니다. 시스템 모니터가 로그 파일에 모든 카운터를 로그하고 동시에 가장 관심 있는 카운터를 그래프 모드와 같은 다른 모드 중 하나에 모니터링하도록 구성합니다. 이러한 방법으로 성능 실행을 하면서 모든 정보를 기록하고 가장 관심 있는 카운터를 정리된 시스템 모니터 그래프로 표시합니다.
기록할 시스템 모니터 세션 설정
참고 로그 파일에 대한 카운터 설정을 저장하려면, 세부 정보 창에서 해당 파일을 마우스 오른쪽 단추로 누른 다음 다른 이름으로 설정 저장을 누릅니다. 그런 다음 설정을 저장할 .htm 파일을 지정할 수 있습니다. 저장한 설정을 새 로그에 다시 사용하려면, 세부 정보 창을 마우스 오른쪽 단추로 누르고 새 로그 설정(기존 구성)을 누르십시오. 기록된 모니터링 세션 시작
기록된 모니터링 세션 중지
분석을 위해 로그된 모니터링 세션에서 시스템 모니터로 데이터 로드
시스템 모니터 로그 이벤트를 적절한 시간 지점으로 연결
여러 가지 성능 카운터가 다양한 영역에 대한 정보를 제공하는데, 여기에는 메모리, 페이징, 프로세서, 입/출력 및 디스크 활동 등이 포함됩니다.
메모리 모니터
기본적으로 SQL Server는 사용 가능한 시스템 리소스에 따라 동적으로 메모리 요구 사항을 바꿉니다. SQL Server에 더 많은 메모리가 필요하면 운영 체제를 쿼리하여 빈 실제 메모리의 사용 가능성을 판단한 후 사용 가능한 메모리를 사용합니다. SQL Server에 현재 할당된 메모리가 필요하지 않으면 운영 체제가 사용할 수 있도록 메모리 점유를 해제합니다. 그러나 메모리를 동적으로 사용하는 옵션보다 min server memory, max server memory 및 작업 집합 크기 설정 서버 구성 옵션이 우선 적용됩니다. 자세한 내용은 SQL Server 온라인 문서를 참조하십시오.
SQL Server에 의해 사용되는 메모리 크기를 모니터하려면 다음과 같은 성능 카운터를 검토하십시오.
Working Set 카운터는 프로세스에 사용된 메모리 크기를 표시합니다. 이 값이 지속적으로 SQL Server 용량으로 구성된 메모리(mix server memory 및 max server memory 서버 옵션으로 지정) 크기 미만으로 유지되면 SQL Server에 필요한 크기 이상으로 메모리를 구성한 것입니다. 반대 현상이 일어나면 작업 집합 크기 설정 서버 옵션을 사용하여 작업 세트 크기를 조정하십시오.
Buffer Cache Hit Ratio 카운터는 응용 프로그램별로 고유하지만 비율을 90% 이상으로 유지하는 것이 좋습니다. 값이 꾸준히 90% 이상으로 유지될 때까지 메모리를 더 추가하십시오. 이는 모든 데이터 요청의 90% 이상이 데이터 캐시에서 만족되는 설정입니다.
Server Memory (KB) 카운터가 컴퓨터의 실제 메모리 크기와 비교해 항상 높게 유지되면 추가 메모리가 필요한 것일 수 있습니다.
Hard Paging
Memory: Pages/sec가 0보다 크거나 Memory: Page Reads/sec가 5보다 큰 경우 Windows는 디스크를 사용하여 메모리 참조를 해결합니다(하드 페이지 결함). 이 과정에서 디스크 입/출력 및 CPU 리소스를 낭비합니다. Memory: Pages/sec는 Windows가 수행하는 페이징의 크기와 데이터베이스 서버의 현재 RAM 구성이 적절한지 보여주는 좋은 지표입니다. 시스템 모니터의 하드 페이징 정보 중 한 가지 하위 집합은 Windows가 메모리 참조를 해결하기 위하여 1초에 페이징 파일에서 읽어야 하는 횟수로 이는 Memory: Page Reads/sec로 표현됩니다. Memory: Pages Reads/sec가 5이면 성능에 나쁜 영향을 미칩니다.
자동 SQL Server 메모리 조정에서는 SQL Server 메모리 활용률의 동적 조정을 시도하여 페이징을 방지합니다. 초당 페이지 읽기 횟수가 적은 것은 정상이나 페이징이 과도하면 교정 작업이 필요합니다.
SQL Server가 메모리를 자동으로 조정할 경우, Memory: Pages/sec를 합당한 수준으로 조정하는 데 RAM 추가 또는 데이터베이스 서버에서 기타 응용 프로그램 제거 옵션을 사용할 수 있습니다.
SQL Server 메모리를 데이터베이스 서버에서 수동으로 구성하는 경우, SQL Server에 주어진 메모리를 줄이고 데이터베이스 서버에서 다른 응용 프로그램을 제거하거나 데이터베이스 서버에 RAM을 추가해야 할 수도 있습니다.
Memory: Memory:Pages/sec를 Windows 페이징과 연관된 전체 드라이브에 걸친 Logical Disk: Disk Reads/sec와 비교하고, Memory: Page Output/sec를 Windows 페이징과 연관된 전체 드라이브에 걸친 Logical Disk: Disk Writes/sec과 비교해 보는 것도 유용한 방법입니다. 그 이유는 이러한 비교를 통해 기타 응용 프로그램(즉, SQL Server)이 아닌 순수한 페이징 관련 디스크 입/출력 크기를 측정할 수 있기 때문입니다. 페이징 파일 입/출력 작업을 쉽게 분리할 수 있는 또 다른 방법은 페이징 파일을 다른 모든 SQL Server 파일과 분리된 별도의 드라이브 세트에 배치하는 것입니다. SQL Server 파일에서 페이징 파일을 분리하면 페이징과 연관된 디스크 입/출력이 SQL Server와 연관된 디스크 입/출력과 병렬로 수행될 수 있으므로 디스크 입/출력 성능에 도움이 될 수 있습니다.
Memory: Pages Input/sec 를 Windows 페이징과 연관된 전체 드라이브에 걸친 Logical Disk: Disk Reads/sec와 비교하고, Memory: Page Output/sec를 Windows 페이징과 연관된 전체 드라이브에 걸친 Logical Disk: Disk Writes/sec과 비교해 보는 것도 유용한 방법입니다. 그 이유는 이러한 비교를 통해 기타 응용 프로그램(즉, SQL Server)이 아닌 순수한 페이징 관련 디스크 입/출력 크기를 측정할 수 있기 때문입니다. 페이징 파일 입/출력 작업을 쉽게 분리할 수 있는 또 다른 방법은 페이징 파일을 다른 모든 SQL Server 파일과 분리된 별도의 드라이브 세트에 배치하는 것입니다. SQL Server 파일에서 페이징 파일을 분리하면 페이징과 연관된 디스크 입/출력이 SQL Server와 연관된 디스크 입/출력과 병렬로 수행될 수 있으므로 디스크 입/출력 성능에 도움이 될 수 있습니다.
소프트 페이징
Memory: Pages Faults/sec 가 0보다 크면 Windows가 페이징을 하지만 하드 및 소프트 페이징 모두가 카운터 내에 포함되게 됩니다. 앞 절에서는 하드 페이징에 대해 설명했습니다. 소프트 페이징이란 데이터베이스 서버의 응용프로그램은 아직 RAM 내에 있지만 Windows Working Set 외부에 있는 메모리 페이지를 요청한다는 의미입니다. Memory: Page Faults/sec는 소프트 페이징 발생 크기를 추정하는 데 도움이 됩니다. Soft Faults/sec라는 카운터는 없습니다. 그 대신 다음 계산을 통해 초당 발생하는 소프트 결함 수를 계산하십시오: Memory: Page Faults/sec - Memory: Pages Faults/sec = Soft Page Fault/sec
다른 프로세스가 아닌 SQL Server가 과도한 페이징을 유발하는지 판단하려면 SQL Server 프로세스의 Process: Page Faults/sec 카운터를 모니터하고 질문에 있는 Sqlservr.exe 인스턴스에 대한 초당 페이지 결함 수가 Memory: Pages/sec 값과 비슷한지 확인하십시오.
성능 측면에서 볼 때 소프트 결함은 일반적으로 CPU 리소스를 소모하기 때문에 하드 결함만큼 나쁘지 않습니다. 하드 결함은 디스크 입/출력 리소스를 소모합니다. 성능에 있어 최상의 환경은 어떤 종류의 부재도 없는 것입니다.
참고 SQL Server가 처음으로 모든 데이터 캐시 페이지에 액세스할 때까지는 각 페이지에 대한 첫째 액세스가 소프트 결함을 일으킬 것입니다. 따라서 SQL Server가 처음 시작될 때와 데이터 캐시를 처음 실행할 때 발생하는 초기 소프트 결함에 대해서는 염려하지 않아도 됩니다.
프로세서 모니터링
사용자의 목표는 서버에 할당되는 모든 프로세서 작업을 병목 현상은 일으키지 않으면서 최대 성능을 낼 수 있는 수준으로 유지하는 것입니다. 성능 조정에서의 문제는 병목 현상이 CPU에서 일어나지 않으면 다른 구성 요소(디스크 하위 시스템이 주된 요인임)에서 발생하여 CPU 용량이 낭비된다는 점입니다. 보통 CPU는 확장(예: 현재의 많은 시스템에서 4 또는 8로 제한하는 것 같이 일부 구성별 고유 수준 이상으로 확장)하기 가장 어려운 리소스이므로 작업량이 많은 시스템에서 CPU 활용률이 95%를 넘는 것은 좋은 신호로 보아야 합니다. 이와 동시에 트랜잭션의 반응 시간이 합당한 수준 내로 유지되는지 모니터해야 합니다. 그렇지 않다면 95% 이상의 CPU 사용량이란 단순히 사용 가능한 CPU 리소스에 대한 작업 로드가 과도하며 CPU 리소스를 늘리거나 작업 로드를 감소 또는 조정해야 한다는 뜻일 수 있습니다.
시스템 모니터 카운터 Processor: % Processor Time을 주시하여 모든 프로세서가 각 CPU에서 항상 95% 미만의 활용률을 유지하는지 확인합니다. System: Processor Queue Length는 Windows 시스템의 모든 CPU용 프로세서 대기열입니다. System: Processor Queue Length가 각 CPU에서 2를 넘으면 CPU 병목 현상을 나타냅니다. CPU 병목 상태가 발견되면 서버에 프로세서를 추가하거나 시스템의 작업 로드를 줄여야 합니다. 작업 로드는 쿼리 조정이나 인덱스를 개선하여 입/출력을 줄임으로써 줄일 수 있으며 결과적으로 CPU 사용량도 줄일 수 있습니다.
CPU 병목 현상이 의심될 때 감시할 또 하나의 시스템 모니터 카운터는 System: Context Switches/sec입니다. 왜냐하면 이 카운터는 Windows 및 SQL Server가 한 스레드에서 다른 스레드로 실행 대상을 바꿔야 했던 수(초당 횟수)를 나타내기 때문입니다. 이 경우 CPU 리소스를 사용하게 됩니다. 컨텍스트 교환은 다중 스레드, 다중 프로세스 환경의 정상적인 구성 요소이지만, 컨텍스트 교환이 과도하면 시스템 성능이 저하됩니다. 프로세서 대기가 있다면 컨텍스트 전환만 고려하는 방법을 선택해야 합니다.
프로세서 대기가 관찰되면 SQL Server 성능 조정 시에 컨텍스트 전환 수준을 측정 단위로 사용합니다. 컨텍스트 전환이 원인으로 판단되는 경우, affinity mask 옵션을 사용하거나 파이버 기반 스케줄링을 사용하는 두 가지 방법을 고려할 수 있습니다.
로드가 많은 환경에서 SMP(Symmetric Multiprocessor) 시스템(마이크로 프로세서 5개 이상 내장)의 성능을 향상시키려면 affinity mask 옵션을 사용하십시오. 한 스레드를 특정 프로세서와 연관시키고 SQL Server가 사용할 프로세서를 지정할 수 있습니다. SQL Server 동작에 특정 프로세서가 사용되지 않도록 지정하는 데도 affinity mask 옵션을 사용합니다. affinity mask의 설정을 변경하기 전에 Windows가 NIC와 연관된 DPC(Deferred Process Call) 동작을 시스템에서 가장 큰 번호의 프로세서로 할당한다는 점을 명심하십시오. 시스템에 NIC가 여러 개 설치되어 현재 사용되고 있는 경우, 추가되는 카드 작업은 우선 순위가 다음으로 높은 프로세서에 할당됩니다. 예를 들어, NIC가 둘이고 8개의 프로세서가 있는 시스템에서는 각 NIC의 DPC가 각각 프로세서 7과 프로세서 6에 할당됩니다(0에서 시작하는 카운트 사용). lightweight pooling 옵션을 사용하면 SQL Server가 기본값인 스레드 기반 스케줄링 모델이 아니라 파이버 기반 스케줄링 모델로 전환합니다. 파이버는 간단한 스레드로 생각할 수 있습니다. sp_configure 'lightweight pooling',1 명령을 사용하여 파이버 기반 스케줄링을 활성화합니다.
프로세서 대기열과 컨텍스트 전환을 관찰하여 affinity mask와 lightweight pooling의 설정 값에 대한 영향을 모니터하십시오. 경우에 따라 이러한 설정이 성능을 향상시키는 것이 아니라 떨어뜨릴 수 있습니다. 또한 시스템에 내장된 프로세서가 4개 이상인 경우를 제외하고는 별로 이점이 없습니다. DBCC SQLPERF (THREADS)는 입/출력, 메모리 및 spids로 다시 매핑된 CPU 사용에 대해 더 자세한 정보를 제공합니다. "select * from master.sysprocesses order by cpu desc" SQL 쿼리를 실행하여 현재 CPU 시간을 가장 많이 사용하는 것이 무엇인지 조사합니다.
select * from master.sysprocesses order by cpu desc.
Processor Queue Length(프로세서 대기열 길이) 모니터링
System: Processor Queue Length 가 2보다 크면, 서버의 프로세서가 하나의 집합적 그룹으로 처리할 수 있는 크기 이상의 작업 요청을 받는 것입니다. 따라서 Windows는 이 요청을 대기열에 넣어야 합니다.
일부 프로세서 대기열 작업은 실제로 전반적인 SQL Server 입/출력 성능을 나타내는 좋은 지표입니다. 대기 중인 프로세서가 없고 CPU 활용률이 낮다면 시스템 어딘가에 성능 병목 지점이 있다는 것을 나타냅니다. 이러한 병목 지점은 디스크 하위 시스템일 가능성이 많습니다. 프로세서에 적당한 정도의 작업 대기가 있는 것은 CPU가 유휴 상태에 있지 않으며 시스템의 나머지 부분이 CPU와 보조를 맞추고 있다는 것을 의미합니다.
양호한 프로세서 대기 수를 계산하는 가장 좋은 방법은 데이터베이스 서버의 CPU 수를 2로 나누는 것입니다.
이 계산 결과보다 훨씬 큰 프로세서 대기열은 검토할 필요가 있습니다. 이는 서버에서 CPU 병목 현상이 일어나는 것을 나타내는 것일 수 있습니다. 과도한 프로세서 대기는 쿼리 실행 시간을 낭비합니다. 여러 다른 작업이 프로세서 대기에 영향을 줄 수 있습니다. 하드 및 소프트 페이징을 제거하는 것이 CPU 리소스를 절약하는 데 도움이 됩니다. 프로세서 대기열 작업을 줄이는 데 도움이 되는 다른 방법으로는 SQL 쿼리 조정, 디스크 입/출력(및 CPU) 감소를 위해 보다 나은 SQL 인덱스 선택 또는 시스템에 CPU(프로세서) 추가 등이 있습니다.
입/출력 모니터링
Disk Write Bytes/sec와 Disk Read Bytes/sec 카운터는 각 논리 및 물리 드라이브가 1초에 처리할 수 있는 데이터 바이트 수를 표시합니다. Disk Reads/sec 및 Disk Writes/sec와 함께 이 수치를 신중히 평가해 보십시오. 초당 바이트 수가 작더라도 입/출력 하위 시스템이 많이 사용되고 있지 않은 것으로 생각해서는 안됩니다!
SQL Server 파일과 연관된 모든 드라이브에 대한 Disk Queue Length를 모니터하고 어느 파일이 과도한 디스크 대기열과 연관되어 있는지 판단합니다.
시스템 모니터가 일부 드라이브는 다른 드라이브만큼 사용량이 많지 않음을 나타내면, 병목 현상이 발생한 드라이브의 SQL Server 파일을 사용량이 적은 드라이브로 옮길 수 있습니다. 이렇게 하여 입/출력 작업을 하드 드라이브 전체에 더 균등하게 분산시킬 수 있습니다. SQL Server 파일용으로 대형 드라이브 풀 하나를 사용하는 경우, 발생하는 디스크 대기열에 대한 해결책은 풀에 더 많은 물리 드라이브를 추가하여 풀의 입/출력 용량을 늘리는 것입니다.
디스크 대기열은 하나의 SCSI 채널이 입/출력 요청으로 가득 찬 상태를 나타내는 것일 수 있습니다. 시스템 모니터는 이러한 상황을 직접 감지할 수 없습니다. 저장소 공급업체는 보통 RAID 컨트롤러가 서비스하는 입/출력 크기와 컨트롤러에 입/출력 요청이 대기하고 있는지 여부를 모니터하는 데 도움이 되는 도구를 제공합니다. 이 경우는 SCSI 채널에 디스크 드라이브가 많이(10개 이상) 장착되어 있고 이러한 드라이브 모두가 최고 속도로 입/출력을 수행하는 경우에 발생하기 쉽습니다. 이러한 경우 솔루션은 디스크 드라이브의 절반을 다른 SCSI 채널 또는 RAID 컨트롤러에 연결하여 해당 입/출력의 균형을 유지하는 것입니다. 일반적으로 SCSI 채널간에 드라이브 균형을 유지하려면 SQL Server 데이터베이스 파일에 대한 RAID 어레이 및 전체 백업/복원을 다시 구축해야 합니다.
Percent Disk Time
시스템 모니터의 PhysicalDisk: % Disk Time 및 LogicalDisk: % Disk Time 카운터는 디스크가 읽기/쓰기 작업에 사용되는 시간 백분율을 모니터합니다. % Disk Time 카운터가 높으면(90% 이상) Current Disk Queue Length 카운터를 보고 디스크 액세스 대기 중인 시스템 요청 수를 확인하십시오. 대기 중인 입/출력 요청 수가 물리 디스크를 구성하는 스핀들 수의 1.5-2배를 넘지 않게 유지해야 합니다. 보통 RAID(Redundant array of Inexpensive Disks) 장치에는 스핀들이 여러 개이지만 대부분의 디스크에는 스핀들이 한 개입니다. 하드웨어 RAID 장치는 시스템 모니터에 하나의 물리 디스크로 나타납니다. 소프트웨어를 통해 작성된 RAID 장치는 여러 개의 인스턴스로 나타납니다.
Disk Queue Length
지나치게 긴 디스크 대기열은 반드시 모니터해야 합니다.
디스크 대기열의 길이를 모니터하려면 여러 가지 시스템 모니터 디스크 카운터를 관찰해야 합니다. 이러한 카운터를 활성화하려면 Windows 2000 또는 Windows NT 명령 창에서 diskperf ?y 명령을 실행한 후 시스템을 다시 시작하십시오.
디스크 대기열이 발생하는 물리 하드 드라이브에서는 디스크 입/출력 요청을 보류하면서 입/출력 처리를 방해합니다. 이러한 드라이브에 대한 SQL Server 응답 시간은 저하될 것입니다. 이로 인해 쿼리 실행 시간이 소비됩니다.
RAID를 사용한다면 각 물리 드라이브에 대한 디스크 대기열을 계산하기 위해 Windows에서 단일 물리 드라이브로 간주하는 각 디스크 어레이와 연관된 물리 하드 드라이브 수를 알아야 합니다. 하드웨어 전문가에게 SCSI 채널과 물리 드라이브 분산에 대한 설명을 요청하여 SQL Server 데이터가 각 물리 드라이브에 보관되는 방법과 각 SCSI 채널에 분산되는 SQL Server 데이터의 양을 알아야 합니다.
시스템 모니터를 통해 디스크 대기열을 보는 방법은 여러 가지가 있습니다. 논리적 디스크 카운터는 디스크 관리자를 통해 지정된 물리 드라이브 문자에 연결되는 데 반해, 물리 디스크 카운터는 디스크 관리자가 단일 물리 디스크 장치로 인식하는 것과 연결되어 있습니다. 디스크 관리자에게 단일 물리 장치로 보여지는 것은 단일 하드 드라이브일 수도 있고 여러 하드 드라이브로 구성된 RAID 어레이일 수도 있습니다. Current Disk Queue Length는 디스크 대기열에 대한 즉각적인 측정값인 데 반해, Avg. Disk Queue Length는 샘플링 기간 동안의 디스크 대기열 측정값을 평균낸 것입니다. 조건 중 하나가 표시되면 메모해 두십시오.
이러한 권장 측정값은 각 물리 하드 드라이브별로 지정됩니다. 디스크 대기 측정값이 RAID 어레이와 연관되어 있는 경우, 물리 하드 드라이브 당 디스크 대기를 결정하려면 측정값을 RAID 어레이의 물리 하드 드라이브 수로 나누어야 합니다.
참고 SQL Server 로그 관리자는 SQL Server 로그 파일에 하나 이상의 입/출력 요청을 대기시키지 않기 때문에 SQL Server 로그 파일을 보관하는 물리 하드 드라이브나 RAID 어레이에서 디스크 대기열은 유용한 측정 방법이 아닙니다.
SQL Server 2000의 원리를 이해하면 데이터베이스의 성능을 관리하는 데 도움이 됩니다.
SQL Server는 데이터베이스 서버에 전달되는 SQL Server 명령 배치를 서비스하는 데 사용하는 Windows 스레드의 풀을 유지합니다. 들어오는 모든 명령 배치를 서비스하는 데 사용할 수 있는 전체 스레드(SQL Server 용어로는 "작업자 스레드") 수는 sp_configure 옵션인 max worker threads 설정에 따라 결정됩니다. 배치를 능동적으로 전달하는 연결의 수가 max worker threads에 대해 지정된 수보다 크면, 능동적으로 배치를 전달하는 연결들은 작업자 스레드를 공유하게 됩니다. 대부분의 설치에서는 기본값 255를 적용하면 충분할 것입니다. 연결은 대부분 배치가 클라이언트에서 수신되기를 기다리는 데 대부분의 시간을 소비합니다.
작업자 스레드는 SQL Server 버퍼 캐시에서 8KB 데이터 페이지를 작성하는 대부분의 작업을 맡습니다. 작업자 스레드는 최대 성능을 위해 자체 입/출력 동작 일정을 비동기적으로 작성합니다.
지연 기록기는 버퍼 관리자 내에서 기능을 수행하는 SQL Server 시스템 프로세스입니다. 지연 기록기는 커밋되지 않고 오래된 버퍼(다른 페이지에 재사용되기 전에 먼저 디스크에 기록해야 할 변경 사항이 포함된 버퍼)를 배치로 플러시하여 사용자 프로세서에 사용될 수 있도록 합니다. 이러한 작업은 사용 가능한 빈 버퍼를 만들고 유지 관리하는 데 도움이 됩니다. 즉, 8KB의 빈 데이터 캐시 페이지를 다시 사용할 수 있게 됩니다. 지연 기록기가 8KB 캐시 버퍼를 플러시할 때마다 캐시 페이지의 ID가 초기화되므로 다른 데이터를 빈 버퍼에 쓸 수 있게 됩니다. 지연 기록기는 디스크 입/출력이 적은 기간에 작동함으로써 이 동작이 기타 SQL Server 작업에 미치는 영향을 최소화합니다.
SQL Server는 사용 가능한 버퍼 수준을 자동으로 구성하고 관리합니다. 성능 카운터 SQL Server: Buffer Manager: Lazy Writes/sec은 실제로 디스크에 기록되는 8KB 페이지 수를 나타냅니다. 이 값이 급격히 떨어지는지 보려면 SQL Server: Buffer Manager: Free Pages를 모니터하십시오. 지연 기록기는 SQL Server 작업 전체에서 이 카운터 수준을 최적으로 유지함으로써 사용 가능한 버퍼에 대한 사용자 요구를 만족시킵니다. 시스템 모니터 개체 SQL Server: Buffer Manager: Free Pages의 값이 0에 도달할 경우, 사용자 로드가 지연 기록기에서 제공할 수 있는 크기 이상의 빈 버퍼를 요구할 때가 있었다는 것입니다.
지연 기록기가 빈 버퍼를 안정 수준으로 또는 적어도 0 이상으로 유지하는 데 문제가 있다면, 이는 디스크 하위 시스템이 충분한 디스크 입/출력 성능을 제공하지 못한다는 뜻일 수 있습니다. 빈 버퍼 수준의 감소를 디스크 대기열과 비교하여 사실을 확인하십시오. 해결책은 추가 물리 디스크 드라이브를 데이터베이스 서버 디스크 하위 시스템에 추가하여 디스크 입/출력 처리 능력을 높이는 것입니다.
시스템 모니터에서 Average Disk Queue Length 또는 Current Disk Queue Length 카운터를 보고 현재 디스크 대기열 수준을 모니터하고, 모든 SQL Server 동작과 연관된 각 물리 드라이브마다 디스크 대기열이 2 미만인지 확인합니다. 하드웨어 RAID 컨트롤러와 디스크 어레이를 채택하는 데이터베이스 서버의 경우, 반드시 논리적 또는 물리 디스크 카운터가 보고하는 수를 디스크 관리자가 보고하는 해당 논리 드라이브 문자와 관련된 실제 하드 드라이브 수 또는 물리 하드 드라이브 수로 나누어야 합니다. 이는 Windows와 SQL Server가 RAID 컨트롤러에 부착된 물리 하드 드라이브 수를 인식하지 못하기 때문입니다. 시스템 모니터가 보고하는 디스크 대기열 수를 적절히 해석하기 위해서는 RAID 어레이 컨트롤러와 연관된 드라이브의 수를 아는 것이 중요합니다.
자세한 내용은 SQL Server 온라인 문서에서 "freeing and writing buffer pages"와 "write-ahead transaction log" 문자열을 검색해 보십시오.
각 SQL Server 인스턴스는 커밋되지 않은 로그와 데이터 페이지를 주기적으로 디스크에 플러시합니다. 이를 검사점이라고 합니다. 검사점은 SQL Server 인스턴스가 다시 시작될 때 장애 복구에 필요한 시간과 리소스를 줄여줍니다. 검사점 중에 커밋되지 않은 페이지(버퍼 캐시에 놓인 이후 수정된 버퍼 캐시 페이지)는 SQL Server 데이터 파일에 기록됩니다. 검사점에 디스크로 쓰여진 버퍼에는 여전히 페이지가 들어 있으며 사용자는 디스크에서 다시 읽지 않고 읽거나 업데이트할 수 있습니다. 이것은 지연 기록기에 의해 작성된 빈 버퍼에는 해당되지 않습니다.
검사점 논리는 작업자 스레드와 지연 기록기가 커밋되지 않은 페이지를 기록하는 대부분의 작업을 수행하도록 합니다. 이를 위하여 검사점 논리는 가능한 경우 커밋되지 않은 페이지를 쓰기 전에 여분의 검사점 대기를 시도합니다. 이렇게 하면 작업자 스레드와 지연 기록기가 커밋되지 않은 페이지를 쓰는 데 더 많은 시간을 할애할 수 있습니다. 이와 같이 커밋되지 않은 페이지에 여분의 대기 시간이 발생하는 조건에 대한 내용은 SQL Server 온라인 문서의 "Checkpoints and the Active Portion of the log" 절에 자세히 나와 있습니다. 기억해 두어야 할 사항은 검사점 논리가 보다 긴 시간 동안 SQL Server 디스크 입/출력 작업을 안정시키기 위해 여분의 검사점 대기를 사용한다는 사실입니다.
보다 효율적인 검사점 작업을 위해서는 캐시에서 플러시해야 할 페이지가 많을 때 SQL Server가 플러시할 데이터 페이지를 디스크에 나타난 페이지 순서로 정렬합니다. 이렇게 하면 캐시 플러시 도중 디스크 암의 움직임을 최소화하는 데 도움이 되며, 가능한 경우 순차적 디스크 입/출력의 이점을 이용할 수 있습니다. 검사점 프로세스는 또한 8KB 디스크 입/출력 요청을 비동기적으로 디스크 하위 시스템에 전달합니다. 이로 인해 SQL Server는 필요한 디스크 입/출력 요청을 더 빨리 전달할 수 있습니다. 이는 검사점 프로세스가 데이터가 실제로 디스크에 기록되었음을 디스크 하위 시스템이 보고할 때까지 기다리지 않기 때문입니다.
SQL Server 데이터 파일과 연관된 하드 드라이브의 디스크 대기열을 살펴보면서 SQL Server가 디스크가 처리할 수 있는 것보다 더 많은 디스크 입/출력 요청을 보내고 있지 않는지 확인하고, 이러한 경우가 발생하면 디스크 하위 시스템에 디스크 입/출력 용량을 추가하여 하위 시스템이 로드를 처리할 수 있도록 해야 합니다.
다른 모든 주요 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 로그 파일과 관련된 디스크 대기를 살펴보는 방법은 SQL Server 데이터베이스 파일과 다릅니다. 시스템 모니터 카운터 SQL Server: Databases 캐시 컨트롤러는 최고 성능을 제공하지만 로그 파일을 포함하는 디스크에는 사용할 수 없습니다. 단, 컨트롤러는 전원 장애가 발생하는 경우에도 마지막에 신뢰할 수 있는 데이터만 디스크에 기록할 것을 보증하는 경우는 예외입니다. 캐시 컨트롤러에 대한 자세한 내용은 뒤에 나오는 "하드웨어 RAID 컨트롤러에 내장된 캐시의 효과" 절을 참조하십시오.
SQL Server 2000은 테이블 스캔 등의 작업에 많은 순차적 읽기에 대한 자동 관리를 제공합니다. 미리 읽기 관리는 완전히 자동으로 구성 및 조정되며 SQL Server 쿼리 프로세서의 작업과 밀접하게 통합됩니다. 미리 읽기 관리는 큰 테이블 스캔, 큰 인덱스 범위 스캔, 클러스터링된 인덱스 및 클러스터링되지 않은 인덱스 바이너리 트리 탐색 및 기타 상황에 사용됩니다. 이것은 미리 읽기가 8KB 입/출력에 비해 더 많은 디스크 처리량을 제공하는 64KB 입/출력으로 발생하기 때문입니다. 아주 많은 양의 데이터를 검색해야 하는 경우, SQL Server는 미리 읽기를 사용하여 처리율을 극대화합니다.
SQL Server는 미리 읽기 관리를 지원하는 간단하고 효율적인 IAM(Index Allocation Map) 저장소 구조를 사용합니다. IAM은 일정 범위의 여러 위치를 기록하는 SQL Server 메커니즘입니다. 각각의 64KB 범위에 데이터나 인덱스 정보가 8페이지씩 들어 있습니다. 각 IAM 페이지의 크기는 8KB이며, 이 페이지는 어느 범위에 필요한 데이터가 들어 있는지에 대한 정보(비트맵 형식)를 포함합니다. IAM 페이지의 압축 특성 때문에 빠르게 읽혀지며, 보다 정기적으로 사용되는 IAM 페이지를 버퍼 캐시에 유지 관리할 수 있습니다.
미리 읽기 관리는 쿼리 프로세서의 쿼리 정보를 IAM 페이지에서 읽어야 할 모든 범위의 위치에 대한 정보와 결합하여 여러 순차적 읽기 요청을 구성합니다. 순차적 64KB 디스크 읽기는 극히 양호한 디스크 입/출력 성능을 제공합니다. SQL Server: Buffer Manager: Read-Ahead Pages/sec 성능 카운터는 미리 읽기 관리의 효율과 효과에 대한 정보를 제공합니다.
SQL Server 2000 Enterprise Edition은 미리 읽기 페이지의 최대 수를 존재하는 메모리 크기를 기준으로 동적으로 조정합니다. 다른 모든 SQL Server 2000 버전에서는 값이 고정됩니다. SQL Server 2000 Enterprise Edition의 또 하나의 확장 기능은 흔히 말하는 회전목마(merry-go-round) 스캔으로, 여러 작업이 전체 테이블 스캔을 공유할 수 있도록 하는 기능입니다. SQL 문의 실행 계획이 테이블의 데이터 페이지 스캔을 요청하고 관계형 데이터베이스가 해당 테이블이 이미 다른 실행 계획에 의해 스캔되고 있음을 발견할 경우, 데이터베이스 엔진은 두번째 스캔의 현재 위치에서 스캔을 첫번째 스캔에 조인합니다. 데이터베이스 엔진은 각 페이지를 읽고 페이지의 행을 두 실행 계획 모두로 전달합니다. 이러한 작업은 테이블의 끝에 도달할 때까지 계속됩니다. 테이블의 끝에 도달하면, 첫번째 실행 계획이 완전한 스캔 결과를 갖게 되지만 두번째 실행 계획은 진행 중이던 스캔과 조인된 시점 이전에 발생한 데이터 페이지 검색을 계속해야 합니다. 두번째 실행 계획에 대한 검색은 테이블의 첫 데이터 페이지로 되돌아가서 첫번째 스캔과 조인된 시점으로 계속 진행됩니다. 나머지 추가 스캔도 이와 같은 방법으로 결합할 수 있습니다. 데이터베이스 엔진은 모든 스캔을 완료할 때까지 데이터 페이지 순환을 계속합니다.
미리 읽기 관리에 있어 한 가지 주의할 사항은 너무 많은 미리 읽기는 전반적인 성능을 떨어뜨릴 수 있다는 점입니다. 그 이유는 다른 목적으로 사용될 수도 있는 입/출력 및 CPU를 사용하여 캐시가 불필요한 페이지로 가득찰 수 있기 때문입니다. 솔루션은 최소한의 페이지가 버퍼 캐시로 들어오도록 모든 SQL 쿼리를 조정하는 일반적인 성능 조정입니다. 인덱스가 올바르며 그러한 인덱스를 사용하고 있는지 확인하는 것도 여기에 포함됩니다. 효율적인 범위 스캔을 위해 클러스터링된 인덱스를 사용하고 단일 행이나 그보다 더 작은 행 집합의 위치를 신속하게 찾는 데 도움이 되도록 클러스터링되지 않은 인덱스를 정의합니다. 예를 들어, 테이블에 한 개의 인덱스만 있도록 하거나 해당 인덱스가 단일 행이나 적은 행 집합을 반입하는 것이 목적인 경우, 인덱스를 클러스터링해야 합니다. 클러스터링된 인덱스는 보통 클러스터링되지 않은 인덱스보다 속도가 빠릅니다.
데이터 웨어하우스는 차원 모델링을 사용하여 분석 용도로 데이터를 구성합니다. 차원 모델링은 별모양과 눈송이 스키마를 만듭니다. 이러한 스키마는 데이터 웨어하우징에서 자주 수행되는 대량의 데이터 읽기 작업에서 성능 효율성을 높여줍니다. 부피가 큰 데이터(종종 수십억 개의 행으로 구성됨)는 길이가 매우 짧은 행들로 구성된 팩트 테이블에 저장됩니다. 이러한 테이블은 저장소 요구와 쿼리 시간을 최소화합니다. 비즈니스 실체의 속성을 비표준 상태로 차원 테이블에 넣어 데이터를 검색할 때 테이블 조인 수를 최소화합니다.
데이터 웨어하우스용 데이터베이스 설계에 대한 자세한 내용은 Microsoft SQL Server 2000 Resource Kit의 "데이터 웨어하우스 설계 고려 사항"을 참조하십시오.
SQL 쿼리에서 부등 연산자를 사용하면 데이터베이스에서 부등 조건을 평가하기 위해 테이블 스캔을 사용하게 됩니다. 아주 큰 테이블에 대해 이러한 쿼리가 자주 수행되면 입/출력 횟수가 많아집니다. "NOT" 연산자(!=, <>, !<, !>)를 포함하는 WHERE 절(예: WHERE 이러한 형태의 쿼리를 실행해야 하는 경우에는 쿼리 구조를 바꾸어 NOT 키워드를 없애도록 하십시오. 예를 들면 다음과 같습니다.
다음 쿼리에서
select * from tableA where col1 != "value"
다음 쿼리로
select * from tableA where col1 < "value" and col1 > "value"
SQL로 작업하는 데이터베이스 프로그래머는 구축할 결과 세트를 고려하는 데 ADO(Microsoft ActiveX® Data Objects), RDO(Remote Data Objects) 및 DAO(Data Access Objects) 데이터베이스 API와 같이 손쉬운 인터페이스를 사용합니다. ADO/RDO/DAO는 프로그래머들이 SQL 프로그램 작성 경험이 많지 않아도 풍부한 SQL 행 집합 기능을 만들 수 있는 뛰어난 데이터베이스 개발 인터페이스입니다. 하지만 추가 비용이 듭니다. 프로그래머의 응용 프로그램이 클라이언트로 반환하는 데이터 양을 신중하게 고려하고 인덱스가 놓일 SQL Server에 대한 추적을 유지하고 SQL Server 데이터의 정렬 방법을 고려하여 성능 문제를 피할 수 있습니다. SQL 프로필러, 인덱스 튜닝 마법사 및 그래픽 방식 실행 계획은 이러한 문제 쿼리를 정확히 찾아내 수정하는 데 아주 유용한 도구입니다.
커서 논리를 사용할 때, 수행하려는 처리 유형에 적합한 커서를 선택하십시오. 다양한 유형의 커서가 각기 다른 가격으로 제공됩니다. 반드시 수행할 작업의 종류(읽기 전용, 전송 처리만 등)를 파악한 다음에 해당되는 커서 유형을 선택하십시오.
선택 목록에서 반환할 필요가 없는 열을 제거하거나 오직 필요한 행만을 반환함으로써 기회가 있을 때마다 반환되는 결과 집합 크기를 줄여야 합니다. 이렇게 하면 입/출력과 CPU 소모량을 줄이는 데 도움이 됩니다.
여러 명령문 사용
데이터베이스에서 처리를 수행하여 클라이언트와 데이터베이스 서버 사이의 불필요한 네트워크 통신과 결과 세트의 크기를 줄일 수 있습니다. SQL Server에서 하나의 Transact-SQL 문만으로는 완료할 수 없는 처리를 수행하기 위해 다음과 같은 방법으로 여러 Transact-SQL 문을 그룹으로 묶을 수 있습니다.
다음과 같은 SQL Server 기능을 사용하여 한 번에 여러 개의 Transact-SQL 문을 사용하는 것을 제어할 수 있습니다.
SQL Server가 이전의 쿼리에서 기존의 실행 계획을 활용할 수 있을 때 성능이 향상될 수 있습니다. 개발자는 여러 가지 방법으로 SQL Server가 실행 계획을 다시 사용하도록 할 수 있습니다. Transact-SQL 문은 다음과 같은 지침에 따라 작성해야 합니다.
예를 들면 다음 SELECT 문과 같이 코딩하지 마십시오.
SELECT * FROM Shippers WHERE ShipperID = 3
ODBC를 사용하는 대신, 다음과 같은 SQLBindParameter ODBC 함수를 사용하십시오.
SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3 예를 들면 다음 SELECT 문과 같이 코딩하지 마십시오.
SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3
ODBC를 사용하는 대신, 한 예로 SQLBindParameter ODBC 함수를 사용하여 매개 변수 표시(?)를 프로그램 변수에 바인드하고 SELECT 문을 다음과 같이 코딩하십시오.
SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = ? DECLARE @IntVariable INT sp_executesql 은 별도의 저장 프로시저를 작성 및 유지 관리할 때 오버헤드가 생기는 것을 원치 않는 경우에 좋은 대안입니다.
일괄 처리에 대한 실행 계획 재사용
여러 개의 동시 실행 응용 프로그램이 알고 있는 매개 변수 집합이 지정된 동일한 일괄 처리를 실행할 경우, 응용 프로그램에 의해 호출되는 저장 프로시저로 일괄 처리를 구현하십시오.
ADO, OLE DB 또는 ODBC 응용 프로그램이 동일한 일괄 처리를 여러 번 실행할 경우, PREPARE/EXECUTE 모델을 사용하여 일괄 처리를 실행하십시오. 프로그램 변수에 바인드된 매개 변수 표시를 사용하여 필요한 모든 입력 값을 제공하십시오. 예를 들면 UPDATE VALUES 절에 사용되는 식이나 검색 조건의 조건자에 사용되는 식입니다.
SQL Server에서는 열이 인덱스에 속하지 않더라도 열의 값 분산에 사용할 통계 정보를 작성할 수 있습니다. 쿼리 프로세서는 이 통계 정보를 사용하여 쿼리 평가에 가장 적합한 전략을 결정할 수 있습니다. 인덱스를 만들면, SQL Server는 인덱싱된 열에 값을 분산시키는 작업과 관련된 통계 정보를 자동으로 저장합니다. 인덱싱된 열 외에, AUTO_CREATE_STATISTICS 데이터베이스 옵션이 ON(기본값)으로 설정된 경우에도 SQL Server가 조건자에 사용된 열에 대한 통계를 자동으로 생성합니다. 해당 열이 인덱싱된 열이 아닐 경우에도 마찬가지입니다.
열의 데이터가 변경되면, 인덱스와 열 통계가 더 이상 올바르지 않게 되고 쿼리 최적화 프로그램이 쿼리 처리 방법에 대해 최적의 결정을 내릴 수 없게 됩니다. 주기적으로 SQL Server는 테이블의 데이터 변경에 따른 통계 정보를 자동으로 업데이트합니다. 샘플링은 데이터 페이지에서 무작위로 이루어지며, 통계에 필요한 열에 최소 크기의 클러스터링되지 않은 인덱스 또는 테이블로부터 가져옵니다. 디스크에서 읽어온 데이터 페이지의 모든 행을 사용하여 통계 정보를 업데이트합니다. 통계 정보의 업데이트 주기는 열의 데이터 크기나 인덱스 및 변경 데이터의 크기에 따라 결정됩니다.
예를 들어, 10,000개의 행을 포함하는 테이블에 대한 통계는 1,000개의 인덱스 값이 변경된 후 업데이트되어야 할 수 있습니다. 이 테이블에서 10,000개 중 1,000개는 큰 비율이기 때문입니다. 그러나 천만 개의 인덱스 항목이 포함된 테이블에서 1,000개의 변경되는 인덱스 값은 그리 중요하지 않기 때문에 자동으로 통계가 업데이트되지 않을 수도 있습니다. 그러나 SQL Server는 언제나 최소의 행이 샘플링되도록 유지합니다. 즉, 항상 8MB 미만의 테이블이 완전 스캔되어 통계를 수집하도록 합니다.
참고 쿼리의 실행 계획이 SQL 쿼리 분석기를 통해 그래픽으로 표시될 때, 오래되었거나 누락된 통계에는 경고(빨간색 테이블 이름) 표시가 됩니다. 또한 SQL 프로필러를 사용하는 Missing Column Statistics 이벤트 클래스는 통계가 유실될 때 이를 표시해 줍니다.
통계는 sp_createstats 시스템 저장 프로시저를 사용하여 한 명령문으로 현재 데이터베이스의 모든 사용자 테이블에서 적합한 모든 열에 쉽게 생성될 수 있습니다. 통계에 적합하지 않은 열에는 결정적이지 않은 또는 정확하지 않은 계산된 열, 또는 image, text 및 ntext 테이터 형식의 열이 있습니다.
수동으로 통계를 작성하면 여러 행 밀도(열 조합에 대한 평균 중복 수)를 포함하는 통계를 구할 수 있습니다. 예를 들면 쿼리가 다음과 같은 절을 포함합니다.
WHERE a = 7 and b = 9
두 열(a, b) 모두에 대한 통계를 수동으로 작성하면 SQL Server가 쿼리를 더 잘 평가할 수 있는데, 그 이유는 a와 b 열 조합에 대한 고유한 값의 평균도 통계에 포함되기 때문입니다. 이렇게 하면 SQL Server에서 테이블 스캔에 의존할 필요 없이 col1에 만들어진 인덱스를 사용할 수 있습니다(이 경우 클러스터링된 인덱스). 열 통계를 작성하는 방법에 대해서는 SQL Server Books Online의 "CREATE STATISTICS"를 참조하십시오.
이 책은 이전 버전(Inside Microsoft SQL Server 7.0)을 SQL Server 2000 정보를 이용하여 업데이트한 것으로, 쉽게 구하기 어려운 SQL Server의 본질적 개념에 대해 중점적으로 설명합니다. 많은 사람들이 최고의 데이터 웨어하우징 입문서 중 하나로 꼽는 책입니다. 데이터 웨어하우스 데이터베이스 설계에 대한 탁월한 통찰력을 제공하고 차원 모델링 개념에 대해 상세하게 설명하고 있습니다. 이 책에는 매우 유용한 정보가 들어 있습니다. 계층 데이터를 표현하고 쿼리하는 것과 같은 공통적인 문제에 대한 해결책이 들어 있습니다. 28장은 SQL 쿼리 최적화에 대한 것입니다. 이 문서에 포함된 정보는 문서를 발행할 때 논의된 문제들에 대한 Microsoft Corporation의 당시 관점을 나타냅니다. Microsoft는 변화하는 시장 환경에 대처해야 하므로 이를 Microsoft 측의 책임으로 해석해서는 안되며 발행일 이후 소개된 어떠한 정보에 대해서도 Microsoft는 그 정확성을 보증하지 않습니다.
이 백서는 정보 제공 목적으로만 제공됩니다. MICROSOFT는 이 문서에서 명시적이든 묵시적이든 막론하고 여하한 보증도 하지 않습니다.
해당 저작권법을 준수하는 것은 사용자의 책임입니다. 저작권의 권리와 별도로, 이 설명서의 어떠한 부분도 Microsoft의 명시적인 서면 승인 없이는 어떠한 형식이나 수단(전기적, 기계적, 복사기에 의한 복사, 디스크 복사 또는 다른 방법)으로 또는 어떠한 목적으로도 복제하거나, 검색 시스템에 저장 또는 도입하거나, 전송할 수 없습니다.
Microsoft는 본 설명서 내용과 관련된 특허권, 상표권, 저작권 또는 기타 지적 소유권을 보유할 수 있습니다. 서면 사용권 계약에 따라 Microsoft에서 귀하에게 명시적으로 권리를 제공하지 않으면, 이 설명서 제공으로는 이러한 특허권, 상표권, 저작권 또는 기타 지적 소유권 등에 대한 어떠한 사용권도 귀하에게 부여되지 않습니다.
최종 수정일 : 2001년 11월 12일 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
대상
에 나와 있습니다. 여기서는 SQL Server의 내부 작업을 개괄적으로 설명하는 많은 최적화 및 조정 힌트, 코드 샘플 및 기타 중요한 정보를 제공합니다. information.
자료출처 : http://www.microsoft.com/korea/technet/prodtechnol/sql/maintain/rdbmspft.asp
'데이터베이스 > SQL Server' 카테고리의 다른 글
| MS SQL Server 성능 조정 및 최적화, 제 2부: 쿼리 최적화 프로그램 (0) | 2008.05.06 |
|---|---|
| 제 3부: 성능 향상을 위한 SQL Server 구성 (0) | 2008.05.06 |
| 김연욱(Vichang)님의 Inside Query Performance - (2) (0) | 2008.05.06 |
| 김연욱(Vichang)님의 Inside Query Performance - (1) (0) | 2008.05.06 |
| 인덱스 관리 개선기능 (0) | 2008.05.06 |