SQL Server 데이터 안정성을 확장하는 로깅 및 데이터 스토리지 알고리즘에 대한 설명

아티클
06/20/2023

원래 제품 버전: SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005
원본 KB 번호: 230785

요약

이 문서에서는 Microsoft SQL Server 로깅 및 데이터 알고리즘이 데이터 안정성 및 무결성을 확장하는 방법을 설명합니다.

엔진의 기본 개념과 ARIES(복구 및 격리 악용 의미 체계 알고리즘)에 대한 자세한 내용은 데이터베이스 시스템의 다음 ACM 트랜잭션 문서(볼륨 17, 번호 1, 1992년 3월)를 참조하세요.

외부 링크: ARIES: Write-Ahead 로깅을 사용하여 Fine-Granularity 잠금 및 부분 롤백을 지원하는 트랜잭션 복구 방법

이 문서에서는 오류와 관련된 데이터 안정성 및 무결성을 확장하기 위한 SQL Server 기술을 다룹니다.

캐싱 및 대체 실패 모드 토론에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하는 것이 좋습니다.

이 문서에 사용된 용어

심층 토론을 시작하기 전에 이 문서 전체에서 사용되는 몇 가지 용어가 다음 표에 정의되어 있습니다.

용어	정의
배터리 사용	데이터 손실을 방지하기 위해 캐싱 메커니즘에 의해 직접 사용 가능하고 제어되는 별도의 지역화된 배터리 백업 기능입니다. 이는 UPS(무정전 전원 공급 장치)가 아닙니다. UPS는 쓰기 작업을 보장하지 않으며 캐싱 디바이스에서 연결을 끊을 수 있습니다.
캐시	물리적 I/O 작업을 최적화하고 성능을 향상시키는 데 사용되는 중간 스토리지 메커니즘입니다.
더티 페이지	안정적인 스토리지로 아직 플러시되지 않은 데이터 수정 내용이 포함된 페이지입니다. 더티 페이지 버퍼에 대한 자세한 내용은 SQL Server 온라인 설명서에서 페이지 쓰기를 참조하세요. 콘텐츠는 Microsoft SQL Server 2012 이상 버전에도 적용됩니다.
실패	SQL Server 프로세스에서 예기치 않은 중단이 발생할 수 있는 모든 항목입니다. 예를 들어 정전, 컴퓨터 재설정, 메모리 오류, 기타 하드웨어 문제, 잘못된 섹터, 드라이브 중단, 시스템 오류 등이 있습니다.
플러시	캐시 버퍼를 안정적인 스토리지에 강제 적용합니다.
래치	리소스의 물리적 일관성을 보호하는 데 사용되는 동기화 개체입니다.
비휘발성 스토리지	시스템 오류에서 계속 사용할 수 있는 모든 매체입니다.
고정된 페이지	데이터 캐시에 남아 있고 연결된 모든 로그 레코드가 안정적인 스토리지 위치에서 보호될 때까지 안정적인 스토리지로 플러시할 수 없는 페이지입니다.
안정적인 스토리지	비휘발성 스토리지와 동일합니다.
휘발성 스토리지	오류 간에 그대로 유지되지 않는 모든 매체입니다.

WAL(Write-Ahead 로깅) 프로토콜

프로토콜이라는 용어는 WAL을 설명하는 훌륭한 방법입니다. 데이터가 올바르게 저장 및 교환되고 오류가 있는 경우 알려진 상태로 복구할 수 있도록 하는 데 필요한 특정하고 정의된 구현 단계 집합입니다. 네트워크에 일관되고 보호된 방식으로 데이터를 교환하는 정의된 프로토콜이 포함되어 있는 것처럼 WAL도 데이터를 보호하기 위한 프로토콜을 설명합니다.

ARIES 문서는 다음과 같이 WAL을 정의합니다.

WAL 프로토콜은 변경된 데이터가 비휘발성 스토리지에 있는 이전 버전의 데이터를 대체할 수 있도록 허용되기 전에 일부 데이터의 변경 내용을 나타내는 로그 레코드가 이미 안정적인 스토리지에 있어야 한다고 주장합니다. 즉, 페이지에 대한 업데이트를 설명하는 로그 레코드의 실행 취소 부분이 안정적인 스토리지에 기록될 때까지 시스템에서 페이지의 비휘발성 스토리지 버전에 업데이트된 페이지를 쓸 수 없습니다.

