クエリ処理アーキテクチャ ガイドQuery Processing Architecture Guide

適用対象: ○SQL Server XAzure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server データベース エンジンSQL Server Database Engine は、ローカル テーブル、パーティション テーブル、複数のサーバーに分散されたテーブルなどさまざまなデータ ストレージ アーキテクチャでクエリを処理します。The SQL Server データベース エンジンSQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. 以下のトピックでは、SQL ServerSQL Server でクエリを処理して、実行プランのキャッシュによりクエリの再利用を最適化する方法について説明します。The following topics cover how SQL ServerSQL Server processes queries and optimizes query reuse through execution plan caching.

実行モードExecution modes

SQL Server データベース エンジンSQL Server Database Engine は、2 つの異なる処理モードで Transact-SQLTransact-SQL ステートメントを処理できます。The SQL Server データベース エンジンSQL Server Database Engine can process Transact-SQLTransact-SQL statements using two distinct processing modes:

  • 行モード実行Row mode execution
  • バッチ モード実行Batch mode execution

行モード実行Row mode execution

行モード実行は、データが行形式で格納される、従来の RDMBS テーブルで使用されるクエリ処理方法です。Row mode execution is a query processing method used with traditional RDMBS tables, where data is stored in row format. クエリが実行され、行ストア テーブルのデータにアクセスするとき、実行ツリーの演算子と子演算子は、テーブル スキーマに指定されている列全体で、必要な行をそれぞれ読み取ります。When a query is executed and accesses data in row store tables, the execution tree operators and child operators read each required row, across all the columns specified in the table schema. SELECT ステートメント、JOIN 述語、フィルター述語で参照される結果セットに必要な列を SQL ServerSQL Server は読み取られる各行から取得します。From each row that is read, SQL ServerSQL Server then retrieves the columns that are required for the result set, as referenced by a SELECT statement, JOIN predicate, or filter predicate.

注意

行モード実行は OLTP シナリオで非常に効率的ですが、データ ウェアハウスのシナリオなど、大量のデータをスキャンするときは効率性が下がることがあります。Row mode execution is very efficient for OLTP scenarios, but can be less efficient when scanning large amounts of data, for example in Data Warehousing scenarios.

バッチ モード実行Batch mode execution

バッチ モード実行は、複数の行をまとめて処理するためのクエリ処理方法です (そのため、バッチという言葉が使われています)。Batch mode execution is a query processing method used to process multiple rows together (hence the term batch). バッチ内の各列は、別個のメモリ領域のベクトルとして格納されています。そのため、バッチ モード処理はベクトル基準となります。Each column within a batch is stored as a vector in a separate area of memory, so batch mode processing is vector-based. バッチ モード処理ではまた、マルチコア CPU 向けに最適化されており、最新ハードウェアでメモリ スループットを上げるアルゴリズムが使用されています。Batch mode processing also uses algorithms that are optimized for the multi-core CPUs and increased memory throughput that are found on modern hardware.

バッチ モード実行は、列ストア ストレージ形式と緊密に統合され、このストレージ形式に合わせて最適化されています。Batch mode execution is closely integrated with, and optimized around, the columnstore storage format. バッチ モードの処理は、可能な場合は圧縮データに対して行われるので、行モード実行で使用される交換操作が不要になります。Batch mode processing operates on compressed data when possible, and eliminates the exchange operator used by row mode execution. 結果的に、並行処理の向上と高速なパフォーマンスが得られます。The result is better parallelism and faster performance.

クエリがバッチ モードで実行され、列ストア インデックスのデータにアクセスするとき、実行ツリーの演算子と子演算子は、列セグメントにある複数の行をまとめて読み取ります。When a query is executed in batch mode, and accesses data in columnstore indexes, the execution tree operators and child operators read multiple rows together in column segments. SQL ServerSQL Server は、SELECT ステートメント、JOIN 述語、フィルター述語で参照される結果に必要な列のみ読み取ります。reads only the columns required for the result, as referenced by a SELECT statement, JOIN predicate, or filter predicate.
列ストア インデックスの詳細については、「列ストア インデックスのアーキテクチャ」を参照してください。For more information on columnstore indexes, see Columnstore Index Architecture.

注意

バッチ モード実行は、大量のデータが読み取られ、集計される、データ ウェアハウス シナリオで非常に効率的となります。Batch mode execution is very efficient Data Warehousing scenarios, where large amounts of data are read and aggregated.

SQL ステートメントの処理SQL Statement Processing

単一の Transact-SQLTransact-SQL ステートメントの処理は、SQL ServerSQL ServerTransact-SQLTransact-SQL ステートメントを実行する最も基本的な方法です。Processing a single Transact-SQLTransact-SQL statement is the most basic way that SQL ServerSQL Server executes Transact-SQLTransact-SQL statements. ローカルのベース テーブルだけを参照する (ビューやリモート テーブルは参照しない) 単一の SELECT ステートメントを処理する手順が、この基本的な処理の良い例です。The steps used to process a single SELECT statement that references only local base tables (no views or remote tables) illustrates the basic process.

論理演算子の優先順位Logical Operator Precedence

1 つのステートメントで複数の論理演算子を使用すると、最初に NOT が評価され、次に AND、最後に OR が評価されます。When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. 算術演算子、およびビット演算子は論理演算子より前に処理されます。Arithmetic, and bitwise, operators are handled before logical operators. 詳細については、「Operator Precedence (Transact-SQL)」 (演算子の順位 (Transact-SQL)) を参照してください。For more information, see Operator Precedence.

次の例では、色の条件が製品モデル 21 には該当しますが、製品モデル 20 には該当しません。これは、ANDOR よりも優先されるためです。In the following example, the color condition pertains to product model 21, and not to product model 20, because AND has precedence over OR.

SELECT ProductID, ProductModelID
FROM Production.Product
WHERE ProductModelID = 20 OR ProductModelID = 21
  AND Color = 'Red';
GO

OR が必ず最初に評価されるように、かっこを付け加えることでクエリの意味を変えることができます。You can change the meaning of the query by adding parentheses to force evaluation of the OR first. 次のクエリでは、モデル 20 とモデル 21 で赤色の製品のみが検索されます。The following query finds only products under models 20 and 21 that are red.

SELECT ProductID, ProductModelID
FROM Production.Product
WHERE (ProductModelID = 20 OR ProductModelID = 21)
  AND Color = 'Red';
GO

必要でない場合でもかっこを使用すると、クエリが読みやすくなり、演算子の優先順位が原因の微妙な間違いを犯す可能性が減少します。Using parentheses, even when they are not required, can improve the readability of queries, and reduce the chance of making a subtle mistake because of operator precedence. かっこを使用することでパフォーマンスが大幅に低下することはありません。There is no significant performance penalty in using parentheses. 次の例は、元の例と構文は同じですが、元の例よりも読みやすくなっています。The following example is more readable than the original example, although they are syntactically the same.

SELECT ProductID, ProductModelID
FROM Production.Product
WHERE ProductModelID = 20 OR (ProductModelID = 21
  AND Color = 'Red');
GO

SELECT ステートメントの最適化Optimizing SELECT statements

SELECT ステートメントは非手続き型であり、要求したデータを取得するときにデータベース サーバーで使用する手順が細かく指定されません。A SELECT statement is non-procedural; it does not state the exact steps that the database server should use to retrieve the requested data. つまり、データベース サーバーが SELECT ステートメントを分析して、要求したデータを抽出する最も効率的な方法を決定する必要があります。This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. これを、 SELECT ステートメントの最適化と呼びます。This is referred to as optimizing the SELECT statement. また、最適化を行うコンポーネントをクエリ オプティマイザーと呼びます。The component that does this is called the Query Optimizer. クエリ オプティマイザーへの入力は、クエリ、データベース スキーマ (テーブル定義やインデックスの定義)、およびデータベース統計で構成されます。The input to the Query Optimizer consists of the query, the database schema (table and index definitions), and the database statistics. クエリ オプティマイザーの出力がクエリ実行プランです。これは、クエリ プランや単にプランと呼ばれることもあります。The output of the Query Optimizer is a query execution plan, sometimes referred to as a query plan or just a plan. クエリ プランの内容については、このトピックの後半で説明します。The contents of a query plan are described in more detail later in this topic.

単一の SELECT ステートメントを最適化する場合のクエリ オプティマイザーの入出力は、次の図のようになります。The inputs and outputs of the Query Optimizer during optimization of a single SELECT statement are illustrated in the following diagram:

query_processor_io

SELECT ステートメントは次の事項だけを定義します。A SELECT statement defines only the following:

  • 結果セットの形式。The format of the result set. ほとんどの場合、選択リスト内で指定します。This is specified mostly in the select list. ただし、 ORDER BYGROUP BY などの句も結果セットの最終形式に影響します。However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
  • 取得するデータが含まれているテーブル。The tables that contain the source data. これは FROM 句で指定します。This is specified in the FROM clause.
  • SELECT ステートメントを実行するためにテーブルを論理的に関連付ける方法。How the tables are logically related for the purposes of the SELECT statement. WHERE の後に指定する ON 句や FROM句などで指定する結合により定義します。This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.
  • 基になるテーブルの行が SELECT ステートメントの対象になるために満たす必要がある条件。The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. WHERE 句と HAVING 句で指定します。These are specified in the WHERE and HAVING clauses.

クエリ実行プランは、次の事項を定義しています。A query execution plan is a definition of the following:

  • 基になるテーブルにアクセスする順序The sequence in which the source tables are accessed.
    通常、データベース サーバーからベース テーブルにアクセスして結果セットを構築する順序は何とおりもあります。Typically, there are many sequences in which the database server can access the base tables to build the result set. たとえば、 SELECT ステートメントが 3 つのテーブルを参照している場合、データベース サーバーは最初に TableAにアクセスし、 TableA のデータを使用して TableBから一致する行を抽出します。次に、 TableB のデータを使用して TableCのデータを抽出することができます。For example, if the SELECT statement references three tables, the database server could first access TableA, use the data from TableA to extract matching rows from TableB, and then use the data from TableB to extract data from TableC. データベースがテーブルにアクセスする際に可能な順序には、次の組み合わせがあります。The other sequences in which the database server could access the tables are:
    TableCTableBTableA、またはTableC, TableB, TableA, or
    TableBTableATableC、またはTableB, TableA, TableC, or
    TableBTableCTableA、またはTableB, TableC, TableA, or
    TableCTableATableBTableC, TableA, TableB

  • 各テーブルからデータを取り出す方法The methods used to extract data from each table.
    通常、各テーブルのデータにアクセスする方法にも何とおりかあります。Generally, there are different methods for accessing the data in each table. 特定のキー値を持つ数行だけが必要な場合、データベース サーバーではインデックスを使用できます。If only a few rows with specific key values are required, the database server can use an index. テーブル内のすべての行が必要な場合は、インデックスを無視してテーブル スキャンを実行できます。If all the rows in the table are required, the database server can ignore the indexes and perform a table scan. テーブル内のすべての行が必要で、 ORDER BYで指定されたキー列のインデックスがある場合、テーブル スキャンではなくインデックス スキャンを行うと、結果セットの並べ替えを個別に行わずに済みます。If all the rows in a table are required but there is an index whose key columns are in an ORDER BY, performing an index scan instead of a table scan may save a separate sort of the result set. テーブルが非常に小さい場合は、テーブルにどのようにアクセスするときでもテーブル スキャンが最も効率的な方法です。If a table is very small, table scans may be the most efficient method for almost all access to the table.

可能性のある多数のプラン候補の中から実行プランを 1 つ選択する処理を最適化と呼びます。The process of selecting one execution plan from potentially many possible plans is referred to as optimization. クエリ オプティマイザーは、SQL データベース システムで最も重要なコンポーネントの 1 つです。The Query Optimizer is one of the most important components of a SQL database system. クエリ オプティマイザーはクエリの分析やプランの選択を行うため、オーバーヘッドが発生します。ただし、クエリ オプティマイザーが効率的な実行プランを選択すれば、このオーバーヘッドの数倍の負荷を削減することができます。While some overhead is used by the Query Optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the Query Optimizer picks an efficient execution plan. たとえば、2 つの建築会社が一軒の住宅に同じ青写真を提供する場合について考えてみます。For example, two construction companies can be given identical blueprints for a house. 一方の会社は数日かけて建築プランを立て、他方の会社はプランを立てずに建築を開始するとします。ほとんどの場合、プロジェクトのプランに時間をかけた会社の方が先に完成します。If one company spends a few days at the beginning to plan how they will build the house, and the other company begins building without planning, the company that takes the time to plan their project will probably finish first.

SQL ServerSQL Server クエリ オプティマイザーは、コストベースのオプティマイザーです。The SQL ServerSQL Server Query Optimizer is a cost-based Query Optimizer. 実行プラン候補ごとに、計算に使用するリソースの量の観点から関連するコストが異なります。Each possible execution plan has an associated cost in terms of the amount of computing resources used. クエリ オプティマイザーでは、候補のプランを分析し、算出コストが最も低いプランを選択する必要があります。The Query Optimizer must analyze the possible plans and choose the one with the lowest estimated cost. 複雑な SELECT ステートメントの場合、何千もの実行プラン候補があります。Some complex SELECT statements have thousands of possible execution plans. このような場合、クエリ オプティマイザーはすべての組み合わせを分析するわけではありません。In these cases, the Query Optimizer does not analyze all possible combinations. 複雑なアルゴリズムを使用して、理論上の最低コストに最も近い実行プランを迅速に見つけ出します。Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

SQL ServerSQL Server クエリ オプティマイザーは、リソース コストが最も低い実行プランに限定して選択するわけではありません。妥当なリソース コストで、最も迅速に結果を返すプランを選択します。The SQL ServerSQL Server Query Optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. たとえば、クエリを並列に処理すれば、直列に処理するよりもリソースを多く使用します。ただし、クエリの完了時間は短縮されます。For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. SQL ServerSQL Server クエリ オプティマイザーは、サーバー側の負荷に悪影響がない限り、並列実行プランを使用して結果を返します。The SQL ServerSQL Server Query Optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.

SQL ServerSQL Server クエリ オプティマイザーは、テーブルやインデックスから情報を取り出す複数の方法のリソース コストを算出する場合、分布統計に大きく依存します。The SQL ServerSQL Server Query Optimizer relies on distribution statistics when it estimates the resource costs of different methods for extracting information from a table or index. 列とインデックスに対する分布統計が保持されます。この分布統計では、基になるデータの密度 1 に関する情報が保持されます。Distribution statistics are kept for columns and indexes, and hold information on the density1 of the underlying data. これは、特定のインデックスまたは列内の値の選択度を表すために使用されます。This is used to indicate the selectivity of the values in a particular index or column. たとえば、車を表すテーブルの場合、同メーカーの車がいくつもあります。ただし、VIN (車両番号) はそれぞれの車両固有のものです。For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). VIN 上のインデックスは、製造元でのインデックスより選択度が高くなります。これは VIN の密度が製造元の場合より低いからです。An index on the VIN is more selective than an index on the manufacturer, because VIN has lower density then manufacturer. インデックス統計が最新でない場合、クエリ オプティマイザーはテーブルの現在の状態に対して最適な選択ができないことがあります。If the index statistics are not current, the Query Optimizer may not make the best choice for the current state of the table. 密度の詳細については、「統計」を参照してください。For more information about densities, see Statistics.

1密度では、データ内に存在する一意の値の分布、または特定の列における重複値の平均数が定義されます。1 Density defines the distribution of unique values that exist in the data, or the average number of duplicate values for a given column. 密度が減少するにつれて、値の選択度が高くなります。As density decreases, selectivity of a value increases.

SQL ServerSQL Server クエリ オプティマイザーを使用すると、プログラマやデータベース管理者が入力しなくても、データベース内の状態の変化に合わせてデータベース サーバーを動的に調整できるので、クエリ オプティマイザーは不可欠です。The SQL ServerSQL Server Query Optimizer is important because it enables the database server to adjust dynamically to changing conditions in the database without requiring input from a programmer or database administrator. これにより、プログラマはクエリの最終結果の記述だけに重点を置くことができます。This enables programmers to focus on describing the final result of the query. SQL ServerSQL Server クエリ オプティマイザーは、ステートメントを実行するたびに、データベースの状態に合わせて効率的な実行プランを構築します。They can trust that the SQL ServerSQL Server Query Optimizer will build an efficient execution plan for the state of the database every time the statement is run.

SELECT ステートメントの処理Processing a SELECT Statement

SQL ServerSQL Server が単一の SELECT ステートメントを処理する基本的な手順は次のとおりです。The basic steps that SQL ServerSQL Server uses to process a single SELECT statement include the following:

  1. パーサーが SELECT ステートメントをスキャンし、キーワード、式、演算子、識別子などの論理単位に分解します。The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
  2. 基になるデータを結果セットで必要な形式に変換する論理手順を記述するクエリ ツリーが構築されます。クエリ ツリーはシーケンス ツリーとも呼ばれます。A query tree, sometimes referred to as a sequence tree, is built describing the logical steps needed to transform the source data into the format required by the result set.
  3. クエリ オプティマイザーでは、基になるテーブルにアクセスできるさまざまな方法が分析されます。The Query Optimizer analyzes different ways the source tables can be accessed. その中から、使用するリソースが最も少なく、最も短時間で結果を返す一連の手順が選択されます。It then selects the series of steps that returns the results fastest while using fewer resources. クエリ ツリーが更新され、この一連の手順が正確に記録されます。The query tree is updated to record this exact series of steps. この最終的に得られた最適化済みのクエリ ツリーを実行プランと呼びます。The final, optimized version of the query tree is called the execution plan.
  4. リレーショナル エンジンによって、実行プランの実行が開始されます。The relational engine starts executing the execution plan. リレーショナル エンジンは、ベース テーブルからのデータを必要とする手順を処理するときに、要求した行セットのデータを渡すようにストレージ エンジンに要求します。As the steps that require data from the base tables are processed, the relational engine requests that the storage engine pass up data from the rowsets requested from the relational engine.
  5. リレーショナル エンジンでは、ストレージ エンジンから返されたデータが結果セット用に定義された形式に変換され、結果セットをクライアントに返します。The relational engine processes the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

定数のたたみ込みと式の評価Constant Folding and Expression Evaluation

SQL ServerSQL Server ではクエリのパフォーマンスを向上するために、いくつかの定数式を前もって評価します。evaluates some constant expressions early to improve query performance. これを定数のたたみ込みと呼びます。This is referred to as constant folding. 定数は、3、'ABC'、'2005-12-31'、1.0e3、0x12345678 のような Transact-SQLTransact-SQL リテラルです。A constant is a Transact-SQLTransact-SQL literal, such as 3, 'ABC', '2005-12-31', 1.0e3, or 0x12345678.

たたみ込み可能な式Foldable Expressions

SQL ServerSQL Server では、次の種類の式に定数のたたみ込みを適用します。uses constant folding with the following types of expressions:

  • 定数のみから構成される数式 (1+1、5/3*2 など)。Arithmetic expressions, such as 1+1, 5/3*2, that contain only constants.
  • 定数のみから構成される論理式 (1=1、1>2 AND 3>4 など)。Logical expressions, such as 1=1 and 1>2 AND 3>4, that contain only constants.
  • SQL ServerSQL Server によってたたみ込み可能と判断された組み込み関数 (CASTCONVERT など)。Built-in functions that are considered foldable by SQL ServerSQL Server, including CAST and CONVERT. 固有の関数は、通常はたたみ込み可能です。ただし結果が関数への入力のみによって決まらず、SET オプション、言語設定、データベース オプション、暗号化キーなどの、状況によって変わりうる他の情報を交えて決まる場合は例外です。Generally, an intrinsic function is foldable if it is a function of its inputs only and not other contextual information, such as SET options, language settings, database options, and encryption keys. 非決定的関数はたたみ込み不可能です。Nondeterministic functions are not foldable. 組み込みの決定的関数はたたみ込み可能ですが、一部例外があります。Deterministic built-in functions are foldable, with some exceptions.

注意

例外の 1 つは LOB 型です。An exception is made for large object types. たたみ込み処理の出力が LOB 型 (text、image、nvarchar(max)、varchar(max)、または varbinary(max) のいずれか) である場合、SQL ServerSQL Server では式はたたみ込まれません。If the output type of the folding process is a large object type (text, image, nvarchar(max), varchar(max), or varbinary(max)), then SQL ServerSQL Server does not fold the expression.

たたみ込み不可能な式Nonfoldable Expressions

その他の種類の式は、すべてたたみ込み不可能です。All other expression types are not foldable. 具体的には、次に示す種類の式です。In particular, the following types of expressions are not foldable:

  • 定数ではない式 (結果が列の値によって変わる式など)。Nonconstant expressions such as an expression whose result depends on the value of a column.
  • 結果がローカル変数またはパラメーター (@x など) によって変わる式。Expressions whose results depend on a local variable or parameter, such as @x.
  • 非決定的関数。Nondeterministic functions.
  • ユーザー定義関数 (Transact-SQLTransact-SQL および CLR)User-defined functions (both Transact-SQLTransact-SQL and CLR).
  • 結果が言語設定によって変わる式。Expressions whose results depend on language settings.
  • 結果が SET オプションによって変わる式。Expressions whose results depend on SET options.
  • 結果がサーバー構成オプションによって変わる式。Expressions whose results depend on server configuration options.

