SQL Server 2005 以降にはファイルの瞬時初期化 (Instant File Initialization) という機能があります。
データベース ファイルの初期化
SQL Server のデータベースはデータファイルとログファイルの 2 種類に分かれます。
これらのファイルは作成 / 拡張されたタイミングでファイル内を [0] で満たすことで初期化が行われます。
ファイルの瞬時初期化はこの [0] で満たすという初期化処理をファイルの作成 / 拡張時ではなくデータが書き込まれる際に行うというものになります。
# (使用している領域のみが初期化されることになります。
この瞬時初期化はデータファイル (.mdf / .ndf) ファイルが対象となり、ログファイル (.ldf) は対象になりません。
# ログファイルは必ず [0] を満たす初期化が行われます。
最終的にデータで使用する領域は初期化がおこわなれるのですが、その処理をどのタイミング (領域を確保したタイミング / 使用するタイミング) で行うかという違いがあります。
このファイルの瞬時初期化ですがリストア時にも有効な設定となります。
今回は、リストア時の瞬時初期化についてみていきたいと思います。
SQL Server におけるバックアップと復元のパフォーマンスの最適化
■ファイルの瞬時初期化の設定
ファイルの瞬時初期化を有効にするためには、SQL Server のサービス起動アカウントにローカルセキュリティポリシーで [ボリュームの保守タスクを実行] (SeManageVolumePrivilege) の権限を付与する必要があります。
ローカルセキュリティポリシー (secpol.msc) を起動して、[ローカル ポリシー] → [ボリュームの保守タスクを実行] を確認することで SQL Server のサービス起動アカウントが権限を保持しているかを確認することができます。
デフォルトの設定では、[Administrators] のみが権限を付与されている状態となっています。
そのため、SQL Server のサービスを
- Administrator
- Local System
で実行している場合には権限を付与しなくてもファイルの瞬時初期化が有効になっています。
SQL Server のセキュリティを考慮し強い権限を持つユーザーではなく、Users グループのユーザーで SQL Server のサービスを起動するということが良くあると思います。
その場合には、明示的に権限を付与する必要があります。
SQL Server のサービス起動ユーザーは [サービス] (services.msc) ではなく、必ず [SQL Server 構成マネージャー] から変更をします。
# これはサービスからユーザーを変更してしまうと、サービス起動ユーザーに適切な権限が付与されないためです。
# Denali CTP1 の SQL Server 構成マネージャーを使っているため英語版になっています。
ユーザーを変更したら、サービスを再起動すると設定したユーザーで SQL Server のサービスが起動されます。
サービスが起動してきたらユーザーに付与されている権限を確認してみます。
権限の確認には Sysinternals の [Process Explorer] が便利です。
ツールをダウンロードして起動したら、[sqlservr.exe] の [Properties] をクリックします。
# [winit.exe] → [services.exe] → [sqlservr.exe] という階層になっていると思います。
[Security] タブを見ると付与されている権限を確認することができます。
[ボリュームの保守タスクの実行] の権限の設定はまだしてませんので、[SeManageVolumePrivilege] は付与されていない状態です。
それでは、[ボリュームの保守タスクの実行] の権限を付与し、サービスの再起動を行ってから再度 Process Explorer で確認をしてみます。
Process Explorer から、[SeManageVolumePrivilege] が付与されていることが確
認できますね。
SQL Server でファイルの瞬時初期化を可能にするための準備が整いました。
■リストア時のファイルの瞬時初期化
ここからが本題のリストア時のファイルの瞬時初期化についてです。
RSS で SQL Server 関連の情報を眺めていたら、海外で以下の技術情報がありました。
Undocumented Trace Flags: Inside the Restore Process
リストア時の詳細な情報を出力するためのトレースフラグについて情報が公開されています。
ということでさっそく検証してみたいと思います。
まずは、[TEST] データベースを以下のクエリでリストアしてみます。
|
DROP DATABASE [TEST]
GO
EXEC sp_cycle_errorlog
RESTORE DATABASE [TEST] FROM DISK = N’H:SQL2008R2TEST.bak’
EXEC xp_readerrorlog
|
xp_readerrorlog を実行しているので、SQL Server のログの内容が結果に出力がされます。
|
2011-04-03 13:12:51.020 spid54 Starting up database ‘TEST’.
2011-04-03 13:12:51.040 spid54 The database ‘TEST’ is marked RESTORING and is in a state that does not allow recovery to be run.
2011-04-03 13:12:51.480 spid54 Starting up database ‘TEST’.
2011-04-03 13:12:51.810 バックアップ Restore is complete on database ‘TEST’. The database is now available.
2011-04-03 13:12:51.830 バックアップ Database was restored: Database: TEST, creation date(time): 2011/04/03(12:45:39), first LSN: 34:16858:37, last LSN: 34:16874:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘H:SQL2008R2TEST.bak’}). Informational message. No user ac
|
それでは、クエリを以下のように変更してリストアをしてみます。
|
DROP DATABASE [TEST]
GO
DBCC TRACEON (3004) WITH NO_INFOMSGS
DBCC TRACEON (3605) WITH NO_INFOMSGS
GO
EXEC sp_cycle_errorlog
RESTORE DATABASE [TEST] FROM DISK = N’H:SQL2008R2TEST.bak’
EXEC xp_readerrorlog
DBCC TRACEOFF (3004) WITH NO_INFOMSGS
DBCC TRACEOFF (3605) WITH NO_INFOMSGS
GO
|
この時の SQL Server のログの内容が以下になります。
|
2011-04-03 13:15:36.750 spid55 RestoreDatabase: Database TEST
2011-04-03 13:15:36.750 spid55 Opening backup set
2011-04-03 13:15:36.770 spid55 Restore: Configuration section loaded
2011-04-03 13:15:36.770 spid55 Restore: Backup set is open
2011-04-03 13:15:36.770 spid55 Restore: Planning begins
2011-04-03 13:15:36.780 spid55 Restore: Planning complete
2011-04-03 13:15:36.780 spid55 Restore: BeginRestore (offline) on TEST
2011-04-03 13:15:36.780 spid55 Restore: Attached database TEST as DBID=5
2011-04-03 13:15:36.780 spid55 Restore: PreparingContainers
2011-04-03 13:15:36.800 spid55 Zeroing G:SQL2008R2TEST_log.ldf from page 1 to 12800 (0x2000 to 0x6400000)
2011-04-03 13:15:36.830 spid55 Restore: Containers are ready
2011-04-03 13:15:36.870 spid55 Restore: Restoring backup set
2011-04-03 13:15:36.870 spid55 Restore: Transferring data to TEST
2011-04-03 13:15:38.610 spid55 Zeroing completed on G:SQL2008R2TEST_log.ldf
2011-04-03 13:15:39.050 spid55 Restore: Waiting for log zero on TEST
2011-04-03 13:15:39.060 spid55 Restore: LogZero complete
2011-04-03 13:15:39.090 spid55 FileHandleCache: 0 files opened. CacheSize: 12
2011-04-03 13:15:39.090 spid55 Restore: Data transfer complete on TEST
2011-04-03 13:15:39.100 spid55 Restore: Backup set restored
2011-04-03 13:15:39.100 spid55 Starting up database ‘TEST’.
2011-04-03 13:15:39.120 spid55 The database ‘TEST’ is marked RESTORING and is in a state that does not allow recovery to be run.
2011-04-03 13:15:39.120 spid55 Restore-Redo begins on database TEST
2011-04-03 13:15:39.280 spid55 Rollforward complete on database TEST
2011-04-03 13:15:39.280 spid55 Restore: Done with fixups
2011-04-03 13:15:39.280 spid55 Restore: Transitioning database to ONLINE
2011-04-03 13:15:39.290 spid55 Restore: Restarting database for ONLINE
2011-04-03 13:15:39.310 spid55 Starting up database ‘TEST’.
2011-04-03 13:15:39.380 spid55 FixupLogTail(progress) zeroing G:SQL2008R2TEST_log.ldf from 0x83f400 to 0x840000.
2011-04-03 13:15:39.380 spid55 Zeroing G:SQL2008R2TEST_log.ldf from page 1056 to 1536 (0x840000 to 0xc00000)
2011-04-03 13:15:39.390 spid55 Zeroing completed on G:SQL2008R2TEST_log.ldf
2011-04-03 13:15:39.440 spid55 PostRestoreContainerFixups: running fixups on TEST
2011-04-03 13:15:39.440 spid55 PostRestoreContainerFixups: fixups complete
2011-04-03 13:15:39.490 spid55 PostRestoreReplicationFixup for TEST starts
2011-04-03 13:15:39.770 spid55 PostRestoreReplicationFixup for TEST complete
2011-04-03 13:15:39.790 spid5
5 Restore: Database is restarted
2011-04-03 13:15:39.790 バックアップ Restore is complete on database ‘TEST’. The database is now available.
2011-04-03 13:15:39.810 spid55 Resuming any halted fulltext crawls
2011-04-03 13:15:39.810 spid55 Restore: Writing history records
2011-04-03 13:15:39.810 バックアップ Database was restored: Database: TEST, creation date(time): 2011/04/03(12:45:39), first LSN: 34:16858:37, last LSN: 34:16874:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘H:SQL2008R2TEST.bak’}). Informational message. No user ac
2011-04-03 13:15:39.820 spid55 Writing backup history records
2011-04-03 13:15:39.850 spid55 Restore: Done with MSDB maintenance
|
ログファイル (.ldf) のリストア処理で [zeroing] という文字が含まれるログが出力されています。
これが 0 で初期化している箇所になります。
データファイル (.mdf) ファイルについては [zeroing] というログは出力がされていません。
これは投稿の最初に書いたように [ファイルの瞬時初期化の対象はデータファイルのみ] となるためです。
ファイルの瞬時初期化を使用することができるデータファイルは 0 で満たすという処理が必要ではないため 0 による初期化は行われません。
ファイルの瞬時初期化が有効になっていない場合のログがこちらになります。
|
2011-04-03 13:27:53.550 spid51 RestoreDatabase: Database TEST
2011-04-03 13:27:53.560 spid51 Opening backup set
2011-04-03 13:27:53.610 spid51 Restore: Configuration section loaded
2011-04-03 13:27:53.610 spid51 Restore: Backup set is open
2011-04-03 13:27:53.610 spid51 Restore: Planning begins
2011-04-03 13:27:53.610 spid51 Restore: Planning complete
2011-04-03 13:27:53.610 spid51 Restore: BeginRestore (offline) on TEST
2011-04-03 13:27:53.610 spid51 Restore: Attached database TEST as DBID=5
2011-04-03 13:27:53.610 spid51 Restore: PreparingContainers
2011-04-03 13:27:53.650 spid51 Zeroing F:SQL2008R2TEST.mdf from page 1 to 12800 (0x2000 to 0x6400000)
2011-04-03 13:27:53.650 spid51 Zeroing G:SQL2008R2TEST_log.ldf from page 1 to 12800 (0x2000 to 0x6400000)
2011-04-03 13:27:55.090 spid51 Zeroing completed on G:SQL2008R2TEST_log.ldf
2011-04-03 13:27:55.090 spid51 Zeroing completed on F:SQL2008R2TEST.mdf
2011-04-03 13:27:55.090 spid51 Restore: Containers are ready
2011-04-03 13:27:55.090 spid51 Restore: Restoring backup set
2011-04-03 13:27:55.090 spid51 Restore: Transferring data to TEST
2011-04-03 13:27:56.480 spid51 Restore: Waiting for log zero on TEST
2011-04-03 13:27:56.500 spid51 Restore: LogZero complete
2011-04-03 13:27:56.540 spid51 FileHandleCache: 0 files opened. CacheSize: 12
2011-04-03 13:27:56.540 spid51 Restore: Data transfer complete on TEST
2011-04-03 13:27:56.550 spid51 Restore: Backup set restored
2011-04-03 13:27:56.560 spid51 Starting up database ‘TEST’.
2011-04-03 13:27:56.610 spid51 The database ‘TEST’ is marked RESTORING and is in a state that does not allow recovery to be run.
2011-04-03 13:27:56.610 spid51 Restore-Redo begins on database TEST
2011-04-03 13:27:56.860 spid51 Rollforward complete on database TEST
2011-04-03 13:27:56.870 spid51 Restore: Done with fixups
2011-04-03 13:27:56.870 spid51 Restore: Transitioning database to ONLINE
2011-04-03 13:27:56.870 spid51 Restore: Restarting database for ONLINE
2011-04-03 13:27:56.890 spid51 Starting up database ‘TEST’.
2011-04-03 13:27:56.960 spid51 FixupLogTail(progress) zeroing G:SQL2008R2TEST_log.ldf from 0x83f400 to 0x840000.
2011-04-03 13:27:56.960 spid51 Zeroing G:SQL2008R2TEST_log.ldf from page 1056 to 1536 (0x840000 to 0xc00000)
2011-04-03 13:27:56.980 spid51 Zeroing completed on G:SQL2008R2TEST_log.ldf
2011-04-03 13:27:57.090 spid51 PostRestoreContainerFixups: running fixups on TEST
2011-04-03 13:27:57.090 spid51 PostRestoreContainerFixups: fixups complete
2011-04-03 13:27:57.120 spid51 PostRestoreReplicationFixup for TEST starts
2011-04-03 13:27:57.500 spid51 PostRestoreReplicationFixup for TEST complete
2011-04-03 13:27:57.520 spid51 Restore: Database is restarted
2011-04-03 13:27:57.520 バックアップ Restore is complete on database ‘TEST’. The database is now available.
2011-04-03 13:27:57.530 spid51 Resuming any halted fulltext crawls
2011-04-03 13:27:57.530 spid51 Restore: Writing history records
2011-04-03 13:27:57.530 バックアップ Database was restored: Database: TEST, creation date(time): 2011/04/03(12:45:39), first LSN: 34:16858:37, last LSN: 34:16874:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘H:SQL2008R2TEST.bak’}). Informational message. No user ac
2011-04-03 13:27:57.530 spid51 Writing backup history records
2011-04-03 13:27:57.940 spid51 Restore: Done with MSDB maintenance
2011-04-03 13:27:57.940 spid51 RestoreDatabase: Finished
|
瞬時初期化が有効でない場合はデータファイル (.mdf) に対しても 0 による初期化が行われているのが確認できますね。
今回はログから瞬時初期化の部分のみを確認しましたが、トレースフラグを設定することにより通常出力されるログと比較してかなり詳細な内容が出力されるようになるので、リストアの動作を理解するのにかなり役に立ちそうですね。
