Reference URL :
http://www.microsoft.com/korea/TechNet/sql/Technote/dat412ef.asp?bPrint=True
개발자를 위한 MS SQL Server 성능 조정 및 최적화, 제 3부: 성능 향상을 위한 SQL Server 구성
DAT 412 Adam Shapiro Microsoft® SQL Server™는 SQL Server를 구성하기 위한 많은 옵션을 제공하며 SQL Server의 동작에 영향을 미칩니다. 3부에서는 이와 관련된 중요한 옵션에 대해 설명합니다.
조직의 하드웨어 환경에 맞게 조정해야 하는 몇 가지 옵션을 제외하고 대부분의 옵션은 전혀 조정할 필요가 없습니다.
시스템 구성 정보는 sysconfigures, syscurconfigs, 구성 블록 및 Windows NT 레지스트리에 저장됩니다.
이 시스템 테이블에는 사용자가 설정할 수 있는 각 구성 옵션에 대한 행이 있습니다. 이 테이블에는 가장 최근에 SQL Server를 시작하기 전에 정의한 구성 옵션 그리고 가장 최근에 SQL Server를 시작한 이후 설정한 동적 구성 옵션이 있습니다.
이 시스템 테이블에도 sysconfigures와 마찬가지로 각 구성 옵션에 대한 항목이 있지만 현재 값이 포함된다는 것이 다릅니다. 또한, 이 테이블에는 구성 구조를 기술하는 4개의 항목이 있습니다.
이 테이블은 sysobjects에 테이블로 표시되지만 실제로는 사용자가 쿼리할 때만 만들어지는 의사 테이블입니다. syscurconfigs에 대해 sysindexes 행이 보이면 데이터 행이 전혀 없다는 뜻입니다.
SQL 엔터프라이즈 관리자를 사용하거나 sp_configure 시스템 저장 프로시저를 실행하여 구성 값을 변경할 수 있습니다. sp_configure를 실행하여 구성 옵션을 변경하는 경우 뒤이어 RECONFIGURE 문을 사용해야 합니다. SQL 엔터프라이즈 관리자가 자동으로 RECONFIGURE 문을 실행합니다.
RECONFIGURE 문은 변경된 구성 옵션을 설치합니다. 이는 syscurconfigs 테이블의 내용이 마스터 장치의 처음 네 페이지에 쓰인다는 뜻입니다. 이들 페이지를 구성 블록이라고 합니다. 다음 번에 SQL Server를 시작할 때 마스터 장치에서 sysconfigures 테이블로 값을 읽어들일 수 있습니다.
SQL Server를 시작하면 SQL Server가 마스터 장치에서 구성 블록을 읽습니다. 마스터 장치는 Microsoft Windows NT® 레지스트리에서 찾을 수 있습니다.
조정할 수 있는 가장 중요한 한 가지 구성이 SQL Server에 할당된 메모리 양일 것입니다.
전역 리소스는 모든 프로세스와 데이터베이스가 사용하는 리소스입니다. 전역 리소스 영역은 대부분 서버에 정의된 각 데이터베이스를 제어하는 구조 등의 다른 구조, 페이지와 확장 영역 그리고 프로시저에 대한 해시 테이블, 서버 차원의 문자 집합 및 정렬 순서에 대한 정보 등을 가리키는 포인터입니다. 이 영역에는 SQL Server에 대한 실행 가능 코드도 포함됩니다.
각 사용자 연결은 서버 구조에 대략 27K 그리고 ODS 구조에 3K 등 총 30K의 SQL Server 메모리를 필요로 합니다. 이 메모리는 PSS(프로세스 상태 구조), 트랜잭션 설명자, 세션 설명자, 보호 캐시, 로그인 레코드, 파서 작업 영역에 사용되며 이들 모두 초기화 시에 SQL Server가 만드는 힙에서 할당됩니다.
또한, 사용자 연결에 1MB의 글로벌 스택이 예약되지만 이 메모리는 즉시 커밋되지 않습니다. SQL Server를 실행하는 동안 더 많은 스택 공간이 필요하면 최고 1MB까지 추가 메모리가 커밋됩니다. 신규 스레드(실제로 사용되고 있는 사용자 연결)마다 스택 공간의 20K 정도를 필요로 합니다.
따라서 각 사용자 연결은 max worker threads 한계까지 총 50K를 필요로 합니다. 그 후에는 연결마다 30K가 필요합니다.
잠금, 열린 데이터베이스, 열린 개체에는 각각의 구성 값을 기반으로 메모리가 할당됩니다. 아래 값을 사용하여 이들 옵션의 총 메모리 소모량을 측정할 수 있습니다.
시스템의 실제 메모리 사용 상태에 대한 추가 정보를 보려면 DBCC MEMUSAGE를 실행하기 전에 추적 플래그 3635를 활성화하십시오.
프로시저와 데이터 캐시에 대해서는 이 문서의 뒷부분에서 자세히 설명합니다.
이 구성 옵션은 사용 가능한 메모리의 크기를 2K 단위로 설정합니다. 초기 값은 컴퓨터의 메모리 양을 기반으로 설치 프로그램이 결정합니다.
시스템에 맞게 이 값을 최적화하려면 전체 실제 메모리에서 Windows NT에 필요한 메모리 그리고 SQL Server 전용 컴퓨터가 아닌 경우 다른 시스템이 사용하는 메모리를 뺍니다.
이상적으로는 시스템의 페이징 동작을 유발하지 않고 SQL Server에 최대한 많은 메모리를 할당하는 것이 좋습니다. Windows NT 성능 모니터를 사용하면 시스템에 대한 임계값을 결정하는 데 도움이 됩니다. 메모리 개체의 Page Faults/sec 카운터는 페이지 부재가 발생하는지 여부를 나타냅니다. 페이지 부재가 발생한다면 SQL Server가 지나치게 많은 메모리를 사용하여 실행되고 있는 것입니다. 임계값은 시스템에 따라 달라집니다.
아래 표는 SQL Server 메모리 할당 시의 적절한 시작 값을 제시한 것입니다. 이 값들은 SQL Server 전용 시스템에 사용한다는 가정 하에 제안하는 수치입니다.
이 값은 시스템에 메모리를 추가하거나 제거하는 경우 또는 시스템 용도를 바꾸는 경우에만 변경해야 합니다.
메모리 옵션을 다시 구성하면 free buffers 구성 옵션도 자동으로 변경됩니다. free buffers 구성 값은 신규 메모리 크기의 5%로 설정됩니다. 이 값의 의미에 대해서는 뒤에서 설명합니다.
memory 옵션의 최대값은 2GB입니다. 그러나 tempdb in RAM 옵션을 사용하여 RAM에 tempdb를 배치한 경우, memory 옵션에 tempdb에 대한 메모리 요구가 포함되지 않습니다.
이 구성 옵션은 SQL Server에서 한 번에 열 수 있는 최대 데이터베이스 수를 설정합니다. 기본값은 20입니다. 열린 데이터베이스는 메모리를 사용하기 때문에 이 값을 늘리면 SQL Server 전용 메모리의 양도 늘려야 할 수 있습니다.
이 구성 옵션은 SQL Server에서 한 번에 열 수 있는 최대 데이터베이스 개체 수를 설정합니다. 기본값은 50입니다.
SQL Server가 열린 개체 수를 초과했다는 메시지를 표시하면 이 값을 늘려야 합니다. 열린 개체는 메모리를 사용하기 때문에 이 값을 늘리면 SQL Server 전용 메모리의 양도 늘려야 할 수 있습니다.
이 구성 옵션은 SQL Server에 동시에 연결할 수 있는 최대 연결 수를 설정합니다. 데이터베이스 환경에 따라, 실제로 연결할 수 있는 개수는 이 값보다 적을 수도 있습니다.
허용되는 사용자 연결 수는 버전에 따라 달라집니다. SQL Workstation에서는 15이고 SQL Server에서는 32,767입니다. 그러나 실제 수는 응용 프로그램 및 하드웨어에 따라 달라지는 실제 한계를 기반으로 정해집니다.
아래 문을 사용하면 시스템에서 사용할 수 있는 최대 사용자 연결 수에 대한 보고서를 받을 수 있습니다.
SELECT @@max_connections 각 사용자에게 허용할 연결 수를 결정하기 위한 공식은 없습니다. 연결 수는 시스템 및 사용자 요구 사항에 따라 설정해야 합니다. DB-Library 또는 ODBC 응용 프로그램을 실행하는 사용자들이 한 응용 프로그램에서 여러 연결을 열 수도 있습니다. 많은 사용자가 있는 시스템에서는 사용자들이 가끔씩만 필요한 연결을 공유할 가능성이 높습니다.
이 구성 옵션은 사용 가능한 잠금 수를 설정합니다. 잠금은 열린 데이터베이스 및 데이터베이스 개체의 공유 방식으로 공유되지 않습니다. 기본값은 5,000입니다.
SQL Server가 사용 가능한 잠금 수를 초과했다는 메시지를 표시하면 이 값을 늘려야 합니다. 잠금마다 32바이트의 메모리가 사용되기 때문에 이 값을 늘리면 SQL Server 전용 메모리의 양도 늘려야 할 수 있습니다.
이 구성 옵션은 SQL Server의 메모리 요구 사항이 충족된 후 프로시저 캐시에 할당되는 메모리 백분율을 지정합니다. SQL Server에 필요한 메모리는 잠금, 열린 데이터베이스, 열린 개체, 사용자 연결, 코드 자체 및 전역 리소스에 필요한 메모리를 합한 양입니다. 나머지 메모리는 이 구성 옵션으로 설정한 백분율에 따라 프로시저 캐시와 데이터 캐시 간에 분할됩니다.
프로시저 캐시는 가장 최근에 사용된 프로시저가 저장된 메모리 영역을 말합니다. 프로시저 캐시는 프로시저를 만들 때 그리고 쿼리를 컴파일할 때도 사용됩니다. 프로시저 캐시 구성 옵션의 기본값은 30입니다. 이 기본값은 SQL Server 요구 사항이 충족된 후 나머지 메모리의 30%를 프로시저 캐시에 할당합니다. 그리고 나머지 70%는 데이터 캐시가 사용합니다.
이 구성 옵션의 최적 값은 응용 프로그램마다 다르므로 이 값을 다시 설정하면 SQL Server의 성능을 향상시킬 수 있습니다. 예를 들어, 서로 다른 많은 프로시저 또는 임의 쿼리를 실행하는 경우, 응용 프로그램이 프로시저 캐시를 더 많이 사용할 것이므로 사용자가 이 값을 늘리고자 할 수 있습니다. 개발 과정에 있는 많은 응용 프로그램이 이 범주에 속합니다.
데이터 캐시 적중률을 높이기 위해 메모리를 추가하는 경우 프로시저 캐시의 크기가 비교적 일정하게 유지되도록 프로시저 캐시의 백분율이 낮아질 수 있습니다.
DBCC 문의 MEMUSAGE 옵션은 메모리 사용에 관한 자세한 보고서를 제공합니다. 이 옵션은 아래와 같은 세 종류의 정보를 제공합니다.
프로시저 캐시에 개체의 복사본이 여러 개 있으면 DBCC MEMUSAGE는 이들이 사용하는 메모리를 모두 합하여 총 메모리를 산출합니다. 이들 복사본 중 일부는 미리 컴파일된 개체 버전(트리)일 것이고 일부는 컴파일된 버전(계획)일 것입니다. DBCC MEMUSAGE는 트리와 계획의 크기를 비롯하여 캐시에 포함된 이들의 총 개수를 표시합니다.
tempdb 데이터베이스는 일부 조인 작업에서 임시 테이블을 만들고 정렬하기 위한 작업 공간으로 사용됩니다. 또한 프로그래머들이 명시적 임시 테이블이나 임시 저장 프로시저를 만들 때도 이 테이터베이스가 사용됩니다.
tempdb in RAM 구성 옵션은 tempdb가 완전히 메모리 상주 데이터베이스가 될 수 있도록 합니다. 이를 통해 일부 특정한 상황에서 성능 이점을 얻을 수 있습니다. 그러나 tempdb in RAM을 잘못 사용하면 SQL Server 데이터 캐시에 사용되어야 할 메모리가 이 구성 옵션에 사용되어 성능이 떨어질 수 있습니다.
대부분의 경우, 사용 가능한 RAM은 tempdb의 위치가 아닌 데이터 캐시에 사용되는 것이 가장 좋습니다. tempdb의 데이터는 SQL Server 데이터 캐시의 LRU(최근에 사용되지 않은 것부터 사용) 알고리즘을 사용하여 캐시에 저장됩니다.
tempdb in RAM을 사용하면 tempdb 동작 속도가 빨라질 수 있지만 SQL Server 데이터 캐시에 사용할 수 있는 메모리가 적어지기 때문에 캐시 적중률이 낮아질 수 있습니다. tempdb in RAM에 사용되는 메모리는 memory 옵션으로 설정한 풀 집합과는 별도로 할당되므로 그에 맞게 서버를 구성해야 합니다.
예를 들어, tempdb in RAM에 100MB를 사용하는 경우 여기에 필요한 메모리를 확보하기 위해 memory 설정으로 할당한 메모리를 100MB 줄여야 할 것입니다. 이와는 반대로, tempdb in RAM에 메모리를 할당하지 않고 사용 가능한 모든 메모리를 SQL Server에 할당하면 캐시 적중률을 높일 수 있습니다. SQL Server는 tempdb와 관련된 동작을 포함하여 모든 디스크 I/O 동작을 캐시에 저장합니다.
여러 시스템에서 사용할 수 있는 RAM이 제한된 경우 tempdb가 RAM에 있으면 tempdb에 허용되는 크기가 제한됩니다. tempdb에 대해 예기치 않은 확장 요구 사항이 생기면 이것이 문제가 될 수 있습니다. 이런 우려가 있는 경우 RAM에 tempdb를 두지 마십시오.
일반적으로, 사용 가능한 RAM을 SQL Server 데이터 캐시에 사용하는 것이 tempdb in RAM에 대량의 메모리를 사용하는 것보다 낫습니다. 그러나 아래의 모든 조건이 성립되는 경우에는 tempdb in RAM을 사용하는 것이 효과적입니다.
RAM에 tempdb를 배치하기로 결정한 경우 이를 통해 얻을 수 있는 성능 이점을 객관적으로 검토하는 것이 좋습니다. 이를 위해 아래 작업을 수행합니다.
성능이 별로 향상되지 않은 경우 RAM을 SQL Server 데이터 캐시에 반환하는 것이 좋습니다.
RAM에 tempdb를 배치하는 것은 위험하지 않으며 데이터베이스 무결성이나 복구 가능성에 나쁜 영향을 미치지 않습니다. 이는 tempdb가 중간 작업에만 사용되고 각 서버를 다시 시작할 때 완전히 재구성되기 때문입니다.
tempdb in RAM 옵션은 분석을 통해 그 유용성이 확인된 경우에 사용할 수 있는 중요한 성능 향상 도구입니다. 이 옵션을 사용하여 성능을 많이 향상시킬 수도 있지만 무분별하게 사용해서는 안 됩니다.
RAM의 tempdb를 포함하여 tempdb 데이터베이스를 확장할 수 있습니다. 그러나 서버를 종료했다 다시 시작하지 않고 RAM에 배치된 tempdb를 변경할 수 있는 횟수는 10회뿐입니다. RAM에 있는 tempdb를 변경하면 데이터베이스가 변경될 때마다 새로운 인접 메모리 "청크"가 tempdb에 할당됩니다. 인접해 있기는 하지만 이 메모리가 반드시 RAM에 있는 tempdb의 기존 부분 옆에 배치되는 것은 아닙니다. 최적의 성능을 얻으려면 tempdb를 변경한 후 서버를 종료하고 다시 시작해야 합니다.
구성 값을 다시 0으로 설정하여 RAM에서 tempdb를 제거할 수 있습니다. 이렇게 하면 기본 크기 2MB를 가진 tempdb가 기본 장치에 생성됩니다.
참고 업그레이드할 때 tempdb가 RAM에 있으면 곧 RAM에서 tempdb가 제거됩니다. tempdb를 만들려면 기본 디스크 장치에 최소한 2MB의 사용 가능한 메모리가 있어야 합니다. 그렇지 않으면 SQL Server가 시작되지 않습니다. 임시로 RAM에 2MB의 tempdb를 만들려면 명령줄에서 SQLSERVR.EXE와 함께 /f 플래그를 사용합니다.
데이터 캐시는 디스크에서 읽어들인 페이지를 보관하는 데 사용되는 버퍼 또는 페이지 프레임 집합입니다. 데이터 캐시는 SQL Server에 할당된 메모리에 포함됩니다. 또한 데이터 캐시를 페이지 캐시 또는 버퍼 캐시라고도 합니다.
조정할 수 있는 가장 중요한 한 가지 구성이 SQL Server에 할당된 메모리 양일 것입니다. 이는 데이터 캐시 양이 할당된 메모리 양에 정비례하기 때문입니다. 페이지가 데이터 캐시에 있느냐 그렇지 않느냐에 따라 페이지에 액세스할 때 물리적 읽기가 이루어는지 여부가 결정되며, 디스크를 물리적으로 읽는 것은 SQL Server가 수행할 수 있는 작업 중 비용이 가장 많이 드는 작업에 속합니다.
지금부터 이 절에 사용되는 용어 몇 가지를 소개합니다.
데이터 수정 문에 의해 변경되었지만 아직 디스크에 쓰이지 않은 데이터 페이지를 말합니다.
어떤 프로세스도 사용하지 않는 커밋된 페이지를 말합니다.
최근에 사용되지 않은 것부터 최근에 사용된 것의 순서로 정렬한 연결된 페이지 목록을 말합니다.
SQL Server가 데이터 캐시의 페이지를 빨리 찾거나 이들이 캐시에 없다는 것을 확인할 수 있도록 데이터 캐시의 페이지가 해시됩니다. hash buckets 구성 옵션은 메모리의 데이터 캐시 버퍼에 페이지를 해시하는 데 사용될 "버킷" 수를 설정합니다. 해시 버킷 수가 많을수록 SQL Server가 참조된 페이지를 더 빨리 찾을 수 있습니다. 해시 버킷이 사용된 페이지 목록의 참조된 페이지에 아주 가깝게 해시된 다음 정확한 페이지를 찾을 때까지 체인을 통과하기 때문입니다.
목적은 특정 해시 버킷에 대한 체인 크기를 제한하는 것입니다. 버킷이 많을수록 체인이 더 짧아집니다. 적당한 수의 해시 버킷만 사용하면 대용량 데이터 캐시도 빠르게 검색할 수 있습니다.
해시 버킷의 기본값은 약 8K입니다. 각 해시 버킷이 최적의 길이(4페이지)를 가진 경우 SQL Server는 아래와 같은 데이터 캐시 크기를 지원할 수 있습니다.
4 x 8K = 32K 페이지 = 64MB
최대 해시 버킷 수(256K)와 최적의 해시 체인 길이를 사용하면 SQL Server가 아래와 같은 데이터 캐시 크기를 지원할 수 있습니다.
4 x 256K = 1024K 페이지 = 2GB
체인 길이를 모두 4페이지 이내로 구성하는 것이 가장 좋습니다. 체인이 이보다 길면 hash buckets 구성 옵션의 값을 늘립니다.
이러한 해시 버킷은 메모리를 아주 조금밖에 사용하지 않으며 SQL Server는 해시 버킷의 실제 수를 입력된 값에 가장 가까운 수로 조정합니다. 기본값은 7,993개이고 최대값은 265,003개입니다.
DBCC BUFCOUNT를 사용하여 현재의 해시 구성이 얼마나 효율적인지 검사할 수 있습니다.
DBCC TRACEON(3604) 검사점 프로세스는 주로 절전 모드일 때 SQL Server에서 항상 실행되는 시스템 프로세스입니다. sp_who 프로시저를 실행하여 이를 확인할 수 있습니다. 검사점 프로세스는 1분마다 활성화되어 각 데이터베이스의 트랜잭션 로그를 검사합니다. 검사점 시스템 프로세스를 통해 마지막 검사점 이후 충분한 작업이 수행되었다는 것이 확인되면 SQL Server는 그 데이터베이스에 대해 다른 검사점을 지정합니다.
충분한 작업이 수행되었는지 여부는 recovery interval 구성 옵션 값으로 결정합니다. 이 값은 분 단위로 설정됩니다. 검사점 시스템 프로세스는 설정된 시간만큼의 복구 시간이 걸릴 정도로 충분한 트랜잭션이 로그에 있는지 확인합니다.
CHECKPOINT 문은 DBO 또는 SA를 사용하여 수동으로 실행할 수 있습니다.
SQL Server는 검사점을 만들 때 커밋되지 않은 모든 데이터 페이지를 디스크에 씁니다. 검사점이 검사되는 데이터베이스의 페이지들을 빠르게 찾을 수 있도록 각 데이터베이스의 페이지들이 서로 연결됩니다.
SQL Server가 자동으로 지정하든 CHECKPOINT 문을 사용하여 수동으로 지정하든 상관없이 일단 지정된 검사점은 두 단계를 거칩니다. 1단계에서는 SQL Server가 플러시되어야 하는 모든 페이지에 표시를 합니다. 추적 플래그 3502를 실행하여 검사점 로깅이 활성화되면 SQL Server 오류 로그는 아래와 비슷한 정보를 얻습니다.
Ckpt dbid 6 started (4000) (검사점 시작) 2단계에서는 검사점 스레드가 표시된 모든 페이지를 플러시합니다. 이 단계는 플러시할 페이지 수, 디스크 하위 시스템의 속도, 서버의 포화 수준 및 기타 요인에 따라 1단계보다 훨씬 오래 걸립니다. 커밋되지 않은 모든 데이터 페이지가 데이터 캐시에서 디스크로 플러시되면 검사점이 완료됩니다.
검사점 프로세스 동안 단일 페이지 쓰기 횟수(개체: SQLServer, 카운터: I/O - Single Page Writes/sec)가 늘어날 수 있습니다. 이 카운터는 일반적으로 0입니다. 검사점 프로세스 동안, 새 트랜잭션이 검사점 스레드에 의해 아직 플러시되지 않은 데이터 페이지를 요청하면 SQL Server는 요청이 들어오는 대로 이들 페이지를 플러시합니다. 이는 정상적인 동작입니다.
SA가 SHUTDOWN 문을 실행할 때 각 데이터베이스에 대해 검사점이 지정됩니다. SQL Server가 시작될 때 그리고 각 데이터베이스가 복구된 후에도 검사점이 지정됩니다.
참고 trun. Log on chkpt. 데이터베이스 옵션을 설정하면 시스템 검사점 프로세스가 활성화될 때마다 로그가 잘립니다.
SQL Server는 지연 기록기라는 시스템 프로세스를 지원하며 sp_who를 실행하면 이를 볼 수 있습니다. 이 프로세스는 사용 가능한 버퍼 수가 free buffers 구성 옵션으로 설정한 임계값보다 적을 때 버퍼 플러시를 자동으로 시작합니다.
지연 기록기 프로세스가 있기 때문에 사용 가능한 버퍼를 만들기 위해 검사점을 자주 지정하지 않아도 됩니다. 지연 기록기가 사용하는 일괄 I/O 크기를 max lazywrite IO 구성 옵션을 사용하여 설정할 수 있습니다.
SQL Server는 아래와 같은 상황일 때 캐시에서 디스크로 페이지를 플러시합니다.
현재 캐시에 없는 페이지가 필요하지만 사용 가능한 버퍼 목록에 버퍼가 없다는 것이 확인되면 SQL Server는 캐시 페이지 체인에서 사용 가능한 버퍼를 찾습니다. 체인 검색이 끝나면 가장 오래된 버퍼의 페이지가 디스크로 플러시됩니다.
사용 가능한 미사용 버퍼가 없고 사용 가능한 버퍼 목록에도 버퍼가 없는 경우, 페이지를 사용할 수 있을 때까지 페이지 요청 프로세스가 일시적으로 중단됩니다. 빠른 bcp(로그되지 않는 버전)를 사용할 때 또는 SELECT INTO를 사용하여 테이블을 만들 때 새로 삽입된 행은 로그에 기록되지 않습니다. 또한 이들 대부분 또는 모두가 여전히 캐시에 있을 수 있습니다. SQL Server가 검사점 없이 중단되는 경우에 데이터 손실을 최대한 막기 위해, 커밋되지 않은 모든 데이터 페이지는 bcp 배치 또는 SELECT INTO가 끝날 때 디스크로 플러시됩니다. 트랜잭션이 커밋되거나 중단되면 로그 페이지가 디스크로 플러시됩니다. 페이지가 분할되면 새로 할당된 페이지가 즉시 디스크로 플러시됩니다. LOAD DATABASE 문을 사용하여 데이터베이스가 복원될 때 덤프의 모든 페이지가 디스크에 직접 쓰입니다. 또한 백업의 일부가 아닌 데이터베이스의 모든 페이지도 초기화되어 디스크로 플러시됩니다. 예를 들어, 4MB 데이터베이스에서 10MB 데이터베이스로 백업을 로드하는 경우 나머지 6MB의 페이지가 모두 초기화되어 디스크에 쓰입니다. 지연 기록기 프로세스는 사용 가능한 버퍼 수가 특정 임계값보다 적으면 버퍼 플러시를 자동으로 시작합니다. 플러시에 사용할 수 있는 버퍼가 없으면 지연 기록기는 오류 로그에 메시지를 씁니다. 지금 소개하는 세 종류의 디스크 하위 시스템을 통해 디스크 하위 시스템 종류마다 비동기 I/O의 성능 효과가 얼마나 달라지는지 확인할 수 있습니다. 맨 먼저 4개의 디스크 드라이브에 장착된 비인텔리전트 컨트롤러를 소개합니다. 그 다음은 각각 단일 디스크 드라이브에 장착된 4개의 비인텔리전트 컨트롤러입니다. 마지막으로 4개의 디스크 드라이브에 장착된 단일 인텔리전트 컨트롤러를 소개합니다.
먼저, 단일 컨트롤러와 4개의 드라이브를 사용할 때 데이터 전송이 어떻게 이루어지는지 살펴보겠습니다. 아웃바운드 전송 시퀀스에서, 장치 드라이버는 컨트롤러의 내장 버퍼로 데이터 버퍼를 보냅니다. 이 동작은 일반적으로 직접 메모리 액세스(DMA), 공유 메모리 또는 프로그램된 I/O를 사용하여 아주 빠르게(일반적인 버스 속도에서 몇 백 밀리초 이내) 이루어집니다. 그런 다음 컨트롤러는 장치 드라이버로부터 여러 가지 지원을 받아 드라이브에서 필요한 찾기 동작을 수행해야 합니다. 이 동작에는 최대 50밀리초가 걸릴 수 있으며 이는 버스와 컨트롤러 간의 전송보다 수백 배나 긴 시간입니다.
이제 드라이브 종류에 따라 결정되는 전송 속도를 사용하여 컨트롤러 버퍼에서 디스크 드라이브로 실제 데이터가 전송됩니다. 전송이 시작되기 전에 회전 대기 시간도 있을 수 있습니다. 상당수의 시스템에서, 이 대기 시간 동안 장치 드라이버 및 장치 드라이버를 호출한 작업은 하드 디스크 드라이브가 완료되기만 기다려야 합니다. 첫째 드라이브가 완료될 때까지는 둘째 드라이브와 그 다음 드라이브에서 작업을 수행할 수 없습니다. 이는 대기 중인 여러 작업을 추적하는 데 필요한 논리를 컨트롤러가 가지고 있지 않기 때문입니다.
각각 자체의 드라이브에 장착된 4개의 컨트롤러를 사용하는 경우, Windows NT 스트라이프를 사용하면 둘째 또는 그 다음의 컨트롤러나 드라이브에서 전송 시퀀스가 즉시 시작될 수 있습니다. 이 경우 4개의 드라이브가 각각 자체의 컨트롤러를 사용하여 전송을 추적하기 때문에 이 드라이브들의 전송 단계가 서로 다를 수 있습니다.
이 하드웨어 구성에 Windows NT 비동기 I/O를 사용하면 도움이 될 수 있습니다. 처리되지 않은 I/O 풀을 만들 수 있고 드라이브 하위 시스템이 이 풀을 한 번에 4개씩 병렬로 처리할 수 있기 때문입니다. 드라이브 하위 시스템의 요청 처리 속도가 다를 수 있기 때문에, 하위 시스템의 용량이 최대한 사용되도록 처리되지 않은 SQL Server 요청 풀을 만드는 것이 좋습니다. 시스템 고유의 여러 가지 요소에 따라, 다수의 비동기 I/O를 허용하도록 SQL Server를 다시 구성하는 것이 유용할 수도 있습니다. 그러나 대다수 시스템의 확장 용량 때문에 드라이브마다 하나의 컨트롤러를 사용하기가 힘듭니다.
기술이 발전함에 따라 이제 여러 비인텔리전트 컨트롤러의 기능을 단일 인텔리전트 컨트롤러에 효과적으로 포함할 수 있게 되었습니다. 컨트롤러는 장치 드라이버로부터 많은 I/O 요청을 빠르게 받아들인 다음 보통 RAID 어레이로 스트라이프되는 장착 드라이브에 요청을 효과적으로 동시 전송할 수 있습니다. 이 경우 컨트롤러의 기능과 구성에 따라, 많은 비동기 I/O를 허용하도록 SQL Server를 다시 구성하면 성능이 향상될 수 있습니다. 서버와 컨트롤러에 따라, 특정 서버나 컨트롤러에서는 디스크 하위 시스템 구성에 따라 그리고 특정 디스크 하위 시스템 구성에서는 응용 프로그램의 I/O 특성에 따라 실제로 사용되는 값은 달라집니다.
max async IO 구성 옵션은 요청할 수 있는 비동기 I/O 수를 설정합니다. 기본값은 8입니다. 서로 다른 실제 디스크에 존재하는 복수의 실제 데이터베이스 장치에 정의된 데이터베이스를 가진 시스템 또는 디스크 스트라이프를 사용하는 시스템에서만 이 기본값을 변경해야 합니다.
max async IO 구성 옵션의 최적 값은 Microsoft TPC-B Benchmark Kit를 사용하거나, 기본 설정 8로 시작해서 이어지는 테스트마다 값을 조금씩 늘리는 방식으로 자체적인 고객 벤치마크를 실시함으로써 특정 상황에 대한 제어된 테스트를 수행하여 결정하는 것이 좋습니다. 더 이상 성능 향상이 보이지 않으면 그 값이 최적 값입니다. 실제적인 테스트를 수행하지 않을 때는 기본값을 그대로 사용해야 합니다.
구성 옵션을 변경하면 SQL Server를 다시 시작해야 변경 사항이 적용됩니다.
max lazywrite IO 구성 옵션은 지연 기록기가 수행하는 비동기 일괄 처리 I/O의 우선 순위를 조정합니다. max lazywrite IO는 대량 복사, 검사점 같은 일괄 I/O를 제어하는 max async IO와 비슷하지만 지연 기록기 고유의 옵션입니다. 이 옵션은 하드 디스크가 여러 개 있는 시스템에서만 구성해야 합니다. max async IO에서 지정한 값까지 동적으로 이 옵션을 구성할 수 있습니다.
이 구성 옵션을 변경하면 변경 사항이 즉시 적용됩니다.
이 구성 옵션은 시스템이 사용할 수 있는 사용 가능한 버퍼의 임계값을 결정합니다. 최소값은 20이며 최대값은 서버가 시작될 때 사용할 수 있는 버퍼 수의 절반입니다. 지연 기록기 프로세스는 시스템이 사용할 수 있는 사용 가능한 버퍼 수가 이 임계값 아래로 내려가지 않도록 합니다.
이 옵션은 memory 옵션이 변경될 때마다 시스템에 의해 자동으로 변경됩니다. free buffers는 사용 가능한 메모리의 5%로 설정됩니다. memory 옵션이 변경되면 free buffers가 변경되었다는 메시지가 나타납니다. 그러면 free buffers를 유효한 값으로 직접 재구성할 수 있습니다.
이 구성 옵션은 정렬에 할당되는 사용자 당 최대 페이지 수를 지정합니다. 대량 정렬을 수행하는 시스템에서 이 값을 늘리면 성능이 향상될 수 있습니다. 추가 정렬 페이지는 메모리를 소모하기 때문에 이 값을 늘리면 서버 전용 메모리의 양도 늘려야 할 수 있습니다.
이 구성 옵션은 페이지를 메모리의 버퍼에 해시하는 데 사용되는 버킷 수를 설정합니다. 지정된 값이 최적 값이 아닌 경우 최적 값에 가장 가까운 값이 사용됩니다. 예를 들어, 8,000을 지정하면 7,993개의 해시 버킷(기본값)이 만들어집니다. 대량의 메모리가 있는 시스템에서는 데이터 캐시의 데이터에 더욱 빠르게 액세스하기 위해 이 값을 늘릴 수 있습니다. 메모리가 160MB 이하인 시스템에서는 7,993이 적당한 값입니다. 이 옵션은 서버를 종료한 후 다시 시작해야 적용됩니다.
이 구성 옵션은 시스템 장애 발생 시 SQL Server가 복구 절차를 완료하는 데 필요한 데이터베이스 당 최대 시간(분)을 설정합니다. 기본값은 5분입니다.
응용 프로그램 및 응용 프로그램 사용 상태가 바뀌면 recovery interval을 변경할 수 있습니다. 예를 들어, 업데이트 동작이 많을 때 변경 사항이 디스크에 자주 쓰이도록 하려면 recovery interval을 줄일 수 있습니다. recovery interval을 줄이면 검사점이 더 자주 지정되기 때문에 시스템 속도가 약간 느려집니다. 그러나 recovery interval을 너무 높게 설정하면 복구 시간이 지나치게 길어질 수 있습니다.
sp_tableoption @TableNamePattern [, 'pintable'] [, true | false ]
pintable 옵션을 true로 설정하면 SQL Server가 테이블과 그 모든 인덱스를 데이터 캐시에 유지합니다. 고정된 테이블에 속하는 페이지들은 새 페이지를 위한 공간 확보를 위해 데이터 캐시 밖으로 플러시되지 않습니다. 고정된 테이블에 대한 모든 수정 사항은 로그에 기록되며 지연 기록기와 검사점은 고정된 모든 테이블의 커밋되지 않은 데이터 페이지를 일반적인 방식대로 씁니다.
sysobjects 테이블에 상태 비트를 설정하여 테이블을 고정된 테이블로 표시할 수 있습니다.
이 저장 프로시저는 테이블과 인덱스를 자동으로 메모리로 가져오지 않습니다. 즉, 데이터 및 인덱스 페이지는 액세스될 때 데이터 캐시로 들어온 다음 SQL Server가 중지되거나 pintable의 값이 false로 설정될 때까지 계속 데이터 캐시에 남아 있습니다. 고정된 테이블을 메모리로 빠르게 가져오려면, 클러스터되지 않은 인덱스가 없는 열을 사용하여 SELECT COUNT(열_이름) FROM 테이블_이름 같은 간단한 명령으로 액세스하면 됩니다.
자주 사용되는 특정 테이블을 고정하면 특정 환경에서 성능을 크게 향상시킬 수 있습니다. 언제나 테이블을 고정한 후에도 데이터 캐시의 양이 충분히 남아 있도록 해야 합니다.
와일드카드를 사용하여 테이블 이름을 지정할 수 있습니다. 이렇게 하면 여러 테이블을 고정하거나 해제할 수 있습니다. true 또는 false 값을 지정하지 않으면 이 명령은 지정된 테이블의 현재 pintable 설정 값을 반환합니다.
참고 테이블 크기에는 제한이 없지만 테이블이 크면 캐시가 소모될 수 있습니다.
추적 플래그 1081은 인덱스 페이지가 데이터 캐시에 유지될 수 있도록 합니다. 새 페이지를 가져오기 위해 어떤 인덱스 페이지를 캐시 밖으로 플러시해야 하는 경우, SQL Server는 다른 페이지를 선택합니다. 단, 이 특정 인덱스 페이지를 건너뛴 적이 없어야 합니다. 따라서 인덱스 페이지가 데이터 캐시에 더 오래 유지될 수 있습니다.
미리 읽기를 병렬 데이터 스캔(PDS)이라고도 합니다. SQL Server는 이 방법을 사용하여 쿼리를 처리하는 데 필요한 물리적 읽기 횟수를 줄입니다. 특정 개수의 필요한 페이지가 캐시에 없으면, SQL Server는 현재의 SQL Server 프로세스에 필요한 페이지를 읽을 다른 스레드를 시작할 수 있습니다.
SQL Server가 데이터 수평 스캔을 수행할 때마다 미리 읽기가 시작될 수 있습니다. 수평 스캔에는 테이블 스캔, 클러스터되지 않은 인덱스에 대한 잎 수준 인덱스 스캔, DBCC 문 및 UPDATE STATISTICS가 포함될 수 있습니다.
SQL Server가 수평 데이터 스캔을 수행하는 동안 이 페이지 개수가 데이터 캐시에서 발견되지 않으면 미리 읽기가 시작됩니다. RA cache miss limit을 1로 설정하면 디스크에서 데이터 페이지가 액세스될 때마다 미리 읽기 요청이 이루어지며 이로 인해 스래싱이 발생하고 성능이 떨어질 수 있습니다.
이 값은 미리 읽기(RA) 관리자가 얼마나 멀리까지 미리 읽어야(확장 영역 단위로) 미리 반입 관리자가 유휴 상태가 되는지 결정합니다. 값이 3이면, 제출된 각 요청에 대해 RA 관리자가 페이지 체인에서 현재 스캔 위치보다 확장 영역 3개를 앞서갑니다.
이 개수의 필요한 페이지가 캐시에서 발견되면 미리 읽기가 중지되었다가 그 후에 처음으로 손실이 발견되면 다시 시작됩니다. 이 옵션은 미리 읽기 관리자가 캐시에서 모든 것을 발견하는 상황을 알아내는 데 사용되며 쿼리에는 거의 도움이 되지 않습니다. 대부분의 시스템에서는 기본값 4로 충분합니다.
각 스레드는 구성 가능한 구조 수를 관리합니다(RA slots per thread 옵션 참조). 여기서 이러한 각 구조(슬롯)는 개별적인 범위 스캔을 나타냅니다. 이 옵션은 시스템에 대한 최대 동시 사용자 수로 설정해야 합니다. 미리 읽기 스캔을 요청하는 스레드 수가 구성된 RA 슬롯 수를 초과하면 오류 로그에 경고가 기록됩니다. 이 값을 0으로 설정하면 미리 읽기가 사용 해제됩니다.
이 값은 각각의 미리 읽기 서비스 스레드가 관리할 동시 요청 수를 지정합니다. 스레드 수에 슬롯 수를 곱하면 시스템이 지원할 동시 미리 읽기 스캔의 총 개수가 됩니다. 대부분의 시스템에서는 기본값이면 충분합니다. 시스템에 효율적인 I/O 하위 시스템이 갖춰져 있으면 단일 스레드가 처리할 수 있는 스캔 수를 늘릴 수 있습니다.
쿼리 스레드가 미리 읽기를 호출할 때 그 시간과 운영 체제가 미리 읽기 스레드를 활성화하는 시간 사이에 약간의 지연이 있을 수 있습니다. 이 지연 옵션은 쿼리 스레드가 얼마 동안 대기해야 작업을 다시 시작할 수 있는지 설정합니다. 이는 미리 읽기 스레드가 시작된 다음 쿼리 스레드 작업이 계속되도록 하기 위해서입니다. 이 값을 0으로 설정하면 기본적으로 미리 읽기가 비활성화됩니다. 쿼리 스레드가 항상 미리 읽기가 활성화되기 전에 다음 페이지에 액세스하기 때문입니다.
DBCC SQLPERF(RASTATS)는 4가지 통계를 반환합니다. 아래 출력 예제를 참조하십시오.
Statistic Value 아래 표는 반환된 4가지 값의 의미를 정리한 것입니다.
프로시저 캐시에는 아래 요소들이 포함됩니다.
캐시 페이지 수만큼의 프로시저 버퍼 슬롯이 고정 배열로 저장됩니다. 각 프로시저 버퍼 슬롯은 122바이트의 프로시저 캐시를 사용합니다. DBCC MEMUSAGE 출력에서 프로시저 버퍼 배열을 유지하는 데 필요한 공간을 프로시저 헤더라고 합니다. 사용된 각 프로시저 버퍼 슬롯은 프로시저 헤더를 가리킵니다. 이 헤더는 캐시에서 프로시저 계획 또는 트리의 첫째 페이지입니다. 이 첫째 페이지에는 계획이나 트리에 있는 다른 페이지의 주소 같은 메모리 관리 정보가 포함됩니다. 또한 호출 프로시저(있는 경우)는 물론 계획의 첫째 문에 대한 포인터도 포함됩니다. 이 구조는 처음 2K 페이지의 606바이트를 사용합니다. 2K 페이지의 나머지 바이트는 계획 또는 트리에 사용할 수 있습니다. 계획이나 트리의 크기에 따라 이들과 관련된 많은 추가 페이지가 있을 수 있습니다. DBCC MEMUSAGE 출력에서 프로시저 캐시로 사용될 사용 가능한 공간을 프로시저 캐시 버퍼라고 합니다. 같은 프로시저에 여러 계획이 있으면 이들 각자가 자체의 프로시저 버퍼 슬롯과 프로시저 헤더를 가집니다.
procedure cache 값은 새 저장 프로시저를 만들고 새 쿼리를 컴파일하기 위해 SQL Server가 가장 최근에 사용된 저장 프로시저를 저장하는 데 사용할 메모리 양을 지정합니다.
지정된 값에 따라 SQL Server 메모리 요구 사항이 충족된 후 프로시저 캐시에 할당되는 메모리 백분율이 결정됩니다. SQL Server에 필요한 메모리는 잠금, 사용자 연결, 코드 자체 등에 필요한 메모리를 합한 양입니다. 나머지 메모리는 이 구성 옵션에 의해 설정된 백분율에 따라 프로시저 캐시와 데이터 캐시 간에 분할됩니다.
저장 프로시저, 트리거, 뷰, 규칙, 기본값 등에 필요한 프로시저 캐시의 양은 이들의 개수와 크기에 따라 달라집니다. 같은 저장 프로시저에 여러 사용자가 액세스하는 경우 캐시에 미사용 복사본이 없으면 SQL Server가 프로시저 계획의 복사본을 또 하나 만들어야 합니다.
이 매개 변수를 변경하면 SQL Server를 다시 시작해야 변경 사항이 적용됩니다.
DBCC MEMUSAGE를 사용하여 프로시저 캐시에 있는 가장 큰 20개의 프로시저를 모니터할 수 있습니다.
DBCC MEMUSAGE의 출력 내용은 프로시저 캐시에 필요한 공간을 예측하는 데 도움이 될 수 있습니다. 이 출력에 캐시에 있는 계획의 크기가 제공되므로 이 수를 예상되는 동시 사용자 수와 곱할 수 있습니다.
프로시저 캐시는 아래의 두 방식 중 하나로 구성될 수 있습니다.
프로시저 캐시가 가득 차서 새 프로시저를 실행할 수 없는 경우 아래와 같은 오류 701이 반환됩니다.
There is insufficient system memory to run this query. 개체: SQLServer – Procedure Cache Procedure Cache Size 카운터는 2K 페이지의 프로시저 캐시 크기를 보고합니다. memory 또는 procedure cache 구성 옵션 값을 변경한 다음 SQL Server를 다시 시작하지 않는 한 이 카운터는 변경되지 않습니다.
Procedure Cache Used % 카운터는 캐시된 저장 프로시저, 트리거, 뷰, 규칙 및 기본값이 사용하는 프로시저 캐시의 백분율을 모니터합니다.
장기적인 관점에서 Procedure Cache Used % 카운터를 90-95% 정도로 만드는 것이 이상적입니다.
Procedure Cache Used % 카운터가 이보다 훨씬 낮은 경우 프로시저 캐시에 메모리를 너무 많이 할당한 것이며 데이터 캐시에 사용할 수 있는 메모리를 낭비하고 있는 것입니다. 따라서 프로시저 캐시 값을 낮추고 SQL Server를 다시 시작한 다음 이 카운터를 계속 모니터해야 합니다.
Procedure Cache Used % 카운터가 지속적으로 95% 이상인 경우 프로시저 캐시를 충분히 할당하지 않은 것입니다. 이런 경우 프로시저 캐시 구성 옵션의 값을 늘리고 SQL Server를 다시 시작한 다음 모니터링 프로세스를 계속해야 합니다.
이 구성 옵션은 SQL Server 프로세스에 사용할 수 있는 작업자 스레드 수를 구성합니다. SQL Server는 운영 체제 고유의 스레드 서비스를 사용합니다. 작업자 스레드는 하나가 아니라 여러 개 있습니다. SQL Server가 동시에 지원하는 각 네트워크는 하나 이상의 스레드에 의해 지원됩니다. 또 다른 스레드는 데이터베이스 검사점을 처리하며 스레드 풀은 모든 사용자를 처리합니다.
max worker threads 옵션을 사용하면 사용자 풀에 할당되는 스레드 수를 제어할 수 있습니다. 사용자 연결 수가 max worker threads보다 적으면 한 스레드가 각 연결을 처리합니다. 그러나 연결 수가 max worker threads보다 많으면 스레드 풀링이 이루어집니다. 또한 설정된 작업자 스레드 값이 초과되면 현재 작업을 완료하는 그 다음 작업자 스레드가 그 요청을 처리합니다. 기본값은 255입니다.
이 구성 옵션은 버퍼가 가득 차지 않은 경우 로그에 대한 쓰기를 얼마 동안(밀리초) 지연시킬 것인지 지정합니다. 이 옵션을 사용하면, 다른 사용자가 더 많은 데이터를 로그 버퍼에 추가할 기회가 많아지기 때문에 필요한 물리적 로그 쓰기가 줄어듭니다. 이 옵션에 허용되는 값은 1에서 500까지입니다. 특별한 값인 -1을 사용하면 로그 쓰기가 지연되지 않습니다. 기본값은 0이며 이 값을 사용하면 다른 사용자가 실행할 준비가 되었을 때만 서버가 대기합니다.
이 매개 변수에 대한 변경 사항은 즉시 적용됩니다.
이 구성 옵션은 SQL Server가 동일 컴퓨터의 다른 프로세스보다 높은 순위로 실행되도록 할 것인지 여부를 결정합니다. 이 옵션을 1로 설정하면 SQL Server가 더 높은 우선 순위로 실행됩니다. 기본값은 0이며 SQL Server 전용 Windows NT 시스템에서만 변경해야 합니다. 네트워크 등 필요한 다른 프로세스가 중단되지 않도록 주의해야 합니다.
이 구성 옵션은 SQL Server가 실행을 위해 Windows NT에 보낼 스레드 수를 제어합니다. 이는 사실상 SQL Server가 사용하는 CPU 수를 제한합니다. 단일 프로세서 컴퓨터인 경우 최적 값은 1입니다. 대칭 다중 프로세서(SMP) 컴퓨터인 경우 서버가 전용 SQL Server인지 여부에 따라 제한이 달라집니다. 전용 서버가 아닌 경우 이 값을 다시 구성하면 동일 시스템에서 실행되는 다른 응용 프로그램에 대한 응답 시간이 느려질 수 있습니다. 다른 응용 프로그램에 대한 응답 시간이 문제가 되지 않는 경우에는 SMP concurrency를 -1, 즉 "전용 SMP 지원"으로 설정하십시오. 이는 어떤 제한도 없다는 뜻입니다.
SQL Server를 설치하면 SMP concurrency가 0으로 설정됩니다. 이는 자동 구성을 뜻합니다. 자동 구성 모드에서는 n-1로 설정됩니다. 여기서 n은 SQL Server를 시작할 때 검색되는 프로세서 수입니다. 단일 프로세서 시스템에서는 이 값이 1로 설정됩니다.
이 매개 변수를 변경하면 SQL Server를 다시 시작해야 변경 사항이 적용됩니다.
이 구성 옵션은 Windows NT가 memory 설정과 tempdb 크기의 합계에 해당하는 실제 메모리 공간(RAM에 있는 경우)을 SQL Server를 위해 예약하도록 합니다.
이 구성 옵션은 기본 네트워크 패킷 크기에 대해 서버 차원의 값을 설정합니다. 클라이언트 응용 프로그램은 이 값을 무시할 수 있습니다. 여러 네트워크 프로토콜을 사용하는 시스템에서는 이 옵션을 가장 일반적으로 사용되는 프로토콜의 크기로 설정해야 합니다. 네트워크 프로토콜이 큰 패킷을 지원하는 경우 이 옵션으로 네트워크 성능을 향상시킬 수 있습니다. 옵션을 재구성하면 변경 사항이 즉시 적용됩니다. 기본값은 4,096입니다.
이 매개 변수에 대한 변경 사항은 즉시 적용됩니다.
데이터베이스 옵션은 데이터베이스 소유자 또는 SA만 변경할 수 있습니다. 데이터베이스 옵션은 마스터 데이터베이스에 저장되기 때문에 데이터베이스 소유자를 가장한 누군가가 이를 변경할 수 없습니다.
아래 데이터베이스 옵션들이 쿼리 성능에 영향을 미칠 수 있습니다.
두 경우 모두, 어떠한 동작에 대해서도 잠금이 이루어지거나 확인되지 않습니다. 해당 문서에서 설명하겠지만 다중 사용자 환경에서는 잠금 경합이 가장 중대한 성능 문제 중 하나입니다.
다른 데이터베이스 옵션들은 더 간접적인 방식으로 성능에 영향을 미칠 수 있습니다. trunc. log on chkpt 옵션은 시스템 검사점 프로세스가 실행될 때마다 추가적인 시스템 오버헤드를 유발합니다. select into/bulkcopy 옵션을 사용하면 빠른 대량 복사 또는 SELECT INTO 작업을 수행할 수 있습니다. 이들은 다른 방법들보다 훨씬 빠릅니다.
테이블 옵션도 성능에 영향을 미칠 수 있습니다. 테이블 옵션은 sp_tableoption을 사용하여 설정합니다. pintable 테이블 옵션에 대해서는 앞에서 이미 설명했습니다. insert row lock 테이블 옵션에 대해서는 해당 문서에서 설명합니다.
세션 옵션은 SET 명령으로 제어하며 세션이 유지되는 동안에만 효력을 나타냅니다. 저장 프로시저에서 세션 옵션을 사용 설정하면 그 저장 프로시저가 종료될 때까지 이들의 효력이 유지됩니다.
아래 세션 옵션들이 쿼리 성능에 영향을 미칠 수 있습니다.
FORCEPLAN
SQL Server 최적화 프로그램 프로세스가 FROM 절에 나타나는 테이블 순서대로 조인하도록 합니다. FORCEPLAN은 기본적으로 최적화 프로그램을 무시합니다.
DEADLOCKPRIORITY {LOW | NORMAL}
교착 상태가 발생할 때 이 세션이 응답할 방식을 제어합니다. LOW로 설정하면 이 프로세스가 교착 상태에서 우선적으로 희생됩니다. NORMAL 옵션을 사용하면 세션을 기본 교착 상태 처리 방법으로 되돌릴 수 있습니다.
TRANSACTION ISOLATION LEVEL
이 연결에 대해 모든 SQL Server SELECT 문의 기본 트랜잭션 잠금 동작을 제어합니다. 해당 문서에서 다양한 값에 대해 설명합니다.
IMPLICIT_TRANSACTIONS
문이 실행될 때 트랜잭션이 묵시적으로 시작되도록 할 것인지 여부를 제어합니다. 이 동작이 성능에 미치는 영향에 대해서는 해당 문서에서 설명합니다.
DISABLE_DEF_CNST_CHK
임시로 지연된 위반 검사를 지정합니다. 이 옵션의 의미 및 성능 영향에 대해서는 해당 문서에서 설명합니다.
© 1997 Microsoft Corporation. All rights reserved.
이 문서에 포함된 정보는 문서를 발행할 때 논의된 문제들에 대한 Microsoft Corporation의 당시 관점을 나타냅니다. Microsoft는 변화하는 시장 환경에 대처해야 하므로 이를 Microsoft 측의 책임으로 해석해서는 안 되며 발행일 이후 소개된 어떠한 정보에 대해서도 Microsoft는 그 정확성을 보장하지 않습니다.
이 설명서는 오직 정보를 제공하기 위한 것입니다. Microsoft는 이 설명서에서 어떠한 명시적이거나 묵시적인 보증도 하지 않습니다.
Microsoft 및 Windows NT는 Microsoft Corporation의 등록 상표입니다.
여기에 인용된 다른 제품이나 회사 이름은 해당 소유자의 상표일 수 있습니다.
Tech-Ed 97에 소개됨
Program Manager
Microsoft Corporation
개요
SQL Server 구성 정보
sysconfigures
syscurconfigs
구성 블록
Windows NT 레지스트리
SQL Server 메모리 및 I/O 관리
SQL Server 메모리 사용
전역 리소스
사용자 연결
잠금, 열린 데이터베이스, 열린 개체
잠금
60바이트
열린 데이터베이스
162바이트
열린 개체
240바이트 프로시저 및 데이터 캐시
메모리 관련 구성 옵션
Memory
RAM(MB) SQL Server 메모리 할당(MB 단위)
SQL Server 메모리 할당(2K 단위)
16
4
2,048
24
8
4,096
32
16
8,192
64
40
20,480
128
100
51,210
256
216
110,592
512
464
237,568 Open Databases
Open Objects
User Connections
Locks
Procedure Cache
DBCC MEMUSAGE
DBCC MEMUSAGE
tempdb in RAM
tempdb in RAM 변경
RAM에서 tempdb 제거
SQL Server 데이터 캐시
커밋되지 않은 데이터 페이지
사용 가능한 페이지
최근에 사용되지 않은 것부터 사용/최근에 사용된 것부터 사용(LRU/MRU) 체인
버퍼 관리자 해시
예제
GO
DBCC BUFCOUNT
GO
**** THE 10 LONGEST BUFFER CHAINS ****
bucket number = 20 chain size = 2
bucket number = 276 chain size = 2
bucket number = 532 chain size = 2
bucket number = 1044 chain size = 2
bucket number = 1300 chain size = 2
bucket number = 1556 chain size = 2
bucket number = 1812 chain size = 2
bucket number = 2324 chain size = 2
bucket number = 3092 chain size = 2
bucket number = 3604 chain size = 2
The Smallest Chain Size is: 0
The Average Chain Size is: 0.671668
검사점
검사점 시스템 프로세스
CHECKPOINT 문
검사점 프로세스 동안 진행되는 작업
Ckpt dbid 6 phase 1 ended (0) (1단계 종료)
Ckpt dbid 6 complete.(2단계 및 검사점 종료)
지연 기록기 프로세스
커밋되지 않은 데이터 페이지 플러시
비동기 I/O
특정 디스크 하위 시스템에서의 성능 수준
4개의 드라이브에 장착된 단일 컨트롤러
개별 드라이브에 장착된 4개의 컨트롤러
4개의 디스크 드라이브에 장착된 단일 인텔리전트 컨트롤러
max async IO 구성
max lazywrite IO 구성
버퍼 관리자 구성
Free Buffers
Sort Pages
Hash Buckets
Recovery Interval
페이지를 캐시에 유지
부분 구문
인덱스 페이지를 캐시에 유지
미리 읽기 처리
미리 읽기 구성
RA Cache Miss Limit
RA Pre-fetches
RA Cache Hit Limit
RA Worker Threads
RA Slots Per Thread
RA Delay
미리 읽기 검토 및 제어
-------------------------------- ------------------------
RA Pages Found in Cache 297.0
RA Pages Placed in Cache 12933.0
RA Physical IO 1644.0
Used Slots 0.0
통계
정의
RA Pages Found in Cache
스캔을 수행하려 할 때 RA 관리자가 캐시에서 발견한 페이지 수
RA Pages Placed in Cache
RA 관리자가 캐시로 가져온 페이지 수
RA Physical IO
RA 관리자가 수행한 16K 읽기 횟수
Used Slots
활성 쿼리에 의해 사용되는 RA 슬롯 수. 단일 쿼리가 여러 개의 RA 슬롯을 사용할 수도 있습니다. SQL Server 프로시저 캐시
프로시저 캐시 구성
프로시저 캐시 크기 조정
프로시저 캐시 구성 옵션
프로시저 캐시 사용 모니터링
성능 모니터 카운터
카운터: Procedure Cache Size 및 Procedure Cache Used %
기타 SQL Server 구성 옵션
Max Worker Threads
Logwrite Sleep
Priority Boost
SMP Concurrency
Set Working Set Size
Network Packet Size
데이터베이스 및 세션 구성
데이터베이스 옵션
single user only테이블 옵션
세션 옵션
최종 수정일 : 2000.5.12
'데이터베이스 > SQL Server' 카테고리의 다른 글
| MS SQL Server 성능 조정 및 최적화, 제 1부: 성능 문제 개요 (0) | 2008.05.06 |
|---|---|
| MS SQL Server 성능 조정 및 최적화, 제 2부: 쿼리 최적화 프로그램 (0) | 2008.05.06 |
| 데이터 웨어하우징용 Microsoft SQL Server 2000 RDBMS (0) | 2008.05.06 |
| 김연욱(Vichang)님의 Inside Query Performance - (2) (0) | 2008.05.06 |
| 김연욱(Vichang)님의 Inside Query Performance - (1) (0) | 2008.05.06 |