SE の雑記

さらに続きの投稿で。

データファイルを追加した際にファイルの再構成ではファイルの平準化はされませんでした。
この時の各ファイルの読み取り / 書き込みの状態を確認してみたいと思います。

データベースのファイルのアクセス状況を確認したい場合には、[sys.dm_io_virtual_file_stats] または、[fn_virtualfilestats]を使用します。
ここから情報を取得することでファイルのアクセス状況を取得することができます。
今回は、以下のクエリを実行して情報を取得してみました。

SELECT     DB_NAME(database_id) AS [database_name],     FILE_NAME(file_id) AS [file_name],     num_of_bytes_read,     num_of_bytes_written FROM     sys.dm_io_virtual_file_stats(DB_ID(N’TEST’), NULL)

それでは、インデックスの再構成を実行し、その後情報を再取得してみたいと思います。

再構成実行後、実行前の情報の差を出すことで、どのファイルに対してアクセスがされたかを確認することができます。

database_name	file_name	num_of_bytes_read	num_of_bytes_written
TEST	TEST	0	19,578,880
TEST	TEST_log	0	55,043,072
TEST	TEST2	0	0

num_of_bytes_written が [TEST] [TEST_log] にだけ発生しているのが確認できます。
インデックスの再構成は再構築とことなり、新規にデータを再構築するのではなく、既存のエクステント内でデータを再構成しますので、このような動きとなります。

それではこの状態から再構築をしてファイルのアクセスを確認したいと思います。

database_name	file_name	num_of_bytes_read	num_of_bytes_written
TEST	TEST	0	13,508,608
TEST	TEST_log	0	25,758,720
TEST	TEST2	0	8,151,040

 

再構築をした場合は、[TEST2] にも書き込みがされていることが確認できます。

データ平準化の動作は、ファイルのアクセス状況を取得することで確認することもできますので、簡単にではありますがまとめてみました。

先ほど投稿した内容の続きになります。

データファイルを追加してデータを平準化すると各ファイルの使用状況は以下のようになります。

何かの理由で、追加したデータファイルを削除する必要が発生し、削除をしようとすると以下のメッセージが表示され削除をすることができません。

DataFile ‘TEST2’ の削除に失敗しました。  (Microsoft.SqlServer.Smo) Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。 (Microsoft.SqlServer.ConnectionInfo) ファイル ‘TEST2’ は空ではないので、削除できません。 (Microsoft SQL Server、エラー: 5042)

メッセージに表示されているように、すでにデータが格納されているファイルは空ではありませんので削除をすることができません。

データを空にするためには、[EMPTYFILE] を指定してファイルを圧縮する必要があります。
SQL で実行する場合は以下のクエリを実行します。

USE [TEST] GO DBCC SHRINKFILE (N’TEST’ , EMPTYFILE) GO

SSMS で圧縮をする場合は、ファイル単位の圧縮で [データを同じファイル グループの他のファイルに移行してファイルを空にする] を選択してファイルの圧縮を行います。

いずれかの操作をすることで対象のファイルから別のファイルにデータを移行することが可能です。

実行前の各ファイルの使用状況は以下のようになっています。

実行後は以下のようになります。

EMPTYFILE を指定してデータベースの圧縮をすると以下のような結果が表示されます。

UsedPages は 0 となっていますが、SHOW FILESTATS の結果では 1 エクステントが使われています。
# 管理用のページが残っているからだからだと思いますが。

この状態でデータを追加してみます。

EMPTYFILE により圧縮をしたファイルにはデータが格納されていません。

以下は BOL に記載されている内容です。

指定したファイルから、同じファイル グループ内の他のファイルにすべてのデータを移動します。データベース エンジンではデータを空のファイルに配置できなくなったので、ファイルを削除するには、ALTER DATABASE ステートメントを使用します。

圧縮により空にしたファイルにはデータの配置ができなくなりますので、データを追加しても使用されなくなります。
# ページヘッダ と sys.database_files を確認してみたのですが違いがいまいちわかりませんでした…。

空にしたファイルは削除することが可能となります。

SSMS から見るとファイルが削除されているのですが、[sys.database_files] を確認すると実はファイルが削除されていません。

削除しただけでは、[OFFLINE] の状態でエントリとしては残った状態となっています。

SSMS のファイルの表示は [state] が [0] (ONLINE) または [2] (RECOVERING) のファイルが表示されるようになっています。
# 実際には sys.database_files ではなく、sys.master_files から取得しています。

そのため、SSMS では表示はされないがエントリとしては残った状態となります。

エントリが残った状態で同じ名前 (TEST2) でファイルを追加してみます。

データベース ‘TEST’ のAlterに失敗しました。  (Microsoft.SqlServer.Smo) Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。 (Microsoft.SqlServer.ConnectionInfo) 次回の BACKUP LOG 操作が終了するまで、ファイル ‘F:DataTEST2.ndf’ を再利用できません。 次回の BACKUP LOG 操作が終了するまで、ファイル ‘TEST2’ を再利用できません。 (Microsoft SQL Server、エラー: 1833)

メッセージに表示されている通りなのですが、トランザクションログのバックアップをしないとエントリが削除されないためエラーが発生しています。
エントリが存在している状態では、[is_name_reserved] が [1] となっています。

BOL には以下のように記載されています。

1 = 削除されたファイル名を再使用できます。新しいファイル名に対して名前 (name または physical_name) を再使用するには、ログのバックアップを実行する必要があります。

0 = ファイル名は再使用できません。

[is_name_reserved] が [1] の状態について明記がされていますね。

それではトランザクションログのバックアップを取って再度確認をしてみたいと思います。

トランザクションログのバックアップを取得することでファイルのエントリが削除されていることが確認できます。

