증상
대량 로드 작업을 실행할 때 데이터베이스의 테이블에 사용되지 않는 공간이 지속적으로 증가합니다. 명령을 실행하는 sp_spaceused 경우 테이블에서 사용되지 않는 공간이 예약된 공간(테이블에 할당된 전체 공간)의 상당 부분을 차지하는 것을 확인할 수 있습니다.
EXEC sp_spaceused 'Sales.Customer'
예제는 다음과 같습니다.
| 테이블 | 예약됨(KB) | 데이터(KB) | 인덱스(KB) | 사용되지 않음(KB) |
|---|---|---|---|---|
| Sales.Customer | 800,000 | 50,000 | 10,000 | 740,000 |
원인
일괄 처리 크기가 작은 대량 로드 작업을 사용하는 경우 테이블은 거의 사용되지 않는 페이지 익스텐트를 할당할 수 있습니다.
최소 로깅을 사용하는 대량 로드 작업을 실행하면 데이터가 미리 정렬되거나 순차적으로 로드되는 경우 인덱스의 데이터 로드 작업의 성능을 향상시킬 수 있습니다. 그러나 이러한 작업에서 사용하는 일괄 처리 크기(BATCHSIZEBULK INSERT 및 -b bcp 유틸리티의 옵션)는 한 손에서 더 빠른 성능을 달성하고 다른 작업에서 효율적인 공간 사용을 달성하는 데 중요한 역할을 합니다. 최소 로깅 모드에서 각 대량 로드 일괄 처리는 하나 이상의 새 익스텐트를 할당할 때 사용 가능한 여유 공간에 대한 조회를 무시합니다. SQL Server는 삽입 성능을 최적화하기 위해 이 캐시 조회를 건너뜁니다. 기존 익스텐트에서 여유 공간을 찾는 대신 새 익스텐션을 직접 만듭니다. 따라서 작은 일괄 처리 크기(예: 일괄 처리당 10개 행)를 사용하는 경우 SQL Server는 10개 레코드의 모든 일괄 처리에 대해 새 64KB 익스텐트를 예약합니다. 이는 대부분의 행 크기에 낭비됩니다(일부 행은 단일 페이지에 맞게 너무 넓을 수 있으며 이 경우 10개의 레코드가 적절할 수 있음). 익스텐트 내의 나머지 페이지는 사용되지 않지만 개체에 대해 예약됩니다. 따라서 더 작은 일괄 처리 크기와 결합된 빠른 부하 최적화로 인해 비효율적인 공간 사용이 발생합니다.
MSSQL Tiger Team 블로그 사이트의 다음 표에서는 이 동작을 설명하는 몇 가지 경험적 증거를 보여 줍니다.
| Batch 크기 | 예약됨(KB) | 데이터(KB) | 인덱스 크기(KB) | 사용되지 않음(KB) | 사용되지 않는 백분율(%) |
|---|---|---|---|---|---|
| 10 | 6,472 | 808 | 8 | 5,656 | 87 |
| 100 | 1,352 | 168 | 8 | 1,176 | 86 |
| 1,000 | 264 | 128 | 8 | 128 | 49 |
해결
이 문제를 해결하려면 다음 지침을 고려하세요.
삽입 수가 적다는 것은 대량 로드 작업이 없음을 의미합니다.
삽입할 행 수가 상대적으로 적은 경우 "대량" 삽입이 아닙니다. 작은 일괄 처리 크기의 경우 로깅 최적화를 최소화하면서 대량 로드 작업 대신 정기적인 완전 로깅 INSERT 문을 사용하는 것이 좋습니다.
대량 로드 작업에 대한 일괄 처리 크기 값 설정
대량 로드 작업의 경우 익스텐트 크기(64KB)의 배수이고 평균 행 크기를 기반으로 하는 일괄 처리 크기를 선택합니다. 이러한 일괄 처리 크기 값을 사용하면 행이 익스텐트 내에서 공간을 효율적으로 채울 수 있습니다. 예를 들어 평균 행 크기가 25바이트인 경우 행당 64KB를 25바이트씩 나누어 일괄 처리 크기로 압축할 수 있는 행 수를 결정합니다. 이 경우 64KB = 65,536바이트/행당 25바이트 = 2,620행. 따라서 이 숫자 주위에 배치 크기를 선택하여 각 데이터 페이지의 머리글에 대한 공간을 허용할 수 있습니다. 2,500에서 2,700 사이의 범위의 일괄 처리 크기를 사용하여 이를 테스트하고 공간 사용량을 관찰할 수 있습니다. 테이블에서 평균 행 크기를 찾으려면 다음 쿼리를 사용합니다. 힙(클러스터형 인덱스가 없는 테이블)의 경우 index_ID 매개 변수(세 번째 매개 변수)에 0 을 사용합니다. 클러스터형 인덱스가 있는 테이블의 경우 다음 예제와 같이 1을 사용합니다.
SELECT
index_type_desc,alloc_unit_type_desc,
avg_record_size_in_bytes,
max_record_size_in_bytes,
avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2016'), OBJECT_ID(N'Production.ProductDocument'), 1, NULL , 'DETAILED')
참고 항목
이렇게 하면 큰 I/O 요청이 버스트될 수 있으므로 일괄 처리 크기를 큰 값으로 설정하지 않도록 주의해야 합니다. 자세한 내용은 매우 큰 일괄 처리 크기의 I/O 효과를 참조 하세요.
일괄 처리 크기 구성이 대량 로드 작업에 대한 옵션이 아닌 경우
어떤 이유로 일괄 처리 크기를 변경하거나 일반INSERTS을 사용할 수 없는 경우 추적 플래그 692(TF 692)를 사용하여 빠른 삽입(최소 로깅) 동작을 사용하지 않도록 설정할 수 있습니다. SQL Server 2016부터는 제품에서 기본적으로 빠른 삽입이 사용하도록 설정됩니다. 따라서 각 대량 로드 일괄 처리는 새 익스텐트를 할당하고 기본적으로 기존 페이지에서 사용 가능한 여유 공간에 대한 조회를 무시합니다. 따라서 일괄 처리 크기가 작은 대량 로드 작업으로 인해 개체에서 사용되지 않는 공간이 증가할 수 있습니다. 추적 플래그 692는 대량 데이터를 힙 또는 클러스터형 인덱스에 로드하는 동안 빠른 삽입을 사용하지 않도록 설정합니다. 이렇게 하면 증상 섹션에 설명 된 사용되지 않는 공간 문제가 최소화됩니다 .
다음 쿼리를 사용하여 SQL Server가 온라인인 동안 추적 플래그를 사용하도록 설정할 수 있습니다.
DBCC TRACEON(692,-1)
또는 SQL Server 서비스를 다시 시작할 때 TF 692를 자동으로 사용하도록 설정하는 SQL Server 서비스 시작 매개 변수로 추가할 -T692 수 있습니다.
매우 큰 일괄 처리 크기의 I/O 효과
대량 로그 복구 모델(최소 로깅)에서 SQL Server는 일괄 처리를 커밋하는 즉시 데이터 페이지를 플러시합니다(즉시 쓰기라고도 함). 최소 로깅은 개별 로그 레코드가 트랜잭션 로그에 기록되지 않고 익스텐트 할당만 기록된다는 것을 의미하기 때문입니다. 가동 중단 시 데이터가 손실되지 않도록 하기 위해 SQL Server는 데이터로 채워진 데이터 페이지를 즉시 디스크에 씁니다. 따라서 큰 일괄 처리 크기를 선택하면 쓰기 I/O 버스트가 발생할 수 있습니다. I/O 하위 시스템이 쓰기 I/O 버스트를 처리할 수 없는 경우 대량 로드 작업 및 해당 당시 SQL Server 인스턴스에서 실행 중인 다른 모든 트랜잭션의 성능에 부정적인 영향을 줄 수 있습니다. 즉, 일괄 처리 크기에 대한 이점이 감소합니다. 일괄 처리 크기를 너무 크게 선택하면 혜택이 감소합니다.
따라서 평균 행 크기에 따라 익스텐트 크기(64KB)의 배수인 일괄 처리 크기를 선택하는 것이 중요합니다. 기본 디스크 I/O 성능에 따라 1익스텐트(64KB)와 64개 익스텐트(4MB) 사이의 배치 크기를 선택할 수 있습니다. 이 범위는 효율적인 공간 사용률과 최적의 대량 로드 성능 간의 균형을 이루게 됩니다.