SQL Server 구성튜닝 - 볼륨유지관리 작업수행(DataFile Initialization)

SQL Server 를 설치하고 기본으로 사용해도 무방하다.

그러나 조금의 성능향상을 위해서 필요한 구성들이 있다.

"볼륨 유지 관리 작업 수행" 옵션 설정하기.

<설명>

SQL Server 2005에서는 데이터 파일을 즉시 초기화할 수 있습니다. 이를 통해 위에서 언급한 파일 작업을 신속하게 수행할 수 있습니다. 즉시 파일 초기화는 사용된 디스크 공간을 0으로 채우지 않고 회수합니다. 대신 파일에 새 데이터를 기록할 때 디스크 내용을 덮어씁니다. 로그 파일은 즉시 초기화할 수 없습니다.

<구성 옵션 설정하기>

SQL Server 시작계정을 아래 위치에 추가를 하면 된다.

보안설정 > 로컬 정책 > 사용자 권한 할당 > 볼륨 유지 관리 작업 수행 (Perform Volume Maintenance Tasks) 을 클릭하여 SQL Server 시작계정을 추가해준다 > SQL Server 재 시작 > 완료.

   

<무엇이 좋은가>

Create a database.

Add files, log or data, to an existing database.

Increase the size of an existing file (including autogrow operations).

Restore a database or filegroup.

위 작업이 수행될 때 성능 향상을 기대할 수 있다. 즉 작업의 소요시간을 단축 할 수 있다.

DB 마이그레이션 시 백업파일을 이전서버에서 복원작업을 하는 경우에는 필수로 해당 옵션을 활성화를 해줘야

복원 소요시간을 조금이나마 단축 할 수 있다.

[시나리오 테스트]

파일을 즉시 초기화하는 작업을 하는지 안하는지는 SQL Errorlog 에서 확인이 가능하다.

위 구성 옵션을 적용을 하지 않으면 아래 로그에서 Zeroing 로그에 MDF 파일로 0 으로 초기화를 하는 로그를 볼 수 있다.

아래는 로그는 적용 후 TF로 로그를 확인 한 결과이다.

DBCC TraceON(3605, 3004, -1) -- TraceFlag 를 설정하면 Errorlog 에 쓰여진다.

<restore database norecovery>

2015-04-16 08:44:02.660 spid54 DBCC TRACEON 3605, server process ID (SPID) 54. This is an informational message only; no user action is required.

2015-04-16 08:44:02.680 spid54 DBCC TRACEON 3004, server process ID (SPID) 54. This is an informational message only; no user action is required.

2015-04-16 08:44:40.340 spid54 RestoreDatabase: Database vfs.db

2015-04-16 08:44:40.340 spid54 Opening backup set

2015-04-16 08:44:40.350 spid54 Restore: Configuration section loaded

2015-04-16 08:44:40.350 spid54 Restore: Backup set is open

2015-04-16 08:44:40.350 spid54 Restore: Planning begins

2015-04-16 08:44:40.380 spid54 Restore: Planning complete

2015-04-16 08:44:40.380 spid54 Restore: BeginRestore (offline) on vfs.db

2015-04-16 08:44:40.380 spid54 Restore: Attached database vfs.db as DBID=6

2015-04-16 08:44:40.380 spid54 Restore: PreparingContainers

2015-04-16 08:44:40.410 spid54 Zeroing K:\MSSQL\Log\vfs.db_log.ldf from page 1 to 4986880 (0x2000 to 0x983000000)

2015-04-16 08:44:40.410 spid54 Restore: Containers are ready

2015-04-16 08:44:40.480 spid54 Restore: Restoring backup set

2015-04-16 08:44:40.480 spid54 Restore: Transferring data to vfs.db

2015-04-16 08:52:38.270 spid54 Zeroing completed on K:\MSSQL\Log\vfs.db_log.ldf

2015-04-16 08:59:18.790 spid54 Restore: Waiting for log zero on vfs.db

2015-04-16 08:59:18.800 spid54 Restore: LogZero complete

2015-04-16 08:59:19.360 spid54 FileHandleCache: 0 files opened. CacheSize: 12

2015-04-16 08:59:19.360 spid54 Restore: Data transfer complete on vfs.db

2015-04-16 08:59:19.370 spid54 Restore: Backup set restored

2015-04-16 08:59:19.380 spid54 Starting up database 'vfs.db'.

2015-04-16 08:59:19.390 spid54 The database 'vfs.db' is marked RESTORING and is in a state that does not allow recovery to be run.

2015-04-16 08:59:19.400 spid54 Restore-Redo begins on database vfs.db

2015-04-16 08:59:37.630 spid54 Database vfs.db has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

2015-04-16 08:59:46.050 spid54 Rollforward complete on database vfs.db

2015-04-16 08:59:46.060 spid54 Restore: Done with fixups

2015-04-16 08:59:46.070 spid54 Restore: Writing history records

