MSSQL Performance Data Collection

Aus Software Entwicklung Projekte
Wechseln zu: Navigation, Suche

SQL Counter for each SQL Server Instance

Sammeln dieser Daten sollte periodisch automatisch erfolgen um eine einfache Baseline zu erhalten. Mit dieser einfachen Baseline können Problemstellen dann gezielter untersucht werden.

SQLAgent:Jobs

  • \SQLAgent:Jobs(_Total)\Active jobs

SQLServer:Access

  • \SQLServer:Access Methods\Full Scans/sec
  • \SQLServer:Access Methods\Index Searches/sec
  • \SQLServer:Access Methods\Range Scans/sec
  • \SQLServer:Access Methods\Table Lock Escalations/sec
  • \SQLServer:Access Methods\Workfiles Created/sec
  • \SQLServer:Access Methods\Worktables Created/sec

SQLServer:Buffer

  • \SQLServer:Buffer Manager\Buffer cache hit ratio
  • \SQLServer:Buffer Manager\Page life expectancy
  • \SQLServer:Buffer Manager\Page lookups/sec
  • \SQLServer:Buffer Manager\Page reads/sec
  • \SQLServer:Buffer Manager\Page writes/sec
  • \SQLServer:Buffer Manager\Target pages

SQLServer:Databases

  • \SQLServer:Databases(_Total)\Log Flush Write Time (ms)
  • \SQLServer:Databases(_Total)\Transactions/sec

SQLServer:General Statistics

  • \SQLServer:General Statistics\Logins/sec
  • \SQLServer:General Statistics\Logouts/sec
  • \SQLServer:General Statistics\User Connections

SQLServer:Latches

  • \SQLServer:Latches\Average Latch Wait Time (ms)
  • \SQLServer:Latches\Latch Waits/sec

SQLServer:Locks

  • \SQLServer:Locks(_Total)\Lock Requests/sec
  • \SQLServer:Locks(_Total)\Lock Wait Time (ms)
  • \SQLServer:Locks(_Total)\Number of Deadlocks/sec

SQLServer:Memory

  • \SQLServer:Memory Manager\Stolen Server Memory (KB)
  • \SQLServer:Memory Manager\Target Server Memory (KB)
  • \SQLServer:Memory Manager\Total Server Memory (KB)

SQLServer:Plan Cache

  • \SQLServer:Plan Cache(_Total)\Cache Hit Ratio

SQLServer:Databases

  • \SQLServer:SQL Errors(_Total)\Errors/sec

SQLServer:SQL Statistics

  • \SQLServer:SQL Statistics\Batch Requests/sec
  • \SQLServer:SQL Statistics\SQL Compilations/sec
  • \SQLServer:SQL Statistics\SQL Re-Compilations/sec

SQLServer:Transactions

  • \SQLServer:Transactions\Free Space in tempdb (KB)
  • \SQLServer:Transactions\Transactions
  • \SQLServer:Transactions\Version Store Size (KB)

SQLServer:Wait Statistics(Average wait time (ms))

  • \SQLServer:Wait Statistics(Average wait time (ms))\Lock waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Log buffer waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Log write waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Memory grant queue waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Network IO waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Non-Page latch waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Page IO latch waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Page latch waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Thread-safe memory objects waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Transaction ownership waits
  • \SQLServer:Wait Statistics(Average wait time (ms))\Wait for the worker
  • \SQLServer:Wait Statistics(Average wait time (ms))\Workspace synchronization waits

