What counters SQL Server needs to monitor

General counters

Collects server performance information for operating system servers, including processor, disk, network, memory

Processor Processor

1.1% Processor time refers to the percentage that the processor uses to perform non-idle threads. This counter allows you to determine the current percentage of CPU usage for the server.

1.2% Privileged is the percentage of time that the process thread spends executing code in privileged mode. When Windows system Services is invoked, this service often runs in privileged mode to gain access to system-specific data.

PhysicalDisk Physical Disk (one raid)

2.1 AVG. Disk Queue Length waits for the requested queue lengths, and if this value is too large, it indicates that there is a bottleneck on the disk and that the value of the natural disk delay is too large.

2.2 AVG. Disk Sec/read;avg. Disk Sec/transfer;avg. Disk Sec/write These three counters indicate how much time it takes each time a disk reads and writes, indicating the disk's latency, in seconds, if the delay exceeds 25mm indicates poor disk performance.

LogicalDisk Logical Disk (a volume)

3.1% Free space a disk's idle percentage; Megabytes disk remaining size MB

3.2 Disk read Bytes/sec;disk Transfers/sec;disk write bytes/sec These three counters represent the read and write of disks per second, indicating disk IO performance (IOPS)

Memory Memory

4.1 Available MBytes can be memory size MB, this memory can be immediately allocated to a process or system for use. It is equal to the sum of memory allocated to the standby (cached), idle, and 0 paged list.

4.2 Memory used by the Cache Bytes system

4.3 The total amount of memory (virtual memory and physical memory) that can be requested by the Commit limit, the amount of memory (virtual memory and physical memory) that the Committed bytes has used, in byte (bytes).

4.4 Free System Page table entries refers to the number of page sheet entries that are not currently in use by the system. Indicates whether memory is idle.

4.5 page Faults/sec The number of pages that have failed per second, that is, the number of pages that have not been hit in memory. As a result, the pages/sec counter is the speed at which pages are read from disk by an error page. A larger value indicates that there may be a bottleneck in memory or that there is a large amount of data access.

Network Interface Network Interface

5.1 Current bandwidth refers to the present bandwidth of a network interface that is estimated at bits per second. For interfaces with constant bandwidth or inability to estimate accurate data, this value is nominal bandwidth.

5.2 Bytes received/sec;bytes Sent/sec;bytes total/sec The number of bytes sent and received per second, indicating the current network usage.

5.3 Output Queue Length outputs packet queues, this value greater than 2 indicates a delay in transmission.

5.4 Packets Outbound Discarded is selected as the number of dropped packets, the reason for discarding may be to free buffer space. Packets Outbound Errors Number of packets that cannot be transmitted due to errors

5.5 Packets Received Discarded is selected as the number of dropped packets, the reason for discarding may be to free buffer space. Packets Received Errors Number of packets that cannot be transmitted due to errors

SQL Server Counters

The Access Methods object in SQL Server provides a counter that monitors access to logical data in the database, and the Buffer Manager counter monitors physical access to the database on disk.

Access Methods access Mode

Access methods monitors the methods used to access data stored in the database to help determine whether you can improve query performance by adding or modifying indexes, adding or moving partitions, adding files or filegroups, defragmenting indexes, or overriding queries. The Access Methods counters can also be used to monitor the number of data, indexes, and free space in the database to indicate data volumes and fragmentation for each server instance. Excessive fragmentation of the index can degrade performance.

1.1 Page splits/sec per second due to overflow of index pages. The larger this data indicates that a table is modified more frequently, and to confirm that the index is suitable for establishing the field.

1.2 Workfiles created/sec number of working files created per second

1.3 worktables created/sec Number of worksheets created per second

Buffer Manager Cache Management

Monitoring the memory used to store data pages, physical IO when reading from and writing to a database page, and using this object to determine if there is a physical memory bottleneck to understand the server's query performance and so on.

2.1 Buffer cache Hit ratio hits the percentage of the Cache area page. If this value is too low, there may be some bottleneck in memory

2.2 Pages of database content in the buffer pool

2.3 Page Life expectancy pages indicate the time, in seconds, that the page will stay in the buffer pool of this node without reference. If this value is lowered, the server frequently queries the data in the database.

2.4 Checkpoint pages/sec Checkpoint The frequency of execution, if the frequency is too high. Indicates that the server is modifying data too frequently.

2.5 Lazy Writes/sec If the lazy write is too frequent, indicating that there is no free buffer for use, there may be some bottleneck in memory. The lazy writer is a system process for batch flushing of dirty expired buffers that contain changed buffers, which must be rewritten back to disk to reuse buffers for other pages, and make them available to user processes.

Memory Manager Memory Management

Monitor overall server memory usage to estimate user activity and resource usage to help pinpoint performance bottlenecks.

3.1 Memory Grants pending specifies the total number of processes that are authorized to wait for the work space. Refers to the number of processes waiting to allocate memory, if this value is too high or there is a bottleneck in memory or disk.

3.2 Target Server Memory (KB), Total Server Memories (KB) Ideally able to use the amount of RAM that is committed with the memory manager. If there is a difference between these two values, the memory exception is indicated.

General Statistics General Statistics

4.1 User Connections;logins/sec;logouts/sec concurrency situation

4.2 Processes blocked Current blocking quantity

4.3 Temp Tables for destruction waits for the number of temporary table/table variables that are destroyed by the purge system thread. Useless temporary tables and table variables.

SQL Statistics; Wait Statistics request and wait statistics

5.1 Batch requests/sec;sql Compilations/sec;sql re-compilations/sec The number of requests, compilations, and recompilation per second that are optimized for reference.

5.2 Lock waits the number of processes waiting for the lock.

5.3 Log write waits number of processes waiting to be written to the log cache

The 5.4 page IO latch waits statistics related to page I/O latches. Used to determine the bottleneck of memory or disk

5.5 page latch Waits statistics related to page latches (excluding I/O latches). Used to determine the bottleneck of memory or disk

Databases for database level

6.1 Log Flush wait Time;log flushes/sec logs flush waits and frequency, if data is abnormal there may be a disk bottleneck

6.2 TRANSACTIONS/SEC Transaction Number

Locks Lock

7.1 Lock Requests/sec;lock timeouts/sec lock request frequency; lock Timeout frequency

7.2 Number of deadlocks/sec deadlock condition.

What counters SQL Server needs to monitor