SE の雑記

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

SQL Server on Azure VM の SQL メトリックで全項目が取得できていないので取得できるように手動で調整をしてみる

leave a comment

2016/5/2 時点 の情報ですので、そのうち修正されるかと思います。
# feedback.azure.com で、フィードバックはしてみました。

現状の仮想マシンの診断には、SQL メトリックという SQL Server 向けのメトリックがあり、SQL Server についての各種情報を取得することができます。
# 既定のインスタンスに限定されているはずですが。

image

このメトリックを追加することで、以下の項目を取得することができるのですが、現状は追加されているカウンター名の誤りで、半分ぐらいの情報が取得できていないようです。

  • SQL buffer page life
  • SQL transaction rate
  • SQL total data file KB
  • SQL total log file KB
  • SQL total log file used KB
  • SQL user connections
  • SQL total memory KB
  • SQL batch request rate
  • SQL attention rate
  • SQL recompilation rate
  • SQL error rate
  • SQL active requests
  • SQL blocked tasks
  • SQL CPU percentage
  • SQL queued requests

上記の項目ですが、以下のような項目をパフォーマンスモニターから取得するように設定が行われます。
下線を付与しているものが、現状方法が取得されていない項目になります。

SQL buffer page life

SQLServer:Buffer Manager\Page life expectancy

SQL transaction rate

SQLServer:Databases\Transactions/sec

SQL total data file KB

SQLServer:Databases\Data file(s) size (KB)

SQL total log file KB

SQLServer:Databases\Log file(s) size (KB)

SQL total log file used KB

SQLServer:Databases\Log file(s) used size (KB)

SQL user connections

SQLServer:General Statistics\User Connections

SQL total memory KB

SQLServer:Memory Manager\Total Server Memory (KB)

SQL batch request rate

SQLServer:SQL Statistics\Batch Requests/sec

SQL attention rate

SQLServer:SQL Statistics\SQL Attention Rate

SQL recompilation rate

SQLServer:SQL Statistics\SQL Re-Compilations/Sec

SQL error rate

SQLServer: SQL errors\Errors/sec

SQL active requests

SQL Server: Workload group stats\Active requests

SQL blocked tasks

SQL Server: Workload group stats\Blocked tasks

SQL CPU percentage

SQL Server: Workload group stats\CPU usage %

SQL queued requests

SQL Server: Workload group stats\Queued requests

 

カウンターをみて気づかれた方もいるかもしれませんが、カウンター名が間違っている (不要なスペースが入っている) のと、カウンターのインスタンスの指定が必要なものがあるのですが、インスタンスが指定されていないため取得がされていないのですよね。。。

上記の項目については SQL メトリックを追加している環境で以下のコマンドを実行すると確認できるかと。

$file = Get-ChildItem "C:\Packages\Plugins\Microsoft.Azure.Diagnostics.IaaSDiagnostics\*\RuntimeSettings" -Recurse | Sort-Object LastWriteTime -Descending | select -First 1
$file.FullName
$json = gc $file | convertfrom-Json
[System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($json.runtimeSettings.handlerSettings.publicSettings.xmlCfg))

 

インスタンス名を「(*)」にするかどうかは検討事項ですが、項目としては以下のようなカウンターを追加することで、SQL メトリックの情報を取得することができます。

SQL buffer page life SQLServer:Buffer Manager\Page life expectancy
SQL transaction rate SQLServer:Databases(_Total)\Transactions/sec
SQL total data file KB SQLServer:Databases(_Total)\Data file(s) size (KB)
SQL total log file KB SQLServer:Databases(_Total)\Log file(s) size (KB)
SQL total log file used KB SQLServer:Databases(_Total)\Log file(s) used size (KB)
SQL user connections SQLServer:General Statistics\User Connections
SQL total memory KB SQLServer:Memory Manager\Total Server Memory (KB)
SQL batch request rate SQLServer:SQL Statistics\Batch Requests/sec
SQL attention rate SQLServer:SQL Statistics\SQL Attention Rate
SQL recompilation rate SQLServer:SQL Statistics\SQL Re-Compilations/Sec
SQL error rate SQLServer:SQL errors(_Total)\Errors/sec
SQL active requests SQLServer:Workload group stats(default)\Active requests
SQL blocked tasks SQLServer:Workload group stats(default)\Blocked tasks
SQL CPU percentage SQLServer:Workload group stats(default)\CPU usage %
SQL queued requests SQLServer:Workload group stats(default)\Queued requests

 

メトリックについては ARM テンプレートからも設定変更できますので、以下のようなテンプレートで、デフォルトで選択されている項目に SQL メトリックを追加できるかと。

