SQL Server Performance Audit Checklist
The key counters (& suggested threshold values) to monitor with Performance Monitor (PerfMon) in order to identify & avoid hardware bottlenecks are:
Memory: Pages/sec - 0-20
Memory: Available Bytes - 10% of physical memory
Physical Disk: % Disk time - less than 55%
Physical Disk: Avg. Disk Queue Length - less than 2 per physical disk
Processor: % Processor Time - less than 80%
System: Processor Queue Length - less than 2 per CPU
SQL Server Buffer: Buffer Cache Hit Ratio - greater than 99%
SQL Server General: User Connections - setting for "Maximum Worker Threads" should be higher than the maximum number of user connections your server ever reaches.
Its a time to worry if the counters start crossing the suggested threshold values.
For more comprehensive analysis, additional counters can be chosen.
Proactive tuning & effective maintenance is required for the smooth functioning of a data driven website.
Memory: Pages/sec - 0-20
Memory: Available Bytes - 10% of physical memory
Physical Disk: % Disk time - less than 55%
Physical Disk: Avg. Disk Queue Length - less than 2 per physical disk
Processor: % Processor Time - less than 80%
System: Processor Queue Length - less than 2 per CPU
SQL Server Buffer: Buffer Cache Hit Ratio - greater than 99%
SQL Server General: User Connections - setting for "Maximum Worker Threads" should be higher than the maximum number of user connections your server ever reaches.
Its a time to worry if the counters start crossing the suggested threshold values.
For more comprehensive analysis, additional counters can be chosen.
Proactive tuning & effective maintenance is required for the smooth functioning of a data driven website.
Comments
Post a Comment