SE の雑記

以前、投稿した以下の内容と重複しているところがいくつかあると思いますが、単体で書いたことはなかったと思うので、まとめてみたいと思います。

ファイルグループにファイル追加後のデータ平準化
追加したデータファイルからデータを移動する方法
データ平準化の再構成時のファイルアクセス状況について

■SQL Server のデータベースの基本構成

---

SQL Server のデータベースの最小ファイル構成は以下のようになります。

 

最小構成の場合、データベースは一つのファイルグループ / 一つのプライマリデータファイル / 一つのログファイルで構成がされます。

プライマリデータファイル (.mdf) は一つしか設定ができないのですが、セカンダリデータファイル (.ndf) をファイルグループに追加することが可能です。
ファイルグループはデータファイル (.mdf / .ndf) を束ねる単位になります。
ファイルグループはデータベース内に複数作成することができ、テーブルは必ずどこかのファイルグループに属します。
# パーティショニングを使う場合以外は、テーブルは一つのファイルグループにしか属することはできません。

セカンダリファイルを使用した場合データベースの構成は以下のようになります。

データファイルを複数で構成した場合に考えられる効果としては I/O の効率化があります。

データファイルは単一のファイルになりますので、作成する際には作成場所を指定する必要があります。
プライマリデータファイル (.mdf) / セカンダリデータファイル (.ndf) は別々の場所に保存することが可能ですので以下のような構成をとることができます。

この構成では、異なるディスク (または LUN) にデータファイルを配置しています。
異なるディスクにデータファイルを配置することで I/O を並列に効率よく処理することが可能となります。

この例ではドライブレターにひもづいたディスクに対してデータファイルを格納しているのですが、ドライブレターは [A～Z] までの [26] しか指定ができません。
# MSCS / WSFC 上の SQL Server のインスタンスが 25 インスタンスしか作成できないのもこの辺のドライブレターの制約が影響しています。

複数のデータベースがあり、データファイルを細かく異なるディスク上に配置したいと言った場合、ドライブレターの数だけでは足りないことがありますよね。
その場合はディスクを空のディレクトリにマウントさせて異なるディスクに配置できるようにします。

空のディレクトリに対してディスクをマウントするので、親となるドライブレターが必要となるのですがこの方法であれば一つのドライブレターで複数のデータファイルを異なるディスク上に配置することが可能となります。
# この方法が SQL Server でサポートされたのは SQL Server 2005 からだったはずです。

 

■サイズを均等化しない場合のデータ格納

---

データベースの基本的な構成とファイル配置については簡単ですがまとめてみました。

次はファイルのサイズについて見ていきたいと思います。
SQL Server のデータファイルは個別にファイルサイズを設定することが可能です。

上記の設定では

• プライマリデータファイル : 2GB
• セカンダリデータファイル : 200 MB

というように各ファイルのサイズを不均等にしています。

今回は Denali CTP1 を使用しているので、各ファイルの使用状況はこのようなクエリで取得が可能です。
# 2008 R2 までは [sys.dm_db_file_space_usage] は tempdb の情報しか返さなかったのですが、Denali CTP1 では、現在のデータベースの情報が取得できるようになっています。

USE [TEST] GO SELECT     file_id,     filegroup_id,     total_page_count,     allocated_extent_page_count,     unallocated_extent_page_count FROM     sys.dm_db_file_space_usage

page_count となっているので、サイズに直す場合には 8KB を乗算する必要があります。
サイズで表示する場合はこのような形で
# KB で表示しています。

USE [TEST] GO SELECT     file_id,     filegroup_id,     total_page_count * 8192  / 1024  AS [total_page_kb],     allocated_extent_page_count * 8192 / 1024 AS [allocated_extent_page_kb],     unallocated_extent_page_count * 8192 / 1024 AS [unallocated_extent_page_kb] FROM     sys.dm_db_file_space_usage

この状態で 200MB 程度のデータを入れてみるとこのようになります。

200MB のデータを入れているので 100MB / 100MB というように各ファイルに均等にデータが入ると効率よく I/O が分散されてよいのですが、入っているデータに偏りが出ています。

ファイルのサイズが均等になっていない場合、I/O に偏りが出ます。
そのため、データファイルを異なるディスクに配置して負荷を分散しようとした場合、ファイルのサイズが均等になっていないと分散したファイルの恩恵が薄くなることがあります。

 

データファイルのサイズを均等にして同じことを試してみます。

こちらの場合は大体均等にデータが入っていますね。
データファイルのサイズが均等になっている場合は各ファイルが効率よく使われていそうですね。

ファイルのサイズが均等にならない原因としてファイルの自動拡張によってサイズが変わってしまった場合があると思います。
この辺の動きとファイルを均等に拡張するための設定については次の投稿でまとめてみたいと思います。