SQL Server의 데이터베이스 자동 증가 이해

Autogrow of file 'DBFile' in database 'DBName' was cancelled by user or timed out after 00000 milliseconds.
Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

---

SQL Server의 데이터베이스 자동 증가 이해

MANVENDRA DEO SINGH 작성 · 발행됨 2017년 9월 26일· 업데이트2021년 7월 23일

SQL Server 데이터베이스 자동 증가  설정을 사용하여 데이터베이스 파일 증가를 자동화합니다. 이 속성은 적절한 계획과 함께 신중하게 사용하는 경우 매우 유용합니다. 그러나 주의를 기울이지 않고 기본값으로 두는 경우 데이터베이스 성능에 부정적인 영향을 미칠 수 있습니다. 데이터베이스에 자동 증가 이벤트가 너무 많으면 데이터베이스의 전체 성능이 저하될 수 있습니다. 다음은 성능 향상을 위해 데이터베이스에서 자동 증가 이벤트를 줄이기 위해 고려할 수 있는 사항입니다.

최적의 데이터베이스 크기 설정

데이터베이스는 절대 커지지 않을 만큼 충분한 크기여야 하며, 커지면 해당 SQL Server 인스턴스에서 인스턴트 파일 초기화 를 활성화해야 합니다. 자동 증가 는 데이터베이스 파일 의 예기치 않은 공간 문제 시나리오를 방지하기 위해 사용해야 하는 옵션입니다 . 자동 증가 설정 사용을 최소화할 수 있는 방식으로 데이터베이스 크기를 계획해야 합니다. 시간 경과에 따라 데이터베이스가 얼마나 커질지 파악하고 데이터베이스 크기를 해당 값으로 설정해야 합니다. 몇 달에 한 번씩 데이터베이스 크기를 분석하여 증가해야 하는 경우 크기를 조정하십시오. 이렇게 하면 데이터베이스 파일에서 수행할 자동 증가 이벤트를 최소화할 수 있습니다.

SQL Server에서 데이터베이스 자동 증가 크기 조정

자동 증가 이벤트는 자동 증가 이벤트가 수행될 때마다 SQL Server가 데이터베이스 처리를 보류하기 때문에 데이터베이스 성능을 저하시키는 비용이 많이 드는 작업입니다. 이는 증가하는 데이터베이스에 대해 처리 중인 SQL 명령에 대한 응답 시간이 느려지는 것과 같습니다.

로그가 커져야 하는 큰 트랜잭션을 실행하는 경우 트랜잭션 로그 에 쓰기가 필요한 다른 트랜잭션 도 자동 증가 작업이 완료될 때까지 기다려야 합니다. 데이터베이스가 자동 증가 이벤트를 자주 수행하는 경우 발생할 자동 증가 이벤트 수를 줄이는 데이터베이스 자동 증가 설정의 크기를 적절하게 조정하여 성능을 향상할 수 있습니다.

자동 증가의 또 다른 부정적인 측면은 디스크 조각화입니다. 자동 증가 이벤트가 많을수록 더 많은 물리적 조각화가 발생합니다. 데이터베이스가 물리적으로 조각난 경우 SQL Server가 해당 데이터베이스를 읽는 데 더 많은 시간이 걸립니다. 자동 증가 이벤트와 관련된 문제를 방지하려면 발생 횟수를 최소화해야 합니다.

기본 자동 증가 설정 변경

내 경험에 따르면 데이터베이스 파일을 기본 자동 증가 설정으로 두지 마십시오. 데이터 파일의 자동 증가 기본값은 1MB이고 로그 파일은 전체 로그 파일 크기의 10%입니다. 이러한 자동 증가 값은 모델 데이터베이스에서 상속됩니다. 이 SQL Server 인스턴스에서 생성하는 모든 데이터베이스에 적용할 모델 데이터베이스의 각 데이터베이스 파일의 자동 증가 크기를 변경할 수 있습니다.

예:  자동 증가 설정을 기본값인 1MB 에서 데이터베이스 파일 크기의 1/8 로 변경할 수 있습니다. 이렇게 하면 데이터베이스의 총 자동 증가 이벤트 수가 다시 줄어듭니다. 해당 SQL Server 인스턴스에서 인스턴트 파일 초기화를 활성화해야 합니다.

모니터링

데이터베이스 증가 패턴을 알고 있는 경우 해당 증가에 따라 자동 증가를 설정합니다. 향후 데이터베이스 증가에 대해 전혀 모르는 경우 데이터베이스 파일 크기와 해당 자동 증가 이벤트를 일주일 또는 한 달 동안 사전에 모니터링한 다음 증가 패턴에 따라 설정해야 합니다. 또한 디스크 또는 데이터베이스 파일이 가득 차서 향후 중단을 방지하기 위해 모든 데이터베이스에 대한 데이터베이스 증가 기록을 사전 예방적으로 모니터링해야 합니다.