たたみ込み可能/不可能な定数式の例Examples of Foldable and Nonfoldable Constant Expressions

次のクエリを考えてみます。Consider the following query:

SELECT *
FROM Sales.SalesOrderHeader AS s 
INNER JOIN Sales.SalesOrderDetail AS d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00;

このクエリの PARAMETERIZATION データベース オプションが FORCED に設定されていない場合、クエリをコンパイルする前に式 117.00 + 1000.00 が評価され、その結果である 1117.00 に置き換えられます。If the PARAMETERIZATION database option is not set to FORCED for this query, then the expression 117.00 + 1000.00 is evaluated and replaced by its result, 1117.00, before the query is compiled. 定数のたたみ込みには、次に示す利点があります。Benefits of this constant folding include the following:

  • 実行時に式を繰り返し評価する必要がありません。The expression does not have to be evaluated repeatedly at run time.
  • クエリ TotalDue > 117.00 + 1000.00 の部分の結果セットのサイズをクエリ オプティマイザーで推定するときは、評価後の式の値が使用されます。The value of the expression after it is evaluated is used by the Query Optimizer to estimate the size of the result set of the portion of the query TotalDue > 117.00 + 1000.00.

一方、ユーザー定義関数を含んだ式は決定的関数であってもたたみ込まれないので、dbo.f をスカラー値のユーザー定義関数とした場合、SQL ServerSQL Server では、式 dbo.f(100) はたたみ込まれません。On the other hand, if dbo.f is a scalar user-defined function, the expression dbo.f(100) is not folded, because SQL ServerSQL Server does not fold expressions that involve user-defined functions, even if they are deterministic. パラメーター化の詳細については、この記事で後述する「強制パラメーター化」を参照してください。For more information on parameterization, see Forced Parameterization later in this article.

式の評価Expression Evaluation

また、定数のたたみ込みは行われませんが、引数がコンパイル時に確定する式は、引数がパラメーターと定数のどちらでも、最適化のときにクエリ オプティマイザーのカーディナリティ (結果セットのサイズ) 推定機能によって評価されます。In addition, some expressions that are not constant folded but whose arguments are known at compile time, whether the arguments are parameters or constants, are evaluated by the result-set size (cardinality) estimator that is part of the optimizer during optimization.

具体的には、次の組み込み関数と特殊演算子は、入力がすべて確定する場合コンパイル時に評価されます: UPPERLOWERRTRIMDATEPART( YY only )GETDATECASTCONVERTSpecifically, the following built-in functions and special operators are evaluated at compile time if all their inputs are known: UPPER, LOWER, RTRIM, DATEPART( YY only ), GETDATE, CAST, and CONVERT. 次に示す演算子も、入力がすべて確定する場合コンパイル時に評価されます。The following operators are also evaluated at compile time if all their inputs are known:

  • 算術演算子 : +、-、*、/、単項演算子の -Arithmetic operators: +, -, *, /, unary -
  • 論理演算子: ANDORNOTLogical Operators: AND, OR, NOT
  • 比較演算子: <、>、<=、>=、<>、LIKEIS NULLIS NOT NULLComparison operators: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

上記以外の関数および演算子は、カーディナリティを推定するときにはクエリ オプティマイザーによって評価されません。No other functions or operators are evaluated by the Query Optimizer during cardinality estimation.

コンパイル時の式の評価の例Examples of Compile-Time Expression Evaluation

次のストアド プロシージャについて考えてみましょう。Consider this stored procedure:

USE AdventureWorks2014;
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1;

このストアド プロシージャの SELECT ステートメントを最適化するとき、条件 OrderDate > @d+1 に対する結果セットの予測カーディナリティがクエリ オプティマイザーによって評価されます。During optimization of the SELECT statement in the procedure, the Query Optimizer tries to evaluate the expected cardinality of the result set for the condition OrderDate > @d+1. @d+1@d がパラメーターであるために定数がたたみ込まれません。The expression @d+1 is not constant-folded, because @d is a parameter. しかし、最適化のときにはパラメーターの値が確定しています。However, at optimization time, the value of the parameter is known. したがって結果セットのサイズがクエリ オプティマイザーによって正確に推定できるので、適切なクエリ プランが選択されます。This allows the Query Optimizer to accurately estimate the size of the result set, which helps it select a good query plan.

次は、上記のクエリの @d2 をローカル変数 @d+1 に置き換え、クエリではなく SET ステートメントで式を評価するストアド プロシージャの例を考えてみます。Now consider an example similar to the previous one, except that a local variable @d2 replaces @d+1 in the query and the expression is evaluated in a SET statement instead of in the query.

USE AdventureWorks2014;
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END;

SQL ServerSQL ServerMyProc2SELECT ステートメントを最適化するとき、@d2 の値は確定していません。When the SELECT statement in MyProc2 is optimized in SQL ServerSQL Server, the value of @d2 is not known. そのため、クエリ オプティマイザーでは OrderDate > @d2 の選択度に対して既定の推定が使用されます (この場合は 30%)。Therefore, the Query Optimizer uses a default estimate for the selectivity of OrderDate > @d2, (in this case 30 percent).

その他のステートメントの処理Processing Other Statements

SELECT ステートメントの処理で説明した基本的な手順は、Transact-SQLTransact-SQLINSERTUPDATE などの DELETE ステートメントにも適用されます。The basic steps described for processing a SELECT statement apply to other Transact-SQLTransact-SQL statements such as INSERT, UPDATE, and DELETE. UPDATE ステートメントと DELETE ステートメントは、いずれも変更または削除する行セットを対象とする必要があります。UPDATE and DELETE statements both have to target the set of rows to be modified or deleted. これらの行を特定する処理は、 SELECT ステートメントの結果セットを得るために使用された、基になる行を特定する処理と同じです。The process of identifying these rows is the same process used to identify the source rows that contribute to the result set of a SELECT statement. UPDATE ステートメントと INSERT ステートメントのどちらにも、更新または挿入するデータ値を指定する SELECT ステートメントを埋め込むことができます。The UPDATE and INSERT statements may both contain embedded SELECT statements that provide the data values to be updated or inserted.

CREATE PROCEDURE または ALTER TABLE などの DDL (データ定義言語) ステートメントも、最終的には、システム カタログ テーブル上の一連のリレーショナル操作になります。また、場合によっては ALTER TABLE ADD COLUMN など、データ テーブルに対する一連のリレーショナル操作になります。Even Data Definition Language (DDL) statements, such as CREATE PROCEDURE or ALTER TABLE, are ultimately resolved to a series of relational operations on the system catalog tables and sometimes (such as ALTER TABLE ADD COLUMN) against the data tables.

作業テーブルWorktables

リレーショナル エンジンでは、Transact-SQLTransact-SQL ステートメントで指定された論理操作を行う際に、作業テーブルの作成を必要とする場合があります。The relational engine may need to build a worktable to perform a logical operation specified in an Transact-SQLTransact-SQL statement. 作業テーブルとは、中間結果を格納するための内部テーブルです。Worktables are internal tables that are used to hold intermediate results. 作業テーブルは、特定の GROUP BYクエリ、 ORDER BYクエリ、 UNION クエリで生成されます。Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. たとえば、インデックスの対象になっていない列を ORDER BY 句が参照している場合、要求された順番に結果セットを並べ替えるための作業テーブルが生成されることがあります。For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. また、クエリ プランの一部を実行した結果を一時的に格納するためのスプールとして作業テーブルが使用されることもあります。Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. 作業テーブルは tempdb に作成され、不要になると自動的に削除されます。Worktables are built in tempdb and are dropped automatically when they are no longer needed.

ビューの解決View Resolution

SQL ServerSQL Server クエリ プロセッサでは、インデックス付きビューとインデックスなしのビューの処理方法が異なります。The SQL ServerSQL Server query processor treats indexed and nonindexed views differently:

  • インデックス付きビューの行は、テーブルと同じ形式でデータベースに格納されます。The rows of an indexed view are stored in the database in the same format as a table. クエリ オプティマイザーでクエリ プランにインデックス付きビューを使用することが決定されると、インデックス付きビューはベース テーブルと同じ方法で処理されます。If the Query Optimizer decides to use an indexed view in a query plan, the indexed view is treated the same way as a base table.
  • インデックスなしのビューでは、ビューの行ではなく、定義のみが格納されます。Only the definition of a nonindexed view is stored, not the rows of the view. クエリ オプティマイザーにより、ロジックがビューの定義から、インデックスなしのビューを参照する Transact-SQLTransact-SQL ステートメントに対して作成された実行プランに組み込まれます。The Query Optimizer incorporates the logic from the view definition into the execution plan it builds for the Transact-SQLTransact-SQL statement that references the nonindexed view.

SQL ServerSQL Server のクエリ オプティマイザーでは、いつインデックス付きビューを使用するかを決定するときに、テーブルのインデックスをいつ使用するかを決定するのとよく似たロジックが使用されます。The logic used by the SQL ServerSQL Server Query Optimizer to decide when to use an indexed view is similar to the logic used to decide when to use an index on a table. インデックス付きビューに含まれるデータが Transact-SQLTransact-SQL ステートメントの一部またはすべてを対象としており、クエリ オプティマイザーによってビュー上のインデックスが低コストのアクセス パスであると判断された場合、クエリ内でビューが名前で参照されるかどうかにかかわらず、クエリ オプティマイザーではインデックスが選択されます。If the data in the indexed view covers all or part of the Transact-SQLTransact-SQL statement, and the Query Optimizer determines that an index on the view is the low-cost access path, the Query Optimizer will choose the index regardless of whether the view is referenced by name in the query.

Transact-SQLTransact-SQL ステートメントからインデックスなしのビューが参照されるときは、パーサーとクエリ オプティマイザーで Transact-SQLTransact-SQL ステートメントとビューのソースが両方分析され、それらが 1 つの実行プランに解決されます。When an Transact-SQLTransact-SQL statement references a nonindexed view, the parser and Query Optimizer analyze the source of both the Transact-SQLTransact-SQL statement and the view and then resolve them into a single execution plan. Transact-SQLTransact-SQL ステートメントの実行プランとビューの実行プランに別々に解決されるわけではありません。There is not one plan for the Transact-SQLTransact-SQL statement and a separate plan for the view.

たとえば、次のビューがあるとします。For example, consider the following view:

USE AdventureWorks2014;
GO
CREATE VIEW EmployeeName AS
SELECT h.BusinessEntityID, p.LastName, p.FirstName
FROM HumanResources.Employee AS h 
JOIN Person.Person AS p
ON h.BusinessEntityID = p.BusinessEntityID;
GO

次の 2 つの Transact-SQLTransact-SQL ステートメントはどちらも、このビューに基づいてベース テーブルに同じ操作を行い、同じ結果を生成します。Based on this view, both of these Transact-SQLTransact-SQL statements perform the same operations on the base tables and produce the same results:

/* SELECT referencing the EmployeeName view. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2014.Sales.SalesOrderHeader AS soh
JOIN AdventureWorks2014.dbo.EmployeeName AS EmpN
ON (soh.SalesPersonID = EmpN.BusinessEntityID)
WHERE OrderDate > '20020531';

/* SELECT referencing the Person and Employee tables directly. */
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2014.HumanResources.Employee AS e 
JOIN AdventureWorks2014.Sales.SalesOrderHeader AS soh
ON soh.SalesPersonID = e.BusinessEntityID
JOIN AdventureWorks2014.Person.Person AS p
ON e.BusinessEntityID =p.BusinessEntityID
WHERE OrderDate > '20020531';

SQL ServerSQL Server Management Studio のプラン表示機能では、リレーショナル エンジンがこの 2 つの SELECT ステートメントのどちらに対しても同じ実行プランを構築することが示されます。The SQL ServerSQL Server Management Studio Showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements.

ビューでのヒントの使用Using Hints with Views

クエリのビューに設定されるヒントは、ベース テーブルにアクセスするためにビューを展開するときに検出される他のヒントと競合することがあります。Hints that are placed on views in a query may conflict with other hints that are discovered when the view is expanded to access its base tables. この競合が発生すると、クエリはエラーを返します。When this occurs, the query returns an error. たとえば、定義にテーブル ヒントが含まれている、次のビューについて考えてみます。For example, consider the following view that contains a table hint in its definition:

USE AdventureWorks2014;
GO
CREATE VIEW Person.AddrState WITH SCHEMABINDING AS
SELECT a.AddressID, a.AddressLine1, 
    s.StateProvinceCode, s.CountryRegionCode
FROM Person.Address a WITH (NOLOCK), Person.StateProvince s
WHERE a.StateProvinceID = s.StateProvinceID;

ここで次のクエリを入力したとします。Now suppose you enter this query:

SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode
FROM Person.AddrState WITH (SERIALIZABLE)
WHERE StateProvinceCode = 'WA';

このクエリは失敗します。その理由として、ビューが展開されるときに、クエリ内の SERIALIZABLE ビューに適用される Person.AddrState ヒントが、ビューの Person.Address テーブルと Person.StateProvince テーブルの両方に反映されます。The query fails, because the hint SERIALIZABLE that is applied on view Person.AddrState in the query is propagated to both tables Person.Address and Person.StateProvince in the view when it is expanded. しかし、ビューを展開することで、 NOLOCKPerson.Addressヒントも公開されます。However, expanding the view also reveals the NOLOCK hint on Person.Address. その結果、 SERIALIZABLE ヒントと NOLOCK ヒントが競合するので、クエリ結果は正しくありません。Because the SERIALIZABLE and NOLOCK hints conflict, the resulting query is incorrect.

PAGLOCKNOLOCKROWLOCKTABLOCKTABLOCKX の各テーブル ヒントが競合するのと同様に、 HOLDLOCKNOLOCKREADCOMMITTEDREPEATABLEREADSERIALIZABLE の各テーブル ヒントも互いに競合します。The PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX table hints conflict with each other, as do the HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE table hints.

ヒントは、入れ子になったビュー全体に反映されます。Hints can propagate through levels of nested views. たとえば、クエリでビュー HOLDLOCKv1ヒントが適用されるとします。For example, suppose a query applies the HOLDLOCK hint on a view v1. v1 を展開すると、ビュー v2 がその定義の一部であることがわかります。When v1 is expanded, we find that view v2 is part of its definition. v2の定義では、ベース テーブルの 1 つに NOLOCK ヒントが含まれています。v2's definition includes a NOLOCK hint on one of its base tables. しかし、このテーブルはビュー HOLDLOCK のクエリから v1ヒントも継承します。But this table also inherits the HOLDLOCK hint from the query on view v1. その結果、 NOLOCK ヒントと HOLDLOCK ヒントが競合するので、クエリが失敗します。Because the NOLOCK and HOLDLOCK hints conflict, the query fails.

ビューが含まれるクエリで FORCE ORDER ヒントを使用すると、ビュー内のテーブルの結合順序は、順序付けられた構造内のビューの位置によって決まります。When the FORCE ORDER hint is used in a query that contains a view, the join order of the tables within the view is determined by the position of the view in the ordered construct. たとえば、次のクエリは 3 つのテーブルとビューから選択を行います。For example, the following query selects from three tables and a view:

SELECT * FROM Table1, Table2, View1, Table3
WHERE Table1.Col1 = Table2.Col1 
    AND Table2.Col1 = View1.Col1
    AND View1.Col2 = Table3.Col2;
OPTION (FORCE ORDER);

さらに、次に示すように View1 が定義されています。And View1 is defined as shown in the following:

CREATE VIEW View1 AS
SELECT Colx, Coly FROM TableA, TableB
WHERE TableA.ColZ = TableB.Colz;

クエリ プランでの結合順序は、 Table1Table2TableATableBTable3の順になります。The join order in the query plan is Table1, Table2, TableA, TableB, Table3.

ビューのインデックスの解決Resolving Indexes on Views

インデックス付きビューは他のインデックスと同様、使用することにメリットがあるとクエリ オプティマイザーで判断された場合にのみ、SQL ServerSQL Server でクエリ プランに使用されます。As with any index, SQL ServerSQL Server chooses to use an indexed view in its query plan only if the Query Optimizer determines it is beneficial to do so.

インデックス付きビューは SQL ServerSQL Server のどのエディションでも作成できます。Indexed views can be created in any edition of SQL ServerSQL Server. SQL ServerSQL Server のバージョンとエディションによっては、クエリ オプティマイザーにより自動的にインデックス付きビューが考慮されます。In some editions of some versions of SQL ServerSQL Server, the Query Optimizer automatically considers the indexed view. その他のバージョンやエディションの SQL ServerSQL Server では、インデックス付きビューを使用するには NOEXPAND テーブル ヒントを使用する必要があります。In some editions of some versions of SQL ServerSQL Server, to use an indexed view, the NOEXPAND table hint must be used. 詳細については、各バージョンのドキュメントを参照してください。For clarification, see the documentation for each version.

SQL ServerSQL Server クエリ オプティマイザーは、次の条件が満たされている場合にインデックス付きビューを使用します。The SQL ServerSQL Server Query Optimizer uses an indexed view when the following conditions are met:

  • 次のセッション オプションが ONである。These session options are set to ON:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
    • NUMERIC_ROUNDABORT セッション オプションは OFF に設定します。The NUMERIC_ROUNDABORT session option is set to OFF.
  • クエリ オプティマイザーにより、ビューのインデックス列とクエリ要素との間で、次のような項目の一致が検出される。The Query Optimizer finds a match between the view index columns and elements in the query, such as the following:
    • WHERE 句の検索条件の述語Search condition predicates in the WHERE clause
    • 結合操作Join operations
    • 集計関数Aggregate functions
    • GROUP BYGROUP BY clauses
    • テーブル参照Table references
  • クエリ オプティマイザーが検討したアクセス方法の中で、インデックスを使用した場合の推定コストが最小である。The estimated cost for using the index has the lowest cost of any access mechanisms considered by the Query Optimizer.
  • クエリ内で (直接、またはビューを展開して基になるテーブルにアクセスすることにより) 参照しているテーブルのうち、インデックス付きビュー内のテーブル参照に対応しているすべてのテーブルについて、クエリ内で適用されているヒントの組み合わせが同じである。Every table referenced in the query (either directly, or by expanding a view to access its underlying tables) that corresponds to a table reference in the indexed view must have the same set of hints applied on it in the query.

注意

現在のトランザクション分離レベルにかかわらず、このコンテキストでは READCOMMITTED ヒントと READCOMMITTEDLOCK ヒントは常に異なるヒントと見なされます。The READCOMMITTED and READCOMMITTEDLOCK hints are always considered different hints in this context, regardless of the current transaction isolation level.

SET オプションおよびテーブル ヒントの要件を除くと、上記の条件は、テーブル インデックスがクエリをカバーするかどうかを判断するためにクエリ オプティマイザーにより使用されるルールと同じです。Other than the requirements for the SET options and table hints, these are the same rules that the Query Optimizer uses to determine whether a table index covers a query. クエリで他に何も指定しなくてもインデックス付きビューを使用できます。Nothing else has to be specified in the query for an indexed view to be used.

クエリの FROM 句でインデックス付きビューを明示的に参照しなくても、インデックス付きビューが使用されます。A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. ベース テーブル内の列に対する参照がクエリに含まれており、その参照がインデックス付きビューにも存在する場合、クエリ オプティマイザーは、インデックス付きビューの使用によりアクセス コストを最小にできると推定できれば、インデックス付きビューを選択します。これはクエリ オプティマイザーが、ベース テーブルのインデックスを、クエリ内で直接参照されていない場合に選択するのと同じ手法です。If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access mechanism, the Query Optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query. クエリ内で指定されている 1 つ以上の列をカバーするオプションとして最もコストが低ければ、クエリで参照されていない列がビューに含まれていても、クエリ オプティマイザーがそのビューを選択することがあります。The Query Optimizer may choose the view when it contains columns that are not referenced by the query, as long as the view offers the lowest cost option for covering one or more of the columns specified in the query.

クエリ オプティマイザーは FROM 句で参照されているインデックス付きビューを標準のビューとして扱います。The Query Optimizer treats an indexed view referenced in the FROM clause as a standard view. 最適化処理の開始時には、ビューの定義をクエリにまで拡張します。The Query Optimizer expands the definition of the view into the query at the start of the optimization process. そのうえで、インデックス付きビューの照合を実行します。Then, indexed view matching is performed. クエリ オプティマイザーが選択した最終的な実行プランでは、インデックス付きビューが使用されることも、ビューが参照するベース テーブルにアクセスすることにより、ビュー内の必要なデータが具体化されることもあります。The indexed view may be used in the final execution plan selected by the Query Optimizer, or instead, the plan may materialize necessary data from the view by accessing the base tables referenced by the view. いずれにしても、最もコストが低いプランが選択されます。The Query Optimizer chooses the lowest-cost alternative.

インデックス付きビューでのヒントの使用Using Hints with Indexed Views

