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.

Thin Client Vs Thick Client

Thin Client	Thick Client
Mostly it is a web based client. Will be accessed through a common client called browser	It will be accessible through client software which is installed locally
Business logic will be there in middleware’s or application servers.	Business logic will be installed in the local machine.
When the URL is accessed most of the business logic will be executed on the server. And some client side processing and rendering happens within the browser.	When the client get installed all the business logic of the application installed locally
Thin client uses stateless connections. For each request connection will be opened and after the response it will be closed	Thick client uses dedicated connections
Thin clients are slower in response • Connection needs to be opened explicitly for each request • Pages needs to be downloaded from presentation layer • Data needs to be retrieved from the DB • Business logic related processing will be happening in the middle wares or application servers	Thick clients are faster in response • most of the processing happens locally • Connections will be closed only explicit logouts
Thin clients will be used by the external users. Ex: Bank customers	Thick clients will be used by the internal users most of the times: Ex: Bank employees and customer support executives