先日、SQL Database のポータル側の新機能として、Index Advisor が追加されました。
Preview: Azure SQL Database Index Advisor
SQL Database Index Advisor
欠落インデックスの情報については DMV から取得することができ、欠落インデックスの情報を発生させたのですが、まだポータルから確認することができていませんので、公開されている情報から仕様を確認してみたいと思います。
インデックスの操作としては、
- ポータルから直接インデックスを作成 (V12 限定)
- インデックスを作成するための CREATE INDEX を表示
- インデックスの作成をキャンセル
の 3 種類ができるようです。
NOTE:
To get index recommendations a database needs to have about a week of usage, and within that week there needs to be some activity. There also needs to be some consistent activity as well. The index advisor can more easily optimize for consistent query patterns than it can for random spotty bursts of activity.
と記載されているように 1 週間の使用状況を元に、推奨インデックスの表示をするようです。
欠落インデックスですが、以下のようなクエリで発生させることができます。
IF OBJECT_ID('TestTable', 'U') IS NOT NULL
DROP TABLE TestTable
CREATE TABLE [dbo].[TestTable](
[Col1] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Col2] [uniqueidentifier] NULL,
[Col3] [uniqueidentifier] NULL,
[Col4] [nvarchar](36) NULL
)
GO
SET NOCOUNT ON
DECLARE @cnt int = 1
WHILE (@cnt <= 100000)
BEGIN
IF @@TRANCOUNT = 0
BEGIN TRAN
INSERT INTO TestTable(Col2, Col3, Col4) VALUES(NEWID(), NEWID(), NEWID())
IF @@TRANCOUNT % 40000 = 0
COMMIT TRAN
SET @cnt += 1
END
IF @@TRANCOUNT > 0
COMMIT TRAN
GO
上記のクエリで作成したテーブルに対して、以下のクエリを実行してデータの取得を行ってみます。
SELECT SUM(CONVERT(bigint, Col1)) FROM TestTable WITH (FORCESCAN) WHERE Col2 BETWEEN NEWID() AND NEWID() GROUP BY Col2 ORDER BY COl2 GO
実行プランを取得すると、以下のような情報を取得することができます。
実行プランからインデックスを作成するための CREATE 文も表示ができ、これをベースに Index Advisor は実装されているのではと思います。
DMV から欠落インデックスの情報を取得する場合、以下のクエリで取得することができます。
SET NOCOUNT ON GO /*********************************************/ -- 不足しているインデックスの取得 /*********************************************/ SELECT DB_NAME(mid.database_id) AS DatabaseName, mid.index_handle, OBJECT_NAME(mid.object_id) As ObjectName, avg_user_impact, avg_total_user_cost, equality_columns, inequality_columns , included_columns , user_seeks, last_user_seek, user_scans, last_user_scan, statement FROM sys.dm_db_missing_index_details mid left join sys.dm_db_missing_index_groups mig on mid.index_handle = mig.index_handle left join sys.dm_db_missing_index_group_stats migs on migs.group_handle = mig.index_group_handle WHERE database_id = DB_ID() OR database_id IS NULL ORDER BY DatabaseName ASC, ObjectName ASC OPTION (RECOMPILE)
先ほどの CREATE INDEX 文であれば、以下のような情報として取得することができます。
# キー項目として、Col2、付加列として Col1 が含まれたインデックスのアドバイス情報となっています。
SQL Database の場合、どのサービス層でも ONLINE =ON を使用することができますので、作成には以下のようなクエリを使用することができます。
USE [TESTDB] GO CREATE NONCLUSTERED INDEX NCIX_TestTable_Col2 ON [dbo].[TestTable] ([Col2]) INCLUDE ([Col1]) WITH (ONLINE=ON) GO
検索の効率化としてインデックスチューニングは重要な要素ですので、ポータルから確認できると楽で良いですね。
# すぐに取得したい場合は、DMV を直接検索して情報取得でも可かと