Friday, September 2, 2011

Performance Monitoring


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.

CountersPurpose
SQL Server: SQL Statistics: Batch Requests/secAnalyzing Statement Recompiles
SQL Server: SQL Statistics: SQL Compilations/sec
SQLServer: SQL Statistics: SQL Recompilations/sec
SQL Server: Access Methods: Mixed Page Allocations /SecAnalyzing 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 SpaceEvaluating Disk Usage
Physical Disk: % Free Space
Physical Disk: Avg. Disk Queue Length
PhysicalDisk: % Disk Time
Memory: Available BytesEvaluating 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/secEvaluating Network Usage
Network Interface: Bytes Sent/sec
Network Interface: Bytes total/sec
Network Interface: Bytes/sec
Network Interface: Output Queue Length
Processor: % Privileged TimeEvaluating Processor Usage
Processor: % Processor Time
System: Processor Queue Length
SQLServer: General Statistics: User ConnectionsEvaluating User Connections
SQLServer: Databases: Log GrowthsMonitoring 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/secMonitoring Server Availability
SQLServer: Databases: Transactions/sec
Paging File: % UsageMonitoring Server Health
Process: % Processor Time
SQLServer: User Settable: Query
SQLServer: Locks: Lock Timeouts/secMonitoring 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 HandlesSQL CLR Memory Usage
.NET CLR Memory: % Time In GC
.NET CLR Memory: Allocated Bytes/Sec


Reference: Monitoring Resource Usage (System Monitor)