SQL Server を WOW64 で動かした時のメモリの使用状況について少しまとめていきたいと思います。
パフォーマンス モニタでメモリの情報を取得しようとして結構はまったので、その 1 として WOW64 で SQL Server を実行した場合の情報取得につて書いていきたいと思います。
64bit OS では SQL Server を WOW64 (Windows 32-bit on Windows 64-bit) でインストールすることが可能です。
64bit OS で SQL Server をインストールしようとすると通常は 64bit の SQL Server がインストールされます。
インストール時に [Options] から [Processor Type] を [x86] に設定することで WOW64 で SQL Server をインストールすることができます。

WOW64 の SQL Server のインストールではインストールする機能の選択画面では、両方の [Program Files] が表示されますが、

データベースのルートディレクトリの選択では、[Instance root directory] が [Program Files (x86)] に設定がされます。

後のインストールは x64 の SQL Server と同じです。
タスクマネージャーを確認すると [*32] となっていますので、WOW64 で実行されているのが確認できます。

それではパフォーマンスモニタでメモリの情報を取得してみたいと思います。

MSSQL～ のカウンターが無いですね…。
Process では、[sqlservr.exe] の情報を取得することが可能なのですが。

WOW64 の SQL Server の場合は、32bit のパフォーマンスモニタを起動して情報を取得します。
32bit のパフォーマンスモニタを起動するためには、[C:WindowsSysWOW64perfmon.exe] を実行します。
# [mmc /32 perfmon.msc] でも起動することが可能です。
32bit のパフォーマンスモニタを起動すると SQL Server のカウンターを取得することができます。

それでは、WOW64 上の SQL Server のメモリ情報を取得してみたいと思います。
まずは SQL Server 2008 R2 で試してみました。

取得したログに SQL Server のカウンターが無いですね…。
ログの取得対象として SQL Server のカウンターは設定されています。

[Relog] コマンドを使って、取得したパフォーマンスモニタのログからカウンターを取得してみたいと思います。
Relog コマンドには [-q] オプションがあり、このオプションを使用するとログに含まれるカウンターの情報を取得することが可能です。

>relog c:PerfLogsAdminPerformanceWIN-KLC3RJ46I7R_20101208-000007DataCollector01.blg -q 入力 —————- ファイル: ???? c:PerfLogsAdminPerformanceWIN-KLC3RJ46I7R_20101208-000007DataCollector 01.blg (バイナリ) 開始:?????????? 2010/12/8 7:48:09 終了:?????????? 2010/12/8 7:52:24 サンプル:?????? 251 \WIN-KLC3RJ46I7RMemoryAvailable KBytes コマンドは、正しく完了しました。

ログから追加できるカウンターに表示されていないという事ではなく、ログ自体に含まれていない (取得されていない) ことが確認できます。
WOW64 のパフォーマンスモニタのログ取得について調べていたところ、SQL Server でとても参考になるサイトが二つ見つかりました。
SQL Server Wow64 Perfmon Issues
How It Works: Almost Everything You Wanted To Know About The SQL Server (2005, 2008) Performance Counter Collection Components
最初のブログは WIndows Server 2003 がベースとなっています。
Performance Log and Alerts のサービスで使用する exe を レジストリを変更してWOW64 のものに差し替えて取得するという方法が紹介されています。


2008 以降はパフォーマンスモニタのサービスは [svchost.exe] 経由になっているのですが、これを [SysWow64] のものにすれば取得できるというわけでもないので残念ながらこの方法では取得できず…。

2 つめのサイトでは 64bit の SQL Server のカウンターのライブラリ DLL を [system32] にコピーするという方法が紹介されています。
SQL Server のパフォーマンスモニタのカウンターですがサービス毎に、ライブラリの DLL が個別に設定されています。
# 使用している DLL 名は HKLM のサービスのレジストリの [Library] から確認することができます。

