SQL Server のチューニングについてまとめてみる - その1 - ( インデックスの調査 )

　SQL Server のチューニングについてまとめていこうかと思います。まずは、インデックスを使用していないクエリの抽出方法です。SQL Server のチューニングには、dm_* で始まる動的管理ビューを多用します。今回は、主に以下の3テーブルを使用して、インデックスを使用していないクエリを探してみましょう。

• sys.dm_db_missing_index_group_stats
  欠落インデックス グループに関する情報を取得します。
• sys.dm_db_missing_index_groups
  sys.dm_db_missing_index_group_stats と sys.dm_db_missing_index_details を JOIN するために必要なテーブルです。
• sys.dm_db_missing_index_details
  欠落したインデックスについての詳細情報を取得します。

　以下のクエリで欠落しているインデックスを探すことが可能です。(いつもの AdventureWorksDBを使用しています。)

USE AdventureWorks2012
SELECT gs.last_user_seek AS [最後にシークした時間],
       id.statement AS [テーブル名] ,
       id.equality_columns AS [等値述語に使用できる列],
       id.inequality_columns AS [不等値述語に使用できる列] ,
       id.included_columns AS [包括列として必要な列],
       gs.unique_compiles AS [コンパイルおよび再コンパイルの数],
       gs.user_seeks AS [クエリによって発生したシーク数]
 FROM  sys.dm_db_missing_index_group_stats AS gs
       INNER JOIN sys.dm_db_missing_index_groups AS ig
			ON gs.group_handle = ig.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details AS id
			ON ig.index_handle = id.index_handle
 WHERE id.[database_id] =DB_ID() Order By gs.last_user_seek ASC

　いくつかインデックスを使用しないクエリを発行後に、上記のクエリを発行すると下図のようになります。赤枠の部分をどのように改善するか説明します。

• 最後にシークした時間 : 2013-09-23 03:42:41.773
• テーブル名 : [AdventureWorks2012].[Purchasing].[PurchaseOrderHeader]
• 等値述語に使用できる列 : [RevisionNumber]
• 不等値述語に使用できる列 : NULL
• 包括列として必要な列 : [PurchaseOrderID], [Status], [EmployeeID], [VendorID], [ShipMethodID], [OrderDate], [ShipDate], [SubTotal], [TaxAmt], [Freight], [TotalDue], [ModifiedDate]
• コンパイルおよび再コンパイルの数 : 1
• クエリによって発生したシーク数   : 2

　上記の結果から、インデックスの存在しない RevisionNumber 列を WHERE 句に指定していることが判断できます。また、付加列として、[PurchaseOrderID], [Status], [EmployeeID], [VendorID], [ShipMethodID], [OrderDate], [ShipDate], [SubTotal], [TaxAmt], [Freight], [TotalDue], [ModifiedDate] が必要なことがわかります。よって、下記のようなクエリを実行することで解決することができます。

CREATE NONCLUSTERED INDEX IX_PurchaseOrderHeader_RevisionNumber
ON [Purchasing].[PurchaseOrderHeader] ([RevisionNumber])
INCLUDE ([PurchaseOrderID],[Status],[EmployeeID],[VendorID],[ShipMethodID],[OrderDate],[ShipDate],[SubTotal],[TaxAmt],[Freight],[TotalDue],[ModifiedDate])

　上記のクエリ発行後にもう一度調査用スクリプトを流すと以下の様な結果になります。問題となっていた行が消えていますね。適切なインデックスが作成できました。

　しかし、このような調査をしなくても、日々の開発の中で開発者がきちんとSQL Server  ManagementStudio の実行プランを見ることで対応可能です。実行プラン表示時にインデックスが足りない場合には下図の赤枠ように表示されます。

　赤枠の部分を右クリックすると、下記のようなメニューが表示されます。メニューから、Missing Index Details... をクリックします。

 　下図のように、CREATE INDEX 文が生成されます。

　生成されたクエリは下記の通りです。何％改善する等も表示してくれます。(今回の例では、83.0941%改善するようです。)

/*
Missing Index Details from SQLQuery1.sql - SQLSERVERTEST.AdventureWorks2012 (SQLSERVERTEST\sqlservertest (53))
The Query Processor estimates that implementing the following index could improve the query cost by 83.0941%.
*/

/*
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Purchasing].[PurchaseOrderHeader] ([RevisionNumber])
INCLUDE ([PurchaseOrderID],[Status],[EmployeeID],[VendorID],[ShipMethodID],[OrderDate],[ShipDate],[SubTotal],[TaxAmt],[Freight],[TotalDue],[ModifiedDate])
GO
*/

 　日頃からマメに SQL Server Management Studio でクエリ確認しましょう。