EXPAND VIEWS クエリ ヒントを指定すると、ビューのインデックスがクエリで使用されるのを禁止できます。 NOEXPAND テーブル ヒントを指定すると、クエリの FROM 句で指定したインデックス付きビューのインデックスを強制的に使用することができます。You can prevent view indexes from being used for a query by using the EXPAND VIEWS query hint, or you can use the NOEXPAND table hint to force the use of an index for an indexed view specified in the FROM clause of a query. ただし、各クエリを使用するための最適なアクセス方法はクエリ オプティマイザーによる動的な判断に任せることをお勧めします。However, you should let the Query Optimizer dynamically determine the best access methods to use for each query. EXPAND および NOEXPAND を使用するのは、テストで結果パフォーマンスの大幅な向上が示された場合のみにしてください。Limit your use of EXPAND and NOEXPAND to specific cases where testing has shown that they improve performance significantly.

EXPAND VIEWS オプションは、クエリ オプティマイザーに対し、クエリ全体に関してビュー インデックスを使用しないことを指定します。The EXPAND VIEWS option specifies that the Query Optimizer not use any view indexes for the whole query.

NOEXPAND をビューに対して指定した場合、ビューに定義されたインデックスを使用することがクエリ オプティマイザーにより検討されます。When NOEXPAND is specified for a view, the Query Optimizer considers using any indexes defined on the view. NOEXPAND をオプションの INDEX() 句で指定すると、クエリ オプティマイザーの判断で指定されたインデックスが強制的に使用されます。NOEXPAND specified with the optional INDEX() clause forces the Query Optimizer to use the specified indexes. NOEXPAND を指定できるのはインデックス付きビューに対してのみであり、インデックスのないビューには指定できません。NOEXPAND can be specified only for an indexed view and cannot be specified for a view not indexed.

ビューを含んだクエリで NOEXPANDEXPAND VIEWS のいずれも指定しない場合、基になるテーブルにアクセスするためにビューが拡張されます。When neither NOEXPAND nor EXPAND VIEWS is specified in a query that contains a view, the view is expanded to access underlying tables. ビューを構成するクエリにテーブル ヒントが含まれている場合、基になるテーブルにヒントが反映されますIf the query that makes up the view contains any table hints, these hints are propagated to the underlying tables. (この処理の詳細については、「ビューの解決」を参照してください)。ビューの基になるテーブルに存在するヒントのセットがテーブル間で同一であれば、クエリはインデックス付きビューと一致する可能性があります。(This process is explained in more detail in View Resolution.) As long as the set of hints that exists on the underlying tables of the view are identical to each other, the query is eligible to be matched with an indexed view. ほとんどの場合、ヒントはビューから直接継承されるので双方のヒントは一致します。Most of the time, these hints will match each other, because they are being inherited directly from the view. ただし、クエリの参照先がビューではなくテーブルであり、参照先テーブルに直接適用されているヒントがテーブルによって異なる場合、そのようなクエリはインデックス付きビューと一致しません。However, if the query references tables instead of views, and the hints applied directly on these tables are not identical, then such a query is not eligible for matching with an indexed view. ビューの展開後、 INDEXPAGLOCKROWLOCKTABLOCKXUPDLOCKXLOCK のいずれかのヒントがクエリの参照先テーブルに適用される場合、クエリはインデックス付きビューと一致しません。If the INDEX, PAGLOCK, ROWLOCK, TABLOCKX, UPDLOCK, or XLOCK hints apply to the tables referenced in the query after view expansion, the query is not eligible for indexed view matching.

INDEX (index_val[ ,...n] ) という形式のテーブル ヒントでクエリ内のビューを参照しているときに、 NOEXPAND ヒントを指定しない場合、インデックス ヒントは無視されます。If a table hint in the form of INDEX (index_val[ ,...n] ) references a view in a query and you do not also specify the NOEXPAND hint, the index hint is ignored. 特定のインデックスを使用するように指定するには NOEXPAND を使用します。To specify use of a particular index, use NOEXPAND.

通常、インデックス付きビューがクエリに一致すると、クエリ内のテーブルまたはビューに指定したヒントが直接インデックス付きビューに適用されます。Generally, when the Query Optimizer matches an indexed view to a query, any hints specified on the tables or views in the query are applied directly to the indexed view. クエリ オプティマイザーがインデックス付きビューを使用しないと判断した場合、ヒントはすべて、ビュー内で参照しているテーブルに直接伝達されます。If the Query Optimizer chooses not to use an indexed view, any hints are propagated directly to the tables referenced in the view. 詳細については、「ビューの解決」を参照してください。For more information, see View Resolution. この伝達は、結合ヒントには当てはまりません。This propagation does not apply to join hints. 結合ヒントはクエリ内の元の場所でのみ適用されます。They are applied only in their original position in the query. クエリ オプティマイザーがクエリとインデックス付きビューを照合する際に、結合ヒントは無視されます。Join hints are not considered by the Query Optimizer when matching queries to indexed views. 結合ヒントを含むクエリの一部に一致したインデックス付きビューがクエリ プランで使用される場合、その結合ヒントはプランに使用されません。If a query plan uses an indexed view that matches part of a query that contains a join hint, the join hint is not used in the plan.

インデックス付きビューの定義ではヒントが許可されていません。Hints are not allowed in the definitions of indexed views. 互換性モードが 80 以上の場合、SQL ServerSQL Server はインデックス付きビューの定義の保守時にも、インデックス付きビューを使用したクエリの実行時にも、定義に含まれているヒントを無視します。In compatibility mode 80 and higher, SQL ServerSQL Server ignores hints inside indexed view definitions when maintaining them, or when executing queries that use indexed views. 互換性モード 80 では、インデックス付きビューの定義でヒントを使用しても構文エラーにはなりませんが、ヒントは無視されます。Although using hints in indexed view definitions will not produce a syntax error in 80 compatibility mode, they are ignored.

分散パーティション ビューの解決Resolving Distributed Partitioned Views

SQL ServerSQL Server のクエリ プロセッサでは、分散パーティション ビューのパフォーマンスが最適化されます。The SQL ServerSQL Server query processor optimizes the performance of distributed partitioned views. 分散パーティション ビューのパフォーマンスで最も重要な点は、メンバー サーバー間で転送されるデータの量を最小限に抑えることです。The most important aspect of distributed partitioned view performance is minimizing the amount of data transferred between member servers.

SQL ServerSQL Server では、リモート メンバー テーブルからのデータにアクセスするときに分散クエリを効率的に使用できる、高機能で動的なプランが構築されます。builds intelligent, dynamic plans that make efficient use of distributed queries to access data from remote member tables:

  • クエリ プロセッサでは、最初に OLE DB を使用して、各メンバー テーブルから CHECK 制約の定義が取得されます。The Query Processor first uses OLE DB to retrieve the check constraint definitions from each member table. これにより、クエリ プロセッサは、メンバー テーブル間でキー値の分布をマップできるようになります。This allows the query processor to map the distribution of key values across the member tables.
  • クエリ プロセッサでは、Transact-SQLTransact-SQL ステートメントの WHERE 句で指定されたキーの範囲が、メンバー テーブルでの行の分布状況を示すマップと比較されます。The Query Processor compares the key ranges specified in an Transact-SQLTransact-SQL statement WHERE clause to the map that shows how the rows are distributed in the member tables. 次に、クエリ プロセッサでは、分散クエリを使用して Transact-SQLTransact-SQL ステートメントの完了に必要なリモート行だけを取得するクエリ実行プランが構築されます。The query processor then builds a query execution plan that uses distributed queries to retrieve only those remote rows that are required to complete the Transact-SQLTransact-SQL statement. この実行プランでは、リモート メンバー テーブルのデータやメタデータへのアクセスが、情報が要求されるまで遅延されます。The execution plan is also built in such a way that any access to remote member tables, for either data or metadata, are delayed until the information is required.

たとえば、顧客テーブルが Server1 (CustomerID 1 ~ 3299999)、Server2 (CustomerID 3300000 ~ 6599999)、および Server3 (CustomerID 6600000 ~ 9999999) にパーティション分割されたシステムがあるとします。For example, consider a system where a customers table is partitioned across Server1 (CustomerID from 1 through 3299999), Server2 (CustomerID from 3300000 through 6599999), and Server3 (CustomerID from 6600000 through 9999999).

Server1 で実行される次のクエリ用に構築される実行プランを考えてみます。Consider the execution plan built for this query executed on Server1:

SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID BETWEEN 3200000 AND 3400000;

このクエリの実行プランでは、ローカル メンバー テーブルから CustomerID のキー値が 3200000 ~ 3299999 の行が抽出され、分散クエリを実行して Server2 からキー値が 3300000 ~ 3400000 の行が取得されます。The execution plan for this query extracts the rows with CustomerID key values from 3200000 through 3299999 from the local member table, and issues a distributed query to retrieve the rows with key values from 3300000 through 3400000 from Server2.

SQL ServerSQL Server のクエリ プロセッサでは、プランを構築する必要があるときにキー値がわからない Transact-SQLTransact-SQL ステートメント用に、クエリの実行プランに動的なロジックを組み込むこともできます。The SQL ServerSQL Server Query Processor can also build dynamic logic into query execution plans for Transact-SQLTransact-SQL statements in which the key values are not known when the plan must be built. たとえば、次のようなストアド プロシージャがあるとします。For example, consider this stored procedure:

CREATE PROCEDURE GetCustomer @CustomerIDParameter INT
AS
SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID = @CustomerIDParameter;

SQL ServerSQL Server では、プロシージャが実行されるたびに @CustomerIDParameter パラメーターによって指定されるキー値を予測できません。cannot predict what key value will be supplied by the @CustomerIDParameter parameter every time the procedure is executed. クエリ プロセッサはキー値を予測できないので、アクセスする必要のあるメンバー テーブルを予測することもできません。Because the key value cannot be predicted, the query processor also cannot predict which member table will have to be accessed. この状況に対処するために、SQL ServerSQL Server では、動的フィルターと呼ばれる条件ロジックを含む実行プランが構築され、アクセスされるメンバー テーブルが入力パラメーター値に基づいて制御されます。To handle this case, SQL ServerSQL Server builds an execution plan that has conditional logic, referred to as dynamic filters, to control which member table is accessed, based on the input parameter value. GetCustomer ストアド プロシージャが Server1 で実行されたと仮定すると、実行プランのロジックは次のように表すことができます。Assuming the GetCustomer stored procedure was executed on Server1, the execution plan logic can be represented as shown in the following:

IF @CustomerIDParameter BETWEEN 1 and 3299999
   Retrieve row from local table CustomerData.dbo.Customer_33
ELSE IF @CustomerIDParameter BETWEEN 3300000 and 6599999
   Retrieve row from linked table Server2.CustomerData.dbo.Customer_66
ELSE IF @CustomerIDParameter BETWEEN 6600000 and 9999999
   Retrieve row from linked table Server3.CustomerData.dbo.Customer_99

SQL ServerSQL Server では、パラメーター化されていないクエリに対してもこのような動的実行プランが構築されます。sometimes builds these types of dynamic execution plans even for queries that are not parameterized. クエリ オプティマイザーでは、クエリがパラメーター化され、実行プランを再利用できるようにします。The Query Optimizer may parameterize a query so that the execution plan can be reused. クエリ オプティマイザーがパーティション ビューを参照しているクエリをパラメーター化すると、必要な行が指定されたベース テーブルにあると仮定することができなくなります。If the Query Optimizer parameterizes a query referencing a partitioned view, the Query Optimizer can no longer assume the required rows will come from a specified base table. そのため、実行プランで動的フィルターを使用する必要があります。It will then have to use dynamic filters in the execution plan.

ストアド プロシージャとトリガーの実行Stored Procedure and Trigger Execution

SQL ServerSQL Server では、ストアド プロシージャとトリガーのソースだけが格納されます。stores only the source for stored procedures and triggers. ストアド プロシージャまたはトリガーを最初に実行するときに、ソースが実行プランにコンパイルされます。When a stored procedure or trigger is first executed, the source is compiled into an execution plan. 時間が経過して実行プランがメモリから削除される前にストアド プロシージャまたはトリガーを再度実行すると、リレーショナル エンジンは既存の実行プランを検出してそれを再利用します。If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. 時間が経過して実行プランがメモリから削除されると、新しい実行プランが構築されます。If the plan has aged out of memory, a new plan is built. これは、SQL ServerSQL Server がすべての Transact-SQLTransact-SQL ステートメントに行うのと同じ処理です。This process is similar to the process SQL ServerSQL Server follows for all Transact-SQLTransact-SQL statements. SQL ServerSQL Server で、動的 Transact-SQLTransact-SQL のバッチと比較した場合の、ストアド プロシージャやトリガーのパフォーマンス上の主な利点は、Transact-SQLTransact-SQL ステートメントが常に同じであることです。The main performance advantage that stored procedures and triggers have in SQL ServerSQL Server compared with batches of dynamic Transact-SQLTransact-SQL is that their Transact-SQLTransact-SQL statements are always the same. したがって、リレーショナル エンジンを既存の実行プランに容易に適合させることができます。Therefore, the relational engine easily matches them with any existing execution plans. その結果、ストアド プロシージャやトリガーのプランを簡単に再利用できます。Stored procedure and trigger plans are easily reused.

ストアド プロシージャとトリガーの実行プランは、ストアド プロシージャを呼び出すバッチやトリガーを起動するバッチの実行プランとは別に実行されます。The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. このため、ストアド プロシージャやトリガーの実行プランを何回でも再利用できます。This allows for greater reuse of the stored procedure and trigger execution plans.

実行プランのキャッシュと再利用Execution Plan Caching and Reuse

SQL ServerSQL Server には、実行プランとデータ バッファーの両方を格納するためのメモリのプールが用意されています。has a pool of memory that is used to store both execution plans and data buffers. 実行プランまたはデータ バッファーに割り当てられるプールの割合は、システムの状態によって動的に変動します。The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. 実行プランの格納に使用されるメモリ プールの部分をプラン キャッシュといいます。The part of the memory pool that is used to store execution plans is referred to as the plan cache.

SQL ServerSQL Server の実行プランは、主に次の要素から構成されます。execution plans have the following main components:

  • クエリ実行プラン Query Execution Plan
    実行プランの大部分は、任意の数のユーザーが使用できる再入可能な読み取り専用のデータ構造体です。The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. これをクエリ プランといいます。This is referred to as the query plan. クエリ プランにはユーザー コンテキストは格納されません。No user context is stored in the query plan. また、メモリに複数のクエリ プランのコピーが配置されることはありません。すべての直列実行に 1 つのコピーが使用され、すべての並列実行に 1 つのコピーが使用されます。There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. 並列実行用コピーは、並列処理の次数に関係なくすべての並列実行に適用されます。The parallel copy covers all parallel executions, regardless of their degree of parallelism.
  • 実行コンテキスト Execution Context
    クエリを現在実行しているユーザーごとに、パラメーター値など、実行に固有のデータを保持するデータ構造体が用意されています。Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. このデータ構造体を実行コンテキストといいます。This data structure is referred to as the execution context. 実行コンテキストのデータ構造体は再利用されます。The execution context data structures are reused. ユーザーがクエリを実行したときに使用されていない構造体が 1 つある場合、新しいユーザーのコンテキストでその構造体が再初期化されます。If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.

execution_context

Transact-SQLTransact-SQL で任意の SQL ServerSQL Server ステートメントを実行すると、まずリレーショナル エンジンにより、プラン キャッシュが調査され、同じ Transact-SQLTransact-SQL ステートメントの既存の実行プランが存在するかどうかが確認されます。When any Transact-SQLTransact-SQL statement is executed in SQL ServerSQL Server, the Relational Engine first looks through the plan cache to verify that an existing execution plan for the same Transact-SQLTransact-SQL statement exists. Transact-SQLTransact-SQL ステートメントは、キャッシュされたプランで前に実行された Transact-SQLTransact-SQL ステートメントと文字単位で一致する場合、既存と見なされます。The Transact-SQLTransact-SQL statement qualifies as existing if it literally matches a previously executed Transact-SQLTransact-SQL statement with a cached plan, character per character. SQL ServerSQL Server では、既存のプランが見つかった場合にはそれが再利用され、Transact-SQLTransact-SQL ステートメントを再びコンパイルするオーバーヘッドが削減されます。reuses any existing plan it finds, saving the overhead of recompiling the Transact-SQLTransact-SQL statement. 既存の実行プランが存在しない場合、SQL ServerSQL Server によってクエリの新しい実行プランが生成されます。If no existing execution plan exists, SQL ServerSQL Server generates a new execution plan for the query.

注意

Transact-SQLTransact-SQL ステートメントの中にはキャッシュされないものもあります。たとえば、行ストア上で実行される一括操作ステートメントや 8 KB より大きい文字列リテラルを含むステートメントなどです。Some Transact-SQLTransact-SQL statements are not cached, such as bulk operation statements running on rowstore or statements containing string literals larger than 8 KB in size.

SQL ServerSQL Server には、特定の Transact-SQLTransact-SQL ステートメントの既存の実行プランを検索する効率的なアルゴリズムが用意されています。has an efficient algorithm to find any existing execution plans for any specific Transact-SQLTransact-SQL statement. ほとんどのシステムでは、このスキャンで使用される最低限のリソースの量が、すべての Transact-SQLTransact-SQL ステートメントをコンパイルせずに既存の実行プランを再利用することで節約できるリソースの量を超えることはありません。In most systems, the minimal resources that are used by this scan are less than the resources that are saved by being able to reuse existing plans instead of compiling every Transact-SQLTransact-SQL statement.

新しい Transact-SQLTransact-SQL ステートメントをキャッシュ内の使用されていない既存の実行プランと照合するアルゴリズムでは、すべてのオブジェクト参照が完全に修飾されている必要があります。The algorithms to match new Transact-SQLTransact-SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. たとえば、Person は下の SELECT ステートメント実行するユーザーに対する既定のスキーマであるものとします。For example, assume that Person is the default schema for the user executing the below SELECT statements. この例では、実行するために Person テーブルが完全修飾されている必要はありませんが、これは、2 番目のステートメントは既存のプランと一致しないのに対し、3 番目は一致することを意味します。While in this example it is not required that the Person table is fully qualified to execute, it means that the second statement is not matched with an existing plan, but the third is matched:

SELECT * FROM Person;
GO
SELECT * FROM Person.Person;
GO
SELECT * FROM Person.Person;
GO

プラン キャッシュからの実行プランの削除Removing Execution Plans from the Plan Cache

実行プランは、格納しておくためのメモリがある間はプラン キャッシュに残ります。Execution plans remain in the plan cache as long as there is enough memory to store them. メモリ負荷が存在する場合、SQL Server データベース エンジンSQL Server Database Engineはコストベースの手法を使用して、どの実行プランをプラン キャッシュから削除するかを判断します。When memory pressure exists, the SQL Server データベース エンジンSQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. コストベースの判断をするために、SQL Server データベース エンジンSQL Server Database Engineは各実行プランの現在のコスト変数を以下のような要因に基づいて増減させます。To make a cost-based decision, the SQL Server データベース エンジンSQL Server Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

ユーザー プロセスは、キャッシュに実行プランを挿入するときに、現在のコストを元のクエリ コンパイル コストと同じ値に設定します。アドホック実行プランの場合、ユーザー プロセスは現在のコストをゼロに設定します。When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to zero. これ以降、ユーザー プロセスは、実行プランを参照するたびに、現在のコストを元のコンパイル コストにリセットします。アドホック実行プランの場合、ユーザー プロセスは現在のコストを増加させます。Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. すべてのプランについて、現在のコストの最大値は元のコンパイル コストです。For all plans, the maximum value for the current cost is the original compile cost.

メモリ負荷が存在する場合、SQL Server データベース エンジンSQL Server Database Engineは実行プランをプラン キャッシュから削除することによって対応します。When memory pressure exists, the SQL Server データベース エンジンSQL Server Database Engine responds by removing execution plans from the plan cache. どのプランを削除するかを決めるために、SQL Server データベース エンジンSQL Server Database Engineは実行プランの状態を繰り返し検証して、現在のコストがゼロであるプランを削除します。To determine which plans to remove, the SQL Server データベース エンジンSQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. 現在のコストがゼロである実行プランは、メモリ負荷がある場合でも、自動的には削除されません。削除されるのは、SQL Server データベース エンジンSQL Server Database Engineがプランを検証し、現在のコストがゼロになっている場合だけです。An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the SQL Server データベース エンジンSQL Server Database Engine examines the plan and the current cost is zero. SQL Server データベース エンジンSQL Server Database Engineが実行プランを検証するとき、クエリがそのプランを現在使用していない場合は、現在のコストが減少し、ゼロに近づきます。When examining an execution plan, the SQL Server データベース エンジンSQL Server Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

SQL Server データベース エンジンSQL Server Database Engineは、メモリ要件を満たすために必要な数の実行プランが削除されるまで、実行プランの検証を繰り返します。The SQL Server データベース エンジンSQL Server Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. メモリ負荷が存在する場合、実行プランのコストは数回にわたって増減することになります。While memory pressure exists, an execution plan may have its cost increased and decreased more than once. メモリ負荷が存在しなくなると、SQL Server データベース エンジンSQL Server Database Engineは使用されていない実行プランの現在のコストを減らさなくなり、コストがゼロの実行プランもプラン キャッシュに残されます。When memory pressure no longer exists, the SQL Server データベース エンジンSQL Server Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the plan cache, even if their cost is zero.

