Performance monitoring is an essential task for every DBA. Windows system monitor, Performance Logs and Alerts can be used for performance monitoring task. A 24-hour performance log is generated using the counters on a normal working day. This log can be used as a performance baseline document. SQL performance Baseline Template can be downloaded here.
If there exists sudden increase or decrease in threshold levels of any of the counters in everyday monitoring, an alert can be raised and to notify us.
The following is a partial list of counters that can be used for performance monitoring. Values of each counter may differ from server to server. So you should check with your baseline document.
| Counters | Purpose |
| SQL Server: SQL Statistics: Batch Requests/sec | Analyzing Statement Recompiles |
| SQL Server: SQL Statistics: SQL Compilations/sec | |
| SQLServer: SQL Statistics: SQL Recompilations/sec | |
| SQL Server: Access Methods: Mixed Page Allocations /Sec | Analyzing tempdb contention issues |
| SQL Server: Access Methods: Workfiles Created /Sec | |
| SQL Server: Access Methods: Worktables Created /Sec | |
| SQL Server: General Statistics: Temp Tables Created /Sec | |
| SQL Server: General Statistics: Temp Tables For Destruction | |
| LogicalDisk: % Free Space | Evaluating Disk Usage |
| Physical Disk: % Free Space | |
| Physical Disk: Avg. Disk Queue Length | |
| PhysicalDisk: % Disk Time | |
| Memory: Available Bytes | Evaluating Memory Usage |
| Memory: Pages/sec | |
| SQLServer: Memory Manager: Total Server Memory (KB) | |
| Physical Disk: Disk Reads/sec | |
| Physical Disk: Disk Writes/sec | |
| SQLServer: Buffer Manager: Buffer Cache Hit Ratio | |
| Network Interface: Bytes Received/sec | Evaluating Network Usage |
| Network Interface: Bytes Sent/sec | |
| Network Interface: Bytes total/sec | |
| Network Interface: Bytes/sec | |
| Network Interface: Output Queue Length | |
| Processor: % Privileged Time | Evaluating Processor Usage |
| Processor: % Processor Time | |
| System: Processor Queue Length | |
| SQLServer: General Statistics: User Connections | Evaluating User Connections |
| SQLServer: Databases: Log Growths | Monitoring Database Availability and Transaction Log Usage |
| SQLServer: Databases: Percent Log Used | |
| SQLServer: Databases:Data File(s) Size (KB) | |
| SQLServer: SQL Errors: Errors/sec | |
| SQLServer: Latches: Average Latch Wait Time (ms) | Monitoring Latch Wait Times |
| SQLServer: Latches:Latch Waits/sec | |
| SQLServer: Latches:Total Latch Wait Time (ms) | |
| SQLServer: Access Methods: Full Scans/sec | Monitoring Server Availability |
| SQLServer: Databases: Transactions/sec | |
| Paging File: % Usage | Monitoring Server Health |
| Process: % Processor Time | |
| SQLServer: User Settable: Query | |
| SQLServer: Locks: Lock Timeouts/sec | Monitoring Waits and Queues |
| SQLServer: Locks: Lock Waits/sec | |
| SQLServer: Locks:Average Wait Time (ms) | |
| SQLServer: Locks:Lock Requests/sec | |
| SQLServer: Locks:Lock Timeouts (Timeout > 0)/sec | |
| SQLServer: Locks:Lock Wait Time (ms) | |
| SQLServer: Locks:Number of Deadlocks/sec | |
| SQLServer:Wait Statistics:Lock Waits | |
| SQLServer:Wait Statistics:Log Buffer Waits | |
| SQLServer:Wait Statistics:Log Write Waits | |
| SQLServer:Wait Statistics:Memory Grant Queue Waits | |
| SQLServer:Wait Statistics:Network IO Waits | |
| SQLServer:Wait Statistics:Non-Page Latch Waits | |
| SQLServer:Wait Statistics:Page IO Latch Waits | |
| SQLServer:Wait Statistics:Page Latch Waits | |
| SQLServer:Wait Statistics:Thread-Safe Memory Objects Waits | |
| SQLServer:Wait Statistics:Transaction Ownership Waits | |
| SQLServer:Wait Statistics:Wait For The Worker | |
| SQLServer:Wait Statistics:Workspace Synchronization Waits | |
| .NET CLR Memory: # GC Handles | SQL CLR Memory Usage |
| .NET CLR Memory: % Time In GC | |
| .NET CLR Memory: Allocated Bytes/Sec |
Reference: Monitoring Resource Usage (System Monitor)
