SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

TABLESAMPLE 句のデータ取得はどのように動作しているか

one comment

昨日、勉強会の後にお話をさせていただいていた中で [TABLESAMPLE] を指定した場合のデータサンプリングはどのように動作しているのかという質問を頂きました。

TABLESAMPLE の存在を知らなかったので、お話を聞かせていただきながらこんな感じですかね~というイメージで話をしていたのですが、実際どのようにデータを取得しているのか調べてみました。

■TABLESAMPLE 句の動作


TABLESAMPLE 句を使用するとデータをランダムにサンプリングして取得することができます。

詳細に関しては [TABLESAMPLE 句の使用による結果セットの制限] に記載されており、ポイントとなるのは以下の記載だと思います。

TABLESAMPLE SYSTEM は、おおよその率の行を返し、テーブル内の 8 KB の物理ページごとにランダム値を生成します。
ページに対して生成されたランダム値とクエリに指定された率に基づいて、そのページをサンプルに含めるか除外するかが決定されます。
サンプルに含められた各ページは、すべての行をサンプル結果セットとして返します。たとえば、TABLESAMPLE SYSTEM 10 PERCENT と指定した場合、SQL Server では指定したテーブルのデータ ページの約 10% からすべての行が返されます。

TABLESAMPLE 句を指定した場合の実行プランは以下のようになります。
image

今回はサンプリングとして 100 行取得するように指定しています。
# データの分布によっては必ず 100 行取れるという保証はないようですが。
この時のプランは [Clustered Index Scan] となっているのでプラン上はテーブルのフルスキャンが実行されています。
今回のテーブルは、100 万行のデータが入っているテーブルなのですが、プロパティではスキャン時に取得された実際の行数は 100 行となっています。

[SET STATISTICS IO] を有効にして、I/O 情報を取得すると以下のようになっています。
image

今回のテーブルには 1 ページ 10 行のデータが入るように調整していますので、フルスキャンをすると 10 万ページの読み取りが発生することになりますが、I/O 情報を見ると 12 ページの読み取りとなっています。
プランだけを見るとフルスキャンをしているように見えますが、実際にはサンプリングに必要となるデータのみを取得しているということになります。

さらに情報をブレークダウンするために、ページ情報を取得してみます。
image

ページ情報を合わせて取得すると行の取得のトレンドが見えてきます。
TABLESAMPLE のサンプリングですが、複数のページから数行ずつデータを取得するのではなく、ランダムで取得したページ内の全データを取得してサンプリングをしているようです。
# 取得対象となったページの特定行を返すのではなくページに含まれる全行を返すようです。

100 ROWS とした場合、1ページ 10 行のデータが入っていますので、100 行の取得となった場合は 10 ページのみをピンポイントで取得して結果を返しているようです。
見かけ上はフルスキャンですがある程度ピンポイントでデータを取得しているので速い速度でサンプリングができるようですね。

行のサンプリングではなくページをサンプリングしていますので、BOL に

  • サンプルは、個別行レベルで真のランダム サンプルである必要がない。
  • テーブルの個々のページの行が、同じページの他の行と相関していない。

の記載があるようです。

勉強会にスピーカーとしてとてとて訪問させていただいて、こちらもとても勉強になりました!!
クエリはもっと勉強しないと駄目ですね~。

Written by masayuki.ozawa

4月 10th, 2012 at 12:55 pm

Posted in SQL Server

Tagged with

One Response to 'TABLESAMPLE 句のデータ取得はどのように動作しているか'

Subscribe to comments with RSS or TrackBack to 'TABLESAMPLE 句のデータ取得はどのように動作しているか'.

  1. […] 以前、TABLESAMPLE 句のデータ取得はどのように動作しているか という投稿をしました。 […]

Leave a Reply

*