SQL Server 자동 증가 및 자동 축소 설정에 대한 고려 사항

아티클
06/20/2023

원래 제품 버전: SQL Server
원래 KB 번호: 315512

요약

기본 자동 증가 및 자동 축소 설정은 많은 SQL Server 시스템에 적합합니다. 그러나 자동 증가 및 자동 축소 매개 변수를 조정해야 하는 환경이 있습니다. 이 문서에서는 환경에 대해 이러한 설정을 선택하는 시기를 안내하는 몇 가지 배경 정보를 제공합니다.

자동 증가 및 자동 축소 매개 변수를 조정하기로 결정한 경우 고려해야 할 몇 가지 사항은 다음과 같습니다.

설정 구성 어떻게 할까요?

다음 중 하나를 사용하여 자동 증가 및 자동 축소 설정을 구성하거나 수정할 수 있습니다.
- SQL Server Management Studio
- ALTER DATABASE 문
  - 파일 및 파일 그룹 옵션을 사용하여 자동 증가 설정 수정
  - SET 옵션을 사용하여 설정을 구성 AUTO_SHRINK 합니다.
참고

데이터베이스 파일 수준에서 이러한 설정을 설정하는 방법에 대한 자세한 내용은 데이터베이스에 데이터 또는 로그 파일 추가를 참조하세요.

데이터베이스를 만들 때 자동 증가 옵션을 구성할 수도 있습니다.

현재 설정을 보려면 다음 Transact-SQL 명령을 실행합니다.
```
sp_helpdb [ [ @dbname= ] 'name' ]
```
자동 증가 설정은 파일당 입니다. 따라서 각 데이터베이스에 대해 두 개 이상의 위치(기본 데이터 파일의 경우 하나, 기본 로그 파일에 대해 하나씩)로 설정해야 합니다. 데이터 및/또는 로그 파일이 여러 개 있는 경우 각 파일에 대한 옵션을 설정해야 합니다. 사용자 환경에 따라 각 데이터베이스 파일에 대해 서로 다른 설정으로 끝날 수 있습니다.

AUTO_SHRINK 대한 고려 사항

AUTO_SHRINK는 SQL Server 데이터베이스 옵션입니다. 데이터베이스에 이 옵션을 사용하도록 설정하면 이 데이터베이스가 백그라운드 작업으로 축소될 수 있습니다. 이 백그라운드 작업은 데이터 또는 로그 파일을 축소 및 축소하기 위한 조건을 충족하는 모든 데이터베이스를 평가합니다.

SQL Server instance 데이터베이스에 대해 이 옵션 설정을 신중하게 평가해야 합니다. 빈번한 증가 및 축소 작업으로 인해 다양한 성능 문제가 발생할 수 있습니다.

여러 데이터베이스가 자주 축소되고 증가하는 작업이 발생하면 파일 시스템 수준 조각화로 쉽게 이어질 수 있습니다. 성능에 심각한 영향을 미칠 수 있습니다. 자동 설정을 사용하든, 수동으로 파일을 자주 확장하고 축소하는지 여부에 관계없이 마찬가지입니다.
데이터 또는 로그 파일을 성공적으로 축소한 후 AUTO_SHRINK 공간이 필요하고 파일이 증가해야 하는 경우 후속 DML 또는 DDL 작업이 상당히 느려질 수 있습니다.
AUTO_SHRINK 축소가 필요한 데이터베이스가 많은 경우 백그라운드 작업이 리소스를 차지할 수 있습니다.
AUTO_SHRINK 백그라운드 작업은 다른 일반 애플리케이션 활동과 충돌할 수 있는 잠금 및 기타 동기화를 획득해야 합니다.

데이터베이스를 필요한 크기로 설정하고 미리 늘리는 것이 좋습니다. 애플리케이션 사용 패턴이 다시 필요하다고 생각되면 데이터베이스 파일에 사용되지 않는 공간을 그대로 둡니다. 이렇게 하면 데이터베이스 파일의 자주 축소 및 증가를 방지할 수 있습니다.

AUTOGROW에 대한 고려 사항

사용 가능한 것보다 더 많은 로그 공간이 필요한 트랜잭션을 실행하고 해당 데이터베이스의 트랜잭션 로그에 대한 자동 증가 옵션을 설정한 경우 트랜잭션이 완료되는 데 걸리는 시간에는 트랜잭션 로그가 구성된 양만큼 증가하는 데 걸리는 시간이 포함됩니다. 증가 증가가 크거나 시간이 오래 걸리는 다른 요소가 있는 경우 시간 제한 오류로 인해 트랜잭션을 여는 쿼리가 실패할 수 있습니다. 동일한 종류의 문제는 데이터베이스의 데이터 부분 자동 증가로 인해 발생할 수 있습니다.
로그가 증가해야 하는 대규모 트랜잭션을 실행하는 경우 트랜잭션 로그에 쓰기가 필요한 다른 트랜잭션도 증가 작업이 완료될 때까지 기다려야 합니다.
로그 파일에 많은 파일이 증가하는 경우 VLF(가상 로그 파일)가 지나치게 많을 수 있습니다. 이로 인해 데이터베이스 시작/온라인 작업, 복제, 미러링 및 CDC(변경 데이터 캡처)의 성능 문제가 발생할 수 있습니다. 또한 이로 인해 데이터 수정에 성능 문제가 발생할 수 있습니다.

참고

