SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Database Index Advisor について

leave a comment

先日、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 

 

実行プランを取得すると、以下のような情報を取得することができます。

image

実行プランからインデックスを作成するための CREATE 文も表示ができ、これをベースに Index Advisor は実装されているのではと思います。

image

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 が含まれたインデックスのアドバイス情報となっています。

image

SQL Database の場合、どのサービス層でも ONLINE =ON を使用することができますので、作成には以下のようなクエリを使用することができます。

USE [TESTDB]
GO
CREATE NONCLUSTERED INDEX NCIX_TestTable_Col2
ON [dbo].[TestTable] ([Col2])
INCLUDE ([Col1])
WITH (ONLINE=ON)
GO

 

検索の効率化としてインデックスチューニングは重要な要素ですので、ポータルから確認できると楽で良いですね。

# すぐに取得したい場合は、DMV を直接検索して情報取得でも可かと

Written by masayuki.ozawa

7月 13th, 2015 at 7:32 pm

Posted in SQL Database

Tagged with

Leave a Reply

*