テーブルをキャッシュに保存するために必要なメモリ量(SQL Server 2008)


5

私は1つの大きなテーブル - 8GB、40M行 - を使用する長く複雑なクエリを実行します。ご参考までに、すべての/ほとんどの行が各クエリで使用されます。最初のクエリとそれ以降のすべてのクエリについて、アクティビティモニタに大量のIOが表示されます。サーバーは現在6.5GBのメモリを使用しているので、アップグレードしたいです。問題は、これらすべてのディスク読み取りを回避するために必要なメモリ量です。テーブルサイズ以上の球場にありますか?

これはSET STATISTICS IO出力です。BigTableは私が質問しているものです、SmallTableはBigTableと1対多の関係にあります。#entranceはクエリの出力(数百行の出力)を保持します。

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+2

このサーバー/データベースは、8GBのデータの1つのテーブルに対してクエリを実行する以外に何もしませんか?私はまた、この質問の1つの表であるが複雑な質問の側面について多少私の頭をかいています。DDLの可能性や、この複雑さの本質に対する少しの洞察がありますか? 14 9月. 112011-09-14 22:20:40

  0

基本的な確認だけで、使用しているSQLサーバーのバージョンは何ですか?それはエンタープライズではない場合、あなたはSQL Serverのためにあなたの現在のメモリのすべてを使用することはできません。 16 9月. 112011-09-16 15:30:00

  0

私は最終的にサーバーのRAMをアップグレードしてテストをしました。私は結果を以下の答えとして投稿しました。 09 2月. 132013-02-09 08:50:11

3

クエリの性質は何ですか?SELECTだけ?それともDUI(DELETE、UPDATE、INSERT)の混合?あなたの回復モデルは何ですか?デフォルトのトランザクション分離レベルを使用していますか?

これらすべてのページをメモリに保存する場合は、メモリを増やす必要があります(ただし、それは既にわかっています)。「いくら」を知りたいのですが、実際のところ、クエリの性質によっては、テーブル全体を保持するのに十分なメモリがあっても、まだI/Oの問題が発生する可能性があります。

上記のすべてを考慮して...ええ、ディスク上のテーブルのサイズをメモリの量としてボールパークします。あとでまだディスクI/Oがあると思っても驚かないでください。

  0

私はサーバのRAMをアップグレードし、「メモリの量としてディスク上のテーブルのサイズをボールパーク」することは絶対に正しいです。下記の私の答えを見てください。 08 2月. 132013-02-08 11:59:48


8

これは2部構成の答えです。

最初の部分はあなたが(サーバーに収まる限り)あなたができる限り多くのRAMを買うことです。

2番目の部分は、データに対する読み取りが常に表示されることです。トリックは、それらの読み取りが物理的なものか論理的なものかを調べることです。物理的な読み取りはディスクに向かっています。論理読み取りは、メモリー内のバッファー・プールから読み取られます。これは、SQLスクリプトの先頭に「SET STATISTICS IO ON」と入力してSSMSで実行することで確認できます。メッセージタブでは、実行されているクエリごとに特定のIO情報が表示されます。具体的には、テーブルごとに発生している論理的操作と物理的操作(この場合は読み取り)の両方の数がわかります。物理的な読み取り数がかなり多い場合(これらの数は基本的に8倍の倍数で読み取られたブロックの数で、その後1024で除算してメグ単位の数になります)、さらにRAMが必要です。論理読み取り数が多い場合は、実際にはディスクにアクセスしていませんが、クエリのチューニングやインデックスの作成などが必要になる可能性があります。

(注:この答えのために、私はSELECTステートメントだけを想定しています。INSERT/UPDATE/DELETE操作はもう少し複雑ですが、同じ基本が適用されます。)

  0

私は試してみましょう:統計IOをオンに設定して報告します。詳細情報:クエリは100%選択です。各クエリは数分かかります。サーバーはクエリを実行して結果をExcelにダンプする小さなアプリも実行しますが、これはサーバーのパフォーマンスに影響するとは思わない。 15 9月. 112011-09-15 05:00:37

  0

出力を読むのに手助けが必要な場合は、実行計画とSET STATISTICS IO出力を投稿してください。 15 9月. 112011-09-15 05:05:59


1

ここでもっと多くのRAMが役立つとは限らない。簡単に計算すると、クエリが125GBのデータ領域のどこかで処理されていることがわかります。8GBを含むテーブルには非常に印象的です。