미리 쓰기 로깅에 대한 자세한 내용은 SQL Server 온라인 설명서의 미리 쓰기 트랜잭션 로그 항목을 참조하세요.

SQL Server 및 WAL

SQL Server WAL 프로토콜을 사용합니다. 트랜잭션이 올바르게 커밋되었는지 확인하려면 트랜잭션과 연결된 모든 로그 레코드를 안정적인 스토리지에서 보호해야 합니다.

이 상황을 명확히 하려면 다음 특정 예제를 고려하세요.

참고

이 예제에서는 인덱스가 없고 영향을 받는 페이지가 150페이지라고 가정합니다.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

다음으로, 다음 표에 설명된 대로 작업을 간단한 로깅 단계로 세분화합니다.

문	수행된 작업
BEGIN TRANSACTION	로그 캐시 영역에 기록되었습니다. 그러나 SQL Server 물리적인 변경을 수행하지 않았기 때문에 안정적인 스토리지로 플러시할 필요는 없습니다.
INSERT INTO tblTest	1. 데이터 페이지 150은 아직 사용할 수 없는 경우 SQL Server 데이터 캐시로 검색됩니다. 2. 페이지가 래치되고, 고정되고, 더티 표시되고, 적절한 잠금이 획득됩니다. 3. 로그 삽입 레코드가 빌드되어 로그 캐시에 추가됩니다. 4. 데이터 페이지에 새 행이 추가됩니다. 5. 래치가 해제되었습니다. 6. 모든 변경 내용이 휘발성 스토리지에 남아 있기 때문에 이 시점에서 트랜잭션 또는 페이지와 연결된 로그 레코드를 플러시할 필요가 없습니다.
COMMIT TRANSACTION	1. 커밋 로그 레코드가 형성되고 트랜잭션과 연결된 로그 레코드를 안정적인 스토리지에 기록해야 합니다. 트랜잭션은 로그 레코드가 안정적인 스토리지에 올바르게 할당될 때까지 커밋된 것으로 간주되지 않습니다. 2. 데이터 페이지 150은 SQL Server 데이터 캐시에 남아 있으며 안정적인 스토리지로 즉시 플러시되지 않습니다. 로그 레코드가 올바르게 보호되면 필요한 경우 복구에서 작업을 다시 실행할 수 있습니다. 3. 트랜잭션 잠금이 해제됩니다.

"잠금" 및 "로깅"이라는 용어로 혼동하지 마세요. 중요하지만 WAL을 처리할 때 잠금 및 로깅은 별개의 문제입니다. 이전 예제에서 SQL Server 일반적으로 트랜잭션의 전체 시간이 아니라 페이지의 실제 삽입 변경 내용을 수행하는 데 필요한 시간 동안 페이지 150의 래치를 보유합니다. 필요에 따라 행, 범위, 페이지 또는 테이블을 보호하기 위해 적절한 잠금 유형이 설정됩니다. 잠금 유형에 대한 자세한 내용은 SQL Server 온라인 설명서 잠금 섹션을 참조하세요.

예제를 자세히 살펴보면 LazyWriter 또는 CheckPoint 프로세스가 실행될 때 어떤 일이 발생하는지 물어볼 수 있습니다. SQL Server 더티 및 고정된 페이지와 연결된 트랜잭션 로그 레코드에 대해 안정적인 스토리지에 대한 모든 적절한 플러시를 실행합니다. 이렇게 하면 연결된 트랜잭션 로그 레코드가 플러시될 때까지 WAL 프로토콜 데이터 페이지를 안정적인 스토리지에 쓸 수 없습니다.

SQL Server 및 안정적인 스토리지

SQL Server 디스크 섹터 크기(일반적으로 4,096바이트 또는 512바이트)에 대한 지식을 포함하여 로그 및 데이터 페이지 작업을 향상시킵니다.