SQL Server データベース エンジンSQL Server Database Engineはリソース モニターとユーザー ワーカー スレッドを使用して、メモリ負荷に応じてプラン キャッシュからメモリを解放します。The SQL Server データベース エンジンSQL Server Database Engine uses the resource monitor and user worker threads to free memory from the plan cache in response to memory pressure. リソース モニターとユーザー ワーカー スレッドは、使用されていない各実行プランの現在のコストを減らすために、プランを同時に実行して検証することができます。The resource monitor and user worker threads can examine plans run concurrently to decrease the current cost for each unused execution plan. リソース モニターは、グローバルなメモリ負荷が存在する場合に、実行プランをプラン キャッシュから削除します。The resource monitor removes execution plans from the plan cache when global memory pressure exists. リソース モニターはメモリを解放することによって、システム メモリ、プロセス メモリ、リソース プール メモリ、およびすべてのキャッシュの最大サイズのポリシーを強制的に適用します。It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

すべてのキャッシュの最大サイズは、バッファー プール サイズの関数で、最大サーバー メモリを超えることはできません。The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. 最大サーバー メモリの構成の詳細については、「 max server memory 」の sp_configure設定を参照してください。For more information on configuring the maximum server memory, see the max server memory setting in sp_configure.

ユーザー ワーカー スレッドは、単一キャッシュ メモリ負荷が存在する場合に、実行プランをプラン キャッシュから削除します。The user worker threads remove execution plans from the plan cache when single cache memory pressure exists. 単一キャッシュの最大サイズおよび最大エントリのポリシーを強制的に適用します。They enforce policies for maximum single cache size and maximum single cache entries.

次の例では、どの実行プランがプラン キャッシュから削除されるかを示しています。The following examples illustrate which execution plans get removed from the plan cache:

  • 実行プランは頻繁に参照されるため、そのコストがゼロになることはありません。An execution plan is frequently referenced so that its cost never goes to zero. メモリ負荷が存在せず、現在のコストがゼロでない場合、実行プランはプラン キャッシュに残り、削除されません。The plan remains in the plan cache and is not removed unless there is memory pressure and the current cost is zero.
  • アドホック実行プランは挿入され、メモリ負荷が生じるまでは再度参照されることはありません。An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. アドホック実行プランは、現在のコストがゼロで初期化されます。そのため、SQL Server データベース エンジンSQL Server Database Engineは実行プランを検証するときに現在のコストがゼロであると認識し、プラン キャッシュから実行プランを削除します。Since ad-hoc plans are initialized with a current cost of zero, when the SQL Server データベース エンジンSQL Server Database Engine examines the execution plan, it will see the zero current cost and remove the plan from the plan cache. メモリ負荷が存在しない場合、アドホック実行プランは、現在のコストがゼロでプラン キャッシュに残ります。The ad-hoc execution plan remains in the plan cache with a zero current cost when memory pressure does not exist.

キャッシュから 1 つまたはすべてのプランを手動で削除するには、 DBCC FREEPROCCACHEを使用します。To manually remove a single plan or all plans from the cache, use DBCC FREEPROCCACHE. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降は、ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE を使用してスコープ内のデータベースのプロシージャ (プラン) キャッシュをクリアします。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE to clear the procedure (plan) cache for the database in scope.

実行プランの再コンパイルRecompiling Execution Plans

データベースに変更を加えた場合、データベースの新しい状態によっては、実行プランの効率が低下したり、実行プランが無効になったりします。Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. 実行プランが無効になるような変更は SQL ServerSQL Server によって検出され、その実行プランは無効としてマークされます。SQL ServerSQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. このため、クエリを実行する次回の接続用に新しい実行プランを再コンパイルする必要があります。A new plan must then be recompiled for the next connection that executes the query. 実行プランが無効になるのは、次の場合です。The conditions that invalidate a plan include the following:

  • クエリ (ALTER TABLE および ALTER VIEW) によって参照されるテーブルまたはビューに変更を加えた場合Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • 単一のプロシージャに変更を加え、キャッシュからこのプロシージャの全プランを削除した場合 (ALTER PROCEDURE)Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • 実行プランで使用されるインデックスに変更を加えた場合Changes to any indexes used by the execution plan.
  • UPDATE STATISTICSなどのステートメントを使用して明示的に生成した実行プラン、または自動的に生成された実行プランによって使用される統計を更新した場合Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • 実行プランで使用されるインデックスを削除した場合Dropping an index used by the execution plan.
  • sp_recompileを明示的に呼び出した場合An explicit call to sp_recompile.
  • クエリによって参照されるテーブルを変更する他のユーザーが、 INSERT ステートメントまたは DELETE ステートメントを使用して大量の変更をキーに加えた場合Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • トリガーを含むテーブルで、inserted テーブルまたは deleted テーブルの行数が大幅に増加する場合For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • WITH RECOMPILE オプションを使用してストアド プロシージャを実行する場合Executing a stored procedure using the WITH RECOMPILE option.

主として再コンパイルが必要になるのは、ステートメントの正確性を維持したり、より処理速度が速いクエリ実行プランを取得したりする場合です。Most recompilations are required either for statement correctness or to obtain potentially faster query execution plans.

SQL ServerSQL Server 2000 では、バッチ内のステートメントが原因で再コンパイルが実行されるときに、そのバッチがストアド プロシージャ、トリガー、アドホック バッチ、または準備されたステートメントのいずれを使用して送信されたかどうかに関係なく、常にバッチ全体が再コンパイルされます。In SQL ServerSQL Server 2000, whenever a statement within a batch causes recompilation, the whole batch, whether submitted through a stored procedure, trigger, ad-hoc batch, or prepared statement, is recompiled. SQL Server 2005 (9.x)SQL Server 2005 (9.x) 以降では、再コンパイルの原因となったバッチ内のステートメントのみが再コンパイルされます。Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x), only the statement inside the batch that causes recompilation is recompiled. この違いにより、SQL ServerSQL Server 2000 以降のリリースの再コンパイル回数を比較することはできません。Because of this difference, recompilation counts in SQL ServerSQL Server 2000 and later releases are not comparable. また、SQL Server 2005 (9.x)SQL Server 2005 (9.x) 以降の再コンパイルには、拡張機能セットによってさらに多くの種類が用意されています。Also, there are more types of recompilations in SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later because of its expanded feature set.

ステートメントレベルの再コンパイルにより、パフォーマンスが向上します。これは、多くの場合、再コンパイルとそれに関連付けられた CPU 時間やロックへの悪影響を引き起こすステートメントの数が少ないからです。Statement-level recompilation benefits performance because, in most cases, a small number of statements causes recompilations and their associated penalties, in terms of CPU time and locks. したがって、再コンパイルする必要がないバッチの他のステートメントでは、これらの影響を回避できます。These penalties are therefore avoided for the other statements in the batch that do not have to be recompiled.

sql_statement_recompile 拡張イベント (xEvent) によりステートメントレベルの再コンパイルが報告されます。The sql_statement_recompile extended event (xEvent) reports statement-level recompilations. この xEvent は、ステートメントレベルの再コンパイルが何らかのバッチにより要求されるときに発生します。This xEvent occurs when a statement-level recompilation is required by any kind of batch. ストアド プロシージャ、トリガー、アドホック バッチ、クエリなどが相当します。This includes stored procedures, triggers, ad hoc batches and queries. バッチは、sp_executesql、動的 SQL、Prepare メソッド、Execute メソッドなど、いくつかのインターフェイスを使用して送信できます。Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods. sql_statement_recompile xEvent の recompile_cause 列には、再コンパイルの理由を示す整数コードが含まれます。The recompile_cause column of sql_statement_recompile xEvent contains an integer code that indicates the reason for the recompilation. 考えられる理由をまとめたのが次の表です。The following table contains the possible reasons:

スキーマの変更Schema changed 統計の変更Statistics changed
コンパイルの遅延Deferred compile SET オプションの変更SET option changed
一時テーブルの変更Temporary table changed リモート行セットの変更Remote rowset changed
FOR BROWSE アクセス許可の変更FOR BROWSE permission changed クエリ通知環境の変更Query notification environment changed
パーティション ビューの変更Partitioned view changed カーソル オプションの変更Cursor options changed
OPTION (RECOMPILE) の要求OPTION (RECOMPILE) requested パラメーター化されたプランのフラッシュParameterized plan flushed
プランに影響を与えるデータベース バージョンの変更Plan affecting database version changed クエリ ストア プラン強制ポリシーの変更Query Store plan forcing policy changed
クエリ ストア プラン強制の失敗Query Store plan forcing failed クエリ ストアにプランがないQuery Store missing the plan

注意

xEvents が利用できない SQL ServerSQL Server バージョンの場合、ステートメントレベルの再コンパイルの報告という同じ目的に SQL ServerSQL Server プロファイラー SP:Recompile トレース イベントを利用できます。In SQL ServerSQL Server versions where xEvents are not available, then the SQL ServerSQL Server Profiler SP:Recompile trace event can be used for the same purpose of reporting statement-level recompilations. トレース イベント SQL:StmtRecompile はまた、ステートメントレベルの再コンパイルを報告します。このトレース イベントを利用し、再コンパイルを追跡記録し、デバッグすることもできます。The trace event SQL:StmtRecompile also reports statement-level recompilations, and this trace event can also be used to track and debug recompilations. SP:Recompile では、ストアド プロシージャとトリガーのみに対して値が生成されますが、SQL:StmtRecompile では、sp_executesql、準備されたクエリ、および動的 SQL を使用して実行されたストアド プロシージャ、トリガー、アドホック バッチ、およびバッチに対して値が生成されます。Whereas SP:Recompile generates only for stored procedures and triggers, SQL:StmtRecompile generates for stored procedures, triggers, ad-hoc batches, batches that are executed by using sp_executesql, prepared queries, and dynamic SQL. SP:RecompileSQL:StmtRecompileEventSubClass 列には、再コンパイルの理由を示す整数コードが含まれます。The EventSubClass column of SP:Recompile and SQL:StmtRecompile contains an integer code that indicates the reason for the recompilation. コードの説明はここにあります。The codes are described here.

注意

AUTO_UPDATE_STATISTICS データベース オプションが ON に設定されていると、対象にしているテーブルまたはインデックス付きビューの統計が更新された場合、または前回の実行から基数が大きく変更された場合、クエリが再コンパイルされます。When the AUTO_UPDATE_STATISTICS database option is set to ON, queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution. この動作は、標準のユーザー定義テーブル、一時テーブル、および DML トリガーによって作成された inserted テーブルと deleted テーブルに当てはまります。This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. 過度の再コンパイルによってクエリのパフォーマンスが低下する場合は、この設定を OFFに変更することを検討してください。If query performance is affected by excessive recompilations, consider changing this setting to OFF. AUTO_UPDATE_STATISTICS データベース オプションを OFF に設定すると、統計や基数の変更に基づく再コンパイルは行われません。ただし、DML INSTEAD OF トリガーで作成した inserted テーブルおよび deleted テーブルは例外です。When the AUTO_UPDATE_STATISTICS database option is set to OFF, no recompilations occur based on statistics or cardinality changes, with the exception of the inserted and deleted tables that are created by DML INSTEAD OF triggers. これらのテーブルは tempdb で作成されるため、それらにアクセスするクエリの再コンパイルは tempdb の AUTO_UPDATE_STATISTICS の設定によって異なります。Because these tables are created in tempdb, the recompilation of queries that access them depends on the setting of AUTO_UPDATE_STATISTICS in tempdb. SQL ServerSQL Server 2000 では、この設定を OFF にした場合も、DML トリガーの inserted テーブルと deleted テーブルにタイする基数の変更に基づいて再コンパイルが引き続き行われます。Note that in SQL ServerSQL Server 2000, queries continue to recompile based on cardinality changes to the DML trigger inserted and deleted tables, even when this setting is OFF.

パラメーターと実行プランの再利用Parameters and Execution Plan Reuse

ADO、OLE DB、ODBC の各アプリケーションのパラメーター マーカーなどのパラメーターを使用すると、実行プランの再利用回数を増やすことができます。The use of parameters, including parameter markers in ADO, OLE DB, and ODBC applications, can increase the reuse of execution plans.

警告

エンド ユーザーが入力した値をパラメーターまたはパラメーター マーカーを使用して保持する方法は、文字列に値を連結し、その後この文字列をデータ アクセス API メソッド、 EXECUTE ステートメント、または sp_executesql ストアド プロシージャのいずれかを使用して実行する方法よりも安全です。Using parameters or parameter markers to hold values that are typed by end users is more secure than concatenating the values into a string that is then executed by using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

次の 2 つの SELECT ステートメントでは、 WHERE 句で比較する値のみが異なっています。The only difference between the following two SELECT statements is the values that are compared in the WHERE clause:

SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * 
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

これらのクエリの実行プランでは、 ProductSubcategoryID 列に対して比較用に格納された値のみが異なります。The only difference between the execution plans for these queries is the value stored for the comparison against the ProductSubcategoryID column. 最終的な目標は、ステートメントが生成するプランは基本的に同じであると SQL ServerSQL Server に常に認識させてプランを再利用することにありますが、Transact-SQLTransact-SQL ステートメントが複雑になると SQL ServerSQL Server がこのことを検出できない場合があります。While the goal is for SQL ServerSQL Server to always recognize that the statements generate essentially the same plan and reuse the plans, SQL ServerSQL Server sometimes does not detect this in complex Transact-SQLTransact-SQL statements.

パラメーターを使用して Transact-SQLTransact-SQL ステートメントと定数を切り離すと、同一のプランをリレーショナル エンジンが認識できるようになります。Separating constants from the Transact-SQLTransact-SQL statement by using parameters helps the relational engine recognize duplicate plans. パラメーターは、次の方法で使用できます。You can use parameters in the following ways:

  • Transact-SQLTransact-SQL では、sp_executesql を次のように使用します:In Transact-SQLTransact-SQL , use sp_executesql:

    DECLARE @MyIntParm INT
    SET @MyIntParm = 1
    EXEC sp_executesql
       N'SELECT * 
       FROM AdventureWorks2014.Production.Product 
       WHERE ProductSubcategoryID = @Parm',
       N'@Parm INT',
       @MyIntParm
    

    この方法は、SQL ステートメントを動的に生成する Transact-SQLTransact-SQL スクリプト、ストアド プロシージャ、またはトリガーで使用することをお勧めします。This method is recommended for Transact-SQLTransact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

  • ADO、OLE DB、および ODBC ではパラメーター マーカーを使用します。ADO, OLE DB, and ODBC use parameter markers. パラメーター マーカーは疑問符 (?) です。SQL ステートメント内の定数の代わりに置かれ、プログラム変数にバインドされます。Parameter markers are question marks (?) that replace a constant in an SQL statement and are bound to a program variable. たとえば、ODBC アプリケーションでは次のような操作を実行できます。For example, you would do the following in an ODBC application:

    • SQLBindParameter を使用して、整数変数を SQL ステートメントの最初のパラメーター マーカーにバインドします。Use SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.
    • 変数に整数値を代入します。Put the integer value in the variable.
    • 次のように、パラメーター マーカー (?) を指定してステートメントを実行します。Execute the statement, specifying the parameter marker (?):
    SQLExecDirect(hstmt, 
       "SELECT * 
       FROM AdventureWorks2014.Production.Product 
       WHERE ProductSubcategoryID = ?",
       SQL_NTS);
    

    アプリケーション内でパラメーター マーカーが使用されると、SQL ServerSQL Server に装備されている SQL ServerSQL Server Native Client OLE DB プロバイダーと SQL ServerSQL Server Native Client ODBC ドライバーが sp_executesql を使用して SQL ServerSQL Server にステートメントを送信します。The SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver included with SQL ServerSQL Server use sp_executesql to send statements to SQL ServerSQL Server when parameter markers are used in applications.

  • ストアド プロシージャをデザインする場合は、意図的にパラメーターを使用できます。To design stored procedures, which use parameters by design.

アプリケーションのデザインにパラメーターを明示的に組み込まない場合は、簡易パラメーター化の既定の動作を使用して、SQL ServerSQL Server クエリ オプティマイザーで自動的に特定のクエリをパラメーター化することもできます。If you do not explicitly build parameters into the design of your applications, you can also rely on the SQL ServerSQL Server Query Optimizer to automatically parameterize certain queries by using the default behavior of simple parameterization. また、ALTER DATABASE ステートメントの PARAMETERIZATION オプションを FORCED に設定することで、クエリ オプティマイザーにデータベース内のすべてのクエリをパラメーター化するように強制できます。Alternatively, you can force the Query Optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCED.

強制パラメーター化が有効になっている場合でも、簡易パラメーター化が行われる可能性はあります。When forced parameterization is enabled, simple parameterization can still occur. たとえば、強制パラメーター化のルールに従えば、次のクエリはパラメーター化できません。For example, the following query cannot be parameterized according to the rules of forced parameterization:

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

ただし、簡易パラメーター化のルールに従ってパラメーター化することはできます。However, it can be parameterized according to simple parameterization rules. 強制パラメーター化の試行に失敗した場合でも、簡易パラメーター化が続けて試行されます。When forced parameterization is tried but fails, simple parameterization is still subsequently tried.

簡易パラメーター化Simple Parameterization

SQL ServerSQL Server では、Transact-SQL ステートメントでパラメーターまたはパラメーター マーカーを使用することで、新しい Transact-SQLTransact-SQL ステートメントと既存のコンパイル済みの実行プランとを照合するリレーショナル エンジンの機能が強化されています。In SQL ServerSQL Server, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new Transact-SQLTransact-SQL statements with existing, previously-compiled execution plans.

警告

パラメーターまたはパラメーター マーカーを使用してエンド ユーザーの入力値を保持する方法は、文字列に値を連結し、その後この文字列をデータ アクセス API メソッド、 EXECUTE ステートメント、または sp_executesql ストアド プロシージャのいずれかを使用して実行する方法よりも安全です。Using parameters or parameter markers to hold values typed by end users is more secure than concatenating the values into a string that is then executed using either a data access API method, the EXECUTE statement, or the sp_executesql stored procedure.

パラメーターを指定せずに Transact-SQLTransact-SQL ステートメントを実行した場合、SQL ServerSQL Server ではステートメントを内部でパラメーター化することにより、既存の実行プランとの照合機能が高められます。If a Transact-SQLTransact-SQL statement is executed without parameters, SQL ServerSQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. この処理を簡易パラメーター化と呼びます。This process is called simple parameterization. SQL ServerSQL Server 2000 では、この処理を自動パラメーター化と呼んでいました。In SQL ServerSQL Server 2000, the process was referred to as auto-parameterization.

次のステートメントについて考えてみます。Consider this statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;

ステートメントの最後の値 1 は、パラメーターとして指定できます。The value 1 at the end of the statement can be specified as a parameter. リレーショナル エンジンにより、値 1 の位置にパラメーターが指定されたときと同様にこのバッチの実行プランが構築されます。The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. この簡易パラメーター化により、SQL ServerSQL Server で次の 2 つのステートメントから基本的に同じ実行プランが生成されると認識され、2 番目のステートメントにも最初のプランが再利用されます。Because of this simple parameterization, SQL ServerSQL Server recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

複雑な Transact-SQLTransact-SQL ステートメントを処理する場合、リレーショナル エンジンでは、どの式をパラメーター化できるのかを簡単に決定できないことがあります。When processing complex Transact-SQLTransact-SQL statements, the relational engine may have difficulty determining which expressions can be parameterized. 複雑な Transact-SQLTransact-SQL ステートメントと既存の使用されていない実行プランを照合するリレーショナル エンジンの機能を向上させるには、sp_executesql またはパラメーター マーカーを使用してパラメーターを明示的に指定します。To increase the ability of the relational engine to match complex Transact-SQLTransact-SQL statements to existing, unused execution plans, explicitly specify the parameters using either sp_executesql or parameter markers.

注意

+、-、*、/、または % の算術演算子を使用して int、smallint、tinyint、または bigint の定数値を float、real、decimal、または numeric のデータ型に暗黙的にまたは明示的に変換した場合、その式の結果の型および有効桁数を計算するための特定の規則が SQL ServerSQL Server により適用されます。When the +, -, *, /, or % arithmetic operators are used to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, SQL ServerSQL Server applies specific rules to calculate the type and precision of the expression results. ただし、これらの規則はクエリがパラメーター化されるかどうかによって異なります。However, these rules differ, depending on whether the query is parameterized or not. したがって、クエリ内の類似の式から異なる結果が生成される場合があります。Therefore, similar expressions in queries can, in some cases, produce differing results.

