PowerShell で操作する SQL Server クックブック（性能情報の取得１）

Microsoft Japan Data Platform Tech Sales Team

西村哲徳

みなさん、こんにちは。1回目から随分時間がたってしまいましたが「PowerShell で操作する SQL Server クックブック」の第二弾です。前回は性能評価用の SQL を PowerShell でまとめて実行する方法を bash と比較しながら紹介しましたが、今回は単に実行するだけでなく性能分析に必要な情報を取得するスクリプトを紹介します。

Windows 環境で性能情報を取得するツールとしてパフォーマンス モニターがあります。GUI で簡単に設定し、性能情報を取得、分析できるので実システムで使用し重宝されている方も多いと思います。 通常の運用であれば常に取得しておく必要があるので、GUI で一度設定しておけば問題ないですが、性能評価などで特定のクエリやバッチを流している間のみ取得したい場合などはパフォーマンス モニターをスクリプトで操作したほうが便利です。特に実行時間が予測できない処理、夜間もテストスクリプトをまわしておきたい場合などもを自動的に取得できるので時間も有効に使えます。

今回の目的は、データベース サーバの性能情報を PowerShell スクリプトで取得することです。一般的に取得すべき情報として表1の項目があげられますが、基本的な手順は同じなので本ブログでは赤太字で記した一部のみの取得としています。

■　データベースサーバで取得する情報　（表１）

オブジェクト	カウンター	インスタンス
Memory	すべてのカウンター	ー
Network Interface	すべてのカウンター	すべての
PhysicalDisk	すべてのカウンター	すべてのインスタンス
Processor Information	すべてのカウンター	すべてのインスタンス
Process	すべてのカウンター	すべてのインスタンス
System	Context Switches/sec	ー
	Processor Queue Length
SQL Server:Access Method	すべてのカウンター	ー
SQL Server:Buffer Manager	すべてのカウンター	ー
SQL Server:Databases	すべてのカウンター	すべてのインスタンス
SQL Server:General Statistics	すべてのカウンター	ー
SQL Server:Locks	Number of Deadlocks/sec	_Total
SQL Server:Memory Manager	すべてのカウンター	ー
SQL Server:SQL Statistics	すべてのカウンター	ー
SQL Server:Wait Statistics	すべてのカウンター	すべてのインスタンス

 

用語の解説

• オブジェクト：　パフォーマンス モニターで監視対象として定義されているもの。例えば CPU であれば Processor オブジェクト、ディスク IO であれば PhysicalDisk などです。　特長的なのは SQL Server で監視しておきたい多くの項目がパフォーマンス モニターによって定義されていることです。そのおかげで１つのツールから OS もデータベースも監視することが可能です。
• カウンター：　実際に監視される項目。例えば Processor オブジェクトの監視項目としては「% User Time」、「% Priviledge Time」などがあり、それらで CPU 使用率を見ることができます。
• インスタンス：　オブジェクトで定義されているものが複数ある場合、その個々をインスタンスといいます。　例えばサーバ内に複数の CPU がある場合は、それぞれの CPU がインスタンスにあたり、個々の CPU に対してカウンターを取得することができます。また、インスタンスすべての情報の集計値を取得するために _total というインスタンスもあります。

では、はじめましょう。前提として今回は PowerShell 3.0 以上のバージョンを対象としています。

１．基本コマンドを覚える

Windows で性能情報を取得するために必要なコマンドは１つのみ覚えれば大丈夫です。 Linux の場合だと sar や dstat など一括で取得できるコマンドもありますが一般的には iostat、vmstat、mpstat など用途ごとにツールを使い分けている方も多いと思います。sar や dstat を使っていても、少なくともデータベースの性能情報に関しては os のコマンドで取得することはできません。その点、Windows では下記コマンドでデータベースを含めた情報も取得できます。

 Get-Counter [[-Counter] <カウンタ-名> ] [-SampleInterval <取得間隔(秒)>] [-MaxSamples <取得回数> ] [-Continuous] [-ComputerName <コンピュータ名>] [<CommonParameters>]

"Get-Counter" というコマンド名は Linux ユーザにとっては違和感がありますが、実は動詞-名詞という命名規則になっているため直観的に理解しやすい形になっています。どうしても違和感があるという方はエイリアスをつけることができますが、意外と使いやすく応用も効きやすいのでこれを機に慣れていただければと思います。

SampleInterval と MaxSamples は取得間隔と取得回数を指定するオプションなので特に問題はないと思います。今回のように取得回数がわからない場合は MaxSamples ではなくContinuous を指定します。そうすることで、Ctrl+C で止めるまで動き続けます。

あとは適切なカウンター名を指定するだけで、性能情報が取得できます。例えば CPU 使用率のカウンター名で "\Processor(_Total)\% Prcessor Time" があるのでこれを指定して実行すると CPU 使用率が取得できます。意外とコマンドで簡単に取得できることがわかっていただけたかと思います。

 PS C:\Users\tetsu.MYDOMAIN> Get-counter -counter "\Processor(*)\% Processor Time"
Timestamp             CounterSamples
---------             --------------
2017/02/06 6:53:56    \\sql2016eesp1-01\processor(0)\% processor time : 0
                      \\sql2016eesp1-01\processor(_total)\% processor time : 0

２．カウンター名の取得

カウンター名を指定すれば簡単に性能情報は取得できますが、そもそもカウンター名がわからないという方のために調べ方を紹介します。簡単な方法としては、パフォーマンス モニターの画面から確認する方法がありますが、せっかくPowerShell関連のブログなので、ここではコマンドから調べてみます。使うコマンドは先程と同じで Get-Counter です。

 Get-Counter [-ListSet] <オブジェクト名>] [-ComputerName <コンピュータ名>] [<CommonParameters>]

