Toad World® Forums

BMF TPC-C error - thread resources


#1

I’m running a BMF TPC-C test against a SQL DB on a server with 32 cores and 512GB RAM. The scale used for the “Load Objects” job is 1000 and there’s a constant 40 users for the TPC-C test. There’s a 5 min pre-sampling followed by a 30 min run.
With right at 3 min to go before the end of the test I start getting errors and the TPS starts slowing down. The errors are:

Stock Level Exec Failed
ODBC Error (37000,21C2) - [Microsoft][ODBC SQL Server Driver][SQL Server]The query processor could not start the necessary thread resources for parallel query execution.

I tracked the thread count for the sqlserver.exe process using perfmon and see that the thread count is 250 right up until that 3 min mark and then it jumps to 1495.
I just started using BMF and can’t yet figure out why this is happening. I’m pretty new to SQL also so I won’t rule out something in SQL.
I couldn’t find anything like this on the community or support site so thought I’d see if anyone here has an idea.
thanks
Tim


#2

It appears that the error message you are receiving is being caused by the load on the server. Here is a couple of links talking about the error message.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/1901f6e1-2dc3-4763-874d-d5420345eb3f

http://blog.sqlauthority.com/2008/01/30/sql-server-fix-error-the-query-processor-could-not-start-the-necessary-thread-resources-for-parallel-query-execution/

Here is another thing that you might try to fix the issue. With the server you have I wouldn’t think it could be a resource issue, so it probably a settings issue.

SQL makes a parallel query plan at optimization time. When you tried to run
the query, maybe not all of the processors were available OR there were not
enough threads available … Perhaps setting MAXDOP to 2 or 3 might help…
This can be done on Enterprise manager, right click your server and go to
the Properties item… (MAX Degree of Parallelism.)


#3

I’ve seen both of those, but they don’t really apply. Benchmark Factory is running the test so that’s not something I control. It’s not “a” query. It’s whatever BMF is doing for the TPC-C test. With a scale of 1000 and 40 users I wouldn’t expect any issues on the server side. But I haven’t seen anything saying that the test does something different at 3 mins to go so I’m also thinking it’s got to be on the server side.
No, there shouldn’t be any resource issue. SQL Server and perfmon are the only things running on the server so everything is wide open for the run. There’s 64 cores and 512GB RAM so those aren’t stressed I don’t think. The thread error is about the process, not processors. The sqlserver.exe process is the one that shows a jump from 250 threads to 1495 threads. This causes the errors to start showing up. I’m not sure what would cause that jump. I just don’t know enough at this point. Maybe something in SQL.
I’ll take a look at MAXDOP and see what happens.
Thanks
Tim


#4

Thought I’d post what happened. Changing MAXDOP to 4 seems to have donethe trick. The thread count still heads up at that same point but onlygoes up to around 450 instead of 1495. Still a little strange I think, but I’m going to move on.