2015-04-16 08:59:46.070 백업 Database was restored: Database: vfs.db, creation date(time): 2014/01/09(14:18:09), first LSN: 562766:19801:61, last LSN: 562769:20567:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'L:\MSSQL\Backup\BackupFile\vfs.db_backup_2015_04_

2015-04-16 08:59:46.070 spid54 Writing backup history records

2015-04-16 08:59:46.090 spid54 Restore: Done with MSDB maintenance

2015-04-16 08:59:46.090 spid54 RestoreDatabase: Finished

<restore log recovery>

2015-04-16 09:01:27.790 spid54 RestoreLog: Database vfs.db

2015-04-16 09:01:27.790 spid54 X-locking database: vfs.db

2015-04-16 09:01:27.790 spid54 Opening backup set

2015-04-16 09:01:27.800 spid54 Restore: Configuration section loaded

2015-04-16 09:01:27.800 spid54 Restore: Backup set is open

2015-04-16 09:01:27.800 spid54 Restore: Planning begins

2015-04-16 09:01:27.810 spid54 Dismounting FullText catalogs

2015-04-16 09:01:27.810 spid54 Restore: Planning complete

2015-04-16 09:01:27.810 spid54 Restore: BeginRestore (offline) on vfs.db

2015-04-16 09:01:27.810 spid54 Restore: PreparingContainers

2015-04-16 09:01:27.810 spid54 Restore: Containers are ready

2015-04-16 09:01:27.820 spid54 Restore: Restoring backup set

2015-04-16 09:01:27.820 spid54 Restore: Transferring data to vfs.db

2015-04-16 09:01:27.820 spid54 Restore: Waiting for log zero on vfs.db

2015-04-16 09:01:27.820 spid54 Restore: LogZero complete

2015-04-16 09:01:32.320 spid54 FileHandleCache: 0 files opened. CacheSize: 12

2015-04-16 09:01:32.320 spid54 Restore: Data transfer complete on vfs.db

2015-04-16 09:01:32.330 spid54 Restore: Backup set restored

2015-04-16 09:01:32.340 spid54 Restore-Redo begins on database vfs.db

2015-04-16 09:01:35.320 spid54 Database vfs.db has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

2015-04-16 09:01:52.880 spid54 Rollforward complete on database vfs.db

2015-04-16 09:01:52.890 spid54 Restore: Done with fixups

2015-04-16 09:01:52.890 spid54 Restore: Transitioning database to ONLINE

2015-04-16 09:01:52.900 spid54 Restore: Restarting database for ONLINE

2015-04-16 09:01:53.020 spid54 Starting up database 'vfs.db'.

2015-04-16 09:01:54.710 spid54 Database vfs.db has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

2015-04-16 09:01:55.250 spid54 FixupLogTail(progress) zeroing K:\MSSQL\Log\vfs.db_log.ldf from 0x886f8da00 to 0x886f8e000.

2015-04-16 09:01:55.260 spid54 Zeroing K:\MSSQL\Log\vfs.db_log.ldf from page 4470727 to 4471207 (0x886f8e000 to 0x88734e000)

2015-04-16 09:01:55.280 spid54 Zeroing completed on K:\MSSQL\Log\vfs.db_log.ldf

2015-04-16 09:01:57.420 spid54 Recovery is writing a checkpoint in database 'vfs.db' (6). This is an informational message only. No user action is required.

2015-04-16 09:01:57.970 spid54 Recovery completed for database vfs.db (database ID 6) in 3 second(s) (analysis 544 ms, redo 1737 ms, undo 380 ms.) This is an informational message only. No user action is required.

2015-04-16 09:01:57.970 spid54 PostRestoreContainerFixups: running fixups on vfs.db

2015-04-16 09:01:57.980 spid54 PostRestoreContainerFixups: fixups complete

2015-04-16 09:01:58.000 spid54 PostRestoreReplicationFixup for vfs.db starts

2015-04-16 09:01:59.020 spid54 PostRestoreReplicationFixup for vfs.db complete

2015-04-16 09:01:59.040 spid54 Restore: Database is restarted

2015-04-16 09:01:59.040 백업 Restore is complete on database 'vfs.db'. The database is now available.

2015-04-16 09:01:59.050 spid54 Resuming any halted fulltext crawls

2015-04-16 09:01:59.060 spid54 Restore: Writing history records

2015-04-16 09:01:59.060 백업 Log was restored. Database: vfs.db, creation date(time): 2014/01/09(14:18:09), first LSN: 562762:22649:1, last LSN: 562793:10349:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'L:\MSSQL\Backup\BackupFile\vfs.db_backup_2015_04_15_090

2015-04-16 09:01:59.060 spid54 Writing backup history records

2015-04-16 09:01:59.070 spid54 Restore: Done with MSDB maintenance

2015-04-16 09:01:59.070 spid54 RestoreLog: Finished

[결론]

SQL Server가 있으면 무조건 해줘라.

[참고문서]

https://msdn.microsoft.com/en-us/library/ms175935(v=sql.90).aspx