簡易パラメーター化の既定の動作では、SQL ServerSQL Server により、比較的小さなクエリがパラメーター化されます。Under the default behavior of simple parameterization, SQL ServerSQL Server parameterizes a relatively small class of queries. ただし、いくつかの制約はありますが、 PARAMETERIZATION コマンドの ALTER DATABASE オプションを FORCEDに設定することにより、データベース内のすべてのクエリをパラメーター化するように指定できます。However, you can specify that all queries in a database be parameterized, subject to certain limitations, by setting the PARAMETERIZATION option of the ALTER DATABASE command to FORCED. これにより、クエリをコンパイルする頻度が下がり、大量のクエリが同時に実行されるデータベースのパフォーマンスが向上します。Doing so may improve the performance of databases that experience high volumes of concurrent queries by reducing the frequency of query compilations.

また、単一クエリと、単一クエリと構文的に等しくパラメーター値だけが異なるクエリをパラメーター化することを指定できます。Alternatively, you can specify that a single query, and any others that are syntactically equivalent but differ only in their parameter values, be parameterized.

強制パラメーター化Forced Parameterization

データベースのすべての SELECTINSERTUPDATEDELETE ステートメントをパラメーター化するように指定することで、いくつかの制約はありますが SQL ServerSQL Server の簡易パラメーター化の既定動作をオーバーライドできます。You can override the default simple parameterization behavior of SQL ServerSQL Server by specifying that all SELECT, INSERT, UPDATE, and DELETE statements in a database be parameterized, subject to certain limitations. 強制パラメータ化を有効にするには、 PARAMETERIZATION ステートメントの FORCED オプションを ALTER DATABASE に設定します。Forced parameterization is enabled by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement. 強制パラメーター化を行うと、クエリをコンパイルおよび再コンパイルする頻度を緩和できるので、データベースによってはパフォーマンスが向上します。Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. 一般的に POS (point-of-sale) などのアプリケーションから大量のクエリが同時に実行されるデータベースは、強制パラメーター化によりパフォーマンスが向上します。Databases that may benefit from forced parameterization are generally those that experience high volumes of concurrent queries from sources such as point-of-sale applications.

PARAMETERIZATION オプションを FORCEDに設定すると、 SELECTINSERTUPDATEDELETE の各ステートメントに使用されているリテラル値は、その形式を問わずクエリのコンパイル時にパラメーターに変換されます。When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE, or DELETE statement, submitted in any form, is converted to a parameter during query compilation. ただし、次に示すクエリ構造に現れるリテラルは例外です。The exceptions are literals that appear in the following query constructs:

  • INSERT...EXECUTE ステートメント。INSERT...EXECUTE statements.
  • ストアド プロシージャ、トリガー、またはユーザー定義関数の本体内のステートメント。Statements inside the bodies of stored procedures, triggers, or user-defined functions. これらのルーチンのクエリ プランは既に SQL ServerSQL Server により再利用されています。SQL ServerSQL Server already reuses query plans for these routines.
  • クライアント側のアプリケーションで既にパラメーター化されている、準備されたステートメント。Prepared statements that have already been parameterized on the client-side application.
  • XQuery メソッド呼び出しを含んでいるステートメントを、 WHERE 句など通常は引数がパラメーター化されるコンテキストで使用した場合。Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. 引数がパラメーター化されないコンテキストでこのメソッドを使用した場合は、ステートメントの残りの部分がパラメーター化されます。If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
  • Transact-SQLTransact-SQL カーソル内のステートメント。Statements inside a Transact-SQLTransact-SQL cursor. (API カーソル内のSELECT ステートメントはパラメーター化されます)。(SELECT statements inside API cursors are parameterized.)
  • 非推奨のクエリ構造。Deprecated query constructs.
  • ANSI_PADDING または ANSI_NULLSOFFに設定されている状態で実行されているステートメント。Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF.
  • パラメーター化が可能なリテラルが 2,097 を超えるステートメント。Statements that contain more than 2,097 literals that are eligible for parameterization.
  • WHERE T.col2 >= @bbなど、変数を参照するステートメント。Statements that reference variables, such as WHERE T.col2 >= @bb.
  • RECOMPILE クエリ ヒントを含んだステートメント。Statements that contain the RECOMPILE query hint.
  • COMPUTE 句を含むステートメント。Statements that contain a COMPUTE clause.
  • WHERE CURRENT OF 句を含むステートメント。Statements that contain a WHERE CURRENT OF clause.

また、次のクエリ句はパラメーター化されません。Additionally, the following query clauses are not parameterized. 以下の場合については、パラメーター化されないのはこれらの句だけです。Note that in these cases, only the clauses are not parameterized. 同じクエリ内のその他の句では強制パラメーター化が可能な場合もあります。Other clauses within the same query may be eligible for forced parameterization.

  • SELECT ステートメントの <select_list>。The <select_list> of any SELECT statement. これには、サブクエリの SELECT リストや INSERT ステートメント内の SELECT も含まれます。This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.
  • SELECT ステートメント内のサブクエリの IF ステートメント。Subquery SELECT statements that appear inside an IF statement.
  • クエリの TOP句、 TABLESAMPLE句、 HAVING句、 GROUP BY句、 ORDER BY句、 OUTPUT...INTO句、 FOR XML 句。The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
  • OPENROWSETOPENQUERYOPENDATASOURCEOPENXML、または任意の FULLTEXT 演算子に渡す引数。直接渡すか、サブ式として渡すかは問いません。Arguments, either direct or as subexpressions, to OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, or any FULLTEXT operator.
  • LIKE 句の pattern 引数と escape_character 引数。The pattern and escape_character arguments of a LIKE clause.
  • CONVERT 句の style 引数。The style argument of a CONVERT clause.
  • IDENTITY 句内の整数の定数。Integer constants inside an IDENTITY clause.
  • ODBC 拡張機能の構文で指定した定数。Constants specified by using ODBC extension syntax.
  • 演算子 +、-、*、/、および % の引数であり、定数のたたみ込みが可能な式。Constant-foldable expressions that are arguments of the +, -, *, /, and % operators. SQL ServerSQL Server は、強制パラメーター化を行うことができるかどうかを決定する際に、次のいずれかの条件が満たされていれば式で定数のたたみ込みが可能であると見なします。When considering eligibility for forced parameterization, SQL ServerSQL Server considers an expression to be constant-foldable when either of the following conditions is true:
    • 式に列、変数、およびサブクエリが使用されていない。No columns, variables, or subqueries appear in the expression.
    • 式に CASE 句が含まれている。The expression contains a CASE clause.
  • クエリ ヒントの句に渡す引数。Arguments to query hint clauses. number_of_rows クエリ ヒントの FAST 引数、 number_of_processors クエリ ヒントの MAXDOP 引数、および MAXRECURSION クエリ ヒントの number 引数がこれに該当します。These include the number_of_rows argument of the FAST query hint, the number_of_processors argument of the MAXDOP query hint, and the number argument of the MAXRECURSION query hint.

パラメーター化は個々の Transact-SQLTransact-SQL ステートメント レベルで行われます。Parameterization occurs at the level of individual Transact-SQLTransact-SQL statements. つまり、バッチ内では個々のステートメントがパラメーター化されます。In other words, individual statements in a batch are parameterized. コンパイルの後、パラメーター化クエリは、最初に送信されたバッチのコンテキストで実行されます。After compiling, a parameterized query is executed in the context of the batch in which it was originally submitted. クエリの実行プランがキャッシュに残っている場合、sys.syscacheobjects 動的管理ビューの sql 列を参照することでクエリがパラメーター化されているかどうかを判断できます。If an execution plan for a query is cached, you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects dynamic management view. クエリがパラメーター化されている場合、この列には送信されたバッチのテキストの前に "@1 tinyint" のように、パラメーターの名前とデータ型が付加されます。If a query is parameterized, the names and data types of parameters come before the text of the submitted batch in this column, such as (@1 tinyint).

注意

パラメーター名の規則はありません。Parameter names are arbitrary. 特定の命名順序に依存することは避けてください。Users or applications should not rely on a particular naming order. また、次のものは、SQL ServerSQL Server のバージョンおよび Service Pack の適用状況によって異なる場合があります:パラメーター名、パラメーター化されるリテラル、およびパラメーター化されたテキストに含まれるスペース。Also, the following can change between versions of SQL ServerSQL Server and Service Pack upgrades: Parameter names, the choice of literals that are parameterized, and the spacing in the parameterized text.

パラメーターのデータ型Data Types of Parameters

SQL ServerSQL Server がリテラルをパラメーター化する際に、パラメーターは次のデータ型に変換されます。When SQL ServerSQL Server parameterizes literals, the parameters are converted to the following data types:

  • int データ型の範囲に収まるサイズの整数リテラルは、int 型にパラメーター化されます。それよりも大きな整数リテラルのうち、比較演算子 (<、<=、=、!=、>、>=、 、!<、!>、<>、ALLANYSOMEBETWEENIN など) を伴う述語で使用されているものは numeric(38,0) 型にパラメーター化されます。Integer literals whose size would otherwise fit within the int data type parameterize to int. Larger integer literals that are parts of predicates that involve any comparison operator (includes <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN, and IN) parameterize to numeric(38,0). 比較演算子を伴う述語で使用されていないものは、リテラルのサイズに見合う十分な有効桁数があり、小数点以下桁数が 0 の numeric 型にパラメーター化されます。Larger literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision is just large enough to support its size and whose scale is 0.
  • 比較演算子を伴う述語で使用されている固定小数点型のリテラルは、有効桁数が 38 桁で、リテラルのサイズに見合う十分な小数点以下桁数がある numeric 型にパラメーター化されます。Fixed-point numeric literals that are parts of predicates that involve comparison operators parameterize to numeric whose precision is 38 and whose scale is just large enough to support its size. 比較演算子を伴う述語で使用されていない固定小数点型のリテラルは、リテラルのサイズに見合う十分な有効桁数および小数点以下桁数がある numeric 型にパラメーター化されます。Fixed-point numeric literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision and scale are just large enough to support its size.
  • 浮動小数点型のリテラルは float(53) 型にパラメーター化されます。Floating point numeric literals parameterize to float(53).
  • Unicode 以外の文字列リテラルは、8,000 文字以内の場合は varchar(8000) 型に、8,000 文字を超える場合は varchar(max) 型にパラメーター化されます。Non-Unicode string literals parameterize to varchar(8000) if the literal fits within 8,000 characters, and to varchar(max) if it is larger than 8,000 characters.
  • Unicode 文字列リテラルは、4,000 文字以内の場合は nvarchar(4000) 型に、4,000 文字を超える場合は nvarchar(max) 型にパラメーター化されます。Unicode string literals parameterize to nvarchar(4000) if the literal fits within 4,000 Unicode characters, and to nvarchar(max) if the literal is larger than 4,000 characters.
  • バイナリ リテラルは、8,000 バイト以内の場合は varbinary(8000) 型にパラメーター化されます。Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. 8,000 バイトを超える場合は、varbinary(max) 型に変換されます。If it is larger than 8,000 bytes, it is converted to varbinary(max).
  • money 型のリテラルは、money 型にパラメーター化されます。Money type literals parameterize to money.

強制パラメーター化使用のガイドラインGuidelines for Using Forced Parameterization

PARAMETERIZATION オプションを FORCED に設定するときは、次のことを考慮してください。Consider the following when you set the PARAMETERIZATION option to FORCED:

  • 強制パラメーター化を行うと、クエリをコンパイルするときにクエリ内のリテラル定数がパラメーターに変更されます。Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. そのため、クエリ オプティマイザーで最適なクエリ プランが選択されない場合があります。Therefore, the Query Optimizer might choose suboptimal plans for queries. 特に、インデックス付きビューや、計算列のインデックスに合わせてクエリが調整されることはあまりありません。In particular, the Query Optimizer is less likely to match the query to an indexed view or an index on a computed column. パーティション テーブルおよび分散パーティション ビューに発行するクエリについても、最適なプランが選択されない場合があります。It may also choose suboptimal plans for queries posed on partitioned tables and distributed partitioned views. インデックス付きビューおよび計算列のインデックスに大きく依存する環境では、強制パラメーター化を使用しないでください。Forced parameterization should not be used for environments that rely heavily on indexed views and indexes on computed columns. PARAMETERIZATION FORCED オプションは、熟練したデータベース管理者が、パフォーマンスに悪影響が出ないことを確認した上でのみ使用してください。Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance.
  • 複数のデータベースを参照する分散クエリでは、そのクエリを実行しているデータベースのコンテキストで PARAMETERIZATION オプションが FORCED に設定されていれば強制パラメーター化を行うことができます。Distributed queries that reference more than one database are eligible for forced parameterization as long as the PARAMETERIZATION option is set to FORCED in the database whose context the query is running.
  • PARAMETERIZATION オプションを FORCED に設定すると、コンパイル中、再コンパイル中、および実行中のクエリ プランを除くすべてのクエリ プランがデータベースのプラン キャッシュから消去されます。Setting the PARAMETERIZATION option to FORCED flushes all query plans from the plan cache of a database, except those that currently are compiling, recompiling, or running. 設定の変更時にコンパイルまたは実行されているクエリのプランは、次回そのクエリを実行するときにパラメーター化されます。Plans for queries that are compiling or running during the setting change are parameterized the next time the query is executed.
  • PARAMETERIZATION オプションはオンライン操作で設定します。このとき、データベースレベルの排他ロックは必要ありません。Setting the PARAMETERIZATION option is an online operation that it requires no database-level exclusive locks.
  • PARAMETERIZATION オプションの現在の設定は、データベースを再アタッチまたは復元するときも維持されます。The current setting of the PARAMETERIZATION option is preserved when reattaching or restoring a database.

単一クエリ、および構文は同じでパラメーター値のみが異なる他の任意のクエリを簡易パラメーター化するように指定することで、強制パラメーター化の動作をオーバーライドできます。You can override the behavior of forced parameterization by specifying that simple parameterization be attempted on a single query, and any others that are syntactically equivalent but differ only in their parameter values. 逆に、データベースで強制パラメーター化の動作が無効になっている場合に、構文が同じクエリに対してのみ強制パラメーター化の動作を指定することもできます。Conversely, you can specify that forced parameterization be attempted on only a set of syntactically equivalent queries, even if forced parameterization is disabled in the database. この方法については、「プラン ガイド 」を参照してください。Plan guides are used for this purpose.

注意

PARAMETERIZATION オプションを FORCED に設定すると、PARAMETERIZATION オプションを SIMPLE に設定する場合と比べて、報告されるエラー メッセージに違いが現れる場合があります。複数のエラー メッセージが強制パラメーター化で報告される場合があり、簡易パラメーター化よりも多くのエラー メッセージが報告されることがあります。また、エラーが発生した行番号が間違って報告されることがあります。When the PARAMETERIZATION option is set to FORCED, the reporting of error messages may differ from when the PARAMETERIZATION option is set to SIMPLE: multiple error messages may be reported under forced parameterization, where fewer messages would be reported under simple parameterization, and the line numbers in which errors occur may be reported incorrectly.

SQL ステートメントの準備Preparing SQL Statements

SQL ServerSQL Server のリレーショナル エンジンでは、Transact-SQLTransact-SQL ステートメントを実行前に準備する方式が完全にサポートされるようになりました。The SQL ServerSQL Server relational engine introduces full support for preparing Transact-SQLTransact-SQL statements before they are executed. アプリケーションでは、Transact-SQLTransact-SQL ステートメントを複数回実行する必要がある場合、データベース API を使用して次の処理を実行できます。If an application has to execute an Transact-SQLTransact-SQL statement several times, it can use the database API to do the following:

  • ステートメントを 1 回準備します。Prepare the statement once. これにより、Transact-SQLTransact-SQL ステートメントがコンパイルされて実行プランが作成されます。This compiles the Transact-SQLTransact-SQL statement into an execution plan.
  • ステートメントの実行が必要になるたびに、コンパイル済みの実行プランを実行します。Execute the precompiled execution plan every time it has to execute the statement. これにより、2 回目以降は実行ごとに Transact-SQLTransact-SQL ステートメントを再コンパイルする必要がなくなります。This prevents having to recompile the Transact-SQLTransact-SQL statement on each execution after the first time.
    ステートメントの準備と実行は、API の関数およびメソッドによって制御されます。Preparing and executing statements is controlled by API functions and methods. これは Transact-SQLTransact-SQL 言語の一部ではありません。It is not part of the Transact-SQLTransact-SQL language. Transact-SQLTransact-SQL ステートメントを実行するための準備/実行のモデルは、SQL ServerSQL Server Native Client OLE DB プロバイダーと SQL ServerSQL Server Native Client ODBC ドライバーによってサポートされています。The prepare/execute model of executing Transact-SQLTransact-SQL statements is supported by the SQL ServerSQL Server Native Client OLE DB Provider and the SQL ServerSQL Server Native Client ODBC driver. 準備要求時に、プロバイダーまたはドライバーから SQL ServerSQL Server に対し、準備要求と共にステートメントが送信されます。On a prepare request, either the provider or the driver sends the statement to SQL ServerSQL Server with a request to prepare the statement. SQL ServerSQL Server で実行プランがコンパイルされ、このプランのハンドルがプロバイダーまたはドライバーに返されます。compiles an execution plan and returns a handle for that plan to the provider or driver. 実行要求時に、プロバイダーまたはドライバーのいずれかによって、ハンドルに関連付けられたプランの実行要求がサーバーに送信されます。On an execute request, either the provider or the driver sends the server a request to execute the plan that is associated with the handle.

SQL ServerSQL Server では、準備されたステートメントを使用して一時オブジェクトを作成することはできません。Prepared statements cannot be used to create temporary objects on SQL ServerSQL Server. また、準備されたステートメントでは、一時テーブルなどの一時オブジェクトを作成するシステム ストアド プロシージャを参照できません。Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. このようなプロシージャは、直接実行する必要があります。These procedures must be executed directly.

準備/実行のモデルを過度に使用すると、パフォーマンスが低下することがあります。Excess use of the prepare/execute model can degrade performance. ステートメントを 1 回だけ実行する場合は、直接実行のためのネットワークからサーバーへのアクセスは 1 回だけで済みます。If a statement is executed only once, a direct execution requires only one network round-trip to the server. 1 回だけ実行される Transact-SQLTransact-SQL ステートメントを準備してから実行する場合は、もう 1 回余分にネットワークからサーバーにアクセスする必要があります。つまり、1 回はステートメントを準備するため、もう 1 回はステートメントを実行するためです。Preparing and executing an Transact-SQLTransact-SQL statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it.

パラメーター マーカーを使用する場合、ステートメントを準備するとより効果的です。Preparing a statement is more effective if parameter markers are used. たとえば、あるアプリケーションに対して、 AdventureWorks サンプル データベースから製品情報を取得するように頻繁に要求する場合を考えます。For example, assume that an application is occasionally asked to retrieve product information from the AdventureWorks sample database. アプリケーションでこの処理を実行できる方法は 2 つあります。There are two ways the application can do this.

最初の方法を使用すると、アプリケーションでは、次のように要求された製品ごとに個別のクエリを実行できます。Using the first way, the application can execute a separate query for each product requested:

SELECT * FROM AdventureWorks2014.Production.Product
WHERE ProductID = 63;

2 番目の方法を使用すると、アプリケーションによって次の処理が行われます。Using the second way, the application does the following:

  1. 次のように、パラメーター マーカー (?) を含むステートメントを準備します。Prepares a statement that contains a parameter marker (?):
    SELECT * FROM AdventureWorks2014.Production.Product  
    WHERE ProductID = ?;
    
  2. プログラム変数をパラメーター マーカーにバインドします。Binds a program variable to the parameter marker.
  3. 製品情報が必要になるたびに、バインドした変数にキー値を入力し、ステートメントを実行します。Each time product information is needed, fills the bound variable with the key value and executes the statement.

ステートメントを 4 回以上実行する場合は、2 番目の方法がより効率的です。The second way is more efficient when the statement is executed more than three times.

SQL ServerSQL Server では、直接実行と比べて準備/実行モデルにパフォーマンス上のメリットはほとんどありません。これは、SQL ServerSQL Server では実行プランが再利用されるためです。In SQL ServerSQL Server, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL ServerSQL Server reuses execution plans. SQL ServerSQL Server には、現在の Transact-SQLTransact-SQL ステートメントを、同じ Transact-SQLTransact-SQL ステートメントの前回の実行用に生成された実行プランと照合する効率的なアルゴリズムが備わっています。has efficient algorithms for matching current Transact-SQLTransact-SQL statements with execution plans that are generated for prior executions of the same Transact-SQLTransact-SQL statement. アプリケーションにより、パラメーター マーカーを使用して Transact-SQLTransact-SQL ステートメントが複数回実行される場合、SQL ServerSQL Server では 2 回目以降の実行に最初の実行で使用した実行プランが再利用されます。ただし、プラン キャッシュのプランが古くなった場合は、実行プランは再利用されません。If an application executes a Transact-SQLTransact-SQL statement with parameter markers multiple times, SQL ServerSQL Server will reuse the execution plan from the first execution for the second and subsequent executions (unless the plan ages from the plan cache). 準備/実行のモデルには次の利点もあります。The prepare/execute model still has these benefits:

  • 識別のためのハンドルによって実行プランを検索する方が、Transact-SQLTransact-SQL ステートメントを既存の実行プランと照合するために使用するアルゴリズムよりも効率的です。Finding an execution plan by an identifying handle is more efficient than the algorithms used to match an Transact-SQLTransact-SQL statement to existing execution plans.
  • 実行プランの作成および再利用のタイミングをアプリケーションで制御できます。The application can control when the execution plan is created and when it is reused.
  • 準備/実行のモデルは、以前のバージョンの SQL ServerSQL Server など、他のデータベースに移植できます。The prepare/execute model is portable to other databases, including earlier versions of SQL ServerSQL Server.