트랜잭션의 ACID 속성을 유지 관리하려면 SQL Server 실패 지점을 고려해야 합니다. 오류가 발생한 동안 많은 디스크 드라이브 사양은 제한된 수의 섹터 쓰기 작업만 보장합니다. 대부분의 사양은 오류가 발생할 때 단일 섹터 쓰기의 완료를 보장합니다.

SQL Server 섹터 크기의 배수에서 8KB 데이터 페이지와 로그(플러시된 경우)를 사용합니다. (대부분의 디스크 드라이브는 기본 섹터 크기로 512바이트를 사용합니다.) 오류가 있는 경우 SQL Server 로그 패리티 및 찢어진 쓰기 기술을 사용하여 섹터보다 큰 쓰기 작업을 설명할 수 있습니다.

찢어진 페이지 검색

이 옵션을 사용하면 SQL Server 정전 또는 기타 시스템 중단으로 인한 불완전한 I/O 작업을 검색할 수 있습니다. true이면 페이지가 디스크에 기록될 때마다 8KB(KB) 데이터베이스 페이지의 각 512바이트 섹터에 대해 비트가 대칭 이동됩니다. 나중에 SQL Server 페이지를 읽을 때 비트가 잘못된 상태인 경우 페이지가 잘못 작성되었습니다. 찢어진 페이지가 검색됩니다. 잘못 작성된 모든 페이지는 복구에서 읽을 가능성이 높기 때문에 복구 중에 찢어진 페이지가 검색됩니다.

SQL Server 데이터베이스 페이지는 8KB이지만 디스크는 512바이트 섹터를 사용하여 I/O 작업을 수행합니다. 따라서 데이터베이스 페이지당 16개 섹터가 작성됩니다. 운영 체제가 디스크에 처음 512바이트 섹터를 쓰는 시간과 8KB I/O 작업의 완료 사이에 시스템이 실패하는 경우(예: 전원 오류로 인해) 찢어진 페이지가 발생할 수 있습니다. 오류가 발생하기 전에 데이터베이스 페이지의 첫 번째 섹터가 성공적으로 작성되면 디스크의 데이터베이스 페이지가 업데이트된 것으로 표시되지만 성공하지 못할 수도 있습니다.

배터리 지원 디스크 컨트롤러 캐시를 사용하면 데이터가 디스크에 성공적으로 기록되거나 전혀 기록되지 않았는지 확인할 수 있습니다. 이 경우 필요하지 않으므로 찢어진 페이지 검색을 "true"로 설정하지 마세요.

참고

찢어진 페이지 검색은 기본적으로 SQL Server 사용하도록 설정되지 않습니다. 자세한 내용은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

로그 패리티

로그 패리티 검사는 찢어진 페이지 검색과 유사합니다. 각 512 바이트 섹터에는 패리티 비트가 포함됩니다. 이러한 패리티 비트는 항상 로그 레코드로 작성되고 로그 레코드가 검색될 때 평가됩니다. 512 바이트 경계에 로그 쓰기를 강제로 적용하면 SQL Server 커밋 작업이 실제 디스크 섹터에 기록되도록 할 수 있습니다.

성능에 미치는 영향

모든 버전의 SQL Server Win32 CreateFile 함수를 사용하여 로그 및 데이터 파일을 엽니다. dwFlagsAndAttributes 멤버는 SQL Server 열 때 옵션을 포함합니다FILE_FLAG_WRITE_THROUGH.

FILE_FLAG_WRITE_THROUGH 는 중간 캐시를 통해 작성하고 디스크로 직접 이동하도록 시스템에 지시합니다. 시스템은 여전히 쓰기 작업을 캐시할 수 있지만 지연 플러시할 수는 없습니다.

옵션은 FILE_FLAG_WRITE_THROUGH 쓰기 작업이 성공적인 완료를 반환할 때 데이터가 안정적인 스토리지에 올바르게 저장되도록 합니다. 이는 데이터를 보장하는 WAL 프로토콜과 일치합니다.

많은 디스크 드라이브(SCSI 및 IDE)에는 512KB, 1MB 이상의 온보딩 캐시가 포함되어 있습니다. 그러나 드라이브 캐시는 일반적으로 배터리 기반 솔루션이 아닌 커패시터에 의존합니다. 이러한 캐싱 메커니즘은 전원 주기 또는 유사한 실패 지점에서 쓰기를 보장할 수 없습니다. 섹터 쓰기 작업의 완료만 보장합니다. 특히 찢어진 쓰기 및 로그 패리티 검색이 SQL Server 7.0 이상 버전에 기본 제공된 이유입니다. 드라이브 크기가 계속 증가하면 캐시가 커지고 실패하는 동안 더 많은 양의 데이터를 노출할 수 있습니다.

많은 하드웨어 공급업체는 배터리 지원 디스크 컨트롤러 솔루션을 제공합니다. 이러한 컨트롤러 캐시는 며칠 동안 캐시의 데이터를 유지하고 캐싱 하드웨어를 두 번째 컴퓨터에 배치할 수도 있습니다. 전원이 올바르게 복원되면 추가 데이터 액세스가 허용되기 전에 기록되지 않은 데이터가 플러시됩니다. 대부분의 경우 최적의 성능을 위해 읽기 및 쓰기 캐시의 비율을 설정할 수 있습니다. 일부는 큰 메모리 스토리지 영역을 포함합니다. 사실, 시장의 특정 세그먼트에 대 한, 일부 하드웨어 공급 업체는 6 GB의 캐시와 하이 엔드 배터리 지원 디스크 캐싱 컨트롤러 시스템을 제공 합니다. 이렇게 하면 데이터베이스 성능이 크게 향상될 수 있습니다.

고급 캐싱 구현은 시스템 재설정, 전원 오류 또는 기타 오류 지점이 발생할 경우 진정한 다시 쓰기 기능을 제공할 수 있으므로 컨트롤러 캐시를 사용하지 않도록 설정하지 않음으로써 요청을 처리 FILE_FLAG_WRITE_THROUGH 합니다.

캐시를 사용하지 않고 I/O 전송은 드라이브 헤드, 스핀 속도 및 기타 제한 요인을 이동하는 데 필요한 기계적 시간으로 인해 더 길어질 수 있습니다.

섹터 순서 지정

I/O 성능을 높이는 데 사용되는 일반적인 기술은 섹터 순서 지정입니다. 기계적 헤드 이동을 방지하기 위해 읽기/쓰기 요청이 정렬되어 헤드의 더 일관된 동작이 데이터를 검색하거나 저장할 수 있습니다.

캐시는 여러 로그 및 데이터 쓰기 요청을 동시에 저장할 수 있습니다. WAL 프로토콜 및 WAL 프로토콜의 SQL Server 구현을 수행하려면 페이지 쓰기를 실행하기 전에 안정적인 스토리지에 로그 쓰기를 플러시해야 합니다. 그러나 캐시를 사용하면 데이터가 실제 드라이브(즉, 안정적인 스토리지에 기록됨)에 기록되지 않고 로그 쓰기 요청에서 성공을 반환할 수 있습니다. 이로 인해 데이터 페이지 쓰기 요청을 SQL Server 발생할 수 있습니다.

쓰기 캐시가 개입되면 데이터는 여전히 휘발성 스토리지에 있는 것으로 간주됩니다. 그러나 Win32 API WriteFile 호출에서 SQL Server 활동을 보는 정확한 방법에서 성공적인 반환 코드를 얻었습니다. SQL Server 또는 WriteFile API 호출을 사용하는 프로세스는 데이터가 안정적인 스토리지를 올바르게 획득했음을 확인할 수 있습니다.

토론을 위해 데이터 페이지의 모든 섹터가 일치하는 로그 레코드의 섹터 앞에 쓰도록 정렬된다고 가정합니다. 이는 WAL 프로토콜을 즉시 위반합니다. 캐시가 로그 레코드 앞에 데이터 페이지를 작성하고 있습니다. 캐시가 완전히 배터리로 지원되지 않는 한 오류가 발생하면 치명적인 결과가 발생할 수 있습니다.

데이터베이스 서버에 대한 최적의 성능 요소를 평가할 때 고려해야 할 여러 요소가 있습니다. 이 중 가장 중요한 것은 "내 시스템에서 유효한 FILE_FLAG_WRITE_THROUGH 기능을 허용하나요?" 입니다.

