メモリ最適化テーブルのクエリ処理のガイド

2017-06-13

SQL Server では、インメモリ OLTP によってメモリ最適化テーブルとネイティブコンパイルストアドプロシージャが導入されています。ここでは、メモリ最適化テーブルとネイティブコンパイルストアドプロシージャの両方に対するクエリ処理の概要について説明します。

ここでは、次の内容を含め、メモリ最適化テーブルに対するクエリがどのようにコンパイルおよび実行されるかについて説明します。

ディスクベーステーブルに対する SQL Server のクエリ処理パイプライン。
クエリ最適化。メモリ最適化テーブルの統計のロール、および不適切なクエリプランのトラブルシューティングのためのガイドライン。
解釈された Transact-SQL を使用したメモリ最適化テーブルへのアクセス。
メモリ最適化テーブルへのアクセスのためのクエリ最適化に関する注意点。
ネイティブコンパイルストアドプロシージャのコンパイルと処理。
オプティマイザーがコストの推定に使用する統計。
不適切なクエリプランを修正する方法。

サンプルクエリ

次の例を使用して、この記事で説明するクエリ処理の概念を示します。

ここでは、Customer と Order という 2 個のテーブルについて検討します。次の Transact-SQL スクリプトには、2 個のテーブルおよび関連するインデックスの定義が (従来の) ディスクベース形式で含まれています。

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO

ここでは、クエリプランを構築できるように、2 個のテーブルに Northwind サンプルデータベースのサンプルデータが読み込まれています。このサンプルデータベースは「 SQL Server 2000 用の Northwind サンプルデータベースと pubs サンプルデータベース」からダウンロードできます。

次のクエリについて考えてみます。このクエリでは、Customer テーブルと Order テーブルを結合し、注文の ID および関連付けられた顧客情報を返します。

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

SQL Server Management Studio では、次のような推定実行プランが表示されます。

ディスクベーステーブルの結合のためのクエリプラン。
ディスクベーステーブルの結合のためのクエリプラン。

このクエリプランについて

Customer テーブルの行は、クラスター化インデックスから取得されます。これは、テーブルデータ全体を含んでいるプライマリデータ構造になっています。
Order テーブルのデータは、CustomerID 列の非クラスター化インデックスを使用して取得されます。このインデックスには、結合に使用される CustomerID 列と、ユーザーに返す主キー列 OrderID の両方が含まれています。 Order テーブルから追加の列を返す場合は、Order テーブルのクラスター化インデックス内の参照が必要です。
論理演算子 Inner Join は、物理操作 Merge Joinによって実装されます。その他の物理結合の種類は、 Nested Loops と Hash Joinです。 Merge Join演算子は、両方のインデックスが結合列 CustomerID で並べ替えられているという事実を利用します。

OrderID だけでなく、Order テーブルからすべての行を返す、このクエリのわずかな違いを考えてみましょう。

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

このクエリの推定プランは、次のとおりです。

ディスクベーステーブルのハッシュ結合のクエリプラン。
ディスクベーステーブルのハッシュ結合のクエリプラン。

このクエリでは、Orders テーブルの行はクラスター化インデックスを使用して取得されます。 Hash Match物理操作がInner Joinに使用されるようになりました。 Order のクラスター化インデックスは CustomerID では並べ替えされないため、 Merge Join には並べ替え演算子が必要になり、パフォーマンスに影響します。前の例 (46%) のMerge Join演算子のコストと比較したHash Match演算子 (75%) の相対コストに注意してください。オプティマイザーは前の例でも Hash Match 演算子を考慮していましたが、 Merge Join 演算子の方がパフォーマンスが向上すると結論付けました。

ディスクベーステーブルに対する SQL Server クエリ処理

次の図は、アドホッククエリに対する SQL Server のクエリ処理フローの概要を示しています。

SQL Server クエリ処理パイプライン。

このシナリオでは:

ユーザーがクエリを実行します。
パーサーと algebrizer は、ユーザーから送信される Transact-SQL テキストに基づき、論理操作でクエリツリーを構築します。
オプティマイザーは物理操作 (たとえば、Nested Loops 結合) を含む最適化されたクエリプランを作成します。最適化後に、そのプランはプランキャッシュに保存される場合もあります。プランキャッシュにこのクエリのプランが既に含まれている場合、この手順は省略されます。
クエリ実行エンジンは、クエリプランの解釈を処理します。
各インデックスのシーク、インデックススキャン、およびテーブルスキャン操作では、実行エンジンはそれぞれのインデックスおよびテーブルの構造からの行を Access Methods から要求します。
Access Methods は、バッファープールのインデックスおよびデータページから行を取得し、必要に応じてバッファープールにディスクからページを読み込みます。