パラメーター スニッフィングParameter Sniffing

"パラメーター スニッフィング" とは、コンパイルまたは再コンパイルの間に SQL ServerSQL Server が現在のパラメーター値を "スニッフィング (傍受)" し、クエリ オプティマイザーに渡すプロセスです。渡されたパラメーター値は、より効率的なクエリ実行プランを生成するために利用できます。"Parameter sniffing" refers to a process whereby SQL ServerSQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.

パラメーター値は、次のようなバッチのコンパイルまたは再コンパイル中にスニッフィングされます。Parameter values are sniffed during compilation or recompilation for the following types of batches:

  • ストアド プロシージャStored procedures
  • sp_executesql 経由で送信されたクエリQueries submitted via sp_executesql
  • 準備されたクエリPrepared queries

不適切なパラメーター スニッフィング問題のトラブルシューティングについては、「Troubleshoot queries with parameter-sensitive query execution plan issues」 (パラメーター依存のクエリ実行プランの問題を解決する) を参照してください。For more information on troubleshooting bad parameter sniffing issues, see Troubleshoot queries with parameter-sensitive query execution plan issues.

注意

RECOMPILE ヒントを利用するクエリの場合、パラメーター値とローカル変数の現在の値の両方がスニッフィングされます。For queries using the RECOMPILE hint, both parameter values and current values of local variables are sniffed. (パラメーターとローカル変数の) スニッフィングされた値は、バッチの中で、RECOMPILE ヒントのあるステートメントの手前に存在する値です。The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the RECOMPILE hint. 特に、パラメーターの場合、バッチ呼び出しで共に与えられた値はスニッフィングされません。In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

並列クエリ処理Parallel Query Processing

SQL ServerSQL Server では、複数の CPU (マイクロプロセッサ) を搭載したコンピューターのクエリ実行およびインデックス操作を最適化するための並列クエリを使用できます。provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). SQL ServerSQL Server はオペレーティング システムのワーカー スレッドを複数使用してクエリやインデックス操作を並列的に実行できるため、操作を短時間で効率的に完了できます。Because SQL ServerSQL Server can perform a query or index operation in parallel by using several operating system worker threads, the operation can be completed quickly and efficiently.

SQL ServerSQL Server は、クエリを最適化する過程で、並列実行による効果が期待できるクエリやインデックス操作を検索します。During query optimization, SQL ServerSQL Server looks for queries or index operations that might benefit from parallel execution. 次に、SQL ServerSQL Server は、そのようなクエリの実行プランに交換操作を挿入して並列実行用クエリを作成します。For these queries, SQL ServerSQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. 交換操作とは、プロセス管理、データの再配布、およびフロー制御を行うクエリ実行プラン内の操作です。An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. 交換操作は Distribute Streams論理操作、 Repartition Streams論理操作、および Gather Streams 論理操作から構成されており、これらは並列クエリのプラン表示出力に含まれる可能性があります。The exchange operator includes the Distribute Streams, Repartition Streams, and Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.

重要

一部の構造は、実行プランの全体または一部分で、並列処理を利用する SQL ServerSQL Server の機能を妨げます。Certain constructs inhibit SQL ServerSQL Server's ability to leverage parallelism on the entire execution plan, or parts or the execution plan.

並列処理を妨げる構造は次のとおりです。Constructs that inhibit parallelism include:

交換操作を挿入すると、並列クエリの実行プランになります。After exchange operators are inserted, the result is a parallel-query execution plan. 並列クエリの実行プランでは複数のワーカー スレッドを使用できます。A parallel-query execution plan can use more than one worker thread. 並列でないクエリで使用する直列の実行プランの場合、実行時に使用するワーカー スレッドは 1 つのみです。A serial execution plan, used by a nonparallel query, uses only one worker thread for its execution. 並列クエリで実際に使用するワーカー スレッドの数は、クエリ プランを実行するための初期化の時点で、プランの複雑さと並列処理の次数に応じて決まります。The actual number of worker threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism. 並列処理の次数は、使用している CPU の最大数によって決まります (これは使用しているワーカー スレッドの数という意味ではありません)。Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of worker threads that are being used. 並列処理の次数はサーバー レベルで設定され、sp_configure システム ストアド プロシージャで変更できます。The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. クエリ ステートメントで MAXDOP クエリ ヒントを、またはインデックス ステートメントで MAXDOP インデックス オプションを指定することにより、この値をオーバーライドできます。You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

次のいずれかの条件が満たされている場合、SQL ServerSQL Server のクエリ オプティマイザーは、クエリに対して並列実行プランを使用しません。The SQL ServerSQL Server Query Optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

  • 代替策として並列実行プランの使用を考えるほど、クエリの直列実行コストが高くない。The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
  • 特定のクエリに対して可能な並列実行プランより、直列実行プランの方が速いと考えられる。A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
  • クエリに、並列では実行できないスカラー演算子または関係演算子が含まれる。The query contains scalar or relational operators that cannot be run in parallel. 演算子によっては、クエリ プランのセクションまたはプラン全体が直列モードで実行される場合があります。Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

並列処理の次数Degree of Parallelism

SQL ServerSQL Server は、並列クエリの実行またはインデックス DDL (データ定義言語) 操作のインスタンスごとに、並列処理の最適な次数を自動的に検出します。automatically detects the best degree of parallelism for each instance of a parallel query execution or index data definition language (DDL) operation. この処理は次の基準に基づいて実行されます。It does this based on the following criteria:

  1. SMP (対称型マルチプロセッシング) コンピューターなど、複数のマイクロプロセッサまたは CPU を搭載したコンピューターで SQL ServerSQL Server が実行されているかどうか。Whether SQL ServerSQL Server is running on a computer that has more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP).
    並列クエリを使用できるのは、複数の CPU を搭載したコンピューターだけです。Only computers that have more than one CPU can use parallel queries.

  2. 十分な数のワーカー スレッドを使用できるかどうか。Whether sufficient worker threads are available.
    各クエリまたはインデックス操作では、一定数のワーカー スレッドを実行する必要があります。Each query or index operation requires a certain number of worker threads to execute. 並列プランの実行には直列プランの場合よりも多くのワーカー スレッドが必要になり、必要なワーカー スレッド数は並列処理の次数が高くなるほど増加します。Executing a parallel plan requires more worker threads than a serial plan, and the number of required worker threads increases with the degree of parallelism. 並列処理の特定の次数に応じた並列プランのワーカー スレッド要件を満たすことができない場合、SQL Server データベース エンジンSQL Server Database Engineが並列処理の次数を自動的に下げるか、指定されたワークロード コンテキストでの並列プランを完全に破棄します。When the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the SQL Server データベース エンジンSQL Server Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. その後、直列プラン (1 つのワーカー スレッド) を実行します。It then executes the serial plan (one worker thread).

  3. 実行するクエリまたはインデックス操作の種類。The type of query or index operation executed.
    インデックスの作成や再構築またはクラスター化インデックスの削除を行うインデックス操作、および CPU サイクルを大量に使用するクエリは並列プランの候補として最適です。Index operations that create or rebuild an index, or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. たとえば、大きなテーブルの結合、大量の集計、および大きな結果セットの並べ替えは候補として適しています。For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates. トランザクション処理アプリケーションに多い単純なクエリの場合、クエリを並列実行するにはさらに調整が必要なので、パフォーマンスの向上は困難です。Simple queries, frequently found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. 並列処理の利点を得られるクエリとそうでないクエリを区別するために、SQL Server データベース エンジンSQL Server Database Enginecost threshold for parallelism 値を使用して、クエリまたはインデックス操作を実行するための推定コストを比較します。To distinguish between queries that benefit from parallelism and those that do not benefit, the SQL Server データベース エンジンSQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. 適切なテストで実行中のワークロードには異なる値がより適していることが判明した場合、ユーザーは sp_configure を使用して既定値の 5 を変更することができます。Users can change the default value of 5 using sp_configure if proper testing found that a different value is better suited for the running workload.

  4. 処理する十分な数の行があるかどうか。Whether there are a sufficient number of rows to process.
    クエリ オプティマイザーによって行数が少なすぎると判断されると、行を分散するための交換操作は導入されません。If the Query Optimizer determines that the number of rows is too low, it does not introduce exchange operators to distribute the rows. したがって、操作は直列に実行されます。Consequently, the operators are executed serially. 直列プランで操作を実行すると、並列操作を実行したときに得られる効果より、起動、分散、および調整のコストの方が上回る事態を回避することができます。Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination costs exceed the gains achieved by parallel operator execution.

  5. 現在の分布統計が使用できるかどうか。Whether current distribution statistics are available.
    並列処理の最高次数が提供されない場合、並列プランが放棄される前に、それより低い次数が検討されます。If the highest degree of parallelism is not possible, lower degrees are considered before the parallel plan is abandoned.
    たとえば、ビューにクラスター化インデックスを作成する場合、クラスター化インデックスはまだ存在しないので、分布統計を評価できません。For example, when you create a clustered index on a view, distribution statistics cannot be evaluated, because the clustered index does not yet exist. この場合、SQL Server データベース エンジンSQL Server Database Engineでは、インデックス操作用に並列処理の最高次数を提供できません。In this case, the SQL Server データベース エンジンSQL Server Database Engine cannot provide the highest degree of parallelism for the index operation. ただし、並べ替えやスキャンなどの一部の操作では、それまでどおり並列実行の利点を得ることができます。However, some operators, such as sorting and scanning, can still benefit from parallel execution.

注意

並列インデックス操作は、SQL ServerSQL Server Enterprise Edition、Developer Edition、および Evaluation Edition でのみ使用できます。Parallel index operations are only available in SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

既に説明したような現在のシステムのワークロードと構成情報で並列実行が可能かどうかは、実行時にSQL Server データベース エンジンSQL Server Database Engineによって判断されます。At execution time, the SQL Server データベース エンジンSQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. 並列実行の条件を満たしている場合、SQL Server データベース エンジンSQL Server Database Engineにより、最適なワーカー スレッド数が判断され、これらのワーカー スレッド全体に並列プランの実行が分散されます。If parallel execution is warranted, the SQL Server データベース エンジンSQL Server Database Engine determines the optimal number of worker threads and spreads the execution of the parallel plan across those worker threads. 複数のワーカー スレッドでクエリまたはインデックス操作が並列実行で開始された後は、操作で使用されるワーカー スレッド数は操作の完了時まで変化しません。When a query or index operation starts executing on multiple worker threads for parallel execution, the same number of worker threads is used until the operation is completed. SQL Server データベース エンジンSQL Server Database Engineは、プラン キャッシュから実行プランを取得するたびに、最適なワーカー スレッド数を再調査して決定します。The SQL Server データベース エンジンSQL Server Database Engine re-examines the optimal number of worker thread decisions every time an execution plan is retrieved from the plan cache. たとえば、1 回のクエリの実行では直列プランを使用し、同じクエリを再度実行するときは 3 つのワーカー スレッドを使用する並列プランを使用し、3 回目に実行するときは 4 つのワーカー スレッドを使用する並列プランを使用する場合があります。For example, one execution of a query can result in the use of a serial plan, a later execution of the same query can result in a parallel plan using three worker threads, and a third execution can result in a parallel plan using four worker threads.

並列クエリの実行プランでは、挿入操作、更新操作、および削除操作が直列に実行されます。In a parallel query execution plan, the insert, update, and delete operators are executed serially. ただし、UPDATE ステートメントや DELETE ステートメントの WHERE 句、または INSERT ステートメントの SELECT 部分は並列に実行できます。However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. 実際のデータ変更は、データベースに直列に適用されます。The actual data changes are then serially applied to the database.

静的カーソルとキーセット ドリブン カーソルは、並列実行プランによって作成できます。Static and keyset-driven cursors can be populated by parallel execution plans. ただし、動的カーソルの動作は、直列実行の場合だけ有効です。However, the behavior of dynamic cursors can be provided only by serial execution. クエリ オプティマイザーは、動的カーソルの一部であるクエリに対しては必ず直列実行プランを生成します。The Query Optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

並列処理の次数のオーバーライドOverriding Degrees of Parallelism

max degree of parallelism (MAXDOP) サーバー構成オプション (SQL DatabaseSQL Databaseでは ALTER DATABASE SCOPED CONFIGURATION) を使用すると、並列プランの実行で使用されるプロセッサの数を制限できます。You can use the max degree of parallelism (MAXDOP) server configuration option (ALTER DATABASE SCOPED CONFIGURATION on SQL DatabaseSQL Database ) to limit the number of processors to use in parallel plan execution. max degree of parallelism オプションは、MAXDOP クエリ ヒントまたは MAXDOP インデックス オプションを指定することにより、個別のクエリやインデックス操作のステートメントでオーバーライドできます。The max degree of parallelism option can be overridden for individual query and index operation statements by specifying the MAXDOP query hint or MAXDOP index option. MAXDOP では、個別のクエリやインデックス操作をより制御できます。MAXDOP provides more control over individual queries and index operations. たとえば、MAXDOP オプションを使用すると、オンライン インデックス操作専用のプロセッサの数を増減することによって制御できます。For example, you can use the MAXDOP option to control, by increasing or reducing, the number of processors dedicated to an online index operation. このようにして、インデックス操作で使用されるリソースと同時実行ユーザーが使用するリソースのバランスをとることができます。In this way, you can balance the resources used by an index operation with those of the concurrent users.

max degree of parallelism オプションを 0 (既定) に設定すると、SQL ServerSQL Server で並列プランの実行で使用するプロセッサの数を最大 64 に制限できます。Setting the max degree of parallelism option to 0 (default) enables SQL ServerSQL Server to use all available processors up to a maximum of 64 processors in a parallel plan execution. MAXDOP オプションを 0 に設定すると、SQL ServerSQL Server では 64 個の論理プロセッサという実行時ターゲットが設定されますが、必要であれば、別の値を手動で設定できます。Although SQL ServerSQL Server sets a runtime target of 64 logical processors when MAXDOP option is set to 0, a different value can be manually set if needed. クエリおよびインデックスに対して MAXDOP を 0 に設定すると、並列プランの実行で指定されたクエリまたはインデックスに対して SQL ServerSQL Server で利用可能なすべてのプロセッサ (最大 64) を使用できます。Setting MAXDOP to 0 for queries and indexes allows SQL ServerSQL Server to use all available processors up to a maximum of 64 processors for the given queries or indexes in a parallel plan execution. MAXDOP はあらゆる並列クエリに強制される値ではなく、並列に望ましいあらゆるクエリにとっての仮のターゲットです。MAXDOP is not an enforced value for all parallel queries, but rather a tentative target for all queries eligible for parallelism. つまり、実行時に十分なワーカー スレッドが利用できない場合、MAXDOP サーバー構成オプションより低い並列度でクエリが実行されることがあります。This means that if not enough worker threads are available at runtime, a query may execute with a lower degree of parallelism than the MAXDOP server configuration option.

MAXDOP 構成のベスト プラクティスについては、この Microsoft サポート記事を参照してください。Refer to this Microsoft Support Article for best practices on configuring MAXDOP.

並列クエリの例Parallel Query Example

次のクエリでは、2000 年 4 月 1 日を開始日とする特定の四半期に受けた注文のうち、納品期日を過ぎた項目を 1 つ以上含む注文の件数を数えます。The following query counts the number of orders placed in a specific quarter, starting on April 1, 2000, and in which at least one line item of the order was received by the customer later than the committed date. さらに、該当する注文を受注優先度別にグループ分けし、受注優先度の昇順に並べ替えてその注文数を一覧表示します。This query lists the count of such orders grouped by each order priority and sorted in ascending priority order.

この例には、架空のテーブル名と列名を使用しています。This example uses theoretical table and column names.

SELECT o_orderpriority, COUNT(*) AS Order_Count
FROM orders
WHERE o_orderdate >= '2000/04/01'
   AND o_orderdate < DATEADD (mm, 3, '2000/04/01')
   AND EXISTS
         (
          SELECT *
            FROM    lineitem
            WHERE l_orderkey = o_orderkey
               AND l_commitdate < l_receiptdate
         )
   GROUP BY o_orderpriority
   ORDER BY o_orderpriority

lineitem テーブルと orders テーブルで次のインデックスが定義されていると想定します。Assume the following indexes are defined on the lineitem and orders tables:

CREATE INDEX l_order_dates_idx 
   ON lineitem
      (l_orderkey, l_receiptdate, l_commitdate, l_shipdate)

CREATE UNIQUE INDEX o_datkeyopr_idx
   ON ORDERS
      (o_orderdate, o_orderkey, o_custkey, o_orderpriority)

上記のクエリに対して生成される並列プランの例を次に示します。Here is one possible parallel plan generated for the query previously shown:

