SQL Serverのヒープテーブル

このブログシリーズの最後の記事では、Bツリーインデックスとは何かについて議論し、その歴史を簡単に説

SQL Serverが使用するインデックスを調べる前に、データ構造の基礎を設定することが重要です。 作成できる最も基本的なテーブルはヒープです。 ヒープは、互いにリンクされていないページのソートされていない構造です。

ヒープはいつ使うべきですか?

ベストプラクティスの推奨事項は、ほとんどの場合、SQL Serverのヒープを避けることです。 キンバリー Trippはここで理由と議論のいくつかを説明しますが、私は高レベルをカバーします。 ヒープは、最も基本的なストレージ構造であり、通常の(OLTP)ワークロードのパフォーマンスを向上させる機能が欠けています。 書き込み操作(更新/挿入/削除)中に維持するページが少ないため、ヒープは抽出、変換、および読み込み(ETL)操作に適していると主張する人もいます。 具体的には、ページ分割が発生し、インデックスの多くのレベルを更新する必要がある場合、ヒープはBツリーインデックスよりも高速になります。 さらに、以下で詳しく説明する行識別子(RID)は、わずか8バイトです。 非クラスタ化インデックスは、インデックス付き行ごとにクラスタ化キーまたはRIDを格納する必要があるため、これは、多くのキー列を持つクラスター化 通常、ヒープではなくクラスター化インデックス(このシリーズを続けるにつれてクラスター化インデックスの詳細)を使用することをお勧めしますが、クラスター化インデックスを使用する場合は、クラスター化インデックスよりも効率的であることが証明されている場合にのみ使用してください。 ヒープの構造について説明した後、ヒープを使用する際のパフォーマンス上の懸念について概説します。

ヒープ構造

ヒープは、リンクされていないソートされていないページのグループです。 すべてのタイプのインデックス付きテーブルと非インデックス付きテーブルが同じページ構造を使用するため、ページの解剖学はこのシリーズの範囲外ですが、

ヒープは、ヒープを構成するデータページを指す1つ以上のindex allocation map(IAM)ページで構成されます。 これに対する唯一の例外は、更新された行があり、そのページに収まらなくなった場合です。 その場合、スペースまたは新しいページを持つ既存のページに移動された行への転送ポインタを取得します。 さらなる操作によって行が引き続き再配置が必要な場合は、転送レコードのチェーンを生成することができます。

heap-structure-1

画像参照:MSDN

ヒープにはsysに1つの行があります。パーティションごとのパーティションとそのindex_idはゼロになります。 このレコードでは、first_iam_pageはインデックス割り当てマップ(IAM)ページの最初のページを指します。 IAMページは、各アロケーションユニットのページをマップし、テーブルの4GBのチャンクを管理します。 配分単位は次のとおりです:

  1. IN_ROW_DATA allocation unit
  2. LOB_DATA allocation unit
  3. ROW_OVERFLOW_DATA allocation unit

IAMページには、標準の96バイトのヘッダーの後にIAMヘッダーが含まれています。 IAMヘッダーは、混合エクステント割り当て用の8つのスロットから始まり、次にテーブルに割り当てられた均一なエクステントを検索するための8000バイ

混合エクステントは、複数のテーブルのページを含むエクステントです。 これは、共有範囲とも呼ばれます。 混合エクステントの目的は、64KBより小さいテーブルでスペースを節約することです。 統一範囲とは、範囲内のすべてのページがその単一のテーブルに属している範囲のことです。

mixed-extent

画像参照:範囲

IAMページを調査するには、まずそれを見つける必要があります。

DBCC IND(‘demo’,’dbo.デモ’,1)

dbcc-ind

DBCC INDでは、17のデータページと1つのIAMページがあります。 IAMページは自己参照ではないため、IAMFID(IAMファイルId)およびIAMPID(IAMページId)はNULLです。 これは、IAMページがファイルId(PageFID)1にあり、ページId(PagePID)297であることを意味します。 DBCCページでは、IAMページを表示できます。

(3604);

DBCCページ(”,1,297,3);

dbcc-page-iam

黄色では、混合範囲には8つの単一ページの割り当てがあります。 オレンジ色では、均一な範囲が範囲で表示されます。

パフォーマンスへの影響