참고

사용 중인 모든 캐시는 배터리 지원 솔루션을 완전히 지원해야 합니다. 다른 모든 캐싱 메커니즘은 데이터 손상 및 데이터 손실이 발생하기 쉽습니다. SQL Server 을 사용하도록 설정하여 WAL을 보장하기 위해 모든 노력을 기울입니다.FILE_FLAG_WRITE_THROUGH

테스트 결과 많은 디스크 드라이브 구성에 적절한 배터리 백업 없이 쓰기 캐싱이 포함될 수 있습니다. SCSI, IDE 및 EIDE 드라이브는 쓰기 캐시를 최대한 활용합니다. SSD가 SQL Server 함께 작동하는 방법에 대한 자세한 내용은 다음 CSS SQL Server 엔지니어 블로그 문서를 참조하세요.

SQL Server 및 SSD - RDORR의 학습 정보 - 1부

대부분의 구성에서 IDE 또는 EIDE 드라이브의 쓰기 캐싱을 올바르게 사용하지 않도록 설정하는 유일한 방법은 특정 제조업체 유틸리티를 사용하거나 드라이브 자체에 있는 점퍼를 사용하는 것입니다. 드라이브 자체에 대해 쓰기 캐시를 사용하지 않도록 설정하려면 드라이브 제조업체에 문의하세요.

SCSI 드라이브에는 쓰기 캐시도 있습니다. 그러나 이러한 캐시는 일반적으로 운영 체제에서 사용하지 않도록 설정할 수 있습니다. 질문이 있는 경우 드라이브 제조업체에 적절한 유틸리티를 문의하세요.

쓰기 캐시 스택

쓰기 캐시 스택은 섹터 순서 지정과 유사합니다. 다음 정의는 선도적인 IDE 드라이브 제조업체의 웹 사이트에서 직접 가져온 것입니다.

일반적으로 이 모드는 활성 상태입니다. 쓰기 캐시 모드는 버퍼가 가득 차거나 호스트 전송이 완료될 때까지 버퍼에 대한 호스트 쓰기 데이터를 허용합니다.

디스크 쓰기 작업이 호스트 데이터를 디스크에 저장하기 시작합니다. 호스트 쓰기 명령은 계속 수락되며 쓰기 명령 스택이 가득 차거나 데이터 버퍼가 가득 차야 데이터가 버퍼로 전송됩니다. 드라이브는 쓰기 명령의 순서를 변경하여 드라이브 처리량을 최적화할 수 있습니다.

AWR(자동 쓰기 재할당)

데이터를 보호하는 데 사용되는 또 다른 일반적인 기술은 데이터 조작 중에 잘못된 섹터를 검색하는 것입니다. 다음 설명은 선도적인 IDE 드라이브 제조업체의 웹 사이트에서 제공됩니다.

이 기능은 쓰기 캐시의 일부이며 지연된 쓰기 작업 중 데이터 손실 위험을 줄입니다. 디스크 쓰기 프로세스 중에 디스크 오류가 발생하면 디스크 작업이 중지되고 의심 섹터가 드라이브 끝에 있는 대체 섹터 풀에 다시 할당됩니다. 재할당 후 디스크 쓰기 작업은 완료될 때까지 계속됩니다.

이 기능은 캐시에 대해 배터리 백업을 제공하는 경우 강력한 기능이 될 수 있습니다. 이렇게 하면 다시 시작할 때 적절한 수정이 제공됩니다. 디스크 오류를 검색하는 것이 좋지만 WAL 프로토콜의 데이터 보안은 지연된 방식이 아니라 실시간으로 수행해야 합니다. WAL 매개 변수 내에서 AWR 기술은 섹터 오류로 인해 로그 쓰기가 실패하지만 드라이브가 가득 찬 상황을 설명할 수 없습니다. 데이터베이스 엔진은 트랜잭션이 올바르게 중단되고, 관리자에게 경고를 받을 수 있고, 데이터를 보호하고 미디어 오류 상황을 수정하기 위해 수행된 올바른 단계를 수행할 수 있도록 오류에 대해 즉시 알고 있어야 합니다.

