none
単純検索時におけるディスクパフォーマンスカウンタの見方について RRS feed

  • 質問

  • Cドライブに標準インストールされたSQLServer2005(x64)を利用しています。

    ※システムデータベース、ユーザデータベースなどのファイルはすべてCドライブです。

    ディスクのパフォーマンスカウンタの状態を勉強しようと以下の手順でSQL文を実行しました。

    ※物理ディスクは2本でRadi1構成

     

    ●手順1

     新規データベースを作成。データファイルはデフォルトのCドライブに配置。

     1カラム「varchar(max)」を保持するテーブルを新規作成

    ●手順2

     insert into table1 values(space(1))を実行しデータが50万行になるまでインサートを実行

    ●手順3

     select * from table1でデータを取得し、その時のパフォーマンスカウンタの状態を監視

    ●想定した挙動

     Logical Disk/Avg. Disk sec/read:C:が高い値を示す。

    ●実際の挙動

     Logical Disk/Avg. Disk sec/write:C:がSelect文が終了するまで継続して高い値を示した。

     次いで、なぜか、Logical Disk/Avg. Disk sec/write:E:がSelect文が終了するまで断続的に高い値を示した。

     なお、Logical Disk/Avg. Disk sec/read:C:、Logical Disk/Avg. Disk sec/read:E:の値は、0を示していた。

     

    なぜ読み込みが発生しているにもかかわらず、読み込み関係のカウンタ値が上昇せず、書き込み関係のカウンタ値が上昇したのでしょうか?

    検証環境であるため他の処理が邪魔をしている訳でもなく、バッファキャッシュのメモリが不足しページファイルに書き込んでいる訳でもありません。

    ひとつ考えられることは、tempdbでselect文発行時にトランザクションが発生していることがカウンタ値から読み取れるので、tempdbへの書き込みが発生しているということなのでしょうか?

     

    宜しくお願い致します。

     

    2008年11月27日 4:29

回答

  • こんにちは、naginoです。

     

    一般的な RDBMS は、パフォーマンスと信頼性を両立するために、上記のような動作をします。

     

    クエリを実行する場合、先ずトランザクションログ(ジャーナル)に処理内容を先行書き込みし、データがキャッシュに無い場合はディスクから読み取りを行い、書き込みに関しては一旦キャッシュのみ更新を行い、処理結果を返します。

    その後、更新されたデータは非同期で書き込みます。

    そのため、クエリの実行とディスクのアクセスのタイミングは一致しません。

    場合によっては tempdb の使用が絡み、より複雑な動作を行います。

     

    詳細は深い話になりますので、先ずは下記のページ、並びにそこからハイパーリンクが張られているページをご参照ください。

    http://msdn.microsoft.com/ja-jp/library/aa337560(SQL.90).aspx

    http://msdn.microsoft.com/ja-jp/library/aa337525(SQL.90).aspx

    あるいは技術系情報サイトで RDBMS の基礎を解説しているページをご参照ください。

     

    ご参考になれば幸いです。

    2008年11月27日 4:42
  • こんにちは、naginoです。

     

    失礼いたしました、前回は手順 2 と 3 を続けて実行した場合を想定していました。

    手順 2 の後にどのような状況だったかによって色々と変わるのは事実です。

    手順 2 の後にチェックポイントを実行してデータの書き込みを完了させていた場合であれば、レイジーライタの書き込みとは考えられないのは確かです。

     

    データファイルを Cドライブということですから、おそらくトランザクションログも Cドライブに配置していると想定します。

    また、システムデータベースも Cドライブにあると想定します。

    この場合、手順 2 の後にチェックポイントを実行してデータの書き込みが完了していても、以下のような SELECT文に伴う各種書き込みが Cドライブに発生します。

     ・トランザクションログの書き込み

     ・OS のセキュリティログの書き込み

     ・SQL Server のログの書き込み

     ・場合によっては、tempdb に対する書き込み(スプール、ソートのための書き出し、明示的な一時的オブジェクト作成等)

     ・場合によっては、SQL Server の使用メモリが増加したことによる Windows によるページングファイルの書き込み

     ・場合によっては、システムデータベースに対して管理情報の書き込み

     ・場合によっては、スナップショット作成のための書き込み

     ・etc...

    今回は 50万行の取得ということのようですし、tempdb にアクセスが見られたとのことですので、おそらくクエリの結果をスプールするために使用されたと推測されます。

     

    なお、データはおそらくキャッシュに全て載っていたので Read が記録されなかったと推測されます。

     

    E ドライブは何に使用されているのか分かりませんので、そちらはちょっと分かりません。

     

    蛇足ながら、SELECT文はデータ取得の命令ですが、書き込みを全く行わないことが保障されている命令ではありません。

    むしろ、多くの環境・設定では、多少なりとも書き込みを発生させます。

     

    ご参考になれば幸いです。

    2008年11月28日 7:28

