fiftynine.io
Performance Monitor Counter Reference
| Area | Object | Counter | Instance | Threshold | Comments | Reference |
|---|---|---|---|---|---|---|
| Memory | Memory | Available Mbytes | na | > 100MB | Available Physical RAM on the box | |
| Memory | Memory | Free System Page Table Entries | na | > 7000 | Free System Page Table Entries is the number of page table entries not currently in use by the system. If < 7000, consider removing /3GB. | |
| Memory | Memory | Pages/sec | na | < 50 | Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays and shouldn’t consistently above 0 | |
| Network | Network Interface | Bytes Received/sec | nic in use by sqlservr | na | Shows the byte rate at which information is received over each network adapter. The bytes used for data packet framing are also counted and included in the value | https://www.sqlshack.com/sql-server-network-performance-metrics-important-metrics/ |
| Network | Network Interface | Bytes Sent/sec | nic in use by sqlservr | na | Shows the rate at which bytes are sent over each network adapter | https://www.sqlshack.com/sql-server-network-performance-metrics-important-metrics/ |
| Network | Network Interface | Bytes Total/sec | nic in use by sqlservr | < 50 % Current Bandwith - 100 % means maximum network capacity | It is the byte rate at which data is received and sent over each network adapter. Framing bytes are included | https://www.sqlshack.com/sql-server-network-performance-metrics-important-metrics/ |
| Network | Network Interface | Current Bandwidth | nic in use by sqlservr | na | Shows an estimate of the current bandwidth of the network interface in bits per second (BPS). For interfaces that do not vary in bandwidth or for those where no accurate estimation can be made, this value is the nominal bandwidth | https://www.sqlshack.com/sql-server-network-performance-metrics-important-metrics/ |
| Disk | Physical Disk / Logical Disk | % Idle Time | > 50% | The disk should not be working constantly | ||
| Disk | Physical Disk / Logical Disk | Avg. Disk Queue Length | None | Be careful when using old thresholds of 2 with SQL Server and SANs. This value can be much higher than 2. Do not rely solely on this counter to diagnose an IO problem | ||
| Disk | Physical Disk / Logical Disk | Avg. Disk sec/Read | ( < .005 excellent; .005 - .010 Good; .010 - .015 Fair; > .015 investigate) | Measure of read latency on the disk. Lower values are better however this can vary and is dependent on the size and nature of the I/Os being issued. Numbers also vary across different storage configurations (cache size/utilization can impact this greatly). | https://blogs.msdn.microsoft.com/askjay/2011/07/08/troubleshooting-slow-disk-io-in-sql-server/ | |
| Disk | Physical Disk / Logical Disk | Avg. Disk sec/Transfer | ( < .005 excellent; .005 - .010 Good; .010 - .015 Fair; > .015 investigate) | Measures average latency for read or write operations | ||
| Disk | Physical Disk / Logical Disk | Avg. Disk sec/Write | ( < .005 excellent; .005 - .010 Good; .010 - .015 Fair; > .015 investigate) | Measure of write latency on the disk. Lower values are better however this can vary and is dependent on the size and nature of the I/Os being issued. Numbers also vary across different storage configurations (cache size/utilization can impact this greatly). | ||
| Disk | Physical Disk / Logical Disk | Disk Bytes/sec | None | Used to determine bandwidth total bandwidth utilization | ||
| Disk | Physical Disk / Logical Disk | Disk Read Bytes/sec | None | Used for determine bandwidth utilization for Read Ops | ||
| Disk | Physical Disk / Logical Disk | Disk reads/sec | None | Number of read IOPs, Raid 0 – I/Os per disk = (reads + writes) / number of disks, Raid 1 – I/Os per disk = [reads + (2 * writes)] / 2, Raid 5 – I/Os per disk = [reads + (4 * writes)] / number of disks, Raid 10 – I/Os per disk = [reads + (2 * writes)] / number of disks | https://blogs.msdn.microsoft.com/askjay/2011/07/08/troubleshooting-slow-disk-io-in-sql-server/ | |
| Disk | Physical Disk / Logical Disk | Disk Transfers/sec | None | Number of read and write IOPs (used to compare against capacity of storage subsystem) | ||
| Disk | Physical Disk / Logical Disk | Disk Write Bytes/sec | None | Used to determine bandwidth utilization for Write Ops | ||
| Disk | Physical Disk / Logical Disk | Disk writes/sec | * | None | Number of write IOPs, Raid 0 – I/Os per disk = (reads + writes) / number of disks, Raid 1 – I/Os per disk = [reads + (2 * writes)] / 2, Raid 5 – I/Os per disk = [reads + (4 * writes)] / number of disks, Raid 10 – I/Os per disk = [reads + (2 * writes)] / number of disks | https://blogs.msdn.microsoft.com/askjay/2011/07/08/troubleshooting-slow-disk-io-in-sql-server/ |
| CPU | Process | % Privileged Time | sqlservr.exe | < 10% , or maximum 30% of the % Total Processor Time | % Privilege Time counter shows how much time the processor spends on executing non-user processes, i.e. privilege (kernel) mode operations. If (Process (sqlservr)% Privileged time/No of Procs) is above 30%, ensure that KB 976700 is applied for Windows 2008 R2 | https://www.sqlshack.com/sql-server-processor-performance-metrics-part-2-processor-user-time-processor-privilege-time-total-times-thread-metrics/ |
| CPU | Process | % Processor Time | _Total, | !> 80% during normal operation | Groups the activity of all the processors together to report the total performance of the entire system | https://www.sqlshack.com/sql-server-processor-performance-metrics-part-2-processor-user-time-processor-privilege-time-total-times-thread-metrics/ |
| Memory | Process | Page Faults/sec | _Total, | < a few hundred is desirable, in the thousands acceptable if other memory counters are OK | A page fault occurs when a program requests an address on a page that is not in the current set of memory resident pages | https://www.sqlshack.com/sql-server-memory-performance-metrics-part-1-memory-pagessec-memory-page-faultssec/ |
| CPU | Processor | % Privileged Time | _Total | < 30% | Amount of total CPU usage in kernel mode across all processors. Processor Time = Privileged Time + User Time | https://blogs.technet.microsoft.com/perfguide/2010/09/28/user-mode-versus-privileged-mode-processor-usage/ |
| CPU | Processor | % Processor Time | _Total | < 80% | Amount of total CPU usage across all processors | https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-2000-server/cc938603(v=technet.10) |
| CPU | Processor | % User Time | _Total | < 80% | Amount of total CPU usage in user mode across all processors | https://blogs.technet.microsoft.com/askperf/2008/01/15/an-overview-of-processor-bottlenecks/ |
| Memory | SQL Server:Access Methods | Page Splits/sec | na | < 20 per 100 batch requests | Number of 8k pages that filled and split into two new pages | https://www.youtube.com/watch?v=T6eoqf6Y_VE |
| Memory | SQL Server:Buffer Manager | Buffer Cache Hit Ratio | na | > 98% | Indication of how often SQL Server is finding data pages in the buffer pool. For most applications, this value should be 90 or higher, indicating that most data requirements are being serviced from the cache. A lower value can indicate a memory bottleneck or a poorly designed application | |
| Memory | SQL Server:Buffer Manager | Checkpoint pages/sec | na | na | Shows the number of pages that are moved from buffer to disk per second during a checkpoint process | https://www.sqlshack.com/sql-server-memory-performance-metrics-part-6-memory-metrics/ |
| Memory | SQL Server:Buffer Manager | Database Pages (or Free Pages if < SQL2012) | na | > 640 | Measure of how many free pages are in the buffer pool for use | |
| Memory | SQL Server:Buffer Manager | Lazy Writes/sec | na | < 20 | Measure of when the lazy writer thread must clear up buffers to free space for pages - inducing IO | |
| Memory | SQL Server:Buffer Manager | Page Life Expectancy | na | > ((MAXBP(MB)/1024)/4)*300 | Duration, in seconds, that a page resides in the buffer pool. Indicates pressure on memory as data pages are flushed to disk - inducing IO | https://blogs.msdn.microsoft.com/mcsukbi/2013/04/11/sql-server-page-life-expectancy/ |
| SQL | SQL Server:General Statistics | User Connections | na | na | Counts the number of users currently connected to SQL Server | |
| SQL | SQL Server:Latches | Average Latch Wait Time (ms) | na | ~ | Average latch wait time (in milliseconds) for latch requests that had to wait | |
| SQL | SQL Server:Latches | Latch Waits/sec | na | (Total Latch Wait Time) / (Latch Waits/Sec) < 10 | Number of latch requests that could not be granted immediately | |
| SQL | SQL Server:Latches | Total Latch Wait Time (ms) | na | (Total Latch Wait Time) / (Latch Waits/Sec) < 10 | Total latch wait time (in milliseconds) for latch requests in the last second | |
| SQL | SQL Server:Locks | Lock Timeouts (timeout > 0)/sec | Extent | 0 | Shows the number of locks per second that timed out | |
| SQL | SQL Server:Locks | Lock Wait Time (ms) | Extent | A high value indicates here might be long-running or ineffiecient transactions that are causing lock contention | The total wait time in milliseconds that a process spends waiting for another process to release a lock. | |
| SQL | SQL Server:Locks | Lock Waits/sec | Extent | A high value indicates here might be long-running or ineffiecient transactions that are causing lock contention | Represents the total number of lock requested generated per second for which a process had to wait before a lock request was granted | |
| App | SQL Server:Locks | Number of Deadlocks/sec | Database | > 1 would require investigating | The number of lock requests per second that resulted in deadlocks | |
| Memory | SQL Server:Memory Manager | Memory Grants Pending | na | > 0 is cause for concern | Memory Grants Pending displays the total number of SQL Server processes that are waiting to be granted workspace in the memory | |
| Memory | SQL Server:Memory Manager | Target Server Memory | na | high or rising value indicates insufficient memory | ||
| Memory | SQL Server:Memory Manager | Total Server Memory | na | ~=Physical Ram | If this value is relatively high in comparison to total system memory, it’s a good indicator that you should install more memory | |
| Memory | SQL Server:Plan Cache | Cache Pages | SQL Plans | 2000 | Prefer stable values, otherwise cache thrash may be occurring | |
| App | SQL Server:SQL Statistics | Batch Requests/sec | na | OLTP workloads: server should support ~2,500/s/core | Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High batch requests mean good throughput. | https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-sql-statistics-object?view=sql-server-2017 |
| App | SQL Server:SQL Statistics | SQL Compilations/sec | na | if compiles > 10% of Batch Requests, then app may not be caching effectively | Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles caused by statement-level recompilations in SQL Server. After SQL Server user activity is stable, this value reaches a steady state. | https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-sql-statistics-object?view=sql-server-2017 |
| App | SQL Server:SQL Statistics | SQL Recompilations/sec | na | Recompiles > 10% of Compilations should be investigated | Number of statement recompiles per second. Counts the number of times statement recompiles are triggered. Generally, you want the recompiles to be low. | https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-sql-statistics-object?view=sql-server-2017 |
| SQL | SQL Server:Wait Statistics | Lock waits | Average wait time (ms) | ? | A lock wait occurs when a transaction tries to obtain a lock on a resource that is already held by another transaction | https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-wait-statistics-object?view=sql-server-2017 |
| Disk | SQL Server:Wait Statistics | Log write waits | Average wait time (ms) | 100 - 800 | This wait type is when a thread is waiting for a log block to be written to disk by an asynchronous I/O. A log block is written to disk when: A transaction commits (unless it is set to be delayed durable in SQL Server 2014 and above) | https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-wait-statistics-object?view=sql-server-2017 |
| Network | SQL Server:Wait Statistics | Network IO waits | Average wait time (ms) | ? | Statistics relevant to wait on network IO | |
| Disk | SQL Server:Wait Statistics | Non-Page latch waits | Average wait time (ms) | ? | Statistics relevant to non-page latches | |
| Disk | SQL Server:Wait Statistics | Page IO latch waits | Average wait time (ms) | ? | Statistics relevant to page IO latches | |
| Disk | SQL Server:Wait Statistics | Page latch waits | Average wait time (ms) | ? | Statistics relevant to page latches, not including IO latches | |
| CPU | SQL Server:Wait Statistics | Wait for the worker | Average wait time (ms) | ? | Statistics relevant to processes waiting for worker to become available | https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-wait-statistics-object?view=sql-server-2017 |
| CPU | System | Processor Queue Length | na | < 2 | Current Depth of the thread Scheduler Ready Queue | https://blogs.technet.microsoft.com/askperf/2008/01/15/an-overview-of-processor-bottlenecks/ |
| Network | TCPv4 | Connection Failures | na | ? | The number of connections that have failed since the service was started (regardless of when you started perfmon). | https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2003/cc787094(v=ws.10) |
| vCPU | VM | % Processor Time | na | ? | https://sqlperformance.com/2017/05/monitoring/troubleshooting-cpu-vmware | |
| vCPU | VM | Effective VM Speed in MHz | na | ? | https://sqlperformance.com/2017/05/monitoring/troubleshooting-cpu-vmware | |
| vCPU | VM | Host processor speed in MHz | na | ? | https://sqlperformance.com/2017/05/monitoring/troubleshooting-cpu-vmware | |
| vMemory | VM | Memory Active in MB | na | Should match Total Server Memory? | https://sqlperformance.com/2017/05/monitoring/troubleshooting-cpu-vmware | |
| vMemory | VM | Memory Ballooned in MB | na | 0 | The Memory Ballooned in MB counter tells how much memory has been reclaimed from the guest VM by the balloon driver due to memory overcommit on the host, which will cause SQL Server to reduce memory usage to respond to memory pressure in Windows caused by the balloon driver inflating to take memory away from the VM | https://sqlperformance.com/2017/05/monitoring/troubleshooting-cpu-vmware |
| vMemory | VM | Memory Swapped in MB | na | 0 | The Memory Swapped in MB counter is tracking how much memory was paged to disk by the host hypervisor due to memory overcommit on the host that couldn’t be resolved by ballooning VM guests with the balloon driver | https://sqlperformance.com/2017/05/monitoring/troubleshooting-cpu-vmware |