同一バージョンの 64bit の SQL Server から、カウンターのライブラリ DLL をコピーし、ファイル名を上記で確認した DLL 名と同一にして、[C:WindowsSystem32] にコピーをして再度ログの取得を試してみました。

>relog c:PerfLogsAdminPerformanceWIN-KLC3RJ46I7R_20101209-000009DataCollector01.blg -q 入力 —————- ファイル: ???? c:PerfLogsAdminPerformanceWIN-KLC3RJ46I7R_20101209-000009DataCollector 01.blg (バイナリ) 開始:?????????? 2010/12/9 22:28:06 終了:?????????? 2010/12/9 22:28:44 サンプル:?????? 39 \WIN-KLC3RJ46I7RMSSQL$DENALIX86:Plan Cache(_Total)Cache Pages \WIN-KLC3RJ46I7RMSSQL$DENALIX86:Buffer ManagerDatabase pages \WIN-KLC3RJ46I7RMSSQL$SQL2008R2X86:Plan Cache(_Total)Cache Pages \WIN-KLC3RJ46I7RMSSQL$SQL2008R2X86:Buffer ManagerDatabase pages \WIN-KLC3RJ46I7RMemoryAvailable KBytes コマンドは、正しく完了しました。

?
2008R2 と Denali の情報を取得してみたのですが両方ともログが取れていますね。
パフォーマンスモニタのログも取得できています。

この方法でログを取得することができるのですが [非サポート] になるようですので注意が必要です。
非サポートではありますがひとまず、ログの取得はできました。
WOW64 で SQL Server を実行した際のメモリ利用については次の投稿でまとめたいと思います。

あらためて SQL Server と AWE の最後の投稿として、AWE を有効にしている場合のメモリ情報の取得についてまとめていきたいと思います。

今まで、DBCC MEMORYSTATUS やパフォーマンスモニタでメモリの情報を取得してきました。
特定のアプリケーションのメモリを取得する際には、タスクマネージャやパフォーマンスモニタの Process でワーキングセットを取得する方法もあるかと思います。

AWE を有効にした状態でタスクマネージャーの [sqlserver.exe] のプライベートワーキングセットを確認してみます。

92 MB となっていますね。
パフォーマンスモニタで [sqlserver.exe] の [Working Set] を確認してみます。

平均が [116,969,472] Byte となっています。

DBCC MEMORYSTATUS で SQL Server から使用しているメモリを確認してみます。

AWE Allocated は [4,235,376] KB となっていますので、4GB 程度のメモリが使用されています。

AWE が有効になっている環境でのメモリ取得に関して、技術文書に以下の記載があります。
SQL Server での AWE メモリの有効化

SQL Server のパフォーマンス モニターの Total Server Memory (KB) カウンターを使用して、AWE モードで実行されている SQL Server のインスタンスによって割り当てられたメモリ量を特定するか、sysperfinfo からメモリの使用量を選択します。

AWE を有効にしている場合はプロセスからではなく、SQL Server のパフォーマンスモニタから情報を取得します。

SQL Server が使用しているメモリの合計は、

• Buffer ManagerTotal pages
• Memory ManagerTotal Server Memry (KB)

の何れかで取得することが可能です。

以下の画像が情報を取得したものになります。

Total pages は 8KB ページの数が表示されますので、サイズにするためには
540,672 ページ × 8KB = 4,325,376 KB となります。

Total Server Memory (KB) に関しては KB 表示そのままですね。
両方とも同じ値となりますのでどちらから値を取得しても問題はありません。

SQL Server の現状を見るときには基本的に SQL Server 用のカウンタから追っていくことになります。
現状のメモリ使用状況だけを軽く見たいといった時でも SQL Server のカウンタを使うことで正確な値を取得することができます。

 

6 回に分けて AWE についてまとめてみました。
64bit 化が進んでいる中で、32bit の SQL Server を使用する機会はそれほどないかとは思いますが、SQL Server のメモリの使用状況を勉強するということではいい機会だったと思います。

次の投稿では WOW64 の SQL Server についてまとめてみたいと思います。