すべての返信

  • こんにちは、naginoです。

     

    一般的な RDBMS は、パフォーマンスと信頼性を両立するために、上記のような動作をします。

     

    クエリを実行する場合、先ずトランザクションログ(ジャーナル)に処理内容を先行書き込みし、データがキャッシュに無い場合はディスクから読み取りを行い、書き込みに関しては一旦キャッシュのみ更新を行い、処理結果を返します。

    その後、更新されたデータは非同期で書き込みます。

    そのため、クエリの実行とディスクのアクセスのタイミングは一致しません。

    場合によっては tempdb の使用が絡み、より複雑な動作を行います。

     

    詳細は深い話になりますので、先ずは下記のページ、並びにそこからハイパーリンクが張られているページをご参照ください。

    http://msdn.microsoft.com/ja-jp/library/aa337560(SQL.90).aspx

    http://msdn.microsoft.com/ja-jp/library/aa337525(SQL.90).aspx

    あるいは技術系情報サイトで RDBMS の基礎を解説しているページをご参照ください。

     

    ご参考になれば幸いです。

    2008年11月27日 4:42
  •  

    naginoさん、ご回答ありがとうございました。

     

    naginoさんがご指摘頂いた内容ですが、更新処理時の内部動作ではないかと私は理解しております。

    つまり、InsertやUpdate処理の場合には、クエリを発行したタイミングとは別のタイミング(チェックポイントやレイジーライタ処理時)でデータファイルへの書き込みが発生すると認識しておりnaginoさんの考えと一致しております。

     

    今回の検証では、検索系のクエリ(Select文)ですので、ディスクへの書き込みが発生するのはおかしい気がします。

    SQLServerがSelect文を受け取るとバッファキャッシュ内を確認し該当データが存在しなければディスクから読み取りバッファキャッシュへ格納するという動作になるものと思われます。この時、バッファキャッシュに空きがなければ拡張、もしくは、レイジーライタ処理によってバッファからデータファイルに書き込まれたりと、色々と複雑な動作をするかとは思いますが、今回の検証サーバはx64でありメモリも仮想環境の検証用途などで20GBを積んでおり、SQLServerとしてもTargetMemoryは20GB程度を示しているのでバッファキャッシュ領域が足りないという現象は起こらないような状況です。

     

    私の理解不足などもあろうかとは思いますが、またご助言を頂ければ幸いです。

    どうぞ、宜しくお願い致します。

    2008年11月28日 5:58
  • こんにちは、naginoです。

     

    失礼いたしました、前回は手順 2 と 3 を続けて実行した場合を想定していました。

    手順 2 の後にどのような状況だったかによって色々と変わるのは事実です。

    手順 2 の後にチェックポイントを実行してデータの書き込みを完了させていた場合であれば、レイジーライタの書き込みとは考えられないのは確かです。

     

    データファイルを Cドライブということですから、おそらくトランザクションログも Cドライブに配置していると想定します。

    また、システムデータベースも Cドライブにあると想定します。

    この場合、手順 2 の後にチェックポイントを実行してデータの書き込みが完了していても、以下のような SELECT文に伴う各種書き込みが Cドライブに発生します。

     ・トランザクションログの書き込み

     ・OS のセキュリティログの書き込み

     ・SQL Server のログの書き込み

     ・場合によっては、tempdb に対する書き込み(スプール、ソートのための書き出し、明示的な一時的オブジェクト作成等)

     ・場合によっては、SQL Server の使用メモリが増加したことによる Windows によるページングファイルの書き込み

     ・場合によっては、システムデータベースに対して管理情報の書き込み

     ・場合によっては、スナップショット作成のための書き込み

     ・etc...

    今回は 50万行の取得ということのようですし、tempdb にアクセスが見られたとのことですので、おそらくクエリの結果をスプールするために使用されたと推測されます。

     

    なお、データはおそらくキャッシュに全て載っていたので Read が記録されなかったと推測されます。

     

    E ドライブは何に使用されているのか分かりませんので、そちらはちょっと分かりません。

     

    蛇足ながら、SELECT文はデータ取得の命令ですが、書き込みを全く行わないことが保障されている命令ではありません。

    むしろ、多くの環境・設定では、多少なりとも書き込みを発生させます。

     

    ご参考になれば幸いです。

    2008年11月28日 7:28
  • こんにちは、フォーラムオペレータ大久保です。

     

    nagino さん、いつもありがとうございます!

     

    ユクヒロ さんこんにちは。その後本件いかがでしょうか?

    nagino さんのアドバイスは非常に参考になったことと思いますので、「回答済み」チェックをつけさせていただきました。

    ユクヒロ さんの中でまだ疑問が残るようでしたら、ぜひその旨返信をいただければと思います。

    私はお役にたてるかどうか微妙ですが (^^; なにか情報が得られるようでしたらご提供しますので。。。

    (といっても、naginoさんがすでに提示されてる資料以上のものはなさそうですが)

     

    それでは

    2008年12月10日 0:37