During my initial days in QA one of my tasks involved doing load tests on systems. We all know that this kind of tests are important especially when trying to figure just how well the current system scales in the face increased load with the current design of software and infrastructure.
I was fancied by the output I was able to produce and even the fact that I could come up with a mock DDoS system. However load test is much much more than that. It is about collecting performance counters on the system hosting the applications you test usually termed as the system under test. At the top of anyone’s mind should be able to capture how the performance of the application server compares to that of Database servers. The following performance counters provided by Microsoft are important in that quest and can be adapted to fit specific types of servers.
http://msdn.microsoft.com/en-us/library/ms998579.aspx
The counters provided in the link above do not give details as to when we want to monitor specific SQL server metrics. I have hence compiled a specific list that can be used as a template.
| Performance Category | Counters |
| SQLServer:Locks | Average Wait Time(ms) Number of DeadLocks/sec Lock Requests/Sec Lock Wait Time(ms) Lock Timeouts(timeout > 0)/sec |
| SQLServer:Transactions | Transactions |
| SQLServer:SQL Errors | Errors/sec |
| SQL Server: Availability Replica* | Bytes Received from Replica/sec Bytes Sent to Replica/sec |
*would be required if the DB servers run on High Availability mode.