前回の投稿で、プランキャッシュの上限について少しまとめてみました。
AWE を有効にしても、プランキャッシュはユーザーモードの中に確保がされますので使用できるメモリは増えません。

プランキャッシュの上限を増やすためには AWE ではなく、/3GB スイッチを利用します。

/3GB スイッチを使用した場合のプランキャッシュの利用状況は以下のようになります。

/3GB スイッチを設定する前は、900 MB 程度だったメモリが、スイッチを設定することにより、1,400 MB 程度まで増加しています。

プランキャッシュですが、Visible ターゲットメモリのサイズに応じて上限が設定されます。
Visible ターゲットメモリは通常のメモリ割り当て (AWE 設定なしで割り当てられるユーザーモード空間) の上限になります。
32bit の場合は 2GB または、3GB が上限となります。

/3GB スイッチを設定しても 3GB のプランキャッシュが設定できるわけではないので注意が必要です。
# いろいろと試してみたのですが、2GB:900 MB / 3GB : 1.4 GB 程度が上限になりそうでしたが、。情報を見ている限りは 75% 取れそうな気もするのですが、これは64bit だけなのかもしれないですね。

/3GB スイッチと AWE を併用した場合のメモリ使用状況はこのようになります。

/3GB スイッチを設定していない場合と比較すると、プランキャッシュの使用状況が増加していることが確認できます。

/3GB スイッチの注意ですが、このスイッチは 16GB を超えるメモリでは使用することができない点です。
AWE の使用

コンピューターに 16 GB を超える使用可能な物理メモリがある場合、オペレーティング システムで 2 GB の仮想アドレス空間がシステム用に必要になるため、サポートできるユーザー モード仮想アドレス空間は 2 GB だけになります。
オペレーティング システムで 16 GB を超えるメモリ範囲を使用するには、Boot.ini ファイルから /3gb パラメーターを削除する必要があります。
このパラメーターがあると、システムで 16 GB を超える物理メモリを使用できません。

プランキャッシュの上限を考えるとできるだけ 64bit のSQL Server を使用したいところですね。

今回は SQL Server 2008 R2 で検証をしていました。
Denali でも 32bit の SQL Server は引き続き提供されます。

次の投稿では、Denali で AWE を有効にした際の動作をまとめていきたいと思います。

AWE を使用すると AWE によってマッピングされたユーザーモード (2GB) を超える領域はデータキャッシュでしか使用されないということを聞くことがあるかと思います。

今回はデータキャッシュとプラン (クエリ) キャッシュについてまとめていきたいと思います。

AWE を使用すると以下のようにメモリを使用することが可能となります。

SQL Server のパフォーマンスモニタでは以下のメモリの情報を取得することが可能です。

以下は Denali で取得できる情報になります。
# SQL Server のバージョンによっては項目が違うのですが、似たような情報は他の項目から取得することができます。

• Connection Memory
• Database Cache Memory (Database pages)
• Free Memory
• Granted Workspace Memory
• Lock Memory
• Optimizer Memory
• SQL Cache Memory (Cache Pages)

これと上の図をマッチングしてみます。

ユーザーモードの領域を超えて割り当てが可能なのは [Database Cache Memory] となります。

それではこのあたりの動きを確認していきたいと思います。
まずはデータベースキャッシュメモリを確認してみます。

今回は SQL Server 2008 R2 の環境を使用しています。
# Denali を使用していないのには理由があるのですが、これは別の機会にまとめる予定です。

■AWE を設定していない状態のデータベースキャッシュメモリの使用状況

まずは AWE を設定していない状態でのデータベースキャッシュメモリの使用状況を確認してみます。
データベースキャッシュメモリはデータをキャッシュするために使用されます。

今回は 17 GB 近いデータが格納されているテーブルを用意しました。

