SE の雑記

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

SQL Live Monitor と PAL Tool を使用したモニタリングとレポーティング

leave a comment

今日は、CodePlex で提供されている [SQL Live Monitor] と [Performance Analysis of Logs (PAL) Tool] を使用した、SQL Server のモニタリングとレポーティングについてまとめてみたいと思います。

■SQL Live Monitor

SQL Live Monitor を使用すると、SQL Server の現状の稼働状況を確認することができます。
image
[SQL Server] の欄に情報を取得する SQL Server 名 (既定のインスタンスの場合はサーバー名、名前付きインスタンスの場合はサーバー名インスタンス名) を入力して、[Start] をクリックすることで情報が取得できます。

パフォーマンスチューニングをするにあたって、いくつかの項目は手動で情報を取得する必要がありますが現状の状態を把握するのにはとても便利なツールだと思います。
データキャッシュ / プロシージャキャッシュのメモリ使用状況は Page Life Expectancy といった定番の情報をリアルタイムに見やすく確認することが可能です。
Cache Hit Ratio 系は SQL Server のサービスが最後に起動してからの情報なのであまり参考にならない可能性がありますが…。
矢印でデータの流れがわかるようになっており、[>->->] であれば、左の項目から右の項目に、[<-<-<-] であれば右の項目から左の項目に対してデータが流れていることになります。

たとえば、[Checkpoints/Sec] ですが、チェックポイントが発生するとダーティーページがデータファイルに対してフラッシュされます。
そのため、[SQL Memory] から [Disk Storage] に対してデータが流れることになります。この矢印の方向を理解しておくと SQL Server のデータの流れが分かるかと思います。

 

情報はパフォーマンスモニタから取得されており、以下のような項目を取得し、画面に表示を行っています。

DiskIdleTime = LogicalDisk% Idle Time
AvgDiskReadSec = LogicalDiskAvg. Disk sec/Read
AvgDiskWriteSec = LogicalDiskAvg. Disk sec/Write
AvgDiskQueueLength = LogicalDiskAvg. Disk Queue Length
DiskReadBytesSec = LogicalDiskDisk Read Bytes/sec
DiskWriteBytesSec = LogicalDiskDisk Write Bytes/sec

TransactionsSec = DatabasesTransactions/sec
LogFlushes = DatabasesLog Flushes/sec
TempDBSize = DatabasesData File(s) Size (KB)tempdb
TempDBLogSize = DatabasesLog File(s) Size (KB)tempdb
TempLogSpaceUsed = DatabasesLog File(s) Used Size (KB)tempdb

CPUPercent = Processor% Processor Time

CompilesSec = SQL StatisticsSQL Compilations/sec
RecompilesSec = SQL StatisticsSQL Re-Compilations/sec
BatchesSec = SQL StatisticsBatch Requests/sec

TargetMemory = Memory ManagerTarget Server Memory (KB)
TotalMemory = Memory ManagerTotal Server Memory (KB)
ConnectionMemory = Memory ManagerConnection Memory (KB)
MemGrantsPending = Memory ManagerMemory Grants Pending

BufferCacheSize = Buffer ManagerDatabase pages
BufferCacheHitRatio = Buffer ManagerBuffer cache hit ratio
UserPageLookups = Buffer ManagerPage lookups/sec
PageLife = Buffer ManagerPage life expectancy
LazyWrites = Buffer ManagerLazy writes/sec
ReadAheads = Buffer ManagerReadahead pages/sec
Checkpoints = Buffer ManagerCheckpoint pages/sec
DiskReads = Buffer ManagerPage reads/sec
DiskWrites = Buffer ManagerPage writes/sec
StolePages = Buffer ManagerStolen pages

LocksPerSecond = LocksLock Requests/sec
LockWaits = LocksLock Waits/sec_Total
AvgLockWaitTime = LocksAverage Wait Time (ms)_Total

