적용 대상:SQL Server
Azure SQL Database
Azure SQL Managed Instance
이 문서에서는 IFI(인스턴트 파일 초기화)와 SQL Server 데이터베이스 파일의 증가 속도를 높일 수 있도록 설정하는 방법에 대해 알아봅니다.
기본값으로 데이터 및 로그 파일이 초기화되어 이전에 삭제한 파일의 디스크에 남아 있는 기존 데이터를 덮어씁니다. 데이터 및 로그 파일은 사용자가 다음 작업 중 하나를 수행할 때 파일을 제로화(0으로 채움)하여 초기화됩니다.
- 데이터베이스 만들기
- 데이터베이스에 데이터 또는 로그 파일을 추가합니다.
- 기존 파일의 크기(자동 증가 작업 포함)를 증가시킵니다.
- 데이터베이스 또는 파일 그룹을 복원합니다.
SQL Server에서 인스턴트 파일 초기화를 사용하면 해당 공간을 0으로 채우지 않고 사용된 디스크 공간을 회수하므로 앞서 언급한 파일 작업을 더 빠르게 실행할 수 있습니다. 대신 새 데이터가 파일에 기록되므로 이전 디스크 콘텐츠를 덮어씁니다.
Azure SQL 데이터베이스 및 Azure SQL Managed Instance에서 인스턴트 파일 초기화는 트랜잭션 로그 파일에 대해서만 사용 가능합니다.
인스턴트 파일 초기화 및 트랜잭션 로그
적용 대상: SQL Server 2022(16.x) 이상, Azure SQL Database 및 Azure SQL Managed Instance.
이전에는 트랜잭션 로그 파일을 즉시 초기화할 수 없었습니다. 그러나 SQL Server 2022(16.x)(모든 버전)부터 Azure SQL Database 및 Azure SQL Managed Instance에서 최대 64MB의 트랜잭션 로그 증가 이벤트가 인스턴트 파일 초기화를 통해 이점을 누릴 수 있습니다. 새 데이터베이스의 기본 자동 증가 크기 증분은 64MB입니다. 64MB보다 큰 트랜잭션 로그 파일 자동 증가 이벤트는 즉시 파일 초기화의 이점을 얻을 수 없습니다.
TDE(투명한 데이터 암호화)를 사용하는 경우 방지되는 데이터 파일에 대한 인스턴트 파일 초기화와 달리 트랜잭션 로그 파일이 증가하는 방식과 트랜잭션 로그가 직렬 방식으로 기록되기 때문에 TDE를 사용하도록 설정된 데이터베이스의 트랜잭션 로그 증가에 대해 인스턴트 파일 초기화가 허용됩니다.
인스턴트 파일 초기화는 트랜잭션 로그 파일의 증가에만 도움이 되도록 Azure SQL Database 및 Azure SQL Managed Instance의 범용 및 중요 비즈니스용 계층에 사용됩니다.
인스턴트 파일 초기화는 Azure SQL Database 및 Azure SQL Managed Instance에서 구성할 수 없습니다.
인스턴트 파일 초기화를 사용하도록 설정
데이터 파일의 인스턴트 파일 초기화는 데이터베이스 엔진 서비스의 서비스 계정 또는 서비스 SID에 권한이 부여된 SE_MANAGE_VOLUME_NAME
경우에만 사용할 수 있습니다. Windows Administrators 그룹의 구성원은 이 권한을 가지며 볼륨 유지 관리 작업 수행 보안 정책에 추가하여 다른 사용자에게 부여할 수 있습니다.
SE_MANAGE_VOLUME_NAME
SQL Server 2022(16.x) 릴리스와 함께 도입된 트랜잭션 로그에서 최대 64MB의 증가 이벤트를 즉시 파일로 초기화하는 데는 권한이 필요하지 않습니다.
데이터베이스 엔진 서비스의 서비스 SID에 SE_MANAGE_VOLUME_NAME
권한을 부여할 것을 권장합니다. 이렇게 하면 데이터베이스 엔진 서비스의 서비스 계정을 변경하더라도 부여가 유지됩니다. 자세한 내용은 서비스 SID를 사용하여 SQL Server의 서비스에 대한 권한을 부여하는 방법을 참조하세요.
Important
TDE(투명한 데이터 암호화)와 같은 일부 기능은 인스턴트 파일 초기화를 막을 수 있습니다. SQL Server 2022(16.x) 이상 버전 및 Azure SQL Database 및 Azure SQL Managed Instance에서는 트랜잭션 로그에서 IFI가 허용됩니다. 자세한 내용은 인스턴트 파일 초기화 및 트랜잭션 로그를 참조하세요.
SQL Server 2016(13.x) 이상 버전에서는 설치 시 SID(데이터베이스 엔진 서비스 보안 식별자)에 이 권한을 부여할 수 있습니다.
명령 프롬프트 설치를 사용하는 경우 /SQLSVCINSTANTFILEINIT
인수를 추가하거나 설치 마법사의 SQL Server 데이터베이스 엔진 서비스에 볼륨 유지 관리 작업 권한 부여 확인란을 선택합니다.
계정 또는 서비스 SID에 Perform volume maintenance tasks
보안 정책을 부여합니다.
데이터 파일을 만들 컴퓨터에서 로컬 보안 정책 애플리케이션(
secpol.msc
)을 엽니다.왼쪽 창에서 로컬 정책을 확장한 다음 사용자 권한 할당을 선택합니다.
오른쪽 창에서 볼륨 유지 관리 작업 수행을 두 번 클릭합니다.
사용자 또는 그룹 추가를 선택하고 데이터베이스 엔진 서비스 계정 또는 해당 서비스 SID를 추가합니다.
적용을 선택한 다음 모든 로컬 보안 정책 대화 상자를 닫습니다.
데이터베이스 엔진 서비스를 다시 시작합니다.
시작할 때 데이터베이스 엔진 오류 로그를 확인합니다.
적용 대상: SQL Server 2012(11.x) SP4, SQL Server 2014(12.x) SP2 및 SQL Server 2016(13.x) 이상입니다.
데이터베이스 엔진 서비스 계정 또는 해당 서비스 SID에 권한이 부여되면
SE_MANAGE_VOLUME_NAME
다음 예제와 유사한 정보 메시지가 기록됩니다.Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
데이터베이스 엔진 서비스 계정 또는 해당 서비스 SID에 권한이 부여
SE_MANAGE_VOLUME_NAME
되지 않은 경우 다음 예제와 유사한 정보 메시지가 기록됩니다.Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
보안 고려 사항
혜택이 보안 위험보다 클 수 있으므로 인스턴트 파일 초기화를 사용하도록 설정하는 것이 좋습니다.
인스턴트 파일 초기화를 사용하는 경우 삭제된 디스크 콘텐츠는 새 데이터가 파일에 기록될 때만 덮어씁니다. 이러한 이유로 일부 다른 데이터가 데이터 파일의 특정 영역에 기록되기 전까지는 권한 없는 보안 주체가 삭제된 콘텐츠에 액세스할 수 있는 가능성이 있습니다.
데이터베이스 파일이 SQL Server 인스턴스에 연결되어 있는 동안 이러한 정보 공개 위험은 파일의 DACL(임의 액세스 제어 목록)에 의해 감소됩니다. DACL은 SQL Server 서비스 계정, 해당 서비스 SID 및 로컬 관리자에 대한 파일 액세스만 허용합니다. 그러나 파일이 분리되면 권한이 없는 SE_MANAGE_VOLUME_NAME
사용자 또는 서비스에서 액세스할 수 있습니다.
다음의 경우에도 비슷한 고려 사항이 있습니다.
데이터베이스가 백업됩니다. 백업 파일이 적절한 DACL로 보호되지 않는 경우 권한이 없는 사용자 또는 서비스가 삭제된 내용을 사용할 수 있게 됩니다.
파일은 IFI를 사용하여 증가합니다. SQL Server 관리자는 잠재적으로 원시 페이지 콘텐츠에 액세스하고 이전에 삭제된 콘텐츠를 볼 수 있습니다.
데이터베이스 파일은 스토리지 영역 네트워크에서 호스트됩니다. 저장 영역 네트워크가 항상 미리 초기화된 새 페이지를 표시하고, 운영 체제에서 페이지를 다시 초기화하도록 하여 불필요한 오버헤드가 발생할 수 있습니다.
삭제된 콘텐츠를 공개할 가능성이 우려되는 경우 다음 작업 중 하나 또는 둘 모두를 수행해야 합니다.
항상 분리된 데이터 파일 및 백업 파일에 제한적인 DACL이 있는지 확인합니다.
SQL Server 인스턴스에 대한 인스턴트 파일 초기화를 사용하지 않도록 설정합니다. 이를 수행하려면 데이터베이스 엔진 서비스 계정과 해당 서비스 SID에서
SE_MANAGE_VOLUME_NAME
의 권한을 해지하십시오.참고 항목
IFI를 사용하지 않도록 설정하면 데이터 파일의 증가 시간이 늘어나고 권한이 취소된 후에 생성되거나 크기가 증가한 파일에만 영향을 줍니다.
SE_MANAGE_VOLUME_NAME 권한
권한은 SE_MANAGE_VOLUME_NAME
Windows 관리 도구, 로컬 보안 정책 애플릿에서 할당할 수 있습니다.
로컬 정책에서 사용자 권한 할당을 선택하고 볼륨 유지 관리 작업 수행 속성을 수정합니다.
성능 고려 사항
데이터베이스 파일 초기화 프로세스는 초기화 중인 파일의 새 영역에 0을 씁니다. 이 프로세스의 기간은 초기화된 파일 부분의 크기와 스토리지 시스템의 응답 시간 및 용량에 따라 달라집니다. 초기화가 오래 걸리는 경우 SQL Server 오류 로그 및 애플리케이션 로그에 다음과 같은 메시지가 기록될 수 있습니다.
Msg 5144
Autogrow of file '%.*ls' in database '%.*ls' was cancelled by user or timed out after %d milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
Msg 5145
Autogrow of file '%.*ls' in database '%.*ls' took %d milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
데이터베이스 및/또는 트랜잭션 로그 파일의 자동 증가가 길면 쿼리 성능 문제가 발생할 수 있습니다. 파일 증가 작업의 기간 동안 파일의 자동 증가가 필요한 작업이 잠금이나 래치 같은 리소스를 유지합니다. 할당 페이지에 대한 래치에서 긴 대기가 나타날 수 있습니다. 긴 자동 증가가 필요한 작업에는 PREEMPTIVE_OS_WRITEFILEGATHER
대기 유형이 표시됩니다.