• [アーティクル]

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

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 など)。

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

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

たたみ込み不可能な式

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

  • 定数ではない式 (結果が列の値によって変わる式など)。

  • 結果がローカル変数またはパラメーター (@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

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 をスカラー値のユーザー定義関数とした場合、SQL Server では、式 dbo.f(100) はたたみ込まれません。

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

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

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

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

  • 算術演算子 : +、-、*、/、単項演算子の -

  • 論理演算子 : AND、OR、NOT

  • 比較演算子 : <、>、<=、>=、<>、LIKE、IS NULL、IS NOT NULL

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

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

次の Transact-SQL ストアド プロシージャについて考えてみましょう。

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

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END;

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

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

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

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

  • クエリでのローカル変数の使用を避けます。代わりにパラメーター、リテラル、または式を使用します。

  • パラメーターを含んでいるクエリに埋め込む演算子および関数を、「Compile-Time Expression Evaluation for Cardinality Estimation」に記載されているものに限定します。

  • クエリの条件で使用している定数のみの式は、定数のたたみ込みが可能であるかコンパイル時に評価できるものにします。

  • クエリで使用する式の評価にローカル変数を使用する必要がある場合、式をクエリとは異なるスコープで評価することを検討してください。たとえば、次のいずれかを実行するとよい場合があります。

    • 評価するクエリを含んだストアド プロシージャに変数の値を渡し、ローカル変数ではなくプロシージャのパラメーターがクエリで使用されるようにします。

    • 部分的にローカル変数の値に基づくクエリが含まれる文字列を作成し、動的 SQL (EXEC または sp_executesql) を使用してこの文字列を実行します。

    • クエリをパラメーター化して sp_executesql によって実行し、変数の値をパラメーターとしてクエリに渡します。