WIPI

프로그래밍과 기술 이슈를 빠르게 정리하는 개발 블로그

목록으로

Programming Notes

하이퍼스케일 Azure SQL 데이터베이스 축소 프로세스 - 팁 & 트릭

작성자: 타나얀카르 차크라보르티(Tanayankar Chakraborty) & 디미트리 퍼맨(Dimitri Furman) 문제 최근 저희는 Azure SQL 데이터베이스(하이퍼스케일) 축소와 관련된 고객 사례를 다루었습니다. 주요 문제는 축소 작업이 느리게 진행되고 예상만큼...

작성자

작성자: 타나얀카르 차크라보르티(Tanayankar Chakraborty) & 디미트리 퍼맨(Dimitri Furman) 

문제 

최근 저희는 Azure SQL 데이터베이스(하이퍼스케일) 축소와 관련된 고객 사례를 다루었습니다. 주요 문제는 축소 작업이 느리게 진행되고 예상만큼 저장 공간을 확보하지 못하는 것이었습니다. 일부 시나리오에서는 고객들이 축소 작업 중 오류 메시지를 만나기도 했습니다. 

![이미지](원문_이미지_URL_여기에_들어감)

 

확인된 오류 

고객은 축소 명령 실행 후 다음과 같은 오류를 확인했습니다: 

Sql 오류 번호: 1222. 오류 메시지: 잠금 요청 시간 초과 기간을 초과했습니다. 

잠금 요청 시간 초과 기간을 초과했습니다. 

잠금 요청 시간 초과 기간을 초과했습니다. 

잠금 요청 시간 초과 기간을 초과했습니다. 

어떤 경우에는 다음과 같은 메시지와 함께 작업이 중단되었습니다: 

축소 작업이 중단되었습니다. 축소에 의해 이동될 페이지가 주 복제본 또는 하나 이상의 보조 복제본에서 활성 트랜잭션에 의해 사용 중입니다. 나중에 다시 축소 작업을 시도하십시오. 

 

 

권장 사항 및 완화 단계 

다음은 대규모 Azure SQL DB 축소를 처리할 때 따라야 할 몇 가지 권장 사항입니다: 

대규모 Azure SQL 데이터베이스 하이퍼스케일 데이터베이스에서 축소 작업을 수행할 때는 다음 모범 사례를 따르는 것이 좋습니다: 

  1. 사용된 공간 대 할당된 공간 평가 -  축소 작업을 시작하기 전에 각 데이터 파일의 사용된 공간과 할당된 공간을 검토하고 결과를 기록하십시오: 

    SELECT file_id, 

           CAST(SUM(FILEPROPERTY(name, 'SpaceUsed')) AS bigint) * 8 / 1024. AS space_used_mb, 

           CAST(SUM(size) AS bigint) * 8 / 1024. AS space_allocated_mb 

    FROM sys.database_files 

    WHERE type_desc = 'ROWS' 

    GROUP BY file_id; 

  2. TRUNCATEONLY를 사용하여 축소 - 

    각 데이터 파일에 대해 DBCC SHRINKFILE을 TRUNCATEONLY 옵션과 함께 실행하십시오. 이는 해당 파일의 끝에 있는 사용되지 않는 공간을 신속하게 확보할 수 있습니다:  

    DBCC SHRINKFILE (<file_id>, TRUNCATEONLY); 

     

    모든 해당 파일 ID를 검색하려면:  

    SELECT file_id 

    FROM sys.database_files 

    WHERE type_desc = 'ROWS'; 

  3. 공간 확보 확인 - 할당된 공간이 줄어들었는지 확인하기 위해 공간 사용량 쿼리를 다시 실행하십시오. 
  4. 점진적 목표 크기 사용 - 

    사용되지 않는 할당된 공간이 남아 있다면, 점진적으로 증가하는 목표 크기를 지정하십시오. 예를 들어, 파일 크기가 1GB이고 100MB만 사용 중인 경우, 파일을 점진적으로 축소하십시오:  

    DBCC SHRINKFILE (1, 900); 

    DBCC SHRINKFILE (1, 800); 

    원하는 크기에 도달할 때까지 계속하십시오 (위 값, 즉 800 또는 900은 MB 단위입니다).. 

     

 

추가 모범 사례: 

1. 데이터베이스에서 여러 DBCC SHRINKFILE 작업을 동시에 실행할 수 있으며, 이는 전체 실행 시간을 줄이는 데 도움이 될 수 있습니다. 문제는 병렬 축소 수를 너무 많이 늘리면 서로를 차단하고 교착 상태에 빠뜨려 역효과를 낳을 수 있다는 것입니다. 이러한 경우 DBCC SHRINKFILE의 `WAIT_AT_LOW_PRIORITY` 옵션을 사용하면 잠금 문제를 완화할 수 있습니다. 