では例として ListSet にオブジェクト PhysicalDisk を指定してカウンター名を取得してみます。オブジェクト名にはワイルドカードも使用できます。

 PS C:\Users\tetsu.MYDOMAIN> Get-Counter -listset "PhysicalDisk"
CounterSetName : PhysicalDisk
MachineName : .
CounterSetType : MultiInstance
Description : Physical Disk パフォーマンス オブジェクトには、ハードディスクまたは固定ディスク ドライブを監視するカウンターがあります。ディスクは、ファイル、プログラム、ページング データの格納に使用され、これらを取り出すために読み取られたり、変更を記録するために書き込まれたりします。物理ディスク カウンターの値は、分割された論理ディスク (またはパーティション) の合計値となります。

Paths : {\PhysicalDisk(*)\Current Disk Queue Length, \PhysicalDisk(*)\% Disk Time, \PhysicalDisk(*)\Avg. Disk Queue Length, \PhysicalDisk(*)\% Disk Read Time...}
PathsWithInstances : {\PhysicalDisk(0 C:)\Current Disk Queue Length, \PhysicalDisk(1 D:)\Current Disk Queue Length, \PhysicalDisk(_Total)\Current Disk Queue Length, \PhysicalDisk(0 C:)\% Disk Time...}
Counter : {\PhysicalDisk(*)\Current Disk Queue Length, \PhysicalDisk(*)\% Disk Time, \PhysicalDisk(*)\Avg. Disk Queue Length, \PhysicalDisk(*)\% Disk Read Time...}

カウンター名は、Paths、PathsWithInstances、Counter に表示されたものを指定することができます。「\オブジェクト名(インスタンス名)\項目名」または「\オブジェクト名\項目名」になっています。すべてのインスタンス、すべてのカウンターを取得する場合にはワイルドカードを使い「\オブジェクト名(*)\*」または「\オブジェクト名\*」と指定します。

 PS C:\Users\tetsu.MYDOMAIN> Get-counter -counter "\PhysicalDisk(*)\*"
Timestamp             CounterSamples
---------             --------------
2017/02/06 7:08:30    \\sql2016eesp1-01\physicaldisk(0 c:)\current disk queue length :0
                      \\sql2016eesp1-01\physicaldisk(0 c:)\% disk time :0
                      （省略）
                      \\sql2016eesp1-01\physicaldisk(_total)\avg. disk bytes/write :0
                      \\sql2016eesp1-01\physicaldisk(_total)\% idle time :99.1187748489833
                      \\sql2016eesp1-01\physicaldisk(_total)\split io/sec :0

同様の方法でその他のオブジェクトのカウンター名も取得してみてください。なお、複数のカウンターを指定する場合はカンマ区切りで指定します。

 # オブジェクト SQLServer:Access Methods、PhysicalDisk、SQL Server:Buffer Managerのカウンターを取得
Get-Counter -counter "\SQLServer:Access Methods\*","\PhysicalDisk(*)\*","\SQLServer:Buffer Manager\*"

３．バックグラウンドでの実行

これで性能情報を取得する準備が整いました。あとは実行するだけですが、処理開始前に取得を開始し終了後に終了するためには Get-Counter をバックグラウンドで実行する必要があります。

Linux では ”＆” でバックグラウンド実行し、処理終了後にそのプロセスを kill します。

 #5秒間隔で取得するiostatをバックグラウンドで実行
iostat -dtx 5 &

# バックグラウンドのPIDを取得
$pid=$!

<測定対象処理の実行>

#処理終了後、iostatをkill
kill -9 $pid

PowerShell ではもちろん"＆"は使えないですが、非常に便利なコマンドレット Start-Job があります。 その名のとおりジョブを開始するという意味なので直観的にバックグラウンドで実行するためのコマンドとわかると思います。help で見るといろいろとオプションがでてきますがここで覚えてほしいのは下記の構文です。

 Start-Job { <性能情報取得コマンド> }

また戻り値として実行したジョブのオブジェクト（正式には PSRemotingJob）を戻しますので、処理実行後はこのジョブを停止するだけです。もちろんジョブを停止するコマンドレットもあり、予想通りの Stop-Job という名前です。

ではこれらを踏まえてスクリプトを見てみましょう。

 # Get-Counterをバックグラウンドジョブとして実行
$job= start-job -Scriptblock { Get-counter -counter "\PhysicalDisk(*)\*" -Continuous }

<測定対象処理の実行>

#処理終了後、Get-Counterのジョブの停止
Stop-Job $job

このままだと画面にもファイルにも出力されないので最後にカウンターの情報をファイルに出力するコマンドを紹介します。これもコマンドレットでその名のとおりExport-Counterというわかりやすいコマンドになっています。　パイプでつないでファイル名、フォーマットを指定すれば整形した状態でファイル出力ができます。

 $job= start-job -Scriptblock { Get-counter -counter "\PhysicalDisk(*)\*" -Continuous | Export-Counter -path C:\temp\getcount.csv -fileformat csv }

ここまでが今回の主な内容となります。既に PowerShell オリジナルの世界に突入し bash とはだいぶ離れてきてますが、少しづつ試してみると PowerShell も簡単だなと感じていただけると幸いです。

 

関連記事

• PowerShell で操作する SQL Server クックブック (bash から PowerShell へ)
• [SQL Troubleshooting] 第2回 : Tips -パフォーマンス ログの採取方法 (Windows Server 2003 ～ Windows Server 2012 R2)
• [SQL Troubleshooting] 第3回： パフォーマンスログの確認方法について