{
    "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "location": {
            "type": "String"
        },
        "virtualMachineName": {
            "type": "String"
        },
        "diagnosticsStorageAccountName": {
            "type": "String"
        }
    },
    "variables": {
        "metricsresourceid": "[concat('/subscriptions/', subscription().subscriptionId, '/resourceGroups/', resourceGroup().name, '/providers/', 'Microsoft.Compute/virtualMachines/', parameters('virtualMachineName'))]",
        "metricsclosing": "[concat('<metrics resourceId=\"', variables('metricsresourceid'), '\"><metricAggregation scheduledTransferPeriod=\"PT1H\"/><metricAggregation scheduledTransferPeriod=\"PT1M\"/></metrics></diagnosticMonitorConfiguration></wadCfg>')]",
        "metricsstart": "<wadCfg><diagnosticMonitorConfiguration xmlns=\"http://schemas.microsoft.com/ServiceHosting/2010/10/DiagnosticsConfiguration\" overallQuotaInMB=\"4096\"><diagnosticInfrastructureLogs scheduledTransferPeriod=\"PT1M\" scheduledTransferLogLevelFilter=\"Warning\"/><windowsEventLog scheduledTransferPeriod=\"PT1M\"><dataSource name=\"Security!*&#91;System&#91;(Level = 1) or (Level = 2)&#93;&#93;\"/><dataSource name=\"Application!*&#91;System&#91;(Level = 1) or (Level = 2)&#93;&#93;\"/><dataSource name=\"System!*&#91;System&#91;(Level = 1) or (Level = 2)&#93;&#93;\"/><dataSource name=\"System!*&#91;System&#91;Provider&#91;@Name='Microsoft Antimalware'&#93;&#93;&#93;\"/></windowsEventLog>",
        "metricscounters": "<performanceCounters scheduledTransferPeriod=\"PT0S\"><performanceCounterConfiguration counterSpecifier=\"\\Processor(_Total)\\% Processor Time\" sampleRate=\"PT15S\" unit=\"Percent\"><annotation displayName=\"CPU percentage guest OS\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Processor(_Total)\\% Interrupt Time\" sampleRate=\"PT15S\" unit=\"Percent\"><annotation displayName=\"CPU interrupt time\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Processor(_Total)\\% Privileged Time\" sampleRate=\"PT15S\" unit=\"Percent\"><annotation displayName=\"CPU privileged time\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Processor Information(_Total)\\Parking Status\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"CPU parking status\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Processor(_Total)\\% User Time\" sampleRate=\"PT15S\" unit=\"Percent\"><annotation displayName=\"CPU user time\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Processor Information(_Total)\\% Processor Performance\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"Processor percent perf.\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Processor Information(_Total)\\Processor Frequency\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"Processor frequency\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\System\\Threads\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"Threads\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\System\\Processes\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"Processes\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Thread(_Total)\\Context Switches/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Context switches\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\Committed Bytes\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\"Memory committed\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\Available Bytes\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\"Memory available\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\% Committed Bytes In Use\" sampleRate=\"PT15S\" unit=\"Percent\"><annotation displayName=\"Memory percentage\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\Cache Faults/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Cache faults\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\Page Faults/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Page faults\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\Page Reads/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Page reads\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\Pages/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Memory pages\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\Transition Faults/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Transition faults\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\Pool Paged Bytes\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\"Paged pool\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Memory\\Pool Nonpaged Bytes\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\"Non-paged pool\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Process(_Total)\\% Processor Time\" sampleRate=\"PT15S\" unit=\"Percent\"><annotation displayName=\"Process total time\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Process(_Total)\\Page Faults/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Process page faults\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Process(_Total)\\Thread Count\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"Process total threads\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Process(_Total)\\Handle Count\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"Process total handles\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Process(_Total)\\Private Bytes\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\"Process function bytes\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Process(_Total)\\Working Set\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\"Process working set\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Process(_Total)\\Working Set - Private\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\"Process function working set\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\PhysicalDisk(_Total)\\Disk Read Bytes/sec\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"Disk read guest OS\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\PhysicalDisk(_Total)\\Disk Write Bytes/sec\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"Disk write guest OS\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Web Service(_Total)\\Bytes Total/Sec\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"Web service bytes\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Web Service(_Total)\\ISAPI Extension Requests/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"ISAPI extension requests\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Web Service(_Total)\\Connection Attempts/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Web connection attempts\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Web Service(_Total)\\Current Connections\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"Web current connections\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Web Service(_Total)\\Get Requests/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Web get requests\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\Web Service(_Total)\\Post Requests/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"Web post requests\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\TCPv4\\Connections Established\" sampleRate=\"PT15S\" unit=\"Count\"><annotatio
n displayName=\"TCP connections established\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\TCPv4\\Connection Failures\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"TCP connections failed\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\TCPv4\\Connections Reset\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\"TCP connections reset\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\TCPv4\\Segments Sent/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"TCP segments sent\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\TCPv4\\Segments Received/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"TCP segments received\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\TCPv4\\Segments Retransmitted/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\"TCP seg. restransmitted\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Jit(_Global_)\\% Time in Jit\" sampleRate=\"PT15S\" unit=\"Percent\"><annotation displayName=\".NET CLR time in jit\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Loading(_Global_)\\% Time Loading\" sampleRate=\"PT15S\" unit=\"Percent\"><annotation displayName=\".NET CLR time loading\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR LocksAndThreads(_Global_)\\Current Queue Length\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\".NET CLR queue length\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR LocksAndThreads(_Global_)\\Contention Rate / sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\".NET CLR contention rate\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR LocksAndThreads(_Global_)\\# of current logical Threads\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\".NET CLR logical threads\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR LocksAndThreads(_Global_)\\# of current physical Threads\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\".NET CLR phys. threads\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Memory(_Global_)\\% Time in GC\" sampleRate=\"PT15S\" unit=\"Percent\"><annotation displayName=\".NET CLR time in GC\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Memory(_Global_)\\Allocated Bytes/sec\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\".NET CLR allocated\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Memory(_Global_)\\Gen 0 heap size\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\".NET CLR gen 0 heap size\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Memory(_Global_)\\Gen 1 heap size\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\".NET CLR gen 1 heap size\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Memory(_Global_)\\Gen 2 heap size\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\".NET CLR gen 2 heap size\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Memory(_Global_)\\Large Object Heap size\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\".NET CLR large obj. heap size\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Memory(_Global_)\\# Bytes in all Heaps\" sampleRate=\"PT15S\" unit=\"Bytes\"><annotation displayName=\".NET CLR heap bytes\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Networking(_Global_)\\Connections Established\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\".NET CLR connections \" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Remoting(_Global_)\\Remote Calls/sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\".NET CLR remote calls\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Exceptions(_Global_)\\# of Exceps Thrown / sec\" sampleRate=\"PT15S\" unit=\"CountPerSecond\"><annotation displayName=\".NET CLR exception rate\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\.NET CLR Interop(_Global_)\\# of marshalling\" sampleRate=\"PT15S\" unit=\"Count\"><annotation displayName=\".NET CLR interop marsh.\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Buffer Manager\\Page life expectancy\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL buffer page life\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Databases(_Total)\\Transactions/sec\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL transaction rate\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Databases(_Total)\\Data file(s) size (KB)\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL total data file KB\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Databases(_Total)\\Log file(s) size (KB)\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL total log file KB\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Databases(_Total)\\Log file(s) used size (KB)\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL total log file used KB\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:General Statistics\\User Connections\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL user connections\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Memory Manager\\Total Server Memory (KB)\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL total memory KB\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:SQL Statistics\\Batch Requests/sec\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL batch request rate\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:SQL Statistics\\SQL Attention Rate\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL attention rate\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:SQL Statistics\\SQL Re-Compilations/Sec\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL recompilation rate\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:SQL errors(_Total)\\Errors/sec\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL error rate\" locale=\"en-us\"/></performanceCounterConfigurati
on><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Workload group stats(default)\\Active requests\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL active requests\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Workload group stats(default)\\Blocked tasks\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL blocked tasks\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Workload group stats(default)\\CPU usage %\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL CPU percentage\" locale=\"en-us\"/></performanceCounterConfiguration><performanceCounterConfiguration counterSpecifier=\"\\SQLServer:Workload group stats(default)\\Queued requests\" sampleRate=\"PT15S\" unit=\"BytesPerSecond\"><annotation displayName=\"SQL queued requests\" locale=\"en-us\"/></performanceCounterConfiguration></performanceCounters>",
        "wadcfgx": "[concat(variables('metricsstart'), variables('metricscounters'), variables('metricsclosing'))]"
    },
    "resources": [
        {
            "type": "Microsoft.Compute/virtualMachines/extensions",
            "name": "[concat(parameters('virtualMachineName'),'/Microsoft.Insights.VMDiagnosticsSettings')]",
            "apiVersion": "2015-05-01-preview",
            "location": "[parameters('location')]",
            "properties": {
                "publisher": "Microsoft.Azure.Diagnostics",
                "type": "IaaSDiagnostics",
                "typeHandlerVersion": "1.5",
                "autoUpgradeMinorVersion": true,
                "settings": {
                    "xmlCfg": "[base64(variables('wadcfgx'))]",
                    "StorageAccount": "[parameters('diagnosticsStorageAccountName')]"
                },
                "protectedSettings": {
                    "storageAccountName": "[parameters('diagnosticsStorageAccountName')]",
                    "storageAccountKey": "[listKeys(concat('Microsoft.Storage/storageAccounts/',parameters('diagnosticsStorageAccountName')),'2015-05-01-preview').key1]",
                    "storageAccountEndPoint": "https://core.windows.net/"
                }
            }
        }
    ],
    "outputs": {
    }
}

 

今回の修正方法では、アラートのメトリックとしては使えなさそうなので、根本から修正されるのを待つ必要があるかと思いますが、取得項目の調整でしたらひとまずは項目を足してしまえばカバーできるかと。

Share

Written by Masayuki.Ozawa

5月 2nd, 2016 at 5:16 pm

Posted in Microsoft Azure,SQL Server

Tagged with ,

«
»

Leave a Reply