데이터 안전성

데이터베이스 관리자가 데이터의 안전을 보장하기 위해 수행해야 하는 몇 가지 예방 조치가 있습니다.

백업 전략이 치명적인 오류로부터 복구하기에 충분한지 확인하는 것이 좋습니다. 오프사이트 스토리지 및 기타 예방 조치가 적절합니다.
보조 또는 테스트 데이터베이스에서 데이터베이스 복원 작업을 자주 테스트합니다.
캐싱 디바이스가 모든 오류 상황(정전, 불량 섹터, 나쁜 드라이브, 시스템 중단, 잠금, 전원 급증 등)을 처리할 수 있는지 확인합니다.
캐싱 디바이스가 다음과 같은지 확인합니다.
- 통합 배터리 백업이 있음
- 전원 켜기에서 쓰기를 다시 실행할 수 있습니다.
- 필요한 경우 완전히 사용하지 않도록 설정할 수 있습니다.
- 잘못된 섹터 다시 매핑을 실시간으로 처리합니다.
찢어진 페이지 검색을 사용하도록 설정합니다. (성능에는 거의 영향을 미치지 않습니다.)
가능한 경우 잘못된 디스크 드라이브의 핫 스왑을 허용하는 RAID 드라이브를 구성합니다.
OS를 다시 시작하지 않고 더 많은 디스크 공간을 추가할 수 있는 최신 캐싱 컨트롤러를 사용합니다. 이는 이상적인 솔루션이 될 수 있습니다.

드라이브 테스트

데이터를 완전히 보호하려면 모든 데이터 캐싱이 올바르게 처리되었는지 확인해야 합니다. 대부분의 경우 디스크 드라이브의 쓰기 캐싱을 사용하지 않도록 설정해야 합니다.

참고

대체 캐싱 메커니즘이 여러 유형의 오류를 올바르게 처리할 수 있는지 확인합니다.

Microsoft는 유틸리티를 사용하여 여러 SCSI 및 IDE 드라이브에 대한 테스트를 수행했습니다 SQLIOSim . 이 유틸리티는 시뮬레이션된 데이터 디바이스 및 로그 디바이스에 대한 무거운 비동기 읽기/쓰기 작업을 시뮬레이션합니다. 테스트 성능 통계에 따르면 쓰기 캐싱이 비활성화된 드라이브의 경우 초당 평균 쓰기 작업이 50에서 70 사이이고 RPM 범위는 5,200에서 7,200 사이입니다.

유틸리티에 SQLIOSim 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하세요.

SQLIOSim 유틸리티를 사용하여 디스크 하위 시스템의 SQL Server 작업을 시뮬레이션하는 방법

많은 컴퓨터 제조업체는 쓰기 캐시를 사용하지 않도록 설정하여 드라이브를 주문합니다. 그러나 테스트는 이것이 항상 그렇지 않을 수 있음을 보여줍니다. 따라서 항상 완전히 테스트합니다.

데이터 디바이스

기록되지 않은 모든 상황에서 SQL Server 로그 레코드만 플러시해야 합니다. 기록되지 않은 작업을 수행할 때 데이터 페이지도 안정적인 스토리지로 플러시되어야 합니다. 오류가 발생한 경우 작업을 다시 생성할 개별 로그 레코드가 없습니다.

LazyWriter 또는 CheckPoint 프로세스가 안정적인 스토리지로 플러시할 때까지 데이터 페이지는 캐시에 남아 있을 수 있습니다. WAL 프로토콜을 사용하여 로그 레코드가 올바르게 저장되었는지 확인하면 복구에서 데이터 페이지를 알려진 상태로 복구할 수 있습니다.

그렇다고 해서 캐시된 드라이브에 데이터 파일을 배치하는 것이 좋습니다. SQL Server 데이터 페이지를 안정적인 스토리지로 플러시하면 트랜잭션 로그에서 로그 레코드를 잘라낼 수 있습니다. 데이터 페이지가 휘발성 캐시에 저장된 경우 오류가 발생할 경우 페이지를 복구하는 데 사용할 로그 레코드를 잘라 내는 것이 가능합니다. 데이터와 로그 디바이스가 모두 안정적인 스토리지를 올바르게 수용하는지 확인합니다.

성능 향상

발생할 수 있는 첫 번째 질문은 다음과 같습니다. "캐싱 중인 IDE 드라이브가 있습니다. 그러나 그것을 사용하지 않도록 설정했을 때, 내 성능은 예상보다 적어졌습니다. 왜?"

Microsoft에서 테스트하는 대부분의 IDE 드라이브는 5,200 RPM에서 실행되고 SCSI 드라이브는 7,200 RPM에서 실행됩니다. IDE 드라이브의 쓰기 캐싱을 사용하지 않도록 설정하면 기계적 성능이 요인이 될 수 있습니다.

성능 차이를 해결하기 위해 따라야 할 방법은 "트랜잭션 속도 해결"입니다.

많은 OLTP(온라인 트랜잭션 처리) 시스템에는 높은 트랜잭션 속도가 필요합니다. 이러한 시스템의 경우 쓰기 캐시를 적절하게 지원하고 데이터 무결성을 유지하면서 원하는 성능 향상을 제공할 수 있는 캐싱 컨트롤러를 사용하는 것이 좋습니다.

캐싱 드라이브의 SQL Server 발생하는 중요한 성능 변화를 관찰하기 위해 작은 트랜잭션을 사용하여 트랜잭션 속도가 증가했습니다.

테스트 결과 버퍼의 쓰기 작업이 512KB 미만이거나 2MB보다 크면 성능이 저하될 수 있습니다.

다음 예제를 살펴보겠습니다.

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

다음은 SQL Server 대한 샘플 테스트 결과입니다.

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

전체 일련의 INSERT 작업을 단일 트랜잭션으로 래핑하는 프로세스는 모든 구성에서 약 4초 만에 실행됩니다. 이는 필요한 로그 플러시 수 때문입니다. 단일 트랜잭션을 만들지 않으면 모든 INSERT 트랜잭션이 별도의 트랜잭션으로 처리됩니다. 따라서 트랜잭션에 대한 모든 로그 레코드를 플러시해야 합니다. 각 플러시 크기는 512바이트입니다. 이를 위해서는 상당한 기계적 드라이브 개입이 필요합니다.

단일 트랜잭션을 사용하는 경우 트랜잭션에 대한 로그 레코드를 번들로 묶을 수 있으며, 더 큰 단일 쓰기를 사용하여 수집된 로그 레코드를 플러시할 수 있습니다. 이것은 기계적인 내정간섭을 현저하게 감소시킵니다.

경고

트랜잭션 scope 늘리지 않는 것이 좋습니다. 장기 실행 트랜잭션은 과도하고 원치 않는 차단 및 증가된 오버헤드를 유발할 수 있습니다. SQL Server:D atabases SQL Server 성능 카운터를 사용하여 트랜잭션 로그 기반 카운터를 봅니다. 특히 Log Bytes Flushed/sec는 높은 기계적 디스크 작업을 유발할 수 있는 많은 작은 트랜잭션을 나타낼 수 있습니다.

로그 플러시와 연결된 문을 검사하여 Log Bytes Flushed/sec 값을 줄일 수 있는지 여부를 확인합니다. 이전 예제에서는 단일 트랜잭션이 사용되었습니다. 그러나 많은 시나리오에서 이로 인해 원치 않는 잠금 동작이 발생할 수 있습니다. 트랜잭션의 디자인을 검토합니다. 다음 코드와 유사한 코드를 사용하여 일괄 처리를 실행하여 빈번하고 작은 로그 플러시 작업을 줄일 수 있습니다.

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

SQL Server 시스템은 SQL Server I/O 안정성 프로그램 검토 요구 사항 다운로드 문서에 설명된 대로 안정적인 미디어에 대한 보장된 배달을 지원해야 합니다. SQL Server 데이터베이스 엔진의 입력 및 출력 요구 사항에 대한 자세한 내용은 Microsoft SQL Server 데이터베이스 엔진 입력/출력 요구 사항을 참조하세요.