SE の雑記

以前、投稿した SQL Server on Linux のデータベース バックアップを PowerShell Core で取得してみる のスクリプトをベースにしたものですが、Runspace を使用して複数のクエリを実行するスクリプトを作ってみました。

Invoke-ParallelQuery.ps1
「複数のセッションからクエリ実行したときの、あの動き、どうだったっけ？」というのを確認するために、ざっくり作ったものなので、時間のある時に清書しようかと。
実行が 10 数ミリ秒で終わってしまうものですと、実行タイミングがかぶることが無く、あまりこのスクリプトを使う必要はないのですが、「数秒以上かかるメンテナンスのクエリが実行されている最中に、他のクエリを実行するとどうなるか？」というような用途での利用を想定したものです。
SQLQueryStress ですと、同一のクエリを複数セッションで実行する方式となり、異なるクエリを同時に実行するためには、複数ツールを起動した状態にしなくてはいけないので、その部分を簡易的に埋める目的もあったりしますが。
データセットの情報を別で表示する際に「Out-Gridview」を使用しているため、Windows PowerShell 専用となっているところもどこかで対応したいですねぇ。。。

使用の例ですが、次のような実行をすることができます。

$Param = @{
    ServerInstance = "<インスタンス名>";
    UserName = "<ログイン名>";
    Password = "<パスワード>";
    SQL =@(
(
"TESTDB", 0, $false,
@"
ALTER INDEX NCIX_C2 ON T1 REBUILD
"@
),
(
"TESTDB", 0, $false,
"INSERT INTO T1(C1) VALUES(NEWID())"
),
(
"TESTDB", 0, $false,
"UPDATE T1 SET C2 = RAND() WHERE C1 = '0000003A-0344-4390-8D73-53317A517691'"
),
(
"TESTDB", 0, $false,
@"
SELECT COUNT(*) FROM T1
"@
),
(
"TESTDB", 0, $true,
@"
WAITFOR DELAY '00:00:05';
SELECT
    request_session_id, DB_NAME(resource_database_id) AS database_name, resource_type, resource_subtype, resource_associated_entity_id, resource_lock_partition,
    request_mode,request_type,request_status,count(*) AS COUNT
FROM
    sys.dm_tran_locks
WHERE
    request_session_id <> @@SPID
GROUP BY
    request_session_id, resource_database_id, resource_type, resource_subtype, resource_associated_entity_id, resource_lock_partition,
    request_mode,request_type,request_status
ORDER BY 1
"@
) | %{
    [PSCustomObject]@{
        Database = $_[0]
        CommandTimeout = $_[1]
        IsDataSet = $_[2]
        Text = $_[3]
        }
    }
)
}
Clear-Host
E:\Share\Invoke-ParallelQuery.ps1 @Param

 
この指定で実施している内容ですが、次のような内容となります。

1. 非クラスター化インデックスのメンテナンスをオフライン実行
2. その最中に、INSERT / UPDATE / SELECT を実行
3. その状態で 5 秒待機したのちに、ロックの状態を取得し、Out-GridView で表示
   
   (IsDataSet が true の場合、データセットの内容を Out-GridView するようになっています)

これは、オフラインのインデックスメンテナンスを実施した場合の、ロック競合の状況を取得するようなクエリパターンですね。
今回はすべてのクエリのコマンドタイムアウトを 0 にしていますが、コマンド単位にタイムアウト時間は変更でできますので、「インデックスの再構築のみ Timeout = 0 で、その他のクエリは 30 秒でタイムアウト」というような設定も可能です。
 
このパターンの場合は、Out-GridView には、次のような内容が表示されます。

「Session ID = 66」は、インデックスの再構築をオフラインで実行しているセッションのロックになるのですが、この際には「Sch-M」のロックが取得されているため、それ以外のセッションで実行されたクエリのロックの状態が「WAIT」になっていますので、「オフラインの非クラスター化インデックスの再構築中にはロック競合が発生し、他の処理の同時実行性に影響を与えている」ことが確認できますね。
実行時間については、次のような順になっています。

1. INSERT
2. ロック競合の取得 (これは、明示的に 5 秒の WAIT が入っているため必ず 5 秒以上かかります)
3. インデックスの再構築
4. INSERTUPDATE
5. SELECT

一部の処理でロック競合が発生し、競合が解消するまで処理が遅延しますので、本来であれば1秒程度で終わるものが10数秒かかっている (メンテナンスにより同時実行性が低下している) ことが確認できますね。

実行のタイミングによっては、次のような処理実行順になることもありますが、インデックスの再構築が、同時実行性を低下させているという状況に変わりはありません。

 
インデックスの再構築のクエリを「ALTER INDEX NCIX_C2 ON T1 REBUILD WITH (ONLINE=ON)」に変更すると、処理時間が次のように変わります。

1. UPDATE
2. INSERT
3. SELECT
4. ロック競合の取得
5. インデックスの再構築

オンラインのインデックス再構築を実行した場合、データの操作は実施できますので、先ほどとは異なり、INSERT / UPDATE / SELECT の実行については、先ほどより短い実行時間で完了していることが確認できますね。
ただし、オンラインのインデックス再構築は、オフラインと比較して、処理時間が伸びますので、ALTER INDEX の実行時間については、先ほどより処理時間が増加していることが確認できます。
クエリのパターンはほかにもありますが、このように複数のクエリを数ミリ秒違いで前のクエリが完了する前に連続的に実行することができますので、メンテナンス系の処理による影響や、同時実行をした場合の処理の動作確認に簡易的に利用することができます。
 
他の実行パターンとしては、次のような実行もできます。

$Param = @{
    ServerInstance = "<インスタンス名>";
    UserName = "<ログイン名>";
    Password = "<パスワード>";
    SQL =@(
    1..130 | %{
    [PSCustomObject]@{
        Database = "TESTDB"
        IsDataSet = $false
        Text = "WAITFOR DELAY '00:00:20'"
        }
    }
    )
}
Clear-Host
E:\Share\Invoke-ParallelQuery.ps1 @Param
Start-Sleep -Seconds 10

先ほどは、異なるクエリを登録していましたが、今回は、同じクエリを 130 クエリ登録しています。

これを実行すると、次のような処理結果になります。

100 は成功しましたが、30 クエリはエラーになっています。

今回、実行に時間のかかるクエリを実行しているのですが、「MaxPoolSize」はデフォルトから変更していないため、「100」が利用されています。

そのため、プール内で利用できる接続が確保できる前に接続のタイムアウトが発生し、30 の接続についてはクエリを実行することができずにエラーとなっています。

$Param = @{
    ServerInstance = "<インスタンス名>";
    UserName = "<ログイン名>";
    Password = "<パスワード>";
    DisableConnectionPool = $true;
    SQL =@(
    1..130 | %{
    [PSCustomObject]@{
        Database = "TESTDB"
        IsDataSet = $false
        Text = "WAITFOR DELAY '00:00:20'"
        }
    }
    )
}
Clear-Host
E:\Share\Invoke-ParallelQuery.ps1 @Param
Start-Sleep -Seconds 10

このように「DisableConnectionPool = $true;」のスイッチを設定することで、コネクションプールを無効にすることができますので、この場合は接続のコストと引き換えにすべての処理を完了させることもできたりはします。

他のパターンとしては、「UniqueAppName」というスイッチを使用することもできます。

$Param = @{
    ServerInstance = "<インスタンス名>";
    UserName = "<ログイン名>";
    Password = "<パスワード>";
    DisableConnectionPool = $true;
    SQL =@(
    1..130 | %{
    [PSCustomObject]@{
        Database = "TESTDB"
        IsDataSet = $false
        Text = "WAITFOR DELAY '00:00:20'"
        }
    }
    )
}
1..100 | %{
Clear-Host
E:\Share\Invoke-ParallelQuery.ps1 @Param
Start-Sleep -Seconds 10
}

このスイッチを有効にした場合、接続文字列の Application Name に GUID が指定されますので、各実行で異なる接続文字列が使用されることになります。
コネクションプールを無効にしていない状態でも異なる接続文字列での接続となり、プールが利用されませんので、接続状態を確認すると次のようになり、実行が繰り返されるたび (上記のスクリプトだと 130 回の実行を 100 回繰り返すことになります)、新しい接続が作成されていることが確認できるかと。

直近で自分が説明する機会がありそうなものを実装したものとなりますが、パターンを考えることでいろいろな利用ができるかと。