Azure Arc enabled SQL Server (Azure Arc 対応 SQL Server) をインストールすることで、Azure 以外で稼働している SQL Server を Azure 上で管理 / Azure の一部の機能の管理性を提供することができるようになります。
インストールしただけの状態でも、いくつかの管理機能を使用することができますが、「SQL Server の運用」を考慮した場合、ちょい足しすると便利になる箇所がありますので、本投稿では、そのちょい足しの一例を紹介したいと思います。
今回は Azure Arc enabled SQL Server の環境で実施していますが、enabled SQL Server ではなく、SQL Server がインストールされている環境に、Log Analytis エージェントをインストールすることでも同様のことは実現できます。
Azure Arc enabled SQL Server に含まれる機能
Azure Arc enabled SQL Server ですが、この機能を有効にした場合、Azure Arc enabled Servers (Azure Arc 対応サーバー) の機能も合わせてインストールされることになります。(Azure Arc enabled SQL Server は、Azure Arc enabled Server の追加機能のような形でインストールが行われます)
そのため、enabled SQL Server がインストールされている環境では、enabled Server の機能を使用することができます。
enabled Server ですが、マシンを監視するために次の機能が含まれています。
- チュートリアル:Azure Monitor for VMs を使用してハイブリッド マシンを監視する
- Azure Monitor for VMs のマップ機能を使用してアプリケーション コンポーネントを把握する
この機能を有効にすることで、「分析情報」から、パフォーマンス / マップ / 正常性の情報を確認することができるようになります。
分析情報の取得は、Log Analytics エージェントをインストールした際に使用されるモジュールと同様に、Microsoft Monitoring Agent (MMA) が使用されており、分析情報の取得を行う際には、指定した Log Analytics ワークスペースに対して情報の取得が行われるようになります。
そのため、Azure Arc enabled SQL Server がインストールされている環境で、Azure Arc enabled Server の分析情報の取得機能を有効化することで、Log Analytics エージェント同様に、OS の任意のパフォーマンスモニターの項目を取得するための次の機能を使用することができるようになります。
この機能を使用により、SQL Server の運用をする際に取得していると便利な情報をちょい足しすることができます。
SQL Server の取得情報をちょい足しする
今回、取得情報をちょい足しするのは SQL Server:Batch Resp Statistics の情報です。
この情報は、サーバー負荷が上昇した際に、クエリチューニングのターゲットを調査する際に大変便利な項目です。
New-AzOperationalInsightsWindowsPerformanceCounterDataSource を使用すると、Log Analytics ワークスペースで取得する、Windows のパフォーマンスカウンター (パフォーマンスモニター) の情報を、コマンドで追加することができます。
今回、ちょい足しする情報であれば、次のようなスクリプトで、項目を追加することができます。
$resourceGroup = "Resource Group Name"
$workspaceName = "Workspace Name"
Connect-AzAccount
$counterNames = @(
"Batches >=000000ms & <000001ms",
"Batches >=000001ms & <000002ms",
"Batches >=000002ms & <000005ms",
"Batches >=000005ms & <000010ms",
"Batches >=000010ms & <000020ms",
"Batches >=000020ms & <000050ms",
"Batches >=000050ms & <000100ms",
"Batches >=000100ms & <000200ms",
"Batches >=000200ms & <000500ms",
"Batches >=000500ms & <001000ms",
"Batches >=001000ms & <002000ms",
"Batches >=002000ms & <005000ms",
"Batches >=005000ms & <010000ms",
"Batches >=010000ms & <020000ms",
"Batches >=020000ms & <050000ms",
"Batches >=050000ms & <100000ms",
"Batches >=100000ms"
)
foreach($counter in $counterNames){
$datasourceName = "DataSource_WindowsPerformanceCounter_{0}" -f (New-Guid).ToString()
New-AzOperationalInsightsWindowsPerformanceCounterDataSource -ResourceGroupName $resourceGroup -WorkspaceName $workspaceName `
-Name $datasourceName -ObjectName "SQLServer:Batch Resp Statistics" -CounterName $counter -IntervalSeconds 10
}
Azure Arc enabled Server の分析情報を取得する Log Analytics ワークスペースをターゲットとして、上記のスクリプトを実行すると、MMA により、Batch Resp Statistics の情報が追加で取得されるようになります。
Batch Resp Statistics は上記のスクリプトに記載しているように、事前に定義されている時間範囲のクエリの実行情報の「累積値」を確認できるものとなります。
Batch Resp Statistics には、「CPU Time:Requests」「CPU Time:Total(ms)」「Elapsed Time:Requests」「Elapsed Time:Total(ms)」の 4 種類の項目が含まれています。
「Batches >=000000ms & <000001ms」を例にすると、次のような情報を確認することができます。
- CPU Time:Requests : 1ms 未満の CPU 使用時間のクエリが秒間に何要求あったか
- CPU Time:Total(ms) : 1ms 未満の CPU 使用時間のクエリの累積により、秒間に何 ms の CPU が使用されたか
- Elapsed Time:Requests : 1ms 未満の経過時間のクエリが秒間に何要求あったか
- Elapsed Time:Total(ms) : 1ms 未満の経過時間のクエリの累積により、秒間に何 ms 実行に時間がかかったか
これらの情報は「累積値」となっていますので「特定の時間帯の情報を確認する」ためには、一つ前の取得間隔との差分を算出する必要があります。
Log Analytics ワークスペースに取得された情報は、該当の Log Analytics ワークスペースで KQL により、情報を確認することができますので、ワークスペースで、次のようなクエリを実行してみます。
let targetInstance = "CPU Time:Total(ms)";
//let targetInstance = "CPU Time:Requests";
//let targetInstance = "Elapsed Time:Requests";
//let targetInstance = "Elapsed Time:Total(ms)";
Perf
| where ObjectName == "SQLServer:Batch Resp Statistics" and InstanceName == targetInstance and TimeGenerated >= ago(30m)
| project TimeGenerated = format_datetime(TimeGenerated, 'yyyy-MM-dd HH:mm'), InstanceName, CounterName, CounterValue
| evaluate pivot(CounterName, max(CounterValue))
| sort by TimeGenerated asc
| serialize
// | extend interval = datetime_diff('second', todatetime(TimeGenerated), todatetime(prev(TimeGenerated,1)))
| project todatetime(TimeGenerated), InstanceName,
['Batches >=000000ms & <000001ms'] = ['Batches >=000000ms & <000001ms'] - prev(['Batches >=000000ms & <000001ms'], 1),
['Batches >=000001ms & <000002ms'] = ['Batches >=000001ms & <000002ms'] - prev(['Batches >=000001ms & <000002ms'], 1),
['Batches >=000002ms & <000005ms'] = ['Batches >=000002ms & <000005ms'] - prev(['Batches >=000002ms & <000005ms'], 1),
['Batches >=000005ms & <000010ms'] = ['Batches >=000005ms & <000010ms'] - prev(['Batches >=000005ms & <000010ms'], 1),
['Batches >=000010ms & <000020ms'] = ['Batches >=000010ms & <000020ms'] - prev(['Batches >=000010ms & <000020ms'], 1),
['Batches >=000020ms & <000050ms'] = ['Batches >=000020ms & <000050ms'] - prev(['Batches >=000020ms & <000050ms'], 1),
['Batches >=000050ms & <000100ms'] = ['Batches >=000050ms & <000100ms'] - prev(['Batches >=000050ms & <000100ms'], 1),
['Batches >=000100ms & <000200ms'] = ['Batches >=000100ms & <000200ms'] - prev(['Batches >=000100ms & <000200ms'], 1),
['Batches >=000200ms & <000500ms'] = ['Batches >=000200ms & <000500ms'] - prev(['Batches >=000200ms & <000500ms'], 1),
['Batches >=000500ms & <001000ms'] = ['Batches >=000500ms & <001000ms'] - prev(['Batches >=000500ms & <001000ms'], 1),
['Batches >=001000ms & <002000ms'] = ['Batches >=001000ms & <002000ms'] - prev(['Batches >=001000ms & <002000ms'], 1),
['Batches >=002000ms & <005000ms'] = ['Batches >=002000ms & <005000ms'] - prev(['Batches >=002000ms & <005000ms'], 1),
['Batches >=005000ms & <010000ms'] = ['Batches >=005000ms & <010000ms'] - prev(['Batches >=005000ms & <010000ms'], 1),
['Batches >=010000ms & <020000ms'] = ['Batches >=010000ms & <020000ms'] - prev(['Batches >=010000ms & <020000ms'], 1),
['Batches >=020000ms & <050000ms'] = ['Batches >=020000ms & <050000ms'] - prev(['Batches >=020000ms & <050000ms'], 1),
['Batches >=050000ms & <100000ms'] = ['Batches >=050000ms & <100000ms'] - prev(['Batches >=050000ms & <100000ms'], 1),
['Batches >=100000ms'] = ['Batches >=100000ms'] - prev(['Batches >=100000ms'], 1)
| where isnotnull(['Batches >=000000ms & <000001ms'])
今回は、「CPU Time:Total(ms)」を対象としていますが、分析の観点によっては他の項目に変更する必要も出てくるかと。
クエリを実行すると、次のように、時系列で各バッチの時間範囲の差分情報を確認することができます。
テーブルだと、状況が把握しづらい箇所がありますので、次のような設定で積み上げ面グラフを作成してみます。
積み上げ面グラフを作成すると、次のようなグラフを作成することができます。
このグラフは、時系列で CPU 使用時間の範囲毎の CPU 使用状況を示したものとなります。
積み上げの面グラフとなっているため「面積が多い時間範囲のクエリの積み重ねが CPU 使用時間を上昇させる原因となっている可能性がある」というような判断に使用することができます。
上記のグラフであれば面積が多いのは「20ms～50ms」の範囲のクエリとなっています。
つまり、CPU 使用時間が 20ms～50ms のクエリの CPU 使用時間を減らす (一つ下の時間範囲に効率化できれば) ことができれば、CPU 負荷の低減に効果がありそうだという判断材料として使用することができます。
クエリストアやキャッシュから、CPU 使用時間が 20ms～50ms の範囲のクエリで、実行回数が多いものを調査して、そのクエリの効率を改善できれば、CPU 負荷を減らすことができる可能性があるということになりますね。
運用している SQL Server でこのような情報が定常的に取得できていると「CPU 負荷が上昇した際にはどの時間範囲の CPU 使用時間のクエリが原因となっているか」を調査することができ、改善が必要なクエリの調査の即応性の向上につなげることができます。
Azure Arc enabled SQL Server では、Azure Arc enabled Servers の分析情報を取得する機能を使用することで、CPU / メモリ / ディスク使用状況などの基本情報が自動的に取得されています。
それに加え、MMA により、任意のパフォーマンスカウンターの情報を追加することができますので、上記の項目のように、通常運用で確認しておいた方がよい項目をちょい足しすることもできます。
今回は、Azure Arc enabled SQL Server で実施していますが、これは、Log Analytics エージェントをインストールしている環境でも活用することができますので、Azure のモニタリング機能を有効化した場合は、通常運用を効率化するためのパフォーマンスモニターのカウンターの取得と可視化も検討してみると良いのではないでしょうか。