Major performance counters to look for


Below are some major perfmon counters with thresholds to be analyzed for SQL Server behaviour.

Performance Counter Counter Object Threshold Notes
% Processor Time Processor > 80% Potential causes include memory pressure, low query plan reuse, non-optimized queries.
Context Switches/sec System > 5000 x processors Potential causes include other applications on the server, more than one instance of SQL Server running on the same server, hyper-threading turned on.
Processor Queue Length System > 5 x processors Potential causes include other applications on the server, high compilations or recompilations, more than one instance of SQL Server running on the same server.
Compilations/sec SQLServer:SQL Statistics Trend Compare to Batch Requests/sec.
Re-Compilations/sec SQLServer:SQL Statistics Trend Compare to Batch Requests/sec.
Batch Request/sec SQLServer:SQL Statistics Trend Compare with the Compilation and Re-Compilations per second.
Page Life Expectancy SQLServer:Buffer Manager < 300 Potential for memory pressure.
Lazy Writes/sec SQLServer:Buffer Manager Trend Potential for large data cache flushes or memory pressure.
Checkpoints/sec SQLServer:Buffer Manager Trend Evaluate checkpoints against PLE and Lazy Writes/sec.
Cache Hit Ratio: SQL Plans SQLServer:Plan Cache < 70% Indicates low plan reuse.
Buffer Cache Hit Ratio SQLServer:Buffer Manager < 97% Potential for memory pressure.
Advertisements

2 Responses

  1. Nice

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: