Zabbix：クエリでSQLServerのパフォーマンス監視

TODO

• 動的管理ビュー(DMV)のパフォーマンスをzabbixで監視する
• zabbix_agentd.confのUserParameterを活用

SQLServer

スクリプト配置

Get-CountSuspended.bat

@echo off
PowerShell -NoProfile -command "C:\workspace\ps1\Get-CountSuspended.ps1"

Get-CountSuspended.ps1

$curPath = "C:\workspace\ps1\"
Import-Module "${curPath}Get-CountSql.psm1"
$sql = "select count(*) as cnt from sys.dm_exec_requests as er where er.status = 'suspended'"
$ret = Get-CountSql -CurPath $curPath  -Sql $sql
$ret

Get-CountSql.psm1

クエリ共通

function Get-CountSql
{
    param(
        [string]$CurPath,
        [string]$Sql
    )
    $ret = 0
    try
    {
        #$sql = $Args[1]
        #$sql = "select count(*) as cnt from sys.dm_exec_requests as er where er.status =  'suspended'"
        $settings = Get-Content "${CurPath}conf\conn.txt" | ConvertFrom-StringData
        $connectionString = $settings.ConnectionString
        #Trust
        $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $connectionString
        $command = $connection.CreateCommand()
        $command.CommandType = [System.Data.CommandType]::Text
        $command.CommandText = $Sql
        $command.CommandTimeout = 300
        $connection.Open()
        $da = New-Object System.Data.SqlClient.SqlDataAdapter
        $da.SelectCommand = $command
        $dtSet = New-Object System.Data.DataSet
        [void]$da.Fill([System.Data.DataSet]$dtSet)
        #$recordCount = $dtSet.Tables[0].Rows.Count
        $ret = $dtSet.Tables[0].cnt
        #$dtSet.Tables[0] > dmv.log
    }
    catch [Exception]
    {
        $Error[0] > Get-CountSql.log
    }
    return $ret
}

Zabbix Agent

zabbix_agentd.conf

UserParameter=dmv.suspended-count,C:\workspace\ps1\Get-CountSuspended.bat

Zabbix

アイテム

• dmv.クエリ実行待ち
• タイプ：Zabbixエージェント（アクティブ）
• キー：dmv.suspended-count
• データ型：数値(整数)

トリガー

• 連続3回100未満がゼロ = 連続3回100超過
• {db-pub:dmv.suspended-count.count(#3,100,"lt")}=0

おまけ

Get-CountRuntime.bat

@echo off
PowerShell -NoProfile -command "C:\workspace\ps1\Get-CountRuntime.ps1"

Get-CountRuntime.ps1

$curPath = "C:\workspace\ps1\"
Import-Module "${curPath}Get-CountSql.psm1"
$sql = "
SELECT max(datediff(MINUTE, der.start_time, GETDATE())) as cnt
FROM sys.dm_exec_requests der
JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id
WHERE der.status in ('running','runnable','suspended')
"
$ret = Get-CountSql -CurPath $curPath  -Sql $sql
$ret

UserParameter=dmv.runtime-count,C:\workspace\ps1\Get-CountRuntime.bat