クエリの最初の例の場合、実行エンジンは、Customer のクラスター化インデックスおよび Order の非クラスター化インデックスの行を Access Methods から要求します。 Access Methods は、要求された行を取得するために B ツリーインデックス構造をスキャンします。この場合は、プランがフルインデックススキャンを必要とするため、すべての行が取得されます。

解釈された Transact-SQL によるメモリ最適化テーブルへのアクセス

Transact-SQL アドホックバッチおよびストアドプロシージャは、解釈された Transact-SQL とも呼ばれます。 "解釈された" とは、クエリプラン内の各演算子について、クエリ実行エンジンによってクエリプランが解釈されることを意味します。実行エンジンは、演算子とそのパラメーターを読み取り、操作を実行します。

解釈された Transact-SQL を使用して、メモリ最適化テーブルとディスクベーステーブルの両方にアクセスできます。次の図は、解釈された Transact-SQL によるメモリ最適化テーブルへのアクセスのクエリ処理を示しています。

解釈された tsql のクエリ処理パイプライン。
解釈された Transact-SQL によるメモリ最適化テーブルへのアクセスのクエリ処理パイプライン。

図で示すように、ほとんどの場合、クエリ処理パイプラインは変更されません。

パーサーと algebrizer はクエリツリーを構築します。
オプティマイザーは実行プランを作成します。
クエリ実行エンジンは、実行プランを解釈します。

従来のクエリ処理パイプライン (図 2) との主な相違点は、メモリ最適化テーブルの行が Access Methods を使用してバッファープールから取得されないことです。代わりに、インメモリデータ構造体からインメモリ OLTP エンジンを使用して行が取得されます。データ構造が異なるために、次の例で示すように、オプティマイザーが異なるプランを引数として取得する場合があります。

次の Transact-SQL スクリプトには、ハッシュインデックスを使用する Order テーブルと Customer テーブルのメモリ最適化バージョンが含まれています。

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO

同じクエリをメモリ最適化テーブルで実行するとします。

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

推定プランは次のとおりです。

メモリ最適化テーブルの結合のためのクエリプラン。
メモリ最適化テーブルの結合のためのクエリプラン。

ディスクベーステーブルの同じクエリに対するプラン (図 1) で、次の相違点を確認します。

このプランでは、Customer テーブルに対するクラスター化インデックススキャンではなくテーブルスキャンが含まれています。
- テーブルの定義には、クラスター化インデックスが含まれていません。
- クラスター化インデックスは、メモリ最適化テーブルでサポートされていません。代わりに、すべてのメモリ最適化テーブルには 1 つ以上の非クラスター化インデックスが必要です。メモリ最適化テーブルのすべてのインデックスは、そのテーブル内のすべての列に効率的にアクセスできます。列をインデックスに格納したり、クラスター化されたインデックスを参照したりする必要はありません。
このプランには、Merge JoinではなくHash Matchが含まれています。 Order テーブルと Customer テーブルの両方のインデックスはハッシュインデックスになるため、順序付けされません。 Merge Joinには、パフォーマンスを低下させる並べ替え演算子が必要です。

ネイティブコンパイルストアドプロシージャ

ネイティブコンパイルストアドプロシージャは、クエリ実行エンジンによって解釈されるのではなく、マシン語コードにコンパイルされる Transact-SQL ストアドプロシージャです。次のスクリプトは、(クエリの例のセクションの) クエリの例を実行する、ネイティブコンパイルストアドプロシージャを作成します。

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END

ネイティブコンパイルストアドプロシージャは作成時にコンパイルされ、解釈されたストアドプロシージャは最初の実行時にコンパイルされます (コンパイルの一部 (特に解析とアルゲブライズ) は、作成時に行われます。ただし、解釈されたストアドプロシージャの場合には、クエリプランの最適化は最初の実行時に行われます)。再コンパイルロジックも同様です。サーバーを再起動した場合、ネイティブコンパイルストアドプロシージャは、プロシージャの最初の実行時に再コンパイルされます。解釈されたストアドプロシージャは、そのプランがプランキャッシュに存在しなくなった場合は再コンパイルされます。次の表は、ネイティブコンパイルストアドプロシージャと解釈されたストアドプロシージャの両方について、コンパイルおよび再コンパイルのケースをまとめたものです。

	ネイティブコンパイル済み	解釈された