ヒープ上でさまざまなデータ操作言語(DML)操作を実行すると、これらの影響があります。

  • 挿入–新しい行は、十分なスペースを持つ最初の利用可能なページに配置することができます。 したがって、新しい行が挿入されるたびに、最後のページに追加される可能性があります。
  • UPDATE–行は、更新後にページに収まる場合は同じページに残り、そうでない場合は現在のページから削除され、十分なスペースを持つ最初の利用可能なページに配置され、転送ポインタが元のページに書き込まれます。
  • DELETE–データは上書きされず、スペースは再利用可能としてフラグが設定されます。 これにより、テーブルが必要以上に多くのディスク領域を消費する可能性があります。
  • SELECT–ほとんどのクエリでは、テーブル全体のテーブルスキャンを読み取る必要があります。 この例外は、使用可能な適切な非クラスター化インデックスがある場合です。 クラスタ化されていないインデックスについては、このシリーズの後半で説明します。

を読み込みます。 これは、物理構造がソートされておらず、範囲スキャンをサポートするページリンクがないためです。

統計IOを

DBCC IND(‘demo’,’dbo.デモ’,1)

dboからCOUNT(*)
を選択します。demo

上からDBCC INDコマンドを繰り返すと、このテーブルには18ページがあることがわかります。 17のデータページと1つのIAMページ。

–すでに上に表示されているため、DBCC INDの結果を削除しました。
-18行の結果、18ページを示します。

(影響を受ける18行)
DBCC実行が完了しました。 DBCCがエラーメッセージを出力した場合は、システム管理者に連絡してくださ

行数
—-
5031
(1 影響を受ける行)

テーブル’Demo’。 スキャンカウント1、論理読み取り17、物理読み取り0、先読み読み取り0、lob論理読み取り0、lob物理読み取り0、lob先読み読み取り0。
(1row(s)affected)

クエリからの17の論理読み取りに注意してください。 これは、IAMページが論理読み取りメトリックでカウントされず、18ページのテーブルのフルスキャンを実行したためです。

書き込みと転送ポインタ

上記のように、挿入と削除はかなり簡単です。 挿入は、新しいページの作成を意味する場合でも、使用可能なスペースを持つ最初のページで発生します。 削除は、行が占有していた領域の割り当てを解除することによって実行されます。

更新は、物事が山のために下り坂に行く場所です。 固定長データ型が更新されると、更新はその場で行われます。 可変長データ型が更新され、長さが増加した場合、行が同じページに収まらなくなる可能性があります。 行が収まらなくなると、転送ポインタが元の行があったスペースに配置され、行が新しいページに再配置されます。 このプロセスは、読み取りパフォーマンスに悪影響を及ぼします。 実証してみましょう。

もともと私たちのすべての記録はdboにありました。デモテーブルには、9文字の文字列を持つVARCHARsが含まれていました。

dboからトップ5*
を選択します。demo

demo table data

いくつかの前方ポインタを作成するために、すべての行を更新し、varLen列の長さを増やします。

dboを更新します。デモ
セットvarLen=replicate(‘a’,100)

すべての行が前方ポインタが残された新しいページに再配置されているため、ヒープに95ページがあります。

DBCC IND(‘demo’,’dbo.デモ’,1)

dbcc-output-1

あなたが想像できるように、ヒープは完全なテーブルスキャンを必要とするので、我々は今、レコードの同じ量のために、より多くのページを読む必要があ

統計IOを

に設定するDBOからCOUNT(*)
を選択します。demo
(影響を受ける1行)

テーブル’Demo’。 スキャンカウント1、論理読み取り4405、物理読み取り0、先読み読み取り0、lob論理読み取り0、lob物理読み取り0、lob先読み読み取り0。

元のページを読まなければならず、順方向のポインタに従わなければならなかったため、読み取りが大幅に増加しました。 クラスター化インデックスが必要な理由は、パフォーマンスへの影響とメモリとディスク上の領域の無駄です。次回

ヒープデータ構造はインデックスではなく、SQL Serverのテーブルで最悪のパフォーマンスを発揮するデータ構造です。 私たちは、インデックスを議論する際に有用であろう理解の基礎として、今日それをカバーしました。 このシリーズの次の部分では、テーブルデータのB+ツリー構造であるクラスター化インデックスについて説明します。

コメントを残す

メールアドレスが公開されることはありません。