1. CPU
Processor: %Processor Time
If measure is consistently > 80%, it indicates a potential problem
Cause of high CPU usage:
1. Excessive Query Compilation and Optimization
2. Unnecessary Recompilation
SQLServer:SQL Statistics.SQL Compilations/sec This should be very low.
SQLServer:SQL Statistics.SQL Re-compilations/sec This should be very low.
SQLServer:SQL Statistics.SQL Batch Requests/sec
3. Inefficient Query Plan
4. Intraquery Parallelism
5. Poor Cursor Usage
SQLServer:Cursor Manager by Type.Cursor Requests/sec
2. Memory
External Physical Memory Pressure
Memory.Available Mbytes <50-100MB
Process.Private Bytes should be close to the size of Process.Working Set
External Virtual Memory Pressure
Memory: Commit Limit
Paging File: %Usage
Paging File: %Usage Peak If high, check if out of memory
SQLServer:Buffer Manager.Checkpoint pages/sec
SQLServer:Buffer Manager.Lazy writes/sec
3. I/O
PhysicalDisk Object: Avg. Disk Queue > 2 indicates a problem
Avg. Disk Sec/Read
Avg. Disk Sec/Write
-
Less than 10 ms - very good
-
Between 10 - 20 ms - okay
-
Between 20 - 50 ms - slow, needs attention
-
Greater than 50 ms – Serious I/O bottleneck
Physical Disk: %Disk Time >50%, there is an I/O bottleneck.
Avg. Disk Reads/Sec > 85 percent of the disk capacity
Avg. Disk Writes/Sec > 85 percent of the disk capacity
4. TempDB
SQLServer:Transactions.Free Space in tempdb (KB)
SQLServer:Transactions.Version Store Size (KB)
SQLServer:Transactions.Version Generation Rate (KB/S)
SQLServer:Transactions.Version Cleanup Rate (KB/S) If = 0, a long running transaction could be preventing the version store cleanup.
SQL Server:Access Methods\Workfiles Created /Sec
SQL Server:Access Methods\Worktables Created /Sec
SQL Server:Access Methods\Mixed Page Allocations /Sec
SQL Server:General Statistics\Temp Tables Created /Sec
SQL Server:General Statistics\Temp Tables for destruction
|