このテーブルに対して SELECT を実行して、データベースキャッシュの状態を確認していきたいと思います。
SELECT は SSMS で実行しているのですが、SSMS のプロセスで余計なメモリを消費されないように実行結果はファイルとして出力しています。
# グリッドやテキストで SSMS 上に結果を表示すると SSMS でメモリを消費してしまいますので。

以下のグラフはサーバーの空きメモリとデータベースキャッシュの状態をグラフにしたものです。

AWE を有効にしていないため、メモリを最大限使用できていないことが確認できます。

それでは、AWE を有効にして同様のデータを取得してみます。

■AWE を設定しいる状態のデータベースキャッシュメモリの使用状況

AWE を設定している状態では以下のようなメモリ使用状態となります。

先ほどと比較して、データベースキャッシュに使用できるサイズが増加していることが確認できます。
AWE が有効に働いていますね。

 

それでは、同様の情報をプランキャッシュでも取得してみたいと思います。

■AWE を設定していない状態のプランキャッシュメモリの使用状況

今回は大量のアドホッククエリを動的に生成して EXEC するクエリを実行して、プランキャッシュにアドホッククエリを大量にキャッシュするようにしてテストをしています。

AWE は設定していないので、プランキャッシュとしては 900 MB 程度が上限となっています。

それでは、AWE を設定して情報を取得してみます。

■AWE を設定している状態のプランキャッシュメモリの使用状況

プランキャッシュに関しては AWE の恩恵が受けられない領域となります。
そのため AWE の設定有無によるメモリ使用量の変更はありません。

AWE を設定してもプランキャッシュは通常のユーザーモードのメモリ空間の中でしか確保ができませんので、AWEの有効有無による差が発生しません。

最後に AWE を有効にした状態で、データベースキャッシュとプランキャッシュを最大限使われるようにした際の情報を取得してみたいと思います。

データベースキャッシュが増加した後にプランキャッシュを増やすようにしています。
# サーバーのスペック的に両方を同時に増加させようとすると結構時間がかかりそうだったもので。

途中からプランキャッシュが増えるようにしていますので、プランキャッシュの増加に合わせてデータベースキャッシュのメモリが減っていります。

AWE を有効にしてもプランキャッシュの上限は変わりません。
プランキャッシュの上限を増やすためには、/3GB スイッチを利用します。

次の投稿では、AWE と /3GB スイッチの併用についてまとめていきたいと思います。

前回の投稿では AWE の設定と設定後の確認について投稿しました。

今回は [動的メモリ] についてまとめていきたいと思います。

SQL Server では [min server memory] と [max server memory] を使用した動的なメモリ割り当てを行うことができます。
この 2 つの値設定することで、SQL Server がどの範囲でメモリを使用するかを指定することができます。
# min server memory はメモリ割り当て後の解放をどの程度まで許容するかの設定で、起動時に確実に確保する設定ではありませんが。

AWE の動的なメモリ割り当てについてですが、以下の技術情報が掲載されています。
SQL Server での AWE メモリの有効化

この中に、以下の記載があります。

Windows 2000 オペレーティング システム上で実行される SQL Server では、AWE マップ メモリの動的割り当てがサポートされていないため、インスタンスごとに max server memory オプションを設定することをお勧めします。

Windows Server 2003 上の SQL Server では、AWE メモリの動的割り当てがサポートされます。

AWE の動的メモリ割り当てに関しては、OS によってサポートの有無が変わってきます。

Windows 2000 Server では動的メモリがサポートされていないため、SQL Server が起動時に可能な限りのメモリを Committed にします。
そのため、[max server memory] を指定することで SQL Server が使用するメモリの条件を設定しておきます。

Windows Server 2003 では動的メモリがサポートされ、SQL Server の起動時には、必要最小限のメモリのみを Committed にして、後は必要に応じて動的にメモリを割り当てます。
動的メモリがサポートされていますので、[min server memory] と [max server memory] を指定しなくても最小限のメモリから必要となるメモリのみを確保していく設定となります。
動的メモリがサポートされていても、サーバー上で稼働しているアプリケーションに影響を及ぼさないように [max server memory] は設定をしていた方が良いとは思います。