UserConnections = General StatisticsUser Connections
LoginsSec = General StatisticsLogins/sec
TempObjectCreate = General StatisticsTemp Tables Creation Rate
TempObjectDestroy = General StatisticsTemp Tables For Destruction
TempActiveTables = General StatisticsActive Temp Tables

ProcedureCacheSize = Plan CacheCache PagesSQL Plans
ProcedureCacheHitRatio = Plan CacheCache Hit RatioSQL Plans

MemPagesSec = MemoryPages/sec
FreePTEs = MemoryFree System Page Table Entries
PagedPool = MemoryPool Paged Bytes
NonPagedPool = MemoryPool Nonpaged Bytes
AvailableSystemRam = MemoryAvailable MBytes

ProcQueueLength = SystemProcessor Queue Length

WorkTables = Access MethodsWorktables Created/sec
WorkFiles = Access MethodsWorkfiles Created/sec
FullScans = Access MethodsFull Scans/sec

msdtc = Exec StatisticsDistributed QueryExecs in progress

手動でパフォーマンス監視をしたいときはこの項目をベースに必要となる項目を追加していくと楽かもしれないですね。

 

Live Monitor でリンクになっている箇所は情報をブレークダウンすることができます。

[User Connections]
image

[Total]
image

[Locks / sec]
image

[Avg Waits]
image

[Latch Stats]
image

[Current Memory]
image

[Plan Cache]
image

[Expensive Queries]
image

[Detailed TempDB Views]
image

 

SQL Live Monitor は現在の使用状況を確認するだけでなく、ログを取得する機能を持っています。
[Option] ボタンを押すことで、ログ取得の設定をすることが可能です。
image

デフォルトではログ取得は有効になっていないのですが、
[Log Data for offline analysis (CSV)]
[Log Blocked Process Details (CSV)]
[Log Data for PAL Analysis]
を有効にすることで、ログを取得することが可能になります。ログですが、SQL Live Monitor を実行したフォルダに取得されます。
# Blocked Process はうまく取得できなかったのですが…。

 

[PAL Analysis] は PAL Tool で解析をするための取得データになり、このデータはパフォーマンスモニタを使用して取得されています。
設定を有効にすると、[mscounters] というユーザー定義のデータコレクタ セットが作成され、このコレクタ セットで PAL Tool で解析するためのパフォーマンス情報が取得されます。
image

この PAL Tool 用のデータですが以下の情報が取得されています。

Network Interface(*)Bytes Total/sec
Network Interface(*)Current Bandwidth
Network Interface(*)Output Queue Length

SystemProcessor Queue Length

Processor(*)% Processor Time
Processor(*)% Privileged Time
Processor(_Total)% Processor Time
Processor(_Total)% Privileged Time
Processor(*)% Interrupt Time

PhysicalDisk(*)% Idle Time
PhysicalDisk(*)Avg. Disk sec/Read
PhysicalDisk(*)Avg. Disk sec/Write

LogicalDisk(*)% Idle Time
LogicalDisk(*)Avg. Disk sec/Read
LogicalDisk(*)Avg. Disk sec/Write
LogicalDisk(C:)Free Megabytes
LogicalDisk(*)Disk Transfers/sec

MemoryFree System Page Table Entries
MemoryPool Nonpaged Bytes
MemoryPool Paged Bytes
MemoryAvailable MBytes
MemoryPages/sec

SystemContext Switches/sec

Process(*)Private Bytes
Process(*)Handle Count
Process(*)Thread Count
Process(*)% Processor Time
Process(*)Virtual Bytes
Process(*)Working Set

MemorySystem Cache Resident Bytes
MemoryPages Input/sec

Paging File(*)% Usage
Paging File(*)% Usage Peak

Process(sqlservr)% Privileged Time
Process(sqlservr)% Processor Time
Process(*)IO Data Operations/sec
Process(*)IO Other Operations/sec

SQL StatisticsBatch Requests/sec

Access MethodsForwarded Records/sec
Access MethodsFreeSpace Scans/sec
Access MethodsFull Scans/sec
Access MethodsIndex Searches/sec
Access MethodsPage Splits/sec
Access MethodsScan Point Revalidations/sec
Access MethodsWorkfiles Created/sec
Access MethodsWorktables Created/sec

Buffer ManagerBuffer cache hit ratio
Buffer ManagerLazy writes/sec
Buffer ManagerCheckpoint pages/sec
Buffer ManagerFree pages
Buffer ManagerPage life expectancy
Buffer ManagerPage lookups/sec
Buffer ManagerPage reads/sec
Buffer ManagerPage writes/sec

General StatisticsLogins/sec
General StatisticsLogouts/sec
General StatisticsUser Connections

LatchesLatch Waits/sec
LatchesTotal Latch Wait Time (ms)

Memory ManagerMemory Grants Pending
Memory ManagerTarget Server Memory (KB)
Memory ManagerTarget Server Memory(KB)
Memory ManagerTotal Server Memory (KB)
Memory ManagerTotal Server Memory(KB)

SQL StatisticsSQL Compilations/sec
SQL StatisticsSQL Re-Compilations/sec

Locks(_Total)Lock Requests/sec
Locks(_Total)Lock Waits/sec
Locks(_Total)Lock Wait Time (ms)
Locks(_Total)Lock Timeouts (timeout > 0)/sec
Locks(_Total)Number of Deadlocks/sec

このログを使用して PAL Tool でレポートを作成することが可能となります。

 

■Performance Analysis of Logs (PAL) Tool

 

PAL Tool はパフォーマンスモニタで取得した情報を解析しレポートを作成することができるツールになります。
# PowerShell 2.0 と .NET Framework 3.5 の Chart Control が必要になります。
Live Monitor では、PAL Tool の [Microsoft SQL Server 2005/2008] のテンプレートに合わせた情報を取得していますので、取得したデータをすぐに PAL Tool にかけてレポートを作成することが可能です。

こちらが PAL Tool を起動した画面になります。
image

レポートの作り方は簡単で、[Counter Log] から Live Monitor で取得したパフォーマンスモニタのログデータを選択し、
image

[Threshold File] から、[SQL Server 2005/2008] をテンプレートとして設定をし、
image

[Execute] から [Finish] をクリックすることでレポートを作成することが可能です。
image

そうすると裏で PowerShell が実行されレポートが作成されます。
image

PAL Tool の実行が完了するとこのようなレポートが生成されます。
# デフォルトだと PAL Tool を実行したユーザーの [ドキュメントPAL Reports] に出力されます。
image

CPU の使用率の警告やメモリの空きの警告など基本的な情報を出力してくれるほかに SQL Server 固有のレポートが生成されます。
たとえば、Page Life Expectancy は 300 以上を推移しているのが良好な状態というのが定番になっていますが、そのレポートに関しても作成がされています。
# 300 を超えていない場合は赤くなって警告のレポートが作成されます。
image

こちらは CPU の使用率のグラフになるのですが、80%~100% は注意が必要な範囲 (Critical) なので赤くなっているのが確認できます。
以下のグラフでは瞬間的に Clitical の領域に達していることが確認できます。

image

ただし、別のグラフ (Processor の Queue) では Warning / Critical には達していませんので瞬間的な CPU の負荷であって、CPU のスペックの限界までは達していない (処理能力には余裕がある) 状態であると考察することができます。
image

Live Monitor で取得したデータを基に SQL Server の状態と、サーバーの基本的な状態をレポートとして確認をできますので、複数のグラフを見比べサーバーの状態を確認することが可能となります。

パフォーマンス チューニングではベースラインとして現状を確認することが重要になりますので、これらのツールを組み合わせると一からベースライン情報の取得を考えなくてもよいので楽になるかと思います。

グラフをどう読み解いていくかが難しいところではあるのですが、最初の一歩としては Warning / Critical となっている箇所を注視して、他のグラフと重ね合わせていくとよいのかな~と。

Written by masayuki.ozawa

11月 12th, 2010 at 12:15 am

Posted in SQL Server

Leave a Reply

*