Azure SQL Managed Instance의 데이터베이스 파일 공간 관리

적용 대상:Azure SQL Managed Instance

이 문서에서는 Azure SQL Managed Instance의 데이터베이스에서 파일을 모니터링하고 관리하는 방법을 설명합니다. 데이터베이스 파일 크기를 모니터링하고, 트랜잭션 로그를 축소하고, 트랜잭션 로그 파일을 확대하고, 트랜잭션 로그 파일의 증가를 제어하는 방법을 설명합니다.

이 문서는 Azure SQL Managed Instance에 적용됩니다. SQL Server에서 트랜잭션 로그 파일의 크기를 관리하는 방법에 대한 자세한 내용은 트랜잭션 로그 파일의 크기 관리를 참조하세요.

데이터베이스의 스토리지 공간 유형 이해

데이터베이스의 파일 공간을 관리하려면 다음 스토리지 공간 수량을 이해하는 것이 중요합니다.

다음 다이어그램에서는 데이터베이스에 대한 여러 스토리지 공간 유형 간의 관계를 보여줍니다.

파일 공간 정보를 위해 단일 데이터베이스 쿼리

sys.database_files에 대한 다음 쿼리를 사용하여 할당 데이터베이스 파일 공간 크기 및 사용하지 않은 할당 공간 크기를 반환합니다. 쿼리 결과의 단위는 MB입니다.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

로그 공간 사용 모니터링

sys.dm_db_log_space_usage를 사용하여 로그 공간 사용을 모니터링합니다. 이 DMV는 현재 사용된 로그 공간 크기에 대한 정보를 반환하고 트랜잭션 로그 잘림을 수행해야 하는 시기를 나타냅니다.

현재 로그 파일 크기, 최대 크기 및 파일의 자동 증가 옵션에 대한 자세한 내용은 sys.database_files 해당 로그 파일에 대한 열 및 growth 열을 사용합니다max_sizesize.

Azure Resource Manager 기반 메트릭 API에 표시되는 스토리지 공간 메트릭은 사용된 데이터 페이지의 크기만 측정합니다. 예제는 PowerShell Get-AZMetric을 참조하세요.

로그 파일 크기 축소

실제 로그 파일의 실제 크기를 줄이려면 사용하지 않는 공간을 제거함으로써 로그 파일을 축소합니다. 축소는 트랜잭션 로그 파일에 사용되지 않는 공간이 포함된 경우에만 차이가 납니다. 열린 트랜잭션으로 인해 로그 파일이 가득 차면 트랜잭션 로그 자름을 차단하는 원인을 조사합니다.

데이터베이스 파일 축소의 잠재적인 부정적인 성능 영향에 유의하세요. 자세한 내용은 축소 후 인덱스 유지 관리를 참조하세요. 드물게 자동화된 데이터베이스 백업은 축소 작업에 영향을 줄 수 있습니다. 필요한 경우 축소 작업을 다시 시도합니다.

트랜잭션 로그를 축소하기 전에 로그 잘림을 지연할 수 있는 요소를 염두에 두어야 합니다. 로그 축소 후 스토리지 공간이 다시 필요한 경우 트랜잭션 로그가 다시 증가하여 로그 증가 작업 중에 성능 오버헤드가 발생합니다. 자세한 내용은 권장 사항 섹션을 참조하세요 .

데이터베이스가 온라인 상태이고 하나 이상의 가상 로그 파일(VLF)에 여유 공간이 있는 경우에만 로그 파일을 축소할 수 있습니다. 경우에 따라 다음에 로그가 잘릴 때까지 로그를 축소하지 못할 수도 있습니다.

장기 실행 트랜잭션과 같이 오랜 시간 동안 VLF를 활성 상태로 유지하는 요인으로 인해 로그 축소가 제한되거나 로그가 전혀 축소되지 못할 수 있습니다. 자세한 내용은 로그 잘림을 지연시킬 수 있는 요소를 참조하세요.

로그 파일을 축소하면 논리 로그 부분이 포함되지 않은 하나 이상의 VLF(비활성 VLF)가 제거됩니다. 트랜잭션 로그 파일을 줄이면 비활성 VLF가 로그 파일의 끝에서 제거되어 로그가 대상 크기로 줄어듭니다.

축소 작업에 대한 자세한 내용은 다음 설명서를 검토하세요.

로그 파일 축소(데이터베이스 파일의 축소 없이)

• DBCC SHRINKFILE(Transact-SQL)

• 파일 축소

로그 파일 축소 이벤트 모니터링

• Log File Auto Shrink 이벤트 클래스입니다.

로그 공간 모니터링

• sys.dm_db_log_space_usage(Transact-SQL)

• sys.database_files(Transact-SQL)(로그 파일의 size, max_size 및 growth 열 참조)

축소 후 인덱스 유지 관리

데이터 파일에 대한 축소 작업이 완료되면 인덱스가 조각화될 수 있습니다. 조각화는 대규모 검사를 사용하는 쿼리와 같은 특정 워크로드에 대한 인덱스의 성능 최적화 효율성을 줄입니다. 축소 작업 완료 후 성능 저하가 발생하면 인덱스 유지 관리를 통해 인덱스를 다시 빌드하는 것을 고려해 봅니다. 인덱스를 다시 빌드하려면 데이터베이스에 사용 가능한 공간이 필요하므로 할당된 공간이 증가하여 축소 효과가 무효화될 수 있습니다.

인덱스 유지 관리에 대한 자세한 내용은 쿼리 성능 향상 및 리소스 소비 감소를 위한 인덱스 유지 관리 최적화를 참조하세요.

인덱스 페이지 밀도 평가

데이터 파일을 잘리면 할당된 공간이 충분히 감소하지 않는 경우 데이터베이스 데이터 파일을 축소하여 해당 파일에서 사용되지 않는 공간을 회수할 수 있습니다. 그러나 선택적이지만 권장되는 단계로, 먼저 데이터베이스의 인덱스에 대한 평균 페이지 밀도를 결정해야 합니다. 동일한 양의 데이터의 경우 페이지 밀도가 높으면 페이지 수가 줄어들기 때문에 축소가 더 빨리 완료됩니다. 일부 인덱스에 대한 페이지 밀도가 낮은 경우 먼저 이러한 인덱스에 대한 유지 관리를 수행하여 페이지 밀도를 높인 후에 데이터 파일을 축소하는 것이 좋습니다. 이 단계를 통해 축소하면 할당된 스토리지 공간을 더 깊이 줄일 수 있습니다.

데이터베이스의 모든 인덱스에 대한 페이지 밀도를 확인하려면 다음 쿼리를 사용합니다. 페이지 밀도는 avg_page_space_used_in_percent 열에 보고됩니다.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

페이지 밀도가 60~70% 미만인 페이지의 수가 많은 인덱스가 있는 경우 데이터 파일을 축소하기 전에 이러한 인덱스를 다시 빌드하거나 다시 구성하는 것이 좋습니다.

페이지 밀도가 낮은 인덱스가 여러 개 있는 경우 여러 데이터베이스 세션에서 인덱스를 병렬로 다시 빌드하여 프로세스 속도를 높일 수 있습니다. 그러나 이렇게 하여 데이터베이스 리소스 한도에 접근하지 않는지 확인하고 애플리케이션 워크로드에 충분한 리소스 헤드룸을 남겨 둡니다. Azure Portal에서 또는 sys.dm_db_resource_stats 보기를 사용하여 리소스 사용량(CPU, 데이터 IO, 로그 IO)을 모니터링합니다. 이러한 각 차원의 리소스 사용률이 100%보다 훨씬 낮은 경우에만 추가 병렬 다시 빌드를 시작합니다. CPU, 데이터 IO 또는 로그 IO 사용률이%100인 경우 더 많은 CPU 코어를 갖도록 데이터베이스를 확장하고 IO 처리량을 늘려 더 많은 병렬 다시 빌드를 통해 프로세스를 더 빠르게 완료할 수 있습니다.

샘플 인덱스 다시 빌드 명령

ALTER INDEX 문을 사용하여 인덱스를 다시 빌드하고 해당 페이지의 밀도를 높이는 샘플 명령은 다음과 같습니다.

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8,
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)),
RESUMABLE = ON);

이 명령은 온라인 및 다시 시작 가능한 인덱스 다시 빌드를 시작합니다. 이 유형의 다시 빌드를 사용하면 다시 빌드가 진행되는 동안 동시 워크로드에서 테이블을 계속 사용할 수 있으며 어떤 이유로든 중단된 경우 다시 빌드를 다시 시작할 수 있습니다. 그러나 이 유형의 다시 빌드는 테이블에 대한 액세스를 차단하는 오프라인 다시 빌드보다 느립니다. 다시 빌드하는 동안 다른 워크로드에서 테이블에 액세스할 필요가 없는 경우 ONLINE 및 RESUMABLE 옵션을 OFF로 설정하고 WAIT_AT_LOW_PRIORITY 절을 제거합니다.

인덱스 유지 관리에 대한 자세한 내용은 쿼리 성능 향상 및 리소스 소비 감소를 위한 인덱스 유지 관리 최적화를 참조하세요.

여러 데이터 파일 축소

앞에서 설명한 대로 데이터 이동을 통한 축소는 장기 프로세스입니다. 데이터베이스에 여러 데이터 파일이 있는 경우 여러 데이터 파일을 병렬로 축소하여 프로세스 속도를 높일 수 있습니다. 여러 데이터베이스 세션을 열고 각 세션에서 다른 file_id 값을 사용하여 DBCC SHRINKFILE 이 작업을 수행합니다. 이전에 인덱스를 다시 빌드하는 경우와 마찬가지로 먼저 충분한 리소스 가용 공간(CPU, 데이터 I/O, 로그 I/O)이 있는지 확인한 후에 새 병렬 축소 명령을 시작해야 합니다.

다음 샘플 명령은 파일 내에서 페이지를 이동하여 file_id가 4인 데이터 파일을 축소해 할당된 크기를 52,000MB로 줄이려고 시도합니다.

DBCC SHRINKFILE (4, 52000);

파일에 할당된 공간을 가능한 최소로 줄이려면 대상 크기를 지정하지 않고 다음 명령문을 실행합니다.

DBCC SHRINKFILE (4);

워크로드가 축소와 함께 동시에 실행되는 경우 축소가 완료되고 파일이 잘리기 전에 축소를 통해 확보된 스토리지 공간의 사용을 시작할 수 있습니다. 이 경우 축소는 지정된 대상에 할당된 공간을 줄일 수 없습니다.

각 파일을 더 작은 단계로 축소하여 이 문제를 완화할 수 있습니다. 즉, DBCC SHRINKFILE 명령에서 파일에 대해 현재 할당된 공간보다 약간 작은 대상을 설정합니다. 예를 들어 file_id가 4인 파일에 할당된 공간이 200,000MB이고 이를 100,000MB로 축소하려는 경우 먼저 대상을 170,000MB로 설정할 수 있습니다.

DBCC SHRINKFILE (4, 170000);

이 명령이 완료되면 파일을 잘라내고 할당된 크기를 170,000MB로 줄입니다. 그런 다음 이 명령을 반복하여 파일이 원하는 크기로 축소될 때까지 대상을 먼저 140,000MB로 설정한 다음 110,000MB로 설정할 수 있습니다. 명령이 완료되었지만 파일이 잘리지 않으면 더 작은 단계(예: 30,000MB가 아닌 15,000MB)를 사용합니다.

동시에 실행되는 모든 축소 세션에 대한 축소 진행률을 모니터링하려면 다음 쿼리를 사용할 수 있습니다.

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

모든 데이터 파일에 대해 축소가 완료되면 공간 사용량 쿼리 를 사용하여 할당된 스토리지 크기의 감소 결과를 결정합니다. 사용된 공간과 할당된 공간 간에 여전히 큰 차이가 있는 경우 인덱스를 다시 작성할 수 있습니다. 다시 빌드하면 할당된 공간을 일시적으로 늘릴 수 있지만 인덱스를 다시 빌드한 후 데이터 파일을 다시 축소하면 할당된 공간이 더 심해집니다.

로그 파일 확대

Azure SQL Managed Instance에서 디스크 공간이 허용되는 경우 기존 로그 파일을 확대하여 로그 파일에 공간을 추가할 수 있습니다. 데이터베이스에 로그 파일을 추가하는 것은 지원되지 않습니다. 로그 공간이 부족하고 로그 파일을 보유하는 볼륨에서도 디스크 공간이 부족하지 않으면 트랜잭션 로그 파일 하나로 충분합니다.

로그 파일을 확대하려면 문의 절을 MODIFY FILEALTER DATABASE 사용하고 구문과 MAXSIZE 구문을 지정 SIZE 합니다. 자세한 내용은 ALTER DATABASE(Transact-SQL) 파일 및 파일 그룹 옵션을 참조하세요.

자세한 내용은 권장 사항을 참조하세요.

트랜잭션 로그 파일 증가 제어

트랜잭션 로그 파일의 증가를 관리하려면 ALTER DATABASE(Transact-SQL) 파일 및 파일 그룹 옵션 문을 사용합니다. 다음 옵션에 유의하세요.

• 이 SIZE 옵션을 사용하여 현재 파일 크기를 KB, MB, GB 및 TB 단위로 변경합니다.
• FILEGROWTH 증가 증분을 변경하는 옵션을 사용합니다. 값 0은 자동 증가를 사용하지 않고 추가 공간을 허용하지 않음을 나타냅니다.
• 이 MAXSIZE 옵션을 사용하여 로그 파일의 최대 크기를 KB, MB, GB 및 TB 단위로 제어하거나 증가를 으로 UNLIMITED설정합니다.

