SQL Server におけるパラメータ スニッフィング (またはスプーフィング) とは？

SQL Server におけるパラメータ スニッフィング (またはスプーフィング)

SQL Server では、クエリのパフォーマンスを向上させるために、クエリ実行時に実行計画を生成します。この実行計画は、クエリの最初の呼び出し時に生成され、その後の呼び出しでは再利用されます。

パラメータ スニッフィング (またはスプーフィング) は、この実行計画生成の仕組みを利用したテクニックです。最初の呼び出し時に、パフォーマンスに悪影響を与えるようなパラメータ値を意図的に渡すことで、後続の呼び出しで効率的な実行計画が生成されるように仕向けます。

仕組み

SQL Server は、クエリのパラメータ値に基づいて、最適な実行計画を選択します。最初の呼び出し時に渡されたパラメータ値に基づいて、クエリの統計情報が収集され、それらの情報に基づいて実行計画が生成されます。

しかし、最初の呼び出し時に渡されたパラメータ値が、実際の後続の呼び出しで頻繁に使用されるパラメータ値と大きく異なる場合、生成された実行計画は効率的ではない可能性があります。

例

次の例では、t-sql を使用して、Customers テーブルからデータを取得するクエリがあります。

SELECT *
FROM Customers
WHERE Country = @Country

このクエリを初めて実行する際に、@Country パラメータに 'USA' という値を渡すとします。この場合、SQL Server は USA に基づいて実行計画を生成します。

しかし、実際の後続の呼び出しでは、@Country パラメータに 'Japan' という値が頻繁に使用されるとします。この場合、USA に基づいて生成された実行計画は効率的ではない可能性があります。

そこで、パラメータ スニッフィングを使用して、最初の呼び出し時に @Country パラメータに 'Japan' という値を渡すようにします。

DECLARE @Country VARCHAR(2)
SET @Country = 'Japan'

EXEC sp_executesql @sql = N'
SELECT *
FROM Customers
WHERE Country = @Country',
@params = N'@Country VARCHAR(2)',
@Country = @Country

このように、最初の呼び出し時に 'Japan' という値を渡すことで、後続の呼び出しで効率的な実行計画が生成されるように仕向けます。

注意点

パラメータ スニッフィングは、パフォーマンスを向上させるための有効なテクニックですが、いくつかの注意点があります。

• 最初の呼び出し時に渡すパラメータ値は、実際の後続の呼び出しで頻繁に使用されるパラメータ値である必要があります。
• パラメータ スニッフィングは、すべてのクエリに有効とは限りません。クエリによっては、パラメータ スニッフィングを使用してもパフォーマンスが向上しない場合があります。
• パラメータ スニッフィングは、クエリの動作を変更する可能性があります。パラメータ スニッフィングを使用する前に、クエリの動作を十分に理解しておく必要があります。

• 上記の例では、sp_executesql ステートメントを使用して、パラメータ スニッフィングを実装しています。
• SQL Server 2005 以降では、OPTION (RECOMPILE) クエリヒントを使用して、パラメータ スニッフィングを無効にすることができます。

用語

• パラメータ スニッフィング (Parameter Sniffing)
• 実行計画 (Execution Plan)
• クエリ統計 (Query Statistics)

関連技術

• クエリプラン キャッシュ (Query Plan Cache)

• パラメータ スニッフィングは、パフォーマンス チューニングの一環として有効なテクニックですが、他のパフォーマンス チューニング手法と組み

USE AdventureWorks2019

-- 最初の呼び出し
DECLARE @Country VARCHAR(2)
SET @Country = 'USA'

EXEC sp_executesql @sql = N'
SELECT *
FROM Customers
WHERE Country = @Country',
@params = N'@Country VARCHAR(2)',
@Country = @Country

-- 2番目の呼び出し
SET @Country = 'Japan'

EXEC sp_executesql @sql = N'
SELECT *
FROM Customers
WHERE Country = @Country',
@params = N'@Country VARCHAR(2)',
@Country = @Country

-- 3番目の呼び出し
SET @Country = 'USA'

EXEC sp_executesql @sql = N'
SELECT *
FROM Customers
WHERE Country = @Country',
@params = N'@Country VARCHAR(2)',
@Country = @Country

• 最初の呼び出しでは、@Country パラメータに 'USA' という値を渡しています。

実行結果

• 最初の呼び出しでは、USA に基づいて実行計画が生成されます。

注意

• 実際のアプリケーションでは、必要に応じてコードを変更する必要があります。

パラメータ スニッフィングの回避方法

クエリプラン キャッシュの無効化

クエリプラン キャッシュを無効にすることで、パラメータ スニッフィングの影響を受けずに、常に新しい実行計画が生成されます。

方法:

• クエリに OPTION (RECOMPILE) ヒントを追加する。
• データベース オプション PARAMETER_SNIFFING を 0 に設定する。

例:

SELECT *
FROM Customers
WHERE Country = @Country
OPTION (RECOMPILE)

• クエリプラン キャッシュを無効化すると、パフォーマンスが低下する可能性があります。

クエリパラメータの型変換

• クエリパラメータを文字列型に変換する。

SELECT *
FROM Customers
WHERE Country = CONVERT(VARCHAR(2), @Country)

• クエリパラメータの型変換は、クエリの動作を変更する可能性があります。

クエリプラン ガイドの使用

クエリプラン ガイドを使用することで、特定のクエリに対して常に同じ実行計画を生成することができます。

• クエリプラン ガイドを作成する。
• クエリに USE PLAN ヒントを追加して、クエリプラン ガイドを指定する。

USE PLAN AdventureWorks2019.dbo.GetCustomersByCountry

SELECT *
FROM Customers
WHERE Country = @Country

• クエリプラン ガイドは、データベース スキーマの変更によって無効になる可能性があります。

ストアドプロシージャの使用

• クエリをストアドプロシージャに作成する。
• ストアドプロシージャを実行する。

CREATE PROCEDURE GetCustomersByCountry
@Country VARCHAR(2)

AS

SELECT *
FROM Customers
WHERE Country = @Country

GO

EXEC GetCustomersByCountry @Country = 'USA'