2. 다음 쿼리를 사용하여 축소 진행 상황을 모니터링할 수 있습니다:

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'); 

3. 향후 축소 작업을 위해서는 사용된 공간에 약 3GB를 더한 목표 크기를 지정하는 것이 좋습니다. 너무 작은 목표 크기 범위는 반복적인 전처리로 인해 종종 더 긴 실행 시간과 감소된 효율성을 초래합니다. 

4. **비업무 시간 또는 계획된 유지 관리 기간** 동안 축소 작업을 수행하십시오. 피크 시간의 활성 작업 부하는 차단 및 시간 초과를 유발할 수 있습니다. 

5. 대규모 LOB 또는 컬럼스토어 데이터를 포함하는 데이터베이스는 축소하는 데 추가 시간이 필요할 수 있습니다. 컬럼스토어 인덱스는 내부적으로 LOB 스토리지를 사용하므로 재구축하면 축소 효율성을 높일 수 있습니다. 또한 이 [문서](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17#reorganize-with--lob_compaction---on--off--)에 명시된 바와 같이, DB에 LOB가 있는 개체가 있다면 축소 전에 압축하는 것이 도움이 될 수 있습니다.   

6. 인덱스 재구축은 축소 작업과 동시에 실행해서는 안 됩니다. 인덱스 재구축에는 여유 공간이 필요하며 파일 증가를 유발하여 축소 노력에 역행할 수 있습니다. 인덱스를 먼저 재구축한 다음 축소 작업을 수행하십시오. 페이지 밀도가 낮으면 재구축하고, 페이지 밀도가 높으면 재구축할 필요가 없습니다.  

7. 새 데이터는 항상 비례 채우기(proportional fill) 알고리즘에 따라 최대 빈 공간을 가진 파일에 분배됩니다. 이는 [여기](https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver17)에 자세히 설명되어 있습니다. 테이블과 파일 간에는 일대일 매핑이 없으며, 병렬 축소 작업 중에 때때로 교착 상태가 발생할 수 있습니다. 

8. 인덱스 재구축은 특히 페이지 밀도가 낮은 경우 축소 효율성을 향상시킬 수 있다는 점에 유의해야 합니다. 핵심은 축소 *전에* 재구축해야 한다는 것입니다. 따라서 특히 대규모 테이블의 경우 페이지 밀도가 80% 미만인 인덱스 재구축에 중점을 두는 것이 가장 좋으며, 장시간 실행되는 쿼리를 피하기 위해 인덱스 통계에 샘플링 모드를 사용하는 것이 좋습니다 (샘플링 모드에 대한 자세한 내용은 DMV `sys.dm_db_index_physical_stats`를 [여기](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver17#scanning-modes)에서 확인하십시오). 페이지 밀도를 확인하려면 [여기](https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage?view=azuresql-db#evaluate-index-page-density)의 스크립트를 사용하십시오. 

9. 컬럼스토어 인덱스는 내부적으로 LOB 데이터 형식을 사용하며, 재구축하지 않으면 축소를 방해할 수 있습니다. 파일 끝에 있는 데이터가 잘라내기를 방해할 수 있으므로 컬럼스토어 인덱스 재구축을 우선시하는 것이 도움이 될 수 있습니다.  

10. 필요한 경우, 한 번에 두세 개의 파일에 대해 축소 작업을 시도하고, 차단 또는 교착 상태를 모니터링하면서 병렬 처리를 신중하게 조정하십시오. 

 

참고 자료 

[Azure SQL Database 하이퍼스케일 축소가 이제 일반 공급됩니다 | Microsoft Community Hub](https://techcommunity.microsoft.com/blog/azuresqlblog/shrink-for-azure-sql-database-hyperscale-is-now-generally-available/4371490) 

[DBCC SHRINKFILE (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver17#syntax) 

[데이터베이스 파일 공간 관리 - Azure SQL Database | Microsoft Learn](https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage?view=azuresql-db#shrink-large-databases) 

[성능 향상 및 리소스 활용 감소를 위한 인덱스 최적 유지 관리 - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver17) 

[페이지 및 익스텐트 아키텍처 가이드 - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver17)  

[sys.dm_db_index_physical_stats (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver17) 

[ALTER INDEX (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver17#reorganize-with--lob_compaction---on--off--)