I have been asked to do a load test on our production server for an extra 500 users. For the purpose of this I used an open source app; hammerora (http://hammerora.sourceforge.net/) which works pretty well. Our system spec as given bellow
**OS: Windows 2008 r2 Ent X64
CPU : inter x64 (4 physical * 6 core) NUMA
Server Memory: 128 GB
SQL : 2008 r2 std 64
No: of instance: 2
Memory allocation per instance ( 64 GB (main), 50GB)
Number of connection : 1500-2250 (Main instance) **
sp_configure 'max worker threads' result as shown bellow
name :max worker threads
Our main production db is on instance 1 (with 64gb mem). For the load testing purpose hammerora application’s database is installed on the main instance and the data and log file of hammerora app’s are located on the same drive where our production data bases data file are.
From perfmon trace I have identified the number of transaction on our production databases are (Databases(xxx)\Transactions/sec)
For the load testing purpose I assume an user would be firing 156 /sec (avg tran+stddev trav value) When I did the load testing today the server became unresponsive and at that time the number of connection was 2655. In the Perfmon trace I couldn’t find anything suspicious. Processor utilisation is not more than 55%. Processor queue length is most of time 0 but one point in it went up to 12 and thats it.
But in the error I could see following msg
New queries assigned to process on Node 3 have not been picked up by a worker thread in the last 300 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 6%. System Idle: 92%.
What would be the cause of server’s unresponsiveness. How do I troubleshoot it