MSSQL Performance Data Collection
Aus Software Entwicklung Projekte
Inhaltsverzeichnis
- 1 SQL Counter for each SQL Server Instance
- 1.1 SQLAgent:Jobs
- 1.2 SQLServer:Access
- 1.3 SQLServer:Buffer
- 1.4 SQLServer:Databases
- 1.5 SQLServer:General Statistics
- 1.6 SQLServer:Latches
- 1.7 SQLServer:Locks
- 1.8 SQLServer:Memory
- 1.9 SQLServer:Plan Cache
- 1.10 SQLServer:Databases
- 1.11 SQLServer:SQL Statistics
- 1.12 SQLServer:Transactions
- 1.13 SQLServer:Wait Statistics(Average wait time (ms))
- 1.14 SQLServer:Wait Statistics(Waits in progress)
- 1.15 Foreach Buffer Node SQLServer:Buffer Node(x)
- 2 System Counter
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