SQL Serverのパフォーマンスチューニング：インデックス、統計情報、クエリプラン

SQL Server: クエリは速いのに、プロシージャだと遅い

SQL Server で、直接実行すると高速なクエリが、ストアドプロシージャとして実行すると遅くなる場合があります。

原因

この現象には、いくつかの原因が考えられます。

• パラメータのスニッフィング: SQL Server は、クエリのパラメータ値に基づいて実行計画を生成します。ストアドプロシージャの場合、パラメータ値が事前にわからないため、最適な実行計画が生成できない可能性があります。
• コンパイル: ストアドプロシージャは、実行前にコンパイルされます。このコンパイル処理に時間がかかり、パフォーマンスが低下する可能性があります。
• プランキャッシュ: ストアドプロシージャは、プランキャッシュに保存されます。プランキャッシュがいっぱいになると、古いプランが削除され、パフォーマンスが低下する可能性があります。

解決策

この現象を解決するには、以下の方法があります。

• パラメータのスニッフィングを無効にする: クエリヒント OPTION (OPTIMIZE FOR UNKNOWN) を使用して、パラメータのスニッフィングを無効にすることができます。
• ストアドプロシージャをローカル変数で実行する: ストアドプロシージャではなく、ローカル変数を使用してクエリを実行することで、コンパイル時間を短縮できます。
• プランキャッシュをクリアする: DBCC FREEPROCCACHE コマンドを使用して、プランキャッシュをクリアできます。

その他の対策

• ストアドプロシージャをレビューする: ストアドプロシージャのコードを見直し、パフォーマンスを向上させるための改善点がないか確認します。
• インデックスを作成する: 必要なインデックスを作成することで、クエリのパフォーマンスを向上させることができます。
• 統計情報を更新する: 統計情報が最新の状態であることを確認することで、SQL Server が最適な実行計画を生成することができます。

上記以外にも、パフォーマンスを向上させるための方法はいくつかあります。詳細については、SQL Server のパフォーマンスに関するドキュメントを参照してください。

---

SELECT *
FROM dbo.Customers
WHERE Country = 'Japan'

ストアドプロシージャの例

CREATE PROCEDURE [dbo].[GetCustomers]
AS
BEGIN
SELECT *
FROM dbo.Customers
WHERE Country = @Country
END

パラメータのスニッフィングを無効にする例

SELECT *
FROM dbo.Customers
WHERE Country = @Country
OPTION (OPTIMIZE FOR UNKNOWN)

ローカル変数で実行する例

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

SELECT *
FROM dbo.Customers
WHERE Country = @Country

プランキャッシュをクリアする例

DBCC FREEPROCCACHE

注意

これらのサンプルコードは、あくまで参考です。実際の環境に合わせて、コードを変更する必要があります。

---

パフォーマンスを向上させるためのその他の方法

• ストアドプロシージャの再帰呼び出しを避ける: 再帰呼び出しは、パフォーマンスを低下させる可能性があります。
• テーブル変数を使用する: テーブル変数を使用することで、中間結果を保存し、パフォーマンスを向上させることができます。
• 不要なカーソルを避ける: カーソルは、パフォーマンスを低下させる可能性があります。
• 適切なデータ型を使用する: 適切なデータ型を使用することで、パフォーマンスを向上させることができます。
• ハードウェアをアップグレードする: ハードウェアをアップグレードすることで、パフォーマンスを向上させることができます。

SQL Server のパフォーマンスを監視するには、以下のツールを使用できます。

• SQL Server Profiler: SQL Server Profiler は、SQL Server のアクティビティをトレースするツールです。
• Performance Monitor: Performance Monitor は、Windows のパフォーマンスを監視するツールです。
• Extended Events: Extended Events は、SQL Server のパフォーマンスを監視するための新しいツールです。

パフォーマンスのチューニング

SQL Server のパフォーマンスをチューニングするには、以下の方法があります。

• クエリプランを分析する: クエリプランを分析することで、パフォーマンスを低下させているボトルネックを見つけることができます。
• 実行計画を強制する: ヒントを使用して、特定の実行計画を強制することができます。

上記以外にも、パフォーマンスを向上させるための方法はいくつかあります。詳細については、SQL Server のパフォーマンスに関するドキュメントを参照してください。

sql-server performance stored-procedures