데이터베이스 디스크 사용량 대시보드 보고서를 분석하여 데이터베이스 파일에 대해 자동 증가 이벤트가 발생한 횟수를 얻을 수 있습니다. 이 기사의 뒷부분에서 이에 대해 설명했습니다.

SQL Server의 데이터베이스 자동 증가 모범 사례

아래 4가지 사항에 중점을 두어 데이터베이스 성능을 향상시키고 디스크 공간 활용도를 관리할 수 있습니다. 이렇게 하면 데이터베이스 파일에서 발생하는 자동 증가 이벤트 수도 줄어듭니다.

1. 데이터베이스 증가 패턴을 분석하고 분석된 증가 설정으로 최적의 데이터베이스 크기를 설정합니다. 데이터베이스는 절대 커지지 않을 만큼 충분한 크기여야 하며, 커지면 해당 SQL Server 인스턴스에서 인스턴트 파일 초기화를 활성화해야 합니다 . 이렇게 하면 일상 생활에서 SQL Server 자동 증가 이벤트가 크게 줄어듭니다.
2. 데이터베이스 파일 크기와 해당 자동 증가 이벤트를 사전에 모니터링합니다. 이렇게 하면 데이터베이스 파일의 증가 패턴을 분석하고 조각화를 방지할 수 있습니다.
3. 데이터베이스에서 자동 증가 이벤트가 많이 발생한 경우 데이터베이스 파일 시스템 조각 모음을 고려하십시오.
4. 데이터베이스 자동 증가를 기본값으로 두지 말고 시간 경과에 따라 분석한 증가 패턴에 따라 변경하십시오.
5. 백분율로 자동 증가를 사용하지 말고 MB/GB 단위로 특정 크기를 지정하십시오. Microsoft는 자동 증가 설정을 파일 크기의 약 1/8로 설정하고 PROD에 배포하기 전에 테스트할 것을 제안합니다.
6. 각 파일에 대해 <MAXSIZE> 설정을 켜서 하나의 파일이 사용 가능한 모든 디스크 공간을 사용하는 지점까지 커지는 것을 방지합니다.
7. 트랜잭션 로그의 증가분은 트랜잭션 단위의 요구 사항보다 앞서 나갈 수 있을 만큼 충분히 커야 합니다. 자동 증가가 켜져 있더라도 쿼리 요구 사항을 충족할 만큼 빠르게 증가할 수 없는 경우 트랜잭션 로그가 가득 찼다는 메시지를 받을 수 있습니다.

SQL Server에서 데이터베이스 자동 증가를 변경하시겠습니까?

아래 명령을 실행하여 데이터베이스 파일의 자동 증가 값을 변경할 수 있습니다.

--NAME 섹션 아래에 데이터베이스 파일의 논리 파일 이름을 전달합니다. 
ALTER DATABASE [Techyaz] 파일 수정( NAME = N'Techyaz_Data', FILEGROWTH = 10000KB )

SSMS에서 GUI를 사용하여 자동 증가 설정을 변경할 수도 있습니다. 데이터베이스 속성 창을 시작합니다. 왼쪽 창에서 파일 탭을 클릭한 다음 모든 데이터베이스 파일 옆에 표시되는 세 개의 점(...)을 클릭합니다. 아래 스크린샷에서 이 창을 볼 수 있습니다.

데이터베이스에서 발생한 총 자동 증가 이벤트 수를 보는 방법은 무엇입니까?

"디스크 사용량" 대시보드 보고서를 실행하여 데이터베이스에 대해 발생한 자동 증가 이벤트 수를 확인할 수 있습니다. 총 자동 증가 이벤트 수를 보려는 데이터베이스를 마우스 오른쪽 버튼으로 클릭합니다. "보고서"를 선택한 다음 아래 이미지와 같이 "디스크 사용량"을 선택합니다.

디스크 사용량에 대한 대시보드 보고서가 아래에 표시됩니다. 공간 사용에 대한 모든 세부 정보를 볼 수 있습니다. 자동 성장 이벤트도 동일한 보고서에 캡처됩니다. 아래 스크린샷에서 해당 항목을 강조 표시했습니다.

더하기 + 기호를 사용하면 데이터베이스에 대해 발생한 총 자동 증가 이벤트 수를 볼 수 있습니다. 총 숫자와 총 공간도 셀 수 있습니다. 이 분석을 기반으로 자동 증가 설정을 최적의 값으로 변경하여 여기에 표시된 만큼의 숫자를 줄일 수 있습니다.

 

출처:  https://techyaz.com/sql-server/understanding-database-autogrowth-sql-server/

Understanding Database Autogrowth in SQL Server