자동 증가 및 자동 축소 옵션을 결합하면 불필요한 오버헤드가 발생할 수 있습니다. 증가 및 축소 작업을 트리거하는 임계값으로 인해 크기가 자주 변경되지 않는지 확인합니다. 예를 들어 트랜잭션 로그가 커밋할 때까지 100MB씩 증가하는 트랜잭션을 실행할 수 있습니다. 그 후 얼마 지나지 않아 자동 축소가 시작되고 트랜잭션 로그가 100MB씩 축소됩니다. 그런 다음 동일한 트랜잭션을 실행하면 트랜잭션 로그가 다시 100MB씩 증가합니다. 이 예제에서는 불필요한 오버헤드를 만들고 잠재적으로 로그 파일의 조각화를 만들어 성능에 부정적인 영향을 줄 수 있습니다.

데이터베이스를 작게 늘리거나 데이터베이스를 확장한 다음 축소하는 경우 디스크 조각화로 끝날 수 있습니다. 디스크 조각화는 경우에 따라 성능 문제를 일으킬 수 있습니다. 작은 증가 시나리오는 시스템의 성능을 저하시킬 수도 있습니다.

SQL Server 인스턴트 파일 초기화를 사용하도록 설정할 수 있습니다. 인스턴트 파일 초기화는 데이터 파일에 대해서만 파일 할당 속도를 향상합니다. 인스턴트 파일 초기화는 로그 파일에 적용되지 않습니다. 자세한 내용은 데이터베이스 인스턴트 파일 초기화를 참조하세요.

자동 증가 및 자동 축소에 대한 모범 사례

관리되는 프로덕션 시스템의 경우 자동 증가는 예기치 않은 성장을 위한 비상일 뿐이라고 생각해야 합니다. 자동 증가로 데이터 및 로그 증가를 매일 관리하지 마세요.
경고 또는 모니터링 프로그램을 사용하여 파일 크기를 모니터링하고 파일을 사전에 늘릴 수 있습니다. 이렇게 하면 조각화를 방지하고 이러한 유지 관리 작업을 사용량이 적은 시간으로 전환할 수 있습니다.
자동 축소 및 자동 증가는 학습된 DBA(데이터베이스 관리자)에 의해 신중하게 평가되어야 합니다. 관리되지 않는 상태로 두면 안 됩니다.
자동 증가 증가는 이전 섹션에 나열된 성능 저하를 방지할 수 있을 만큼 커야 합니다. 구성 설정에 사용할 정확한 값과 백분율 증가율과 특정 MB 크기 증가 중에서 선택하는 것은 환경의 여러 요인에 따라 달라집니다. 테스트에 사용할 수 있는 일반적인 규칙은 자동 증가 설정을 파일 크기의 약 1-8로 설정하는 것입니다.
각 파일에 대한 설정을 켜 \<MAXSIZE> 면 한 파일이 사용 가능한 모든 디스크 공간을 사용하는 지점으로 증가하지 않도록 합니다.
계획되지 않은 파일 증가를 방지하기 위해 트랜잭션 크기를 가능한 한 작게 유지합니다.

크기 설정이 자동으로 제어되는 경우 디스크 공간에 대해 걱정해야 하는 이유

자동 증가 설정은 파일이 정의된 드라이브에서 사용 가능한 디스크 공간의 제한을 초과하여 데이터베이스 크기를 늘릴 수 없습니다. 따라서 자동 증가 기능을 사용하여 데이터베이스 크기를 조정하는 경우 사용 가능한 하드 디스크 공간을 독립적으로 검사 합니다. 자동 증가 설정은 각 파일에 대해 선택하는 매개 변수에 MAXSIZE 의해 제한됩니다. 공간이 부족할 가능성을 줄이기 위해 성능 모니터 카운터 SQL Server: 데이터베이스 개체: KB(데이터 파일) 크기를 모니터링하고 데이터베이스가 특정 크기에 도달하면 경고를 설정할 수 있습니다.
데이터 또는 로그 파일의 계획되지 않은 증가는 다른 애플리케이션이 사용할 수 있을 것으로 예상되는 공간을 차지할 수 있으며 다른 애플리케이션에 문제가 발생할 수 있습니다.
트랜잭션 로그의 증가 증가는 트랜잭션 단위의 요구 사항을 앞지를 수 있을 만큼 커야 합니다. 자동 증가가 켜져 있더라도 쿼리 요구 사항을 충족할 만큼 빠르게 증가할 수 없는 경우 트랜잭션 로그가 가득 찼다는 메시지를 받을 수 있습니다.
SQL Server 자동 축소에 대해 구성된 임계값에 도달한 데이터베이스에 대해 지속적으로 테스트하지 않습니다. 대신 사용 가능한 데이터베이스를 살펴보고 자동 축소하도록 구성된 첫 번째 데이터베이스를 찾습니다. 필요한 경우 해당 데이터베이스를 확인하고 해당 데이터베이스를 축소합니다. 그런 다음 자동 축소에 대해 구성된 다음 데이터베이스를 확인하기 전에 몇 분 정도 기다립니다. 즉, SQL Server 모든 데이터베이스를 한 번에 검사 한 번에 모두 축소하지는 않습니다. 일정 기간 동안 부하를 엇갈리게 하기 위해 라운드 로빈 방식으로 데이터베이스를 통해 작동합니다. 따라서 특정 SQL Server instance 자동 축소하도록 구성한 데이터베이스 수에 따라 데이터베이스가 임계값에 도달한 시간부터 실제로 축소될 때까지 몇 시간이 걸릴 수 있습니다.