|--Stream Aggregate(GROUP BY:([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=COUNT(*)))
    |--Parallelism(Gather Streams, ORDER BY:
                  ([ORDERS].[o_orderpriority] ASC))
         |--Stream Aggregate(GROUP BY:
                  ([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=Count(*)))
              |--Sort(ORDER BY:([ORDERS].[o_orderpriority] ASC))
                   |--Merge Join(Left Semi Join, MERGE:
                  ([ORDERS].[o_orderkey])=
                        ([LINEITEM].[l_orderkey]),
                  RESIDUAL:([ORDERS].[o_orderkey]=
                        [LINEITEM].[l_orderkey]))
                        |--Sort(ORDER BY:([ORDERS].[o_orderkey] ASC))
                        |    |--Parallelism(Repartition Streams,
                           PARTITION COLUMNS:
                           ([ORDERS].[o_orderkey]))
                        |         |--Index Seek(OBJECT:
                     ([tpcd1G].[dbo].[ORDERS].[O_DATKEYOPR_IDX]),
                     SEEK:([ORDERS].[o_orderdate] >=
                           Apr  1 2000 12:00AM AND
                           [ORDERS].[o_orderdate] <
                           Jul  1 2000 12:00AM) ORDERED)
                        |--Parallelism(Repartition Streams,
                     PARTITION COLUMNS:
                     ([LINEITEM].[l_orderkey]),
                     ORDER BY:([LINEITEM].[l_orderkey] ASC))
                             |--Filter(WHERE:
                           ([LINEITEM].[l_commitdate]<
                           [LINEITEM].[l_receiptdate]))
                                  |--Index Scan(OBJECT:
         ([tpcd1G].[dbo].[LINEITEM].[L_ORDER_DATES_IDX]), ORDERED)

以下の図は、並列処理の次数が 4 で実行され、2 つのテーブルが結合されているクエリ プランを示しています。The illustration below shows a query plan executed with a degree of parallelism equal to 4 and involving a two-table join.

parallel_plan

並列プランには、3 つの並列処理操作が含まれています。The parallel plan contains three parallelism operators. o_datkey_ptr インデックスの Index Seek 操作と l_order_dates_idx インデックスの Index Scan 操作が並列で実行されます。Both the Index Seek operator of the o_datkey_ptr index and the Index Scan operator of the l_order_dates_idx index are performed in parallel. これにより、複数の排他ストリームが生成されます。This produces several exclusive streams. この処理は、Index Scan 操作と Index Seek 操作の上位にあり最も近い Parallelism 操作からそれぞれ決定できます。This can be determined from the nearest Parallelism operators above the Index Scan and Index Seek operators, respectively. どちらの操作もストリームを再分割し、交換の種類を決定しています。Both are repartitioning the type of exchange. つまり、単にストリーム間でデータを再移動して、入力時と同じ数のストリームを出力時に生成しているということです。That is, they are just reshuffling data among the streams and producing the same number of streams on their output as they have on their input. このストリーム数は並列処理の次数と同じになります。This number of streams is equal to the degree of parallelism.

l_order_dates_idx Index Scan 操作の上位にある並列操作は、L_ORDERKEY の値を使用して入力ストリームを再分割します。The parallelism operator above the l_order_dates_idx Index Scan operator is repartitioning its input streams using the value of L_ORDERKEY as a key. このようにして、同じ L_ORDERKEY の値が同じ出力ストリームで終了します。In this way, the same values of L_ORDERKEY end up in the same output stream. 同時に、出力ストリームは L_ORDERKEY 列の順序を維持し、Merge Join 操作の入力要件を満たします。At the same time, output streams maintain the order on the L_ORDERKEY column to meet the input requirement of the Merge Join operator.

Index Seek 操作の上位にある並列操作は、O_ORDERKEY の値を使用して入力ストリームを再分割します。The parallelism operator above the Index Seek operator is repartitioning its input streams using the value of O_ORDERKEY. この入力は O_ORDERKEY 列の値で並べ替えられておらず、Merge Join 操作の結合列であるので、並列操作と Merge Join 操作の間の Sort 操作により、Merge Join 操作のために入力を結合列で並べ替えています。Because its input is not sorted on the O_ORDERKEY column values and this is the join column in the Merge Join operator, the Sort operator between the parallelism and Merge Join operators make sure that the input is sorted for the Merge Join operator on the join columns. Sort 操作は、Merge Join 操作と同様に並列処理されます。The Sort operator, like the Merge Join operator, is performed in parallel.

最上位にある並列操作は、複数のストリームから得た結果を 1 つのストリームに集めます。The topmost parallelism operator gathers results from several streams into a single stream. この並列操作の下位にある Stream Aggregate 操作で実行された部分集計は、並列操作の上位にある Stream Aggregate 操作の O_ORDERPRIORITY の異なる値ごとに 1 つの SUM 値に累計されます。Partial aggregations performed by the Stream Aggregate operator below the parallelism operator are then accumulated into a single SUM value for each different value of the O_ORDERPRIORITY in the Stream Aggregate operator above the parallelism operator. このプランには並列処理の次数が 4 に設定された 2 つの交換セグメントが含まれているため、8 個のワーカー スレッドが使用されます。Because this plan has two exchange segments, with degree of parallelism equal to 4, it uses eight worker threads.

この例で使用されている演算子の詳細については、「Showplan Logical and Physical Operators Reference」 (プラン表示の論理および物理演算子のリファレンス) を参照してください。For more information on the operators used in this example, refer to the Showplan Logical and Physical Operators Reference.

並列インデックス操作Parallel Index Operations

インデックスの作成や再構築、クラスター化インデックスの削除などのインデックス操作用に構築されたクエリ プランでは、複数のマイクロプロセッサを備えたコンピューターでの並列マルチワーカー スレッド操作が可能になります。The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-worker threaded operations on computers that have multiple microprocessors.

注意

並列インデックス操作は、SQL Server 2008SQL Server 2008 より、Enterprise Edition でのみ使用できます。Parallel index operations are only available in Enterprise Edition, starting with SQL Server 2008SQL Server 2008.

SQL ServerSQL Server では、インデックス操作の並列処理の限度 (実行に使用する個別ワーカー スレッドの合計数) を決める際に、他のクエリに行うのと同じアルゴリズムが使用されます。uses the same algorithms to determine the degree of parallelism (the total number of separate worker threads to run) for index operations as it does for other queries. インデックス操作に必要な並列処理の最大限度は、 max degree of parallelism サーバー構成オプションによって決まります。The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. CREATE INDEX、ALTER INDEX、DROP INDEX、および ALTER TABLE の各ステートメントで MAXDOP インデックス オプションを設定することにより、個別のインデックス操作の max degree of parallelism 値をオーバーライドできます。You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.

SQL Server データベース エンジンSQL Server Database Engineによってインデックス実行プランが構築される際に、並列操作の数は、次のうち最も低い値に設定されます。When the SQL Server データベース エンジンSQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

  • コンピューター内のマイクロプロセッサ (CPU) の数。The number of microprocessors, or CPUs in the computer.
  • max degree of parallelism サーバー構成オプションで指定されている数。The number specified in the max degree of parallelism server configuration option.
  • SQL ServerSQL Server のワーカー スレッド用に実行される作業のしきい値以下の CPU の数。The number of CPUs not already over a threshold of work performed for SQL ServerSQL Server worker threads.

たとえば、CPU が 8 基ある場合でも、max degree of parallelism が 6 に設定されているコンピューターでは、インデックス操作用に生成される並列ワーカー スレッドの数は最大 6 つです。For example, on a computer that has eight CPUs, but where max degree of parallelism is set to 6, no more than six parallel worker threads are generated for an index operation. インデックス実行プランを構築するとき、コンピューター内の 5 基の CPU が SQL ServerSQL Server の作業のしきい値を超えている場合は、実行プランによって指定される並列ワーカー スレッドの数は 3 つだけになります。If five of the CPUs in the computer exceed the threshold of SQL ServerSQL Server work when an index execution plan is built, the execution plan specifies only three parallel worker threads.

並列インデックス操作の主なフェーズには、次のフェーズがあります。The main phases of a parallel index operation include the following:

  • 調整ワーカー スレッドがテーブルを迅速かつランダムにスキャンし、インデックス キーの分布を算出します。A coordinating worker thread quickly and randomly scans the table to estimate the distribution of the index keys. 調整ワーカー スレッドが、並列操作の限度と等しい数のキー範囲を作成するキーの境界を確立します。それぞれのキー範囲は、同じ数の行を処理対象として算出されます。The coordinating worker thread establishes the key boundaries that will create a number of key ranges equal to the degree of parallel operations, where each key range is estimated to cover similar numbers of rows. たとえば、テーブル内に 400 万行あって並列処理の限度が 4 の場合、調整ワーカー スレッドは 1 セット 100 万行で 4 セットの行に区切るキー値を決定します。For example, if there are four million rows in the table and the degree of parallelism is 4, the coordinating worker thread will determine the key values that delimit four sets of rows with 1 million rows in each set. すべての CPU を使用するために十分なキー範囲を確立できない場合、並列処理の限度が適宜減少されます。If enough key ranges cannot be established to use all CPUs, the degree of parallelism is reduced accordingly.
  • 調整ワーカー スレッドは、並列操作の限度と等しい数のワーカー スレッドを優先して、それらのワーカー スレッドの作業が完了するまで待ちます。The coordinating worker thread dispatches a number of worker threads equal to the degree of parallel operations and waits for these worker threads to complete their work. 各ワーカー スレッドは、ワーカー スレッドに割り当てられた範囲内のキー値を持つ行だけを取得するフィルターを使用して、ベース テーブルをスキャンします。Each worker thread scans the base table using a filter that retrieves only rows with key values within the range assigned to the worker thread. 各ワーカー スレッドは、そのキーの範囲内で行のインデックス構造を構築します。Each worker thread builds an index structure for the rows in its key range. パーティション インデックスの場合、各ワーカー スレッドで指定した数のパーティションが構築されます。In the case of a partitioned index, each worker thread builds a specified number of partitions. パーティションはワーカー スレッド間では共有されません。Partitions are not shared among worker threads.
  • すべての並列ワーカー スレッドが完了した後で、調整ワーカー スレッドはインデックスのサブユニットを単一のインデックスに接続します。After all the parallel worker threads have completed, the coordinating worker thread connects the index subunits into a single index. このフェーズは、オフライン インデックス操作にのみ適用されます。This phase applies only to offline index operations.

個別の CREATE TABLE ステートメントまたは ALTER TABLE ステートメントには、インデックスの作成を必要とする複数の制約が課される場合があります。Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require that an index be created. これらの複数のインデックス作成操作は連続して実行されますが、個別のインデックス作成操作は複数の CPU を備えたコンピューターでは並列操作になることがあります。These multiple index creation operations are performed in series, although each individual index creation operation may be a parallel operation on a computer that has multiple CPUs.

分散クエリ アーキテクチャDistributed Query Architecture

Microsoft SQL ServerSQL Server では、Transact-SQLTransact-SQL ステートメント内で異種 OLE DB データ ソースを参照する方法として、次の 2 つがサポートされています。Microsoft SQL ServerSQL Server supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQLTransact-SQL statements:

  • リンク サーバー名Linked server names
    sp_addlinkedserversp_addlinkedsrvlogin の各システム ストアド プロシージャを使用して、OLE DB データ ソースにサーバー名を渡します。The system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to give a server name to an OLE DB data source. リンク サーバー内のオブジェクトは、4 部構成の名前を使用して Transact-SQLTransact-SQL ステートメント内で参照できます。Objects in these linked servers can be referenced in Transact-SQLTransact-SQL statements using four-part names. たとえば、DeptSQLSrvr というリンク サーバー名が SQL ServerSQL Server の別のインスタンスで定義されている場合、次のステートメントはこのサーバー上のテーブルを参照します。For example, if a linked server name of DeptSQLSrvr is defined against another instance of SQL ServerSQL Server, the following statement references a table on that server:

    SELECT JobTitle, HireDate 
    FROM DeptSQLSrvr.AdventureWorks2014.HumanResources.Employee;
    

    リンク サーバー名を OPENQUERY ステートメントで指定して、OLE DB データ ソースの行セットを開くこともできます。The linked server name can also be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. この行セットは、Transact-SQLTransact-SQL ステートメント内でテーブルと同じように参照できます。This rowset can then be referenced like a table in Transact-SQLTransact-SQL statements.

  • アドホック コネクタ名Ad hoc connector names
    データ ソースを頻繁に参照しない場合は、リンク サーバーに接続するために必要な情報を含めた OPENROWSET 関数または OPENDATASOURCE 関数を指定します。For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the linked server. これにより、行セットは Transact-SQLTransact-SQL ステートメント内でテーブルと同じように参照できます。The rowset can then be referenced the same way a table is referenced in Transact-SQLTransact-SQL statements:

    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
          'c:\MSOffice\Access\Samples\Northwind.mdb';'Admin';'';
          Employees);
    

SQL ServerSQL Server では、OLE DB を使用してリレーショナル エンジンとストレージ エンジンの間の通信を行います。uses OLE DB to communicate between the relational engine and the storage engine. リレーショナル エンジンは、各 Transact-SQLTransact-SQL ステートメントを一連の操作に分割します。それぞれの操作は、ストレージ エンジンがベース テーブルから開いた単純な OLE DB 行セットに対する操作です。The relational engine breaks down each Transact-SQLTransact-SQL statement into a series of operations on simple OLE DB rowsets opened by the storage engine from the base tables. これは、リレーショナル エンジンは、OLE DB データ ソースに対する単純な OLE DB 行セットも開くことができることを意味しています。This means the relational engine can also open simple OLE DB rowsets on any OLE DB data source.
oledb_storageoledb_storage
リレーショナル エンジンは OLE DB API (アプリケーション プログラミング インターフェイス) を使用して、リンク サーバー上の行セットを開き、行をフェッチし、トランザクションを管理します。The relational engine uses the OLE DB application programming interface (API) to open the rowsets on linked servers, fetch the rows, and manage transactions.

OLE DB データ ソースにリンク サーバーとしてアクセスするには、SQL ServerSQL Server が動作するサーバー上に OLE DB プロバイダーが存在している必要があります。For each OLE DB data source accessed as a linked server, an OLE DB provider must be present on the server running SQL ServerSQL Server. OLE DB データ ソースに対して使用できる Transact-SQLTransact-SQL 操作の数は、OLE DB プロバイダーの機能によって決まります。The set of Transact-SQLTransact-SQL operations that can be used against a specific OLE DB data source depends on the capabilities of the OLE DB provider.

たとえば、sysadmin 固定サーバー ロールのメンバーは、SQL ServerSQL Server のインスタンスごとに SQL ServerSQL Server DisallowAdhocAccess プロパティを使用して、OLE DB プロバイダーのアドホック コネクタ名の使用を有効化または無効化できます。For each instance of SQL ServerSQL Server, members of the sysadmin fixed server role can enable or disable the use of ad-hoc connector names for an OLE DB provider using the SQL ServerSQL Server DisallowAdhocAccess property. アドホック アクセスが有効化されると、そのインスタンスにログオンしているユーザーは、アドホック コネクタ名を含んだ Transact-SQLTransact-SQL ステートメントを実行し、OLE DB プロバイダーを使用してアクセスできるネットワークのすべてのデータ ソースを参照できます。When ad-hoc access is enabled, any user logged on to that instance can execute Transact-SQLTransact-SQL statements containing ad-hoc connector names, referencing any data source on the network that can be accessed using that OLE DB provider. sysadmin ロールのメンバーは、データ ソースへのアクセスを制御するために、その OLE DB プロバイダーに対するアドホック アクセスを無効化できます。その結果、ユーザーは管理者が定義したリンク サーバー名で参照されるデータ ソースのみに制限されます。To control access to data sources, members of the sysadmin role can disable ad-hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. 既定では、SQL ServerSQL Server OLE DB プロバイダーではアドホック アクセスが有効化されており、他のすべての OLE DB プロバイダーでは無効化されています。By default, ad-hoc access is enabled for the SQL ServerSQL Server OLE DB provider, and disabled for all other OLE DB providers.

分散クエリを使用すると、SQL ServerSQL Server サービスを実行している Microsoft Windows アカウントのセキュリティ コンテキストを使用して、他のデータ ソース (たとえば、ファイルや Active Directory などのリレーショナルでないデータ ソース) にアクセスできます。Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory, and so on) using the security context of the Microsoft Windows account under which the SQL ServerSQL Server service is running. SQL ServerSQL Server は Windows ログインでは正常にログインを借用できますが、SQL ServerSQL Server ログインでは借用できません。impersonates the login appropriately for Windows logins; however, that is not possible for SQL ServerSQL Server logins. この場合、SQL ServerSQL Server サービスを実行しているアカウントに権限があり、分散クエリ ユーザーには権限のない他のデータ ソースに、分散クエリ ユーザーからのアクセスを許してしまう可能性が生じます。This can potentially allow a distributed query user to access another data source for which they do not have permissions, but the account under which the SQL ServerSQL Server service is running does have permissions. 対応するリンク サーバーにアクセスする権限のある特定のログインを定義するには、 sp_addlinkedsrvlogin を使用します。Use sp_addlinkedsrvlogin to define the specific logins that are authorized to access the corresponding linked server. このような制御は、アドホック名では使用できないので、OLE DB プロバイダーでアドホック アクセスを有効にする場合は十分に注意してください。This control is not available for ad-hoc names, so use caution in enabling an OLE DB provider for ad-hoc access.

SQL ServerSQL Server は、可能であれば、OLE DB データ ソースに対して、結合、条件、射影、並べ替え、および GROUP BY (SQL 言語) 操作などのリレーショナル操作を試行します。When possible, SQL ServerSQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB data source. 特に指定しない限り、SQL ServerSQL Server 自身がベース テーブルをスキャンして SQL ServerSQL Server に読み込んだり、リレーショナル操作を実行することはありません。SQL ServerSQL Server does not default to scanning the base table into SQL ServerSQL Server and performing the relational operations itself. SQL ServerSQL Server は OLE DB プロバイダーに問い合わせて、そのプロバイダーがサポートする SQL 文法のレベルを判別し、その情報に基づいて、可能な限り多くのリレーショナル操作をプロバイダーに送ります。queries the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, pushes as many relational operations as possible to the provider.

SQL ServerSQL Server では、OLE DB プロバイダーのメカニズムを指定して、OLE DB データ ソース内でのキー値の分布状況を示す統計を返すようにします。specifies a mechanism for an OLE DB provider to return statistics indicating how key values are distributed within the OLE DB data source. これにより、各 Transact-SQLTransact-SQL ステートメントの必要条件に対して、SQL ServerSQL Server クエリ オプティマイザーがデータ ソース内のデータのパターンをより正確に分析できるようになり、最適な実行プランを作成するクエリ オプティマイザーの機能が向上します。This lets the SQL ServerSQL Server Query Optimizer better analyze the pattern of data in the data source against the requirements of each Transact-SQLTransact-SQL statement, increasing the ability of the Query Optimizer to generate optimal execution plans.

パーティション テーブルとパーティション インデックスに対するクエリ処理の機能強化Query Processing Enhancements on Partitioned Tables and Indexes

SQL Server 2008SQL Server 2008 では、多くの並列プランでのパーティション テーブルに対するクエリ処理のパフォーマンスが向上しています。また、並列プランと直列プランを表す方法が変更され、コンパイル時と実行時の両方の実行プランで示されるパーティション分割情報が強化されています。improved query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhanced the partitioning information provided in both compile-time and run-time execution plans. このトピックでは、これらの機能強化について説明します。また、パーティション テーブルとパーティション インデックスのクエリ実行プランを解釈する方法、およびパーティション分割されたオブジェクトに対するクエリのパフォーマンス向上に関するベスト プラクティスについて説明します。This topic describes these improvements, provides guidance on how to interpret the query execution plans of partitioned tables and indexes, and provides best practices for improving query performance on partitioned objects.

注意

パーティション テーブルとパーティション インデックスは、SQL ServerSQL Server Enterprise Edition、Developer Edition、および Evaluation Edition でのみサポートされます。Partitioned tables and indexes are supported only in the SQL ServerSQL Server Enterprise, Developer, and Evaluation editions.

新しいパーティション対応のシーク操作New Partition-Aware Seek Operation

SQL ServerSQL Server ではパーティション テーブルの内部表現が変更され、テーブルはクエリ プロセッサに対して、先頭列に PartitionID を持つ複数列インデックスとして表されます。In SQL ServerSQL Server, the internal representation of a partitioned table is changed so that the table appears to the query processor to be a multicolumn index with PartitionID as the leading column. PartitionID は、特定の行を含むパーティションの ID を表すために内部的に使用される非表示の計算列です。PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row. たとえば、 T(a, b, c)として定義されているテーブル T が列 a でパーティション分割され、列 b にクラスター化インデックスがあるとします。For example, assume the table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. SQL ServerSQL Server では、このパーティション テーブルは内部的に、スキーマが T(PartitionID, a, b, c) で複合キー (PartitionID, b) にクラスター化インデックスがある非パーティション テーブルとして扱われます。In SQL ServerSQL Server, this partitioned table is treated internally as a nonpartitioned table with the schema T(PartitionID, a, b, c) and a clustered index on the composite key (PartitionID, b). これにより、クエリ オプティマイザーは、パーティション テーブルまたはパーティション インデックスの PartitionID に基づいてシーク操作を実行できます。This allows the Query Optimizer to perform seek operations based on PartitionID on any partitioned table or index.

パーティションの解消は、このシーク操作で行われるようになりました。Partition elimination is now done in this seek operation.

In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (論理的な先頭列) およびその他のインデックス キー列に基づいて実行し、続いて 2 番目のレベルのシークを実行できます。このシークは最初のレベルのシーク操作の条件を満たす値ごとに、別の条件を指定した 1 つ以上の追加の列に基づいて実行できます。In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (as the logical leading column) and possibly other index key columns, and then a second-level seek, with a different condition, can be done on one or more additional columns, for each distinct value that meets the qualification for the first-level seek operation. つまり、スキップ スキャンと呼ばれるこの操作によって、クエリ オプティマイザーは、ある条件に基づいてシーク操作またはスキャン操作を実行してアクセス対象のパーティションを特定し、その操作内で 2 番目のレベルのインデックスのシーク操作を実行し、特定済みのパーティションのうち別の条件を満たすパーティションから行を返すことができます。That is, this operation, called a skip scan, allows the Query Optimizer to perform a seek or scan operation based on one condition to determine the partitions to be accessed and a second-level index seek operation within that operator to return rows from these partitions that meet a different condition. たとえば、次のクエリについて考えてみます。For example, consider the following query.

SELECT * FROM T WHERE a < 10 and b = 2;

この例では、 T(a, b, c)として定義されているテーブル T が列 a でパーティション分割され、列 b にクラスター化インデックスがあるとします。For this example, assume that table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. テーブル T のパーティション境界は、次のパーティション関数によって定義されます。The partition boundaries for table T are defined by the following partition function:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

クエリを解決するために、クエリ プロセッサによって最初のレベルのシーク操作が実行され、条件 T.a < 10を満たす行を含むすべてのパーティションが検索されます。To solve the query, the query processor performs a first-level seek operation to find every partition that contains rows that meet the condition T.a < 10. これにより、アクセス対象のパーティションが特定されます。This identifies the partitions to be accessed. 特定された各パーティション内で、プロセッサによって列 b のクラスター化インデックスに対する 2 番目のレベルのシーク操作が実行され、条件 T.b = 2 および T.a < 10を満たす行が検索されます。Within each partition identified, the processor then performs a second-level seek into the clustered index on column b to find the rows that meet the condition T.b = 2 and T.a < 10.

次の図は、スキップ スキャン操作を論理的に表したものです。The following illustration is a logical representation of the skip scan operation. T および a にデータが格納されているテーブル b を示しています。It shows table T with data in columns a and b. パーティションには 1 ~ 4 の番号が割り当てられており、パーティション境界は破線の縦線で表示されています。The partitions are numbered 1 through 4 with the partition boundaries shown by dashed vertical lines. パーティションに対する最初のレベルのシーク操作 (図には示されていません) で、パーティション 1、2、および 3 が、テーブルに対して定義されたパーティション分割および列 a の述語で暗黙的に指定された Seek 条件を満たすことが特定されています。A first-level seek operation to the partitions (not shown in the illustration) has determined that partitions 1, 2, and 3 meet the seek condition implied by the partitioning defined for the table and the predicate on column a. つまり、条件 T.a < 10を満たします。That is, T.a < 10. スキップ スキャン操作の 2 番目のレベルの Seek 部分でスキャンされるパスは、曲線で表示されています。The path traversed by the second-level seek portion of the skip scan operation is illustrated by the curved line. 基本的に、スキップ スキャン操作によって、前の各パーティションで条件 b = 2を満たす行がシークされます。Essentially, the skip scan operation seeks into each of these partitions for rows that meet the condition b = 2. スキップ スキャン操作の総コストは、3 つの個別のインデックス シークの総コストと同じです。The total cost of the skip scan operation is the same as that of three separate index seeks.

