どうする？ SQL Server のクエリ パフォーマンスが低下した！

アプリケーションの応答が急に遅くなった、バッチ処理がいつもの時間に終わらない・・・
クエリのパフォーマンスが低下し早急に対処が必要な場合に、まずお試しいただきたいことをまとめました。

まずは 1 を実施してパフォーマンスが改善されるか確認し、ダメなら 2 または 3 へ進んでください。

1. 統計情報を更新する

統計情報を最新の状態にすることで、現在のデータ分布に最適な実行プランが選択されるようになる可能性があります。

a) データベース単位で実行   
    sp_updatestats

どのクエリを実行しても遅い、どのオブジェクトの統計情報を更新したらよいのかわからない、という場合にお試しください。

ステートメント)
----------------------------
USE <データベース名>;
GO
EXEC sp_updatestats;
----------------------------

使用例)
----------------------------
USE mydatabase;
GO
EXEC sp_updatestats;
----------------------------

sp_updatestats (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms173804.aspx

b) テーブル単位 or インデックス単位で実行
    UPDATE STATISTICS

特定のクエリが遅いとわかっている場合にお試しください。

ステートメント)
----------------------------
USE <データベース名>;
GO
UPDATE STATISTICS <テーブル名 or インデックス付きビュー名>;
GO
----------------------------
USE <データベース名>;
GO
UPDATE STATISTICS <テーブル名 or インデックス付きビュー名> <インデックス名 or 統計名>;
GO
----------------------------

使用例) tbl_01 テーブルのインデックス idx_01 の統計を更新します。
----------------------------
USE mydatabase;
GO
UPDATE STATISTICS dbo.tbl_01 idx_01;
GO
----------------------------

UPDATE STATISTICS (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms187348.aspx

※ WITH FULLSCAN  オプション
すべての行をスキャンして統計を計算することにより、高品質のクエリプランを作成できる場合もあります。

2. 実行プランを作成し直す

パラメータクエリ、ストアドプロシージャに有効です。
実行プラン生成時に使用されたパラメータ値が特殊だった場合、大半のパラメータ値にとっては最適な実行プランになっていないことがあります。   
実行プランを作成し直すことによって、より適した実行プランが生成される可能性があります。

アドホッククエリの中には毎回実行プランが作成されるものもあるため、そのようなクエリに対してはこの対処は効果が期待できません。

a) プランキャッシュをクリアする（インスタンス単位）    
    DBCC FREEPROCCACHE

どのクエリが遅いのかわからない場合にお試しください。

ステートメント)
----------------------------
DBCC FREEPROCCACHE;
----------------------------

DBCC FREEPROCCACHE (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms174283(v=sql.110).aspx

b) 次回実行時にリコンパイルする（テーブル or ストアドプロシージャ単位）     sp_recompile

特定のクエリが遅いとわかっている場合にお試しください。

ステートメント)
----------------------------
USE <データベース名>;
GO
EXEC sp_recompile N'<テーブル名 or ストアドプロシージャ名>';
GO
----------------------------

使用例) tbl_01 テーブルを対象とするストアド プロシージャおよびトリガーが次回実行時に再コンパイルされます。
----------------------------
USE mydatabase;
GO
EXEC sp_recompile N'dbo.tbl_01';
GO
----------------------------

sp_recompile (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms181647(v=sql.110).aspx

3. インデックスを再構築する

    ALTER INDEX ... REBUILD

インデックスの断片化を解消することにより、I/O 負荷を軽減し、パフォーマンスが向上する可能性があります。

ステートメント)
----------------------------
USE <データベース名>;
GO
ALTER INDEX <インデックス名> ON <テーブル名> REBUILD;
GO
----------------------------
USE <データベース名>;
GO
ALTER INDEX ALL ON <テーブル名> REBUILD;  -- テーブルに関連付けられているすべてのインデックスを対象
GO
----------------------------

使用例) tbl_01 テーブルのインデックス idx_01 を再構築します。
----------------------------
USE mydatabase;
GO
ALTER INDEX idx_01 ON dbo.tbl_01 REBUILD;
GO
----------------------------

ALTER INDEX (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms188388.aspx  

※ 断片化率の確認方法

以下のクエリを実行すると、再構築が必要と思われるインデックスに対する ALTER INDEX ... REBUILD 文が生成されます。
再構築の対象とする条件は、断片化率が 30 ％ 以上かつページの合計数が 1000 ページ以上のインデックスです。

USE <データベース名>;
GO
SELECT 'ALTER INDEX ' + '[' + C.name + ']' + ' ON [' + D.name + '].[' + B.name + '] REBUILD' cmd,     
             D.name AS schemaname,     
             B.name AS table_name,     
             C.name AS index_name,     
             C.index_id,
             A.partition_number,
             A.avg_fragmentation_in_percent,
             A.page_count
  FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,null) as A
    JOIN  sys.objects AS B
      ON  A.object_id = B.object_id
    JOIN  sys.indexes AS C
      ON  A.object_id = C.object_id  AND A.index_id = C.index_id
    JOIN  sys.schemas D
      ON  B.schema_id = D.schema_id
WHERE B.type = 'U'
      and C.index_id > 0
      and A.page_count > 1000
      and A.avg_fragmentation_in_percent > 30
ORDER BY A.avg_fragmentation_in_percent DESC;
GO

sys.dm_db_index_physical_stats (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms188917.aspx

どれも試してみたけどダメだった場合は・・・

  詳細な調査のご参考に

      [SQL Troubleshooting] SQL Server トラブルシューティング 6 回シリーズのご案内
      https://blogs.msdn.com/b/jpsql/archive/2012/03/30/sql-server-6.aspx

  クエリ チューニングのご参考に

      実行プランを読む - 基本編
      https://blogs.msdn.com/b/jpsql/archive/2011/09/07/10207003.aspx

      実行プランを読む - 活用編
      https://blogs.msdn.com/b/jpsql/archive/2011/11/15/10235685.aspx

• Anonymous
  October 29, 2014
  この内容と、「SQL Server トラブルシューティング 6 回」の内容は、SQL Server 2005以降で適用できるものと考えてよいでしょうか？

• Anonymous
  October 29, 2014
  この内容と、「連載 SQLトラブルシューティング」の内容は、SQL Server 2005以降で適用できると思っていてよいでしょうか？ ※同じ投稿をしたところ、反映されなかったので再送しました。 　重複してしまったら申し訳ありません。

• Anonymous
  November 05, 2014
  ご認識の通り、本内容と「SQL Server トラブルシューティング 6 回」の内容は、SQL Server 2005以降で利用可能です。なお、新しいバージョンでは、ログのパスが変わり、Transact-SQL の利用方法も変更されている場合がありますので、状況に合わせてご利用ください。 これらの blog も新しいバージョンに合わせて順次更新する予定です。

• Anonymous
  June 27, 2017
  ある日突然社内システムのパフォーマンスが悪くなり、調査の結果特定のクエリが遅いとわかりましたので「1. 統計情報を更新する」を試したところ劇的に改善されました。ありがとうございました。