次の方法で共有

クエリのパフォーマンスが低下している場合のトラブルシューティング : 基数推定中の定数のたたみ込みおよび式の評価

  • [アーティクル]

SQL Server ではクエリのパフォーマンスを向上するために、いくつかの定数式を前もって評価します。これを定数のたたみ込みと呼びます。定数とは Transact-SQL リテラルのことで、3、'ABC'、'2005-12-31'、1.0e3、0x12345678 などがあります。

たたみ込み可能な式

SQL Server では、次の種類の式に定数のたたみ込みを適用します。

  • 定数のみから構成される数式 (1+1, 5/3*2 など)。
  • 定数のみから構成される論理式 (1=1、1>2 AND 3>4 など)。
  • たたみ込み可能と判断された組み込み関数 (CAST、CONVERT など)。固有の関数は、通常はたたみ込み可能です。ただし結果が関数への入力のみによって決まらず、SET オプション、言語設定、データベース オプション、暗号化キーなどの、状況によって変わりうる他の情報を交えて決まる場合は例外です。非決定的関数はたたみ込み不可能です。組み込みの決定的関数はたたみ込み可能ですが、一部例外があります。

例外の 1 つは LOB 型です。たたみ込み処理の出力が LOB 型 (text、image、nvarchar(max)、varchar(max)、varbinary(max) のいずれか) である場合、式はたたみ込まれません。

たたみ込み不可能な式

その他の種類の式は、すべてたたみ込み不可能です。具体的には、次に示す種類の式です。

  • 定数ではない式 (結果が列の値によって変わる式など)。
  • 結果がローカル変数またはパラメータ (@x など) によって変わる式。
  • 非決定的関数。
  • ユーザー定義関数 (Transact-SQL および CLR)。
  • 結果が言語設定によって変わる式。
  • 結果が SET オプションによって変わる式。
  • 結果がサーバー構成オプションによって変わる式。

たたみ込み可能/不可能な定数式の例

次のクエリを考えてみます。

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

このクエリの PARAMETERIZATION データベース オプションが FORCED に設定されていない場合 (既定値は SIMPLE)、クエリをコンパイルする前に式 117.00 + 1000.00 が評価され、その結果である 1117.00 に置き換えられます。定数のたたみ込みには、次に示す利点があります。

  • 実行時に式を繰り返し評価する必要がありません。
  • クエリ TotalDue > 117.00 + 1000.00 の部分の結果セットのサイズをクエリ オプティマイザで推定するときは、評価後の式の値が使用されます。

一方、ユーザー定義関数を含んだ式は決定的関数であってもたたみ込まれないので、dbo.f をスカラ値のユーザー定義関数とした場合、式 dbo.f(100) はたたみ込まれません。

カーディナリティを推定するためのコンパイル時の式の評価

また、定数のたたみ込みは行われませんが、引数がコンパイル時に確定する式は、引数がパラメータと定数のどちらでも、最適化のときにクエリ オプティマイザのカーディナリティ (結果セットのサイズ) 推定機能によって評価されます。

具体的には、組み込み関数 UPPER、LOWER、RTRIM、DATEPART (YY のみ)、GetDate、CAST、および CONVERT は、入力がすべて確定する場合コンパイル時に評価されます。

次に示す演算子も、入力がすべて確定する場合コンパイル時に評価されます。

  • 算術演算子 : +、-、*、/、単項演算子の -
  • 論理演算子 : AND、OR、NOT
  • 比較演算子 : <、>、<=、>=、<>、LIKE、IS NULL、IS NOT NULL

上記以外の関数および演算子は、カーディナリティを推定するときには評価されません。

コンパイル時の式の評価の例

次の Transact-SQL ストアド プロシージャを考えてみます。

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

このストアド プロシージャの SELECT ステートメントを最適化するとき、条件 OrderDate > @d+1 に対する結果セットの予測カーディナリティが評価されます。式 @d+1 は @d がパラメータであるために定数がたたみ込まれません。しかし、最適化のときにはパラメータの値が確定しています。したがって結果セットのサイズが正確に推定できるので、適切なクエリ プランが選択されます。

次は、上記のクエリの @d+1 をローカル変数 @d2 に置き換え、クエリではなく SET ステートメントで式を評価するストアド プロシージャの例を考えてみます。

USE AdventureWorks
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 Server 2005 で MyProc2 の SELECT ステートメントを最適化するとき、@d2 の値は確定していません。そのため、OrderDate > @d2 の選択度については、既定の推定が使用されます (この場合は 30%)。

ベスト プラクティス : 定数のたたみ込みおよびコンパイル時の式の評価を使用した最適なクエリ プランの生成

最適なクエリ プランを生成するには、データの分散に関する統計を基にクエリ内の条件の選択度を正確に推定することができるようにクエリ、ストアド プロシージャ、およびバッチをデザインしてください。そのようなデザインにしないと、選択度の推定には既定の推定 (上の例の 30% など) が使用されます。

クエリ オプティマイザのカーディナリティ推定機能で良い推定結果を得るには、AUTO_CREATE_STATISTICS および AUTO_UPDATE_STATISTICS データベースの SET オプションが ON (既定値) に設定されていること、またはクエリの条件で参照しているすべての列の統計を手動で作成してあることを最初に確認してください。その上で、クエリの条件をデザインするときに、できる限り次のことを守ってください。

  • クエリでのローカル変数の使用を避けます。代わりにパラメータ、リテラル、または式を使用します。
  • パラメータを含んでいるクエリに埋め込む演算子および関数を、「カーディナリティを推定するためのコンパイル時の式の評価」に記載されているものに制限します。
  • クエリの条件で使用している定数のみの式が、定数のたたみ込みが可能であるかコンパイル時に評価できることを確認します。
  • クエリで使用する式の評価にローカル変数を使用する必要がある場合、式をクエリとは異なるスコープで評価することを検討してください。たとえば、次のいずれかを実行するとよい場合があります。
    • 評価するクエリを含んだストアド プロシージャに変数の値を渡し、ローカル変数ではなくプロシージャのパラメータがクエリで使用されるようにします。
    • ローカル変数の値を一部に使用したクエリから構成される文字列を作成し、動的 SQL (EXEC または sp_executesql) を使用してこの文字列を実行します。
    • クエリをパラメータ化して sp_executesql によって実行し、変数の値をパラメータとしてクエリに渡します。

参照

概念

クエリのパフォーマンスが低下している場合のトラブルシューティング : 基数推定

その他の技術情報

クエリのチューニング
クエリに関するトラブルシューティング

ヘルプおよび情報

SQL Server 2005 の参考資料の入手