最初のコンパイル	作成時。	最初の実行時。
自動再コンパイル	データベースまたはサーバーの再起動後、プロシージャの最初の実行時。	サーバーの再起動時。または、通常はスキーマや統計の変更またはメモリ不足に基づく、プランキャッシュからの削除時。
手動での再コンパイル	サポートされていません。回避策は、ストアドプロシージャを削除して再作成することです。	`sp_recompile` を使用してください。たとえば DBCC FREEPROCCACHE を使用して、キャッシュからプランを手動で削除できます。また、WITH RECOMPILE ストアドプロシージャを作成することもできます。このストアドプロシージャは、実行のたびに再コンパイルされます。

コンパイルとクエリ処理

次の図は、ネイティブコンパイルストアドプロシージャのコンパイル処理を示しています。

ストアドプロシージャのネイティブでのコンパイル
ストアドプロシージャのネイティブでのコンパイル

この処理は次のとおりです。

ユーザーは SQL Server に CREATE PROCEDURE ステートメントを発行します。
パーサーと algebrizer は、プロシージャの処理フロー、およびストアドプロシージャ内の Transact-SQL クエリのクエリツリーを作成します。
オプティマイザーは、ストアドプロシージャ内のすべてのクエリに対して最適化されたクエリ実行プランを作成します。
インメモリ OLTP コンパイラは、埋め込みの最適化されたクエリプランで処理フローを受け取り、ストアドプロシージャを実行するためのマシン語コードを含む DLL を生成します。
生成された DLL がメモリに読み込まれます。

ネイティブコンパイルストアドプロシージャの呼び出しは、DLL 内の関数の呼び出しに変換されます。

ネイティブコンパイルストアドプロシージャの実行。
ネイティブコンパイルストアドプロシージャの実行。

ネイティブコンパイルストアドプロシージャの呼び出しは、次のとおりです。

ユーザーが EXECusp_myproc ステートメントを発行します。
パーサーは、名前とストアドプロシージャのパラメーターを抽出します。

ステートメントが準備されている場合 (たとえば、 sp_prep_execを使用する場合)、パーサーは実行時にプロシージャ名とパラメーターを抽出する必要はありません。
インメモリ OLTP ランタイムがストアドプロシージャに対する DLL エントリポイントを特定します。
DLL のマシン語コードが実行され、その結果がクライアントに返されます。

パラメーターを見つけ出す

解釈された Transact-SQL ストアドプロシージャは最初の実行時にコンパイルされますが、ネイティブコンパイルストアドプロシージャは作成時にコンパイルされます。解釈されたストアドプロシージャが呼び出し時にコンパイルされる場合、この呼び出しに指定されたパラメーターの値が、実行プランの生成時にオプティマイザーによって使用されます。コンパイル時にパラメーターをこのように使用することを、"パラメーターを見つけ出す" と表現します。

パラメーターを見つけ出すことは、ネイティブコンパイルストアドプロシージャのコンパイルには使用されません。ストアドプロシージャに対するすべてのパラメーターは、UNKNOWN 値があると見なされます。解釈されるストアドプロシージャと同様に、ネイティブコンパイルストアドプロシージャでも OPTIMIZE FOR ヒントがサポートされます。詳細については、「クエリヒント (Transact-SQL)」を参照してください。

ネイティブコンパイルストアドプロシージャ用のクエリ実行プランの取得

ネイティブコンパイルストアドプロシージャのクエリ実行プランは、Management Studio の推定実行プランを使用するか、Transact-SQL の SHOWPLAN_XML オプションを使用して取得できます。例えば次が挙げられます。

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO

クエリオプティマイザーによって生成される実行プランは、ノード上のクエリ演算子を含むツリーおよびツリーのリーフで構成されます。ツリーの構造により、演算子間の対話 (演算子間での行のフロー) が決定されます。 SQL Server Management Studio のグラフィカルなビューでは、フローは右から左に流れます。たとえば、図 1 のクエリプランは、2 個のインデックススキャン操作を含み、マージ結合操作に行を渡しています。マージ結合操作が選択操作に行を渡します。選択操作は、最終的にはクライアントに行を返します。

ネイティブコンパイルストアドプロシージャのクエリ演算子

次の表は、ネイティブコンパイルストアドプロシージャの内部でサポートされるクエリ演算子をまとめたものです。