書籍によっては、[min server memory] と [max server memory] を同一値に設定して、固定メモリ設定とすると書かれているものがありますので、固定メモリ化するのが良いのかもしれないですね。

 

それでは、AWE の動的メモリ設定についてみていきたいと思います。
今回は SQL Server のバージョンではなく、OS のバージョンによって差が出るということを確認したいため、Windows 2000 Server と Windows Server 2008 上に SQL Server 2005 SP3 の環境を構築しています。
どちらの SQL Server も min server memory = 0 / max server memory =4096 に設定しています。

■Windows 2000 Server の AWE

 

まずは、Windows 2000 Server の AWE 有効時のメモリ割り当てについて見ていきたいと思います。
SQL Server 2005 でも DBCC MEMORYSTATUS は使用できますので AWE Allocated の値を確認してみます。

起動直後の状態なのですが、3,326,920 KB のメモリが割り当てられているのが確認できます。
今回の環境ではメモリは 4GB 割り当てています。
# 物理メモリ 4GB に対して、SQL Server の割り当てメモリが 4GB なのでこのような割り当て状況になっています。

この環境では、AWE を使用しても 3GB と少しが SQL Server のメモリ割り当ての上限のようですね。
この状態では、物理メモリを確保していますが SQL Server 上では大半のメモリは [Free Pages] として認識されています。

以下の画像はパフォーマンスモニタで [Buffer ManagerFree Pages] を取得した内容になります。

平均して [414,107] ページの空きがあります。
414,107 ページ = 414,107 × 8 KB = 3,312,856 KB
となりますので、メモリを確保していても大半が Free Page として認識されています。

■ Windows Server 2008 の AWE

それでは、Windows Server 2008 の SQL Server 2005 でも同様の確認をしてみたいと思います。

Windows Server 2008 の場合は、Windows Server 2003 と同様に動的メモリがサポートされています。
そのため、SQL Server の起動時には必要最小限のメモリのみを確保した状態で起動がされます。

Windows 2000 Server と同様に、Free Page の状態を確認してみます。

Free Page のサイズは、
73 ページ = 73 × 8 KB = 592 KB
となりますので、確保しているメモリの大半は Free Page ではなく起動に必要な領域となっていることが確認できます。

 

■SQL Server 2000 で AWE を確認

先ほどは AWE を SQL Server 2005 で確認をしてみましたが SQL Server 2000 SP4 (8.00.2039) でも確認をしてみたいと思います。

SQL Server 2000 でも DBCC MEMORY STATUS は使用できます。
表示形式が SQL Server 2005 とは異なるのですが、[Buffer Distribution] からメモリを確認することができます。
今回は Windows 2000 Server 上に SQL Server 2000 をインストールしていますので、サービスの起動時は可能な限りのメモリが割り当てられます。

SQL Server 2000 の場合は KB ではなく、ページ数で表示がされます。
250,755 ページ = 250.755 × 8KB = 2,006,040 KB
となります。

SQL Serve 2000 では、Free Pages からはうまく値を取得できなかったので、[Memory ManagerTotal Server Memory (KB)] から情報を取得しています。

平均して、[2,021,680 KB] となっています。

4 GB のメモリを搭載した環境で、AWE を有効にして 2GB のメモリ割り当てというのは少し気になりますよね。
SQL Server 2000 SP4 用に以下の修正プログラムが提供されています。
[FIX] 32 ビット版の SQL Server 2000 SP4 を実行するコンピュータで AWE を有効にすると使用できないメモリ領域がある

SP4 では AWE を有効にしても物理メモリの半分までしかメモリが使用できないという不具合があります。
修正プログラムを実行して再度メモリの使用状況を確認してみます。

421,092 ページ = 421,092 × 8KB = 3,368,736
になりますので、先ほどの SQL Server 2005 の時と同程度のメモリが割り当てられています。

修正プログラムを適用すると Free Page の状況も正常に取得することができました。

