Some Important SQL Performance counters by DBA

Following table list out of some important SQL performance counters should be collected by
DBA against performance testing entity.

Performance Counter Name	Comments
SQLServer:Access Methods - Full Scans / sec	Value greater than 1 or 2 indicates that we are having table / Index page scans. We need to analyze how this can be avoided.
SQLServer:Access Methods - Page Splits/sec	Interesting counter that can lead us to our table / index design. This value needs to be low as possible.
SQLServer:Access Methods - Table Lock Escalations/sec	This gives us the number of times a table lock was asked for in a second. A high number needs a revisit to the query and the indexes on the table.
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: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 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 how active our SQL Server system is. 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 the value the 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. We need to have a steady value in this counter. A large variation in the value suggests there is lot of Dynamic SQL is getting executed.
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.