オペレーター	サンプルクエリ
選択する	`SELECT OrderID FROM dbo.[Order]`
挿入する	`INSERT dbo.Customer VALUES ('abc', 'def')`
更新	`UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'`
削除	`DELETE dbo.Customer WHERE CustomerID='abc'`
スカラーを計算する	この操作は、組み込み関数と型変換の両方で使用されます。一部の関数と型変換は、ネイティブコンパイルストアドプロシージャの内部でサポートされません。 `SELECT OrderID+1 FROM dbo.[Order]`
Nested Loops 結合	Nested Loops は、ネイティブコンパイルストアドプロシージャでサポートされている唯一の結合操作です。解釈された Transact-SQL として実行される同じクエリのプランにハッシュ結合またはマージ結合が含まれている場合でも、結合を含むすべてのプランは Nested Loops 操作を使用します。 `SELECT o.OrderID, c.CustomerID` `FROM dbo.[Order] o INNER JOIN dbo.[Customer] c`
ソート	`SELECT ContactName FROM dbo.Customer` `ORDER BY ContactName`
ページのトップへ	`SELECT TOP 10 ContactName FROM dbo.Customer`
上位並べ替え	`TOP`式 (返される行数) は 8,000 行を超えることはできません。クエリに結合演算子および集計演算子がある場合、処理できる行数はこれより少なくなります。一般的に結合と集計を行うと、並べ替える行数は、ベーステーブルの行数より少なくなります。 `SELECT TOP 10 ContactName FROM dbo.Customer` `ORDER BY ContactName`
ストリーム集計	Hash Match 操作が集計をサポートしていないことに注意してください。したがって、解釈された Transact-SQL 内の同じクエリに対するプランが Hash Match 操作を使用しても、ネイティブコンパイルストアドプロシージャ内のすべての集計は Stream Aggregate 操作を使用します `SELECT count(CustomerID) FROM dbo.Customer`

列統計と結合

SQL Server は、インデックススキャンやインデックスシークなど特定の操作のコストを推定できるように、インデックスキー列に値の統計を保持します。 (SQL Server では、非インデックスキー列に対しても、明示的に作成された場合またはクエリオプティマイザーによってクエリの述語に応じて作成された場合、統計が作成されます)。コストの推定の主要な基準は、1 個の操作によって処理される行数です。ディスクベーステーブルの場合、コストの推定では、特定の操作でアクセスされるページ数が重要です。ただし、メモリ最適化テーブルではページ数は重要ではないため (常にゼロ)、ここでは行数を中心に説明します。推定は、プラン内のインデックスシークおよびスキャン操作で開始され、続いて、結合操作などの他の操作へと進みます。結合操作によって処理される行数の推定値は、基になるインデックス、シーク、およびスキャン操作の推定値に基づきます。解釈された Transact-SQL によるメモリ最適化テーブルへのアクセスの場合は、実際の実行プランを調べて、プラン内の操作の推定行数と実際の行数の違いを確認することができます。

図 1 の例の場合は、次のようになります。

Customer でのクラスター化インデックススキャンは、推定値が 91、実際の値が 91。
CustomerID での非クラスター化インデックススキャンは、推定値が 830、実際の値が 830。
マージ結合操作は、推定値が 815、実際の値が 830。

インデックススキャンの推定値は正確です。 SQL Server はディスクベーステーブルの行数を保持します。テーブル全体の推定およびインデックススキャンは常に正確です。結合の推定も非常に正確です。

これらの推定が変わると、さまざまなプランの選択肢に対するコストの注意点も変わります。たとえば、1 個の結合操作の推定値が 1 または少ない行数である場合、Nested Loops 結合を使用する方が低コストです。

クエリのプランを次に示します。

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

テーブル Customer 内の 1 行以外のすべての行を削除した後:

列統計と結合。

このクエリプランについて

Hash Match は、Nested Loops 物理結合操作で置き換えられました。
IX_CustomerID でのフルインデックススキャンは、インデックスシークで置き換えられました。これにより、スキャンの対象は 5 行となり、フルインデックススキャンに必要な 830 行ではなくなります。

Memory-Optimized テーブルの統計とカーディナリティ

SQL Server では、メモリ最適化テーブルの列レベルの統計が保持されます。さらに、テーブルの実際の行数が保持されます。ただし、ディスクベースのテーブルとは異なり、メモリ最適化テーブルの統計は自動的には更新されません。そのため、テーブルの大幅な変更後に統計を手動で更新する必要があります。詳細については、「メモリ最適化テーブルの統計」を参照してください。

こちらもご覧ください

メモリ最適化テーブル

次の方法で共有

メモリ最適化テーブルのクエリ処理のガイド

サンプル クエリ

ディスク ベース テーブルに対する SQL Server クエリ処理