昨日、勉強会の後にお話をさせていただいていた中で [TABLESAMPLE] を指定した場合のデータサンプリングはどのように動作しているのかという質問を頂きました。
TABLESAMPLE の存在を知らなかったので、お話を聞かせていただきながらこんな感じですかね~というイメージで話をしていたのですが、実際どのようにデータを取得しているのか調べてみました。
■TABLESAMPLE 句の動作
TABLESAMPLE 句を使用するとデータをランダムにサンプリングして取得することができます。
詳細に関しては [TABLESAMPLE 句の使用による結果セットの制限] に記載されており、ポイントとなるのは以下の記載だと思います。
TABLESAMPLE SYSTEM は、おおよその率の行を返し、テーブル内の 8 KB の物理ページごとにランダム値を生成します。
ページに対して生成されたランダム値とクエリに指定された率に基づいて、そのページをサンプルに含めるか除外するかが決定されます。
サンプルに含められた各ページは、すべての行をサンプル結果セットとして返します。たとえば、TABLESAMPLE SYSTEM 10 PERCENT と指定した場合、SQL Server では指定したテーブルのデータ ページの約 10% からすべての行が返されます。
TABLESAMPLE 句を指定した場合の実行プランは以下のようになります。
今回はサンプリングとして 100 行取得するように指定しています。
# データの分布によっては必ず 100 行取れるという保証はないようですが。
この時のプランは [Clustered Index Scan] となっているのでプラン上はテーブルのフルスキャンが実行されています。
今回のテーブルは、100 万行のデータが入っているテーブルなのですが、プロパティではスキャン時に取得された実際の行数は 100 行となっています。
[SET STATISTICS IO] を有効にして、I/O 情報を取得すると以下のようになっています。
今回のテーブルには 1 ページ 10 行のデータが入るように調整していますので、フルスキャンをすると 10 万ページの読み取りが発生することになりますが、I/O 情報を見ると 12 ページの読み取りとなっています。
プランだけを見るとフルスキャンをしているように見えますが、実際にはサンプリングに必要となるデータのみを取得しているということになります。
さらに情報をブレークダウンするために、ページ情報を取得してみます。
ページ情報を合わせて取得すると行の取得のトレンドが見えてきます。
TABLESAMPLE のサンプリングですが、複数のページから数行ずつデータを取得するのではなく、ランダムで取得したページ内の全データを取得してサンプリングをしているようです。
# 取得対象となったページの特定行を返すのではなくページに含まれる全行を返すようです。
100 ROWS とした場合、1ページ 10 行のデータが入っていますので、100 行の取得となった場合は 10 ページのみをピンポイントで取得して結果を返しているようです。
見かけ上はフルスキャンですがある程度ピンポイントでデータを取得しているので速い速度でサンプリングができるようですね。
行のサンプリングではなくページをサンプリングしていますので、BOL に
- サンプルは、個別行レベルで真のランダム サンプルである必要がない。
- テーブルの個々のページの行が、同じページの他の行と相関していない。
の記載があるようです。
勉強会にスピーカーとしてとてとて訪問させていただいて、こちらもとても勉強になりました!!
クエリはもっと勉強しないと駄目ですね~。
[…] 以前、TABLESAMPLE 句のデータ取得はどのように動作しているか という投稿をしました。 […]
TABLESAMPLE と TOP 句を組み合わせる場合の注意点 « SE の雑記
20 4月 12 at 18:48