권장 사항

트랜잭션 로그 파일을 사용하는 경우 다음 권장 사항을 고려합니다.

• 옵션에 의해 FILEGROWTH 구성된 대로 트랜잭션 로그의 자동 증가(자동 증가) 증가가 워크로드 트랜잭션의 요구 사항을 충족할 만큼 충분히 커지도록 설정합니다. 자주 확장되지 않도록 로그 파일의 파일 증가를 충분히 크게 만듭니다. 다음 중에 사용되는 로그의 양을 모니터링하여 트랜잭션 로그의 크기를 적절하게 지정할 수 있습니다.

  • 로그 백업이 완료될 때까지 발생할 수 없으므로 전체 백업을 실행하는 데 필요한 시간입니다.
  • 가장 큰 인덱스 유지 보수 작업에 필요한 시간.
  • 데이터베이스에서 가장 큰 일괄 처리를 실행하는 데 필요한 시간.

• 백분율이 계속 증가하는 양이므로 증가 비율을 더 잘 제어할 수 있도록 대신 옵션을 percentagesize 사용하여 FILEGROWTH 데이터 및 로그 파일에 대한 자동 증가를 설정합니다.

  • Azure SQL Managed Instance에서 인스턴트 파일 초기화를 통해 최대 64MB의 트랜잭션 로그 증가 이벤트를 지원할 수 있습니다. 새 데이터베이스의 기본 자동 증가 크기 증분은 64MB입니다. 64MB보다 큰 트랜잭션 로그 파일 자동 증가 이벤트는 즉시 파일 초기화의 이점을 얻을 수 없습니다.
  • 트랜잭션 로그에 대해 옵션 값을 1,024MB 이상으로 설정하는 FILEGROWTH 것이 가장 좋습니다.

• 너무 많은 작은 VLL 을 생성하고 성능을 줄일 수 있으므로 작은 자동 증가 증분을 설정하지 마세요. 지정된 인스턴스에 있는 모든 데이터베이스의 현재 트랜잭션 로그 크기에 대한 최적의 VLF 분포 및 필수 크기가 달성되도록 필수 증가 증분을 결정하려면 SQL Tiger Team에서 제공하는 VFL 분석 및 수정용 스크립트를 참조하세요.

• 두 가지 문제가 발생할 수 있으므로 큰 자동 증가 증분을 설정하지 마세요.

  • 새 공간이 할당되는 동안 데이터베이스가 일시 중지되어 쿼리 시간 제한이 발생할 수 있습니다.
  • 너무 적고 큰 VLF를 생성할 수 있으며 성능에도 영향을 줄 수 있습니다. 지정된 인스턴스에 있는 모든 데이터베이스의 현재 트랜잭션 로그 크기에 대한 최적의 VLF 분포 및 필수 크기가 달성되도록 필수 증가 증분을 결정하려면 SQL Tiger Team에서 제공하는 VFL 분석 및 수정용 스크립트를 참조하세요.

• 자동 증가가 활성화되어 있더라도 쿼리의 요구 사항을 충족할 만큼 빠르게 확장할 수 없는 경우 트랜잭션 로그가 가득 찼다는 메시지를 받을 수 있습니다. 증가 증분 변경에 대한 자세한 내용은 ALTER DATABASE(Transact-SQL) 파일 및 파일 그룹 옵션을 참조하세요.

• 로그 파일이 자동으로 축소되도록 설정할 수 있습니다. 그러나 이 방법은 권장되지 않으며auto_shrink 데이터베이스 속성은 기본적으로 FALSE로 설정됩니다. auto_shrink TRUE로 설정하면 자동으로 축소하면 해당 공간의 25% 이상이 사용되지 않는 경우에만 파일 크기가 줄어듭니다.

  • 파일의 25%만 사용되지 않는 공간이거나 파일의 원래 크기 중 더 큰 크기로 축소되는 크기로 축소됩니다.
  • auto_shrink 속성의 설정 변경에 대한 자세한 내용은 데이터베이스의 속성 보기 또는 변경 또는 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

관련 콘텐츠

• Azure SQL Managed Instance의 자동화된 백업
• ALTER DATABASE (Transact-SQL) 파일 및 파일 그룹 옵션
• Azure SQL Managed Instance 리소스 제한 개요
• Azure SQL Managed Instance의 트랜잭션 로그 오류 문제 해결