SQLServer:Wait Statistics(Waits in progress)

  • \SQLServer:Wait Statistics(Waits in progress)\Lock waits
  • \SQLServer:Wait Statistics(Waits in progress)\Log buffer waits
  • \SQLServer:Wait Statistics(Waits in progress)\Log write waits
  • \SQLServer:Wait Statistics(Waits in progress)\Memory grant queue waits
  • \SQLServer:Wait Statistics(Waits in progress)\Network IO waits
  • \SQLServer:Wait Statistics(Waits in progress)\Non-Page latch waits
  • \SQLServer:Wait Statistics(Waits in progress)\Page IO latch waits
  • \SQLServer:Wait Statistics(Waits in progress)\Page latch waits
  • \SQLServer:Wait Statistics(Waits in progress)\Thread-safe memory objects waits
  • \SQLServer:Wait Statistics(Waits in progress)\Transaction ownership waits
  • \SQLServer:Wait Statistics(Waits in progress)\Wait for the worker
  • \SQLServer:Wait Statistics(Waits in progress)\Workspace synchronization waits

Foreach Buffer Node SQLServer:Buffer Node(x)

  • \SQLServer:Buffer Node(000)\Remote node page lookups/sec
  • \SQLServer:Buffer Node(001)\Remote node page lookups/sec
  • ...
  • \SQLServer:Buffer Node(n)\Remote node page lookups/sec


System Counter

LogicalDisk(_Total)

  • \LogicalDisk(_Total)\Avg. Disk sec/Read
  • \LogicalDisk(_Total)\Avg. Disk sec/Transfer
  • \LogicalDisk(_Total)\Avg. Disk sec/Write
  • \LogicalDisk(_Total)\Current Disk Queue Length
  • \LogicalDisk(_Total)\Disk Read Bytes/sec
  • \LogicalDisk(_Total)\Disk Reads/sec
  • \LogicalDisk(_Total)\Disk Transfers/sec
  • \LogicalDisk(_Total)\Disk Write Bytes/sec
  • \LogicalDisk(_Total)\Disk Writes/sec
  • \LogicalDisk(_Total)\Free Megabytes

For each logical disk LogicalDisk(X)

  • \LogicalDisk(LogDisk1..n)\Avg. Disk sec/Read
  • \LogicalDisk(LogDisk1..n)\Avg. Disk sec/Transfer
  • \LogicalDisk(LogDisk1..n)\Avg. Disk sec/Write
  • \LogicalDisk(LogDisk1..n)\Current Disk Queue Length
  • \LogicalDisk(LogDisk1..n)\Disk Read Bytes/sec
  • \LogicalDisk(LogDisk1..n)\Disk Reads/sec
  • \LogicalDisk(LogDisk1..n)\Disk Transfers/sec
  • \LogicalDisk(LogDisk1..n)\Disk Write Bytes/sec
  • \LogicalDisk(LogDisk1..n)\Disk Writes/sec
  • \LogicalDisk(LogDisk1..n)\Free Megabytes

Memory

  • \Memory\Available MBytes
  • \Memory\Cache Faults/sec
  • \Memory\Page Faults/sec
  • \Memory\Page Reads/sec
  • \Memory\Page Writes/sec
  • \Memory\Pages Input/sec
  • \Memory\Pages Output/sec
  • \Memory\Pages/sec

For each used network interface

  • \Network Interface(Microsoft Hyper-V Network Adapter)\Bytes Received/sec
  • \Network Interface(Microsoft Hyper-V Network Adapter)\Bytes Sent/sec
  • \Network Interface(Microsoft Hyper-V Network Adapter)\Bytes Total/sec
  • \Network Interface(Microsoft Hyper-V Network Adapter)\Packets Outbound Errors
  • \Network Interface(Microsoft Hyper-V Network Adapter)\Packets Received Errors
  • \Network Interface(Microsoft Hyper-V Network Adapter)\Packets/sec

For each sql server process

  • \Process(sqlservr)\% Privileged Time
  • \Process(sqlservr)\% Processor Time

Processor(_Total)

  • Processor(_Total)\% Privileged Time
  • Processor(_Total)\% Processor Time

For each CPU core Processor(X)

  • \Processor(0)\% Privileged Time
  • \Processor(0)\% Processor Time
  • \Processor(1)\% Privileged Time
  • \Processor(1)\% Processor Time
  • \Processor(n)\% Privileged Time
  • \Processor(n)\% Processor Time

System

  • \System\Context Switches/sec