skip_scan

クエリ実行プランのパーティション分割情報の表示Displaying Partitioning Information in Query Execution Plans

パーティション テーブルとパーティション インデックスに対するクエリの実行プランは、Transact-SQLTransact-SQL SET ステートメントの SET SHOWPLAN_XML または SET STATISTICS XML を使用するか、SQL ServerSQL Server Management Studio のグラフィカル実行プラン出力を使用して調べることができます。The execution plans of queries on partitioned tables and indexes can be examined by using the Transact-SQLTransact-SQL SET statements SET SHOWPLAN_XML or SET STATISTICS XML, or by using the graphical execution plan output in SQL ServerSQL Server Management Studio. たとえば、コンパイル時の実行プランを表示するにはクエリ エディターのツール バーの [推定実行プランの表示] をクリックし、実行時のプランを表示するには [実際の実行プランを含める] をクリックします。For example, you can display the compile-time execution plan by clicking Display Estimated Execution Plan on the Query Editor toolbar and the run-time plan by clicking Include Actual Execution Plan.

これらのツールを使用すると、次の情報を確認できます。Using these tools, you can ascertain the following information:

  • パーティション テーブルまたはパーティション インデックスにアクセスする scansseeksinsertsupdatesmergesdeletes などの操作。The operations such as scans, seeks, inserts, updates, merges, and deletes that access partitioned tables or indexes.
  • クエリによってアクセスされるパーティション。The partitions accessed by the query. たとえば、実行時の実行プランでは、アクセスされるパーティションの総数やアクセスされる連続したパーティションの範囲を確認できます。For example, the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans.
  • シーク操作またはスキャン操作でスキップ スキャン操作が使用され、1 つ以上のパーティションからデータが取得されるタイミング。When the skip scan operation is used in a seek or scan operation to retrieve data from one or more partitions.

パーティション情報に関する機能強化Partition Information Enhancements

SQL ServerSQL Server では、コンパイル時と実行時の両方の実行プランのパーティション分割情報が強化されています。provides enhanced partitioning information for both compile-time and run-time execution plans. 実行プランで次の情報が示されるようになりました。Execution plans now provide the following information:

  • Partitionedseekscaninsertupdatemergeなどの操作がパーティション テーブルに対して実行されることを示す delete属性 (オプション)。An optional Partitioned attribute that indicates that an operator, such as a seek, scan, insert, update, merge, or delete, is performed on a partitioned table.
  • 先頭のインデックス キー列として SeekPredicateNew を含み、 SeekKeys に対して範囲シークを指定するフィルター条件を含む PartitionID サブ要素を持つ新しい PartitionID要素。A new SeekPredicateNew element with a SeekKeys subelement that includes PartitionID as the leading index key column and filter conditions that specify range seeks on PartitionID. 2 つの SeekKeys サブ要素が存在する場合は、 PartitionID に対するスキップ スキャン操作が使用されることを示しています。The presence of two SeekKeys subelements indicates that a skip scan operation on PartitionID is used.
  • アクセスされるパーティションの総数を示す概要情報。Summary information that provides a total count of the partitions accessed. この情報は、実行時のプランでのみ確認できます。This information is available only in run-time plans.

この情報をグラフィカル実行プラン出力と XML プラン表示出力の両方で表示する方法の説明のために、パーティション テーブル fact_salesに対する次のクエリについて考えます。To demonstrate how this information is displayed in both the graphical execution plan output and the XML Showplan output, consider the following query on the partitioned table fact_sales. このクエリでは、2 つのパーティションのデータが更新されます。This query updates data in two partitions.

UPDATE fact_sales
SET quantity = quantity * 2
WHERE date_id BETWEEN 20080802 AND 20080902;

次の図は、このクエリのコンパイル時の実行プランにおける Clustered Index Seek 操作のプロパティを示しています。The following illustration shows the properties of the Clustered Index Seek operator in the compile-time execution plan for this query. fact_sales テーブルの定義およびパーティション定義を確認するには、このトピックの「例」を参照してください。To view the definition of the fact_sales table and the partition definition, see "Example" in this topic.

clustered_index_seek

Partitioned 属性Partitioned Attribute

Index Seek などの操作がパーティション テーブルまたはパーティション インデックスに対して実行される場合、コンパイル時のプランと実行時のプランに Partitioned 属性が表示され、 True (1) に設定されます。When an operator such as an Index Seek is executed on a partitioned table or index, the Partitioned attribute appears in the compile-time and run-time plan and is set to True (1). False (0) に設定された場合、この属性は表示されません。The attribute does not display when it is set to False (0).

Partitioned 属性は、次の物理操作と論理操作で表示されます。The Partitioned attribute can appear in the following physical and logical operators:

  • Table Scan
  • Index Scan
  • Index Seek
  • Insert
  • Update
  • Delete
  • Merge

前の図に示したように、この属性は、属性が定義されている操作のプロパティに表示されます。As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. XML プラン表示出力では、この属性は、属性が定義されている操作の Partitioned="1" ノードに RelOp として表示されます。In the XML Showplan output, this attribute appears as Partitioned="1" in the RelOp node of the operator in which it is defined.

新しい Seek 述語New Seek Predicate

XML プラン表示出力では、 SeekPredicateNew 要素がその要素を定義している操作に表示されます。In XML Showplan output, the SeekPredicateNew element appears in the operator in which it is defined. この要素には、 SeekKeys サブ要素が最大 2 つ含まれることがあります。It can contain up to two occurrences of the SeekKeys sub-element. 最初の SeekKeys アイテムは、論理インデックスのパーティション ID レベルで最初のレベルのシーク操作を指定します。The first SeekKeys item specifies the first-level seek operation at the partition ID level of the logical index. つまり、この Seek によって、クエリの条件を満たすためにアクセスする必要があるパーティションが特定されます。That is, this seek determines the partitions that must be accessed to satisfy the conditions of the query. 2 番目の SeekKeys アイテムは、最初のレベルの Seek で特定された各パーティション内で行われるスキップ スキャン操作の 2 番目のレベルの Seek 部分を指定します。The second SeekKeys item specifies the second-level seek portion of the skip scan operation that occurs within each partition identified in the first-level seek.

パーティションの概要情報Partition Summary Information

実行時の実行プランでは、パーティションの概要情報に、アクセスしたパーティションの数やアクセスした実際のパーティションの ID が示されます。In run-time execution plans, partition summary information provides a count of the partitions accessed and the identity of the actual partitions accessed. この情報を使用して、クエリで正しいパーティションにアクセスしているかどうか、および他のすべてのパーティションが考慮の対象から除外されているかどうかを確認できます。You can use this information to verify that the correct partitions are accessed in the query and that all other partitions are eliminated from consideration.

示される情報は、 Actual Partition CountPartitions Accessedです。The following information is provided: Actual Partition Count, and Partitions Accessed.

Actual Partition Count は、クエリでアクセスされるパーティションの総数です。Actual Partition Count is the total number of partitions accessed by the query.

Partitions Accessed(XML プラン表示出力内) は、新しい RuntimePartitionSummary 要素に表示されるパーティションの概要情報です。この要素は、要素が定義されている操作の RelOp ノードに表示されます。Partitions Accessed, in XML Showplan output, is the partition summary information that appears in the new RuntimePartitionSummary element in RelOp node of the operator in which it is defined. 次の例に、合計 2 つのパーティション (パーティション 2 および 3) にアクセスすることを示す RuntimePartitionSummary 要素の内容を示します。The following example shows the contents of the RuntimePartitionSummary element, indicating that two total partitions are accessed (partitions 2 and 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

プラン表示のその他のメソッドを使用したパーティション情報の表示Displaying Partition Information by Using Other Showplan Methods

SHOWPLAN_ALLSHOWPLAN_TEXTSTATISTICS PROFILE の各プラン表示のメソッドでは、このトピックで説明したパーティション情報はレポートされません。The Showplan methods SHOWPLAN_ALL, SHOWPLAN_TEXT, and STATISTICS PROFILE do not report the partition information described in this topic, with the following exception. ただし、例外として、アクセスされるパーティションが、 SEEK 述語の一部としてパーティション ID を表す計算列の範囲述語によって特定されます。As part of the SEEK predicate, the partitions to be accessed are identified by a range predicate on the computed column representing the partition ID. 次の例に、 SEEK 操作の Clustered Index Seek 述語を示します。The following example shows the SEEK predicate for a Clustered Index Seek operator. パーティション 2 および 3 にアクセスし、シーク オペレーターによって条件 date_id BETWEEN 20080802 AND 20080902を満たす行がフィルター選択されます。Partitions 2 and 3 are accessed, and the seek operator filters on the rows that meet the condition date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]), 

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] \<= (3) 

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802) 

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902)) 

                ORDERED FORWARD)

パーティション分割されたヒープの実行プランの解釈Interpreting Execution Plans for Partitioned Heaps

パーティション分割されたヒープは、パーティション ID の論理インデックスとして扱われます。A partitioned heap is treated as a logical index on the partition ID. パーティション分割されたヒープのパーティションの解消は、実行プランでは、 Table Scan 述語がパーティション ID に対して指定された SEEK 操作として表されます。Partition elimination on a partitioned heap is represented in an execution plan as a Table Scan operator with a SEEK predicate on partition ID. 次の例は、プラン表示情報を示しています。The following example shows the Showplan information provided:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

併置結合の実行プランの解釈Interpreting Execution Plans for Collocated Joins

結合のコロケーションは、同じパーティション関数または同等のパーティション関数を使用して 2 つのテーブルをパーティション分割し、結合の両側のパーティション列をクエリの結合条件に指定すると発生します。Join collocation can occur when two tables are partitioned using the same or equivalent partitioning function and the partitioning columns from both sides of the join are specified in the join condition of the query. クエリ オプティマイザーでは、同じパーティション ID を持つ各テーブルのパーティションが個別に結合されるプランを生成できます。The Query Optimizer can generate a plan where the partitions of each table that have equal partition IDs are joined separately. 併置結合では必要なメモリが少なくなり、処理時間が短縮されることがあるので、この結合は非併置結合よりも処理が高速になる場合があります。Collocated joins can be faster than non-collocated joins because they can require less memory and processing time. クエリ オプティマイザーでは、コストの推定に基づいて、非併置プランまたは併置プランが選択されます。The Query Optimizer chooses a non-collocated plan or a collocated plan based on cost estimates.

併置プランでは、 Nested Loops 結合は内側の 1 つ以上の結合テーブルまたはインデックス パーティションを読み取ります。In a collocated plan, the Nested Loops join reads one or more joined table or index partitions from the inner side. Constant Scan 操作内の数値は、パーティション番号を表します。The numbers within the Constant Scan operators represent the partition numbers.

パーティション テーブルまたはパーティション インデックスに対して併置結合の並行プランが生成された場合、結合操作 Constant ScanNested Loops の間に Parallelism 操作が表れます。When parallel plans for collocated joins are generated for partitioned tables or indexes, a Parallelism operator appears between the Constant Scan and the Nested Loops join operators. その場合、結合外部にある複数のワーカー スレッドがそれぞれ異なるパーティションに対して読み取りや操作を行います。In this case, multiple worker threads on the outer side of the join each read and work on a different partition.

次の図は、併置結合の並列クエリ プランを示しています。The following illustration demonstrates a parallel query plan for a collocated join.
colocated_join

パーティション分割されたオブジェクトの並列クエリの実行方法Parallel Query Execution Strategy for Partitioned Objects

クエリ プロセッサは、パーティション分割されたオブジェクトから選択するクエリに対して並列実行を使用します。The query processor uses a parallel execution strategy for queries that select from partitioned objects. クエリ プロセッサでは、実行方法の一環として、クエリに必要なテーブル パーティションと、各パーティションに割り当てるワーカー スレッドの数を決定します。As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of worker threads to allocate to each partition. ほとんどの場合、クエリ プロセッサは、各パーティションに同数またはほぼ同数のワーカー スレッドを割り当て、パーティション全体でクエリを並列実行します。In most cases, the query processor allocates an equal or almost equal number of worker threads to each partition, and then executes the query in parallel across the partitions. 以降、ワーカー スレッドの割り当てについてさらに詳しく説明します。The following paragraphs explain worker thread allocation in greater detail.

ワーカー スレッド 1

ワーカー スレッドの数がパーティションの数よりも少ない場合、クエリ プロセッサが各ワーカー スレッドを別々のパーティションに割り当てると、最初に、ワーカー スレッドが割り当てられていないパーティションが 1 つ以上残ります。If the number of worker threads is less than the number of partitions, the query processor assigns each worker thread to a different partition, initially leaving one or more partitions without an assigned worker thread. パーティションでワーカー スレッドの実行が完了すると、クエリ プロセッサは、各パーティションに 1 つのワーカー スレッドが割り当てられるまで、そのワーカー スレッドを次のパーティションに割り当てます。When a worker thread finishes executing on a partition, the query processor assigns it to the next partition until each partition has been assigned a single worker thread. これが当てはまるのは、クエリ プロセッサがワーカー スレッドを他のパーティションに再割り当てする場合に限ります。This is the only case in which the query processor reallocates worker threads to other partitions.
終了後に再割り当てされたワーカー スレッド。Shows worker thread reassigned after it finishes. ワーカー スレッドの数とパーティションの数が同じ場合、クエリ プロセッサは各パーティションに 1 つのワーカー スレッドを割り当てます。If the number of worker threads is equal to the number of partitions, the query processor assigns one worker thread to each partition. ワーカー スレッドの終了時に、そのワーカー スレッドが別のパーティションに再割り当てされることはありません。When a worker thread finishes, it is not reallocated to another partition.

ワーカー スレッド 2

ワーカー スレッドの数がパーティションの数よりも多い場合、クエリ プロセッサは各パーティションに同じ数のワーカー スレッドを割り当てます。If the number of worker threads is greater than the number of partitions, the query processor allocates an equal number of worker threads to each partition. ワーカー スレッドの数がパーティションの数で割り切れないと、クエリ プロセッサは、使用できるすべてのワーカー スレッドを使用するために、一部のパーティションにワーカー スレッドを 1 つ多く割り当てます。If the number of worker threads is not an exact multiple of the number of partitions, the query processor allocates one additional worker thread to some partitions in order to use all of the available worker threads. パーティションが 1 つしかない場合は、すべてのワーカー スレッドがそのパーティションに割り当てられることに注意してください。Note that if there is only one partition, all worker threads will be assigned to that partition. 次の図では、4 個のパーティションと 14 個のワーカー スレッドがあります。In the diagram below, there are four partitions and 14 worker threads. 各パーティションには 3 つのワーカー スレッドが割り当てられ、さらに 2 つのパーティションには追加のワーカー スレッドが割り当てられて、合計で 14 ワーカー スレッドが割り当てられています。Each partition has 3 worker threads assigned, and two partitions have an additional worker thread, for a total of 14 worker thread assignments. ワーカー スレッドは、終了時に別のパーティションに再割り当てされることはありません。When a worker thread finishes, it is not reassigned to another partition.

ワーカー スレッド 3

上の例では簡単にワーカー スレッドを割り当てる方法を示していますが、実際の方法はより複雑で、クエリの実行中に発生する他の変数を使用します。Although the above examples suggest a straightforward way to allocate worker threads, the actual strategy is more complex and accounts for other variables that occur during query execution. たとえば、テーブルがパーティション分割され、その列 A にクラスター化インデックスが設定されている場合、クエリで述語句 WHERE A IN (13, 17, 25)を使用すると、クエリ プロセッサは、1 つ以上のワーカー スレッドを、各テーブル パーティションではなくワーカー スレッドの 3 つの各シーク値 (A=13、A=17、A=25) に割り当てます。For example, if the table is partitioned and has a clustered index on column A and a query has the predicate clause WHERE A IN (13, 17, 25), the query processor will allocate one or more worker threads to each of these three seek values (A=13, A=17, and A=25) instead of each table partition. これらの値を含むパーティションでクエリを実行すれば十分です。これらのシーク述語がすべて同じテーブル パーティションに含まれる場合は、すべてのワーカー スレッドが同じテーブル パーティションに割り当てられます。It is only necessary to execute the query in the partitions that contain these values, and if all of these seek predicates happen to be in the same table partition, all of the worker threads will be assigned to the same table partition.

別の例として、テーブルで列 A に境界点が (10、20、30) である 4 つのパーティションと、列 B にインデックスが設定されており、クエリに述語句 WHERE B IN (50, 100, 150)が含まれるとします。To take another example, suppose that the table has four partitions on column A with boundary points (10, 20, 30), an index on column B, and the query has a predicate clause WHERE B IN (50, 100, 150). テーブル パーティションは A の値に基づいているため、B の値はどのテーブル パーティションにも存在する可能性があります。Because the table partitions are based on the values of A, the values of B can occur in any of the table partitions. したがって、クエリ プロセッサは、4 つの各テーブル パーティションで、B の 3 つの値 (50、100、150) を検索します。Thus, the query processor will seek for each of the three values of B (50, 100, 150) in each of the four table partitions. クエリ プロセッサは、これらの 12 個の各クエリ スキャンを並列実行できるように、ワーカー スレッドを均等に割り当てます。The query processor will assign worker threads proportionately so that it can execute each of these 12 query scans in parallel.

列 A に基づいたテーブル パーティションTable partitions based on column A 各テーブル パーティションにおける列 B の検索Seeks for column B in each table partition
テーブル パーティション 1:A < 10Table Partition 1: A < 10 B=50、B=100、B=150B=50, B=100, B=150
テーブル パーティション 2:A >= 10 AND A < 20Table Partition 2: A >= 10 AND A < 20 B=50、B=100、B=150B=50, B=100, B=150
テーブル パーティション 3:A >= 20 AND A < 30Table Partition 3: A >= 20 AND A < 30 B=50、B=100、B=150B=50, B=100, B=150
テーブル パーティション 4:A >= 30Table Partition 4: A >= 30 B=50、B=100、B=150B=50, B=100, B=150

ベスト プラクティスBest Practices

大きなパーティション テーブルおよびパーティション インデックスの大量のデータにアクセスするクエリのパフォーマンスを向上するために推奨するベスト プラクティスを次に示します。To improve the performance of queries that access a large amount of data from large partitioned tables and indexes, we recommend the following best practices:

  • 各パーティションを多くのディスクにわたってストライピングします。Stripe each partition across many disks. これは、回転ディスクを使用する場合に特に関連性が高くなります。This is especially relevant when using spinning disks.
  • 可能な場合は、アクセス頻度の高いパーティションまたはすべてのパーティションを保持できる十分なメイン メモリがあるサーバーを使用して、I/O コストを軽減します。When possible, use a server with enough main memory to fit frequently accessed partitions or all partitions in memory to reduce I/O cost.
  • クエリ対象のデータがメモリ内に収まらない場合は、テーブルおよびインデックスを圧縮します。If the data you query will not fit in memory, compress the tables and indexes. これにより、I/O コストを軽減します。This will reduce I/O cost.
  • 高速なプロセッサおよびできるだけ多くのプロセッサ コアを搭載したサーバーを使用して、並列クエリ処理機能を活用します。Use a server with fast processors and as many processor cores as you can afford, to take advantage of parallel query processing capability.
  • サーバーに十分な I/O コントローラーの帯域幅があることを確認します。Ensure the server has sufficient I/O controller bandwidth.
  • すべての大きなパーティション テーブルにクラスター化インデックスを作成して、B ツリーのスキャンの最適化を活用します。Create a clustered index on every large partitioned table to take advantage of B-tree scanning optimizations.
  • パーティション テーブルへのデータの一括読み込みを行う場合は、 データ読み込みのパフォーマンス ガイドに関するホワイト ペーパーで説明されている推奨事項に従ってください。Follow the best practice recommendations in the white paper, The Data Loading Performance Guide, when bulk loading data into partitioned tables.

Example

次の例では、7 つのパーティションを持つ 1 つのテーブルを含むテスト データベースを作成します。The following example creates a test database containing a single table with seven partitions. この例でクエリを実行し、コンパイル時と実行時の両方のプランのパーティション分割情報を表示する場合は、前に説明したツールを使用します。Use the tools described previously when executing the queries in this example to view partitioning information for both compile-time and run-time plans.

注意

この例では、100 万以上の行をテーブルに挿入します。This example inserts more than 1 million rows into the table. この例を実行すると、ハードウェアによっては数分かかる場合があります。Running this example may take several minutes depending on your hardware. この例を実行する前に、1.5 GB を超えるディスク領域が確保されていることを確認してください。Before executing this example, verify that you have more than 1.5 GB of disk space available.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO

その他の情報Additional Reading

プラン表示の論理操作と物理操作のリファレンスShowplan Logical and Physical Operators Reference
拡張イベントExtended Events
クエリ ストアを使用する際の推奨事項Best Practice with the Query Store
カーディナリティ推定Cardinality Estimation
インテリジェントなクエリ処理 Intelligent query processing
演算子の優先順位 Operator Precedence
実行プラン Execution Plans
SQL Server データベース エンジンと Azure SQL Database のパフォーマンス センターPerformance Center for SQL Server Database Engine and Azure SQL Database