(2829948 + 13482115 + 1568)x 8/1048576 = 125 GB

270MBの物理的な読み込みは、低速実行の一部を占めますが、メモリ内のオーバーヘッドほどのものはありません。

メモリを追加するよりも、クエリを再処理した方が良い結果が得られます。新しい質問にDDL/query/execution planを投稿すれば、誰かが確かに手助けすることができるでしょう。


3

私はいつもパーティーに遅刻します。:)このクエリは最も効果的に調整できます。しかし、8 GBのテーブルをキャッシュするためのRAM容量については、予備的な質問として、SQL Serverがサーバーに対していくつのメモリノードをレポートするのかという疑問があります。各メモリノード(物理NUMAノードと論理的に同等)には、独立して管理されるバッファプールキャッシュがあります。そのノードのプロセッサで実行されるタスクは、そのノードのバッファプールにブロックを挿入します。

そのため、8 GBのテーブル全体を読み取るタスクが単一のNUMAノードで実行される可能性がある場合は、そのノードのバッファープールに8 GBのテーブルを含めることができなければなりません。そのため、NUMAノードあたり最大8 GBのサーバーメモリから始めます。それからそれを増やし始めましょう。クエリメモリはバッファプールの最大70%を消費し、データベースブロックの内容には30%しか残されません。そのため、8 GBのテーブルは、サーバー用の単一のNUMAノード上の最大サーバーメモリの30%以下にする必要があります。最大サーバーメモリに26 2/3 GB。NUMAノードごと。SQL Server 2012の場合、SQL Server 2008 R2以前でマルチページアロケータを使用していたもの(つまり、最大サーバーメモリ範囲外)は、現在、最大サーバーメモリ範囲内であり、考慮する必要があります。SQL Serverの最大サーバーメモリ制限の一部であるかどうかにかかわらず、どちらの方法でも合計サーバーメモリに含める必要があります。

ああそうです…一時テーブルの使用や他のtempdbの使用は、テーブルがキャッシュから追い出されないように、最大​​サーバーメモリにも収容されるべきです。

Windows OS用に十分なメモリーを追加してください(少なくとも4 GB)。SQL Serverワーカースレッド、SQL Serverコアプロセス、SQL Agentジョブ、その他のアプリケーションメモリの必要量に十分な数を追加します。

1台のNUMAノードサーバーで、8 GBのテーブルを確実にキャッシュするために、最大で27 GBのサーバーメモリを搭載した約36 GBのRAMを設置します。また、複数のNUMAノードがある場合、追加のワーカースレッドが追加のNUMAノードの追加のコアに付随するのと同様に、NUMAノードあたり最大26 2/3 GBの最大サーバーメモリが増加します。

それは過度に思えるかもしれませんが、それはSQL ServerがNUMAをどのように処理するかに一部起因します。しかし、あなたは運がいいかもしれません!単一のレイジーライターで十分な場合は、スタートアップトレースフラグ8015を使用してNUMAを無視し、単一のバッファプールを管理できます。そのため、サーバー上にNUMAノードがいくつあっても、8 GBのテーブルを常駐させるには、約36 - 40 GBで十分です。次のブログ記事でこれについてもう少し話し合ってください。トレースフラグ8015を使用する場合は、スピンフラグ競合とCMEMTHREAD待機がメモリ割り当てに関連して増大するのを防ぐために、トレースフラグ8048をそれと一緒に評価することをお勧めします。http://sqlsasquatch.blogspot.com/2013/02/sql-server-cache-database-working-set.html


2

サーバーのRAMを8GBから64GBにアップグレードしてテストしました。テーブルサイズは16.05GBになり、インデックススペースは187MBになりました(1つのクラスタ化インデックスと1つの一意の非クラスタ化インデックス)。テーブル内の137個のフィールドすべてを平均するクエリを実行する前後で、サーバーのRAM使用量の合計を確認しました。総メモリは4.22GBから20.4GB = 16.18GBに増加しました。これは表のサイズの100.8%です。SQLRockstarは、RAMの使用量がテーブルのサイズの大きさであると予測していました。

より詳しい情報:

  • クラスタ化インデックスのページいっぱいは、98.1%です。
  • テーブルに対するそれ以上のクエリはディスクIOを引き起こしませんでした。
  • SQL Server 2008 R2開発者
  • 実行計画は、クラスタード・インデックスが使用されたことを示しています