ファイルの削除をする際にはトランザクションログのバックアップをして [sys.database_files] または、[sys.master_files] からエントリが削除されるところまでを確認しておいた方が良いかもしれないですね。

Twitter でご質問をいただきましたので軽くまとめて見たいと思います。

SQL Server のファイルグループは一つ以上のデータファイルで構成がされます。

ファイルグループはデータファイルの集合を管理する論理単位のため、SQL Server 上にデータとして情報が存在するだけですが、データファイルは実際のファイルになりますので、ディスク上にファイルが存在することになります。

データベースのデータはファイルグループと関連付けますので、データも絵に含めると以下のような形になります。

 

テーブルの件数が増加し、ディスクのサイズが枯渇 / ディスク負荷低減のため、ファイルグループに新規にデータファイル (ndf) を追加することがあります。

テーブルはファイルグループに関連づいていますので、そのファイルグループにデータファイルが追加されれば、使用できる領域が増えることになり、ディスクサイズの枯渇に関しては対応ができます。

ディスク負荷低減についてはどのようになるかを考えてみます。
ディスク追加後の各ファイルのデータの充填状況は以下のようになっています。
# 赤が使用領域となります。

追加したデータファイルにはデータは格納されていませんので、読み取りは今まで存在したデータファイルに、書き込みは新規に追加したデータファイルに集中することになります。
# 読み取りはデータが格納されているファイル / 書き込みは空きページの多いファイルに対して行われますので。

通常、同一のファイルグループにデータファイルを追加する場合は別のディスクにしますので、データの充填率は以下のようになっているとディスク I/O が分散されることになります。

データファイル追加後は上記の図のようにデータファイル内のデータは各ファイルに平準化して格納されていません。
平準化をするためにはデータ領域を再構築してデータを均等に配置する必要があります。

それでは、各状態を SQL Server で実際に確認をしてみたいと思います。

■初期状態

今回は [TEST] というデータベースを作成しています。

このデータベースですが単一のデータファイルで構成されています。
テスト用のテーブルを作成して、データを入れてありますので現在のファイルの使用状況を確認してみたいと思います。
ファイルの使用状況を確認するためには [DBCC SHOWFILESTATS] を使用します。
この DBCC コマンドを実行することで、現在のデータベースのファイル使用状況を取得することができます。

ファイルの使用状況はエクステントで表示されます。
[TEST.mdf] は [1,600] エクステント (1,600 × 64 KB = 102,400 KB = 100 MB) 割り当てられ、そのうち [339] エクステント (339 × 64 KB = 21,696 KB = 21 MB)  が使用されていることが確認できます。

■ファイル追加後のデータ充填状況

それではファイルグループにデータファイルを追加して、データの充填状況を確認してみたいと思います。
# ドライブ構成の関係で既存のファイルと同じドライブに格納してしまっています…。

今回は [TEST2.ndf] というファイルを追加しています。

それではデータファイル追加後の使用状況を取得してデータの充填状況を確認してみたいと思います。

新規に吹ファイルが追加 (Fileid = 3) され、UsedExtents が [1] (管理用ページを作成する必要があるため) となっています。
データファイルを追加後はこのような状態となります。

 

■データを平準化する

データを平準化するためにはどうすればいいかというと、データ領域の再構築を行います。
# ヒープの場合は別の機会に考えて見たいと思います…。
再構築はインデックスの再構築をすれば実施できますので、インデックスの [REBUILD] を行います。

インデックスの再構築では、エクステントも含めてデータを再配置し直しますので、データが各ファイルに均等に再配置されます。

インデックスの再構成 (REORGANIZE) では、エクステントの再割り当てはせずに既に存在しているエクステント内でページの並び替えを行いますのでデータは平準化されません。

それでは、実際に確認をしてみたいと思います。

まずはインデックスを再構成してみます。
# GUID をキーにしてテスト用のデータを生成しているので断片化が著しいです…。

再構成後のデータ充填率を取得すると各ファイルにデータが分散していないことが確認できます。

それでは、再構成 (REORGANIZE) ではなく 再構築 (REBUILD) を行ってみます。

完全に平準化 (199 ページずつ) とはいきませんでしたが今まで UsedExtents が 1 となっていた TEST2.ndf のエクステントが利用されていることが確認できます。

データファイルの追加後にデータ領域を再構築することで、既に格納されているデータについて使用するファイルを平準化することができます。

 

■ファイルサイズの差によるデータファイルの使用状況

データファイルのサイズを変更して、

• TEST.mdf : 100 MB
• TEST2.ndf : 1 MB

にしてみました。
# 自動拡張は有効です。

この状態でデータを挿入するとどうなるか試してみます。

TEST2.ndf は 1 MB のサイズに変更しましたので 16 × 64 KB = 1,024 MB と割り当てていたサイズはすべて使用されていますが、自動拡張はせずに残りのデータは TEST.mdf に格納がされています。
データ格納時は空きページを考慮してデータを格納していきますので空きがある TEST.mdf が使用されたことになります。

これは極端な例ですので、

• TEST.mdf : 100 MB
• TEST2.ndf : 20 MB

として、

両方のファイルで分散させたデータが格納できる状態にしてデータを挿入してみます。

この場合は両ファイルに十分な空き領域がありますので、両方のファイルが均等と言っていいレベルで使用されています。

 

使用されるファイルは空き領域 (ページ) が考慮されて使われますので、均等にデータを書き込んだ結果片一方の空きページに余裕が無くなってしまうと思ったようにディスク利用が分散されない可能性があります。

使用領域を増やすのではなく、ディスクのアクセス効率を向上するためにファイルを追加した場合は追加後に各ファイルでデータを平準化するかを考慮し、作業 / 確認をした方がよいかもしれないですね。