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

minimum :128

maximum 32767

config_value: 0

run_value :0

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)

max 666.0089

min 4.999489

Avg 52.7313

StdDev 102.1578

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



From the error message, it sounds like you are running out of worker threads.

The number of worker threads available for use varies depending on your hardware. You can see the specifics here.

Among (a lot of) other things, worker threads process queries. When an application sends a query to the server, SQL picks an idle worker thread and gives it the query to process. After the query has been completed and the results return to the app, the worker thread becomes idle again. If there are no idle worker threads, the query gets queued and it waits. If it waits too long, the query is not scheduled and the application receives an error. (That is the detail-free, super-glossy explanation.) When SQL Server runs out of worker threads, it can bog down in weird and frustrating ways. Often, you won't be able to log in.

You can increase the number of worker threads (it still requires a restart, IIRC), but Microsoft says that you shouldn't do that. Years and years ago, I actually had them grumble at me for doing that, which is why your post caught my eye.

Back to the point: Basically, for the amount of load that you are throwing at the server, the server is starved for worker threads. This might be exacerbated by any slowness that comes from having the data files on drives that are possibly already busy.

I'm not familiar with hammerora. Does hammerora use captured traffic from your "real", production application or does it make up it's own?

"transaction" is a funny term. Every application has a different idea of what goes into a transaction, but perfmon only reports a very coarse number. Think about it: a one-record selection from an employee table and an update of a million row table both count as "one transaction", each, but they are a very different amount of work for the server to do.

Why does hammerora have it's own database? To run tests on? Or is the database just used to store results? If hammerora is testing it's own database, you are really testing your server's ability to scale hammerora and not your "real" production app, which may behave differently.

Also, the number you see form the perfmon trace will include lots of lightweight queries that qualify as transactions but don't put significant load on the server. (sp_reset_connection is a good example. It gets called a lot, but it isn't much load. You could probably run many thousands of those a second.) So, if the production server is running 156 transactions per second and most of them are lightweight and then hammerora goes and runs 156 heavy transactions per second, that's a problem.

If I were load testing the server, I would start off small and then crank things up. In other words, don't start at 156, start at 15. Watch how the server performs with perfmon. When that is done, double the load and try again. How does that work out? Then, double the load and try again. Rinse. Repeat. Etc. Eventually the server will start to misbehave. That is when you say "That is what this server can do" and, if necessary, start looking for ways to optimize the queries or improve the server.

Oh, and I'm not sure that I would be load testing on the production server. It is bound to bog down at some point, and the users won't like that.


Darin is right:

a) Start with a small load and build up

And also

b) get a blocking / lock monitor set up that fires frequently and logs to a table (pref on another spindle set). Look for blocks/locks.... optimize, repeat...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.