Here a short overview of what performance indicators is better to collect for SQL performance analysis. Is better to start from “macro indicators” like CPU, I/O, Memory and then go deeply on SQL counters for better characterize the issue.
SQLServer:Access Methods – Full Scans / sec
Value greater than 1 or 2 indicates that there are table / Index page scans. Better to analyze how this can be avoided.
SQLServer:Access Methods – Page Splits/sec
This counter indicate that SQL server are moving data to a new data page. This value needs to be low as possible. http://www.sql-server-performance.com/2006/reduce-page-splits/
SQLServer:Access Methods – Table Lock Escalations/sec
This is the number of times a table lock was asked for in a second. A high number means something on query and the indexes on the table are wrong.
SQL Server:Buffer Manager – Buffer cache hit ratio
Indicate the percentage of pages that were found in the memory. Higher the value the better. The correct value is around 90%.
SQL Server:Buffer Manager – Database pages
This number shows the number of pages that constitute the SQL data cache. A large changes in this value indicates the database is swapping cache values from the cache. We need to either increase the memory of the system or the max server memory parameter.
SQL Server:Buffer Manager – Procedure cache pages
This indicates the number of procedures are present in the cache. This is the location where the compiled queries are stored.
SQL Server:Buffer Manager – Stolen pages
This is the number of pages that were stolen from the buffer cache to satisfy other memory requests.
SQL Server:Cache Manager – Cache hit ratio
The ratio between the cache hits and misses. This counter is a good indicator of caching mechanism in SQL Server. This value needs to be high.
SQL Server:Databases – Active Transactions
The number of currently active transactions in the system.
SQL Server:Databases – Log growths
The number of times the log files have been extended. If there is a lot of activity in this counter we need to allocate static and large enough space for our log files.
SQL Server:Databases – Transactions/sec
This number indicates the workload of SQL Server system. A higher value indicates more activity is occurring.
SQL Server:General Statistics – User Connections
The number of users currently connected to the SQL Server.
SQL Server:Locks – Lock Requests/sec
Number of requests for a type of lock per second.
SQL Server:Locks – Average Wait Time
This is the average wait time in milliseconds to acquire a lock. Lower value, better it is.
SQL Server:Locks – Number of Deadlocks/sec
The number of lock requests that resulted in a deadlock.
SQL Server:Memory Manager – Optimizer Memory
The amount of memory in KB that the server is using for query optimization.
SQL Server:Memory Manager – Connection Memory
Amount of memory in KB used to maintain the connections.
SQL Server:SQL Statistics – SQL Compilations/sec
The number of times per second that SQL Server compilations have occurred. This value needs to be as low as possible.
SQL Server:SQL Statistics – SQL Re-Compilations/sec
This needs to be zero as much as possible.
Processor – %Processor Time
The percentage of time the processor spent executing a non-idle thread. This value is subtracted from the time the processor was idle 100 percent. This is an indicator to the overall CPU utilization in the system.
Processor – %Interrupt Time
The percentage of time the processor spent servicing hardware interrupts.
Processor – Processor Queue Length
This counter indicates the number of threads that are waiting in the processor queue. It can be also interpreted as the number of threads that are waiting to be run by the processor. If this value is greater than the number of processor then we have a CPU bottleneck in the system.
Processor – Context Switches/sec
A typical context switch occurs when the OS or the application is forced to change the executing thread on one processor to another thread executed on another processor. This value has to be as small as possible. Context switches are not avoidable in multi-processor machines. Hence any value below 10000 is fine.
PhysicalDisk – %Disk Read Time
Time spent by the disk for read operation.
PhysicalDisk – %Disk Write Time
Time spent by the disk for write operation.
PhysicalDisk – Avg. Disk Queue Length
Average number of requests that waited for the read and write requests in the particular disk. A high value suggests we have a IO bottleneck if is not a SAN. http://technet.microsoft.com/en-us/library/aa997558(EXCHG.65).aspx
Memory – Page Faults/sec
Total number of faulted pages handled by the processor per second. This value needs to as small as possible.
Memory – Pages/sec
The number of pages written to disk or from disk to resolve page faults. This would be the sum of page reads/sec and page writes/sec counter.
UPDATE: Check also this link http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx