SE の雑記

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

非クラスター化インデックス作成時の処理状況 (進行状況) を確認する

without comments

以前書いた、SQL Server のクエリプロファイリングの活用 の応用ではありますが、この方法は、非クラスター化インデックスを新規に作成するときにも活用することができます。

Enterprise Edition であれば、オンラインのインデックス操作でインデックスを作成することもできますが、データ量が大きい場合、実際のインデックス作成の状況がどこまで進んでいるのかを確認したいということはあると思います。(クエリの処理状況がわかると、メンテナンス作業を実行している最中の心理的安定性にもつながりますので)

クエリ プロファイリング インフラストラクチャ の機能を使用しており、SQL Server 2016 SP1 / Azure SQL Database であれば、オーバーヘッドを抑えて使用することができるかと。

軽量なクエリプロファイリングによる情報の取得が可能なことを確認

実行中の要求を確認できる sys.dm_exec_requests でも一部の操作については、処理状況を確認すすることはできるのですが、情報が取得可能な操作については、限定的なものとなっています。

image

 

今回、対象としているインデックスの作成については、sys.dm_exec_requests からは情報を取得することができませんので、そのような操作の処理状況のデータを取得するためには、 sys.dm_exec_query_profiles という DMV を活用します。

SQL Server 2019 / Azure SQL Database であれば、特に設定を変更しなくても情報の取得が可能となっています。

SQL Server 2016 SP1 以降であれば、いくつかの設定で情報の取得ができるのですが、簡単に情報を取得するのであれば SSMS で「実際の実行プランを含める」を有効にして、インデックスの再構築のクエリを実行すれば、情報の取得ができるはずです。

image

 

DMV から情報が取得できるかについては、最初に、次のようなクエリを実行してみて、

SELECT * FROM sys.objects AS o1
CROSS JOIN sys.objects AS o2
CROSS JOIN sys.objects AS o3
CROSS JOIN sys.objects AS o4
OPTION(MAXDOP 1)

 

このクエリが実行されている最中に、次のクエリを別のセッションから実行してみます。

クエリ内では、session_id = 54 としていますが、session_id は上記のクエリを実行している session の ID を指定してください。

SELECT * FROM sys.dm_exec_query_profiles 
WHERE session_id = 54 

 

軽量なクエリプロファイリングが有効になっている環境であれば、次のように指定したセッションのクエリの実行状況を確認することができます。

image

この情報が取得できるようになっていれば、インデックス作成をしている際の処理状況 (進捗) を確認することができます。

 

非クラスター化インデックス作成時の処理状況の確認

今回は、session_id = 54 のクエリウィンドウから、次のクエリで非クラスター化インデックスの作成を行ってみます。

CREATE NONCLUSTERED INDEX [NCIX_LINEITEM01] ON [dbo].[LINEITEM]
(
	[L_QUANTITY] ASC
)WITH (SORT_IN_TEMPDB = OFF, MAXDOP=0, ONLINE=ON) ON [PRIMARY]
GO

 

LINEITEM というテーブルに対して、新しくインデックスを作成しているクエリとなり、このクエリの処理状況の取得を行います。

軽量なクエリプロファイリングが有効な場合、非クラスター化インデックスの作成についても処理状況を確認できます。

情報取得を行うためのクエリの例としては次のようになります。

DECLARE @rows bigint = (SELECT rows FROM sys.partitions WITH(NOLOCK) WHERE object_id = OBJECT_ID('LINEITEM') AND index_id = 1)
SELECT 
    physical_operator_name, 
    FORMAT(SUM(row_count), '#,##0') AS processed_rows,
    CAST(SUM(row_count) * 1.0 / @rows * 100 AS numeric(5,2)) AS progress_percent
FROM 
    sys.dm_exec_query_profiles 
WHERE 
    session_id = 54 
GROUP BY 
    physical_operator_name

 

今回は、クラスター化インデックスのあるテーブルに対して、非クラスター化インデックスを作成していますので、最初にクラスター化インデックスの行数の取得を行います。

次に軽量なクエリプロファイリングで、非クラスター化インデックスの作成を実行しているセッションのクエリの処理状況の取得を行います。

この時には、最初に取得した全体の行数と、各処理で操作された行数を使用して、処理の進行状況を取得します。

実際に取得した情報がこちらになります。

image

非クラスター化インデックスの作成の処理状況としては、2 つの処理を確認します。

  1. Clustered Index Scan : インデックスを作成するために、テーブルのベースデータを読み込む
  2. Sort : 指定したキー項目でソートされているデータの生成

処理の進行状況上、Sort と Index への Insert は同時に実行されているため、確認をするのは上記の 2 ステップだけで問題はないはずです。

Clustred Index Scan が 100% になり、その後 Sort の処理の進捗状況が進んでいき、Sort が 100% になったタイミングで、非クラスター化インデックスを作成していたクエリも完了するはずです。

 

今回は、非クラスター化インデックスの作成を例にしましたが、軽量なクエリプロファイリングを使用した処理状況の確認は様々なケースで活用することができます。

データベースのメンテナンスに関しての作業は、神経を使うものであり、自分が実施したメンテナンスの処理状況がどこまで進んでいるかが把握できるようにしていると、作業を実施している最中の心理的安定性に大きく影響するかと思います。

データのメンテナンスを実施する際には、自分が実施している作業の進行状況を把握することができるような仕組みが作れるかを考えておくことは、作業実施時の精神的負荷を減らすことにもつながりますので、どのように状況を把握できるかを考えてみるのは大事ではないでしょうか。

Written by Masayuki.Ozawa

9月 6th, 2020 at 10:23 pm

Leave a Reply