421,083 ページ = 421,083 ページ × 8 KB = 3,368,664 KB
になりますので、大半が Free Page となっているのが修正プログラムを適用すると確認ができます。

 

AWE を有効にすると AWE によってマッピングされた 2GB を超える領域はデータキャッシュのみで使用でき、クエリキャッシュはユーザーモードの空間 (2GB の空間)  でしか使用できないという話をよく聞くことがあると思います。

次の投稿では AWE で有効にしたメモリの使用状況についてまとめたいと思います。

さらに続きの投稿で。

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

データベースのファイルのアクセス状況を確認したい場合には、[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

として、

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

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

 

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

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

最近は 64 ビット (x64) の OS を使う機会の方が多いと思いますが、あらためて 32 ビット (x86) の SQL Server のメモリチューニングについてまとめていきたいと思います。

今回は

• Windows Server 2008 Datacenter Edition x86 SP2
• SQL Server 2008 R2 Enterprise Evaluation x86
• SQL Server Code Name ‘Denali’ Enterprise Evaluation x86
• 4 CPU
• 6GB Memory

の環境を使用しています。

■仮想アドレス空間の確認

32 ビット の SQL Server の場合、ユーザーモードの仮想アドレス空間 (VAS : Virtual Address Space) の最大サイズは通常 [2GB] となります。

SQL Server のユーザーモードの VAS ですが、SQL を実行して確認をするためには二種類の方法があります。

1. DBCC MEMORYSTATUS を実行
2. sys.dm_os_memory_nodes を参照

この 2 種類の方法で VAS の状態を確認することが可能です。

DBCC MEMORY STATUS については技術情報が提供されています。
DBCC MEMORYSTATUS コマンドを使用して SQL Server 2005 のメモリ使用量を監視する方法

sys.dm_os_memory_nodes に関しては BOL に情報が記載されています。
sys.dm_os_memory_nodes (Transact-SQL)

これらを使用することで SQL Server の VAS の情報を確認することができます。

DBCC MEMORYSTATUS  を実行するとこのような情報が取得できます。

Memory Manager                           KB —————————————- ———– VM Reserved                              1114756 VM Committed                             1048580 AWE Allocated                            0 Large Pages Allocated                    0 Emergency Memory                         1024 Emergency Memory In Use                  16 Target Committed                         1048576 Current Committed                        1048576 Pages Allocated                          1019480 Pages Reserved                           0 Pages Free                               2672 Pages In Use                             105968 Page Alloc Potential                     890176 NUMA Growth Phase                        2 Last OOM Factor                          0 Last OS Error                            0

sys.dm_os_memory_nodes ではこのような情報が取得できます。

memory_node_id virtual_address_space_reserved_kb virtual_address_space_committed_kb ————– ——————————— ———————————- 0              1114692                           1048568                            32             0                                 12                                 locked_page_allocations_kb pages_kb             shared_memory_reserved_kb ————————– ——————– ————————- 0                          1019480              0                         0                          1019480              0                         shared_memory_committed_kb cpu_affinity_mask    online_scheduler_mask ————————– ——————– ——————— 0                          15                   15                    0                          15                   15                    processor_group foreign_committed_kb ————— ——————– 0               0 0               0

どちらのコマンドを実行しても VAS の情報を確認可能です。
DBCC MEMORYSTATUS で取得できる情報は、[sys.dm_os_memory_nodes] [sys.dm_os_memory_clerks] [sys.dm_os_memory_objects] [sys.dm_os_memory_cache_counters] [sys.dm_os_memory_pools] から取得することも可能です。

これらの情報を取得することで、[reserved] と [committed] の2 種類を確認することができます。
[reserved] (予約) は仮想メモリの領域を予約しているが実際には物理メモリを割り当てていない状態、 [committed] (確定) は物理メモリを割り当てている状態になります。

■max server memory の設定

SQL Server では [max server memory] を設定することでメモリの上限を設定することができますが、この設定をすることで、[reserved] とするサイズの上限を制限することが可能です。

今回は 32 ビット版の SQL Server を使用しているのですが、1.6GB 程度 VAS が確保できています。
# 大量のデータを検索して SQL Server のメモリを使用させた状態です。

この情報は以下のクエリを実行して取得しています。

SELECT     [virtual_address_space_reserved_kb] / 1024 AS [VAS Reserved (MB)],     [virtual_address_space_committed_kb] / 1024 AS [VAS Committed (MB)] FROM     [sys].[dm_os_memory_nodes] WHERE     [memory_node_id] = 0

初期設定の状態なので max server memory は [2147483647] が設定されているので、使用できる上限まで SQL Server はメモリを確保します。

それでは、max server memory を [1024] に設定して VAS の状態を確認してみたいと思います。

max server memory を設定することで、[Committed] のサイズが変更されていることが確認できます。
今回は設定変更後はサービスを再起動していないため、[Reserved] に関しては 1024 MB 以上の値となっています。
このことから max server memory は [Reserved] ではなく [Committed] 状態のメモリのサイズを制御していることが確認できます。

サービスの再起動をして、Reserved を解放し再度メモリの割り当てを確認してみます。

起動直後の VAS の状態は以下のようになっています。
SQL Server は通常の設定では max server memory を設定しても、起動直後は最小限のメモリのみ [Reserved] [Committed] で確保を行います。

サービスを再起動すると最小限の [Reserved] [Committed] から開始されますので、[Reserved] の確保も抑えられた状態となります。
# [Committed] が [1024 MB] で上限となるので、[Reserved] の確保も抑えらえた状態となります。

 

■/3GB スイッチによる VAS 上限の変更

今回の環境ではメモリを 6GB 割り当てているのですが、32 ビット OS の制限でユーザーモードの VAS の上限は 2GB となっています。
以下は SQL Server のメモリ割り当て (Memory ManagerTotal Server Memory (KB)) とサーバーの空きメモリ (MemoryAvailable MBytes) の関係をグラフ化したものになります。

サーバーの空きメモリはあるのですが、SQL Server で使用しているメモリについては VAS の上限に達してから頭打ちになっているのが確認できます。

この状態が 32 ビット版の SQL Server の通常設定時の限界となります。
空きメモリがあるにも関わらず、32 ビット OS の制限でメモリを最大限使用できない状態となっています。

32 ビット OS では [/3GB] オプションを設定することで、ユーザーモードのVAS の上限値を 2GB → 3GB に変更することが可能となります。
# [/3GB スイッチ] [4GB チューニング] と呼ばれる設定になります。

Windows Server 2003 向けなりますが以下の技術情報が公開されています。
/userva スイッチと /3GB スイッチを使用してユーザー モード領域を 2 ～ 3 GB の間でチューニングする方法
4 GB RAM チューニング機能と物理アドレス拡張のスイッチの説明

Windows Server 2008 以降は boot.ini ではなく、BCD が使用されていますので bcdedit を使用して設定を行う必要があります。

[/3GB] スイッチは、[increaseuserva 3072] で設定することが可能です。

bcdedit /set {current} increaseuserva 3072

上記コマンドをコマンドプロンプトで実行することでユーザーモードの VAS を3GB まで使用することが可能となります。
# 代わりにカーネルモードが 1GB に制限されますが。

設定を削除する場合は以下のコマンドを実行します。

bcdedit /deletevalue {current} increaseuserva

 

設定をしたら一度サーバーを再起動して、SQL Server のメモリの使用状況を確認してみます。

 

設定をすることで設定前と比較して 1GB 程メモリの割り当てが増えていることが確認できます。

この設定をすることで 2GB 以上のメモリを使用することが可能となりますが、サーバーの空きメモリはまだ残っておりメモリを最大限活用できていない状態となっています。

これ以上のメモリを使用するためには AWE (Address Windowing Extensions) を有効にする必要があります。
AWE については次の投稿でまとめたいと思います。