SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running out of workers


Running out of workers

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87314 Visits: 41113
I don't personally have any good sources for how to troubleshoot connection leaks. They can happen in the front-end or in SQL code (usually because of not closing connections when using sp_OA* code). Connection leaks are very difficult to find.

Hopefully, someone else will have some good articles on the subject they can point us to. I haven't looked at the articles themselves, but there seems to be a decent selection of articles on the subject if you Google for "troubleshoot connection leaks" once you filter out the plumbing problems. :-D

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2131 Visits: 872
ELLEN-610393 (10/3/2013)
I see that you said that the default 0 for worker threads should be good. What is the impact of having a process spin up several thousand worker threads? Today I was looking at the Activity Monitor and saw that there were two different processes that each had about 3,000 worker threads. This is a new system for me and I was thinking that might be bad


I'm not sure why Jeff thinks this is a connection leak. I assume that with "process" you mean a single spid/session_id. A connection leak is an application problems and manifests itself with lots of session_ids, all having the same client_process_id and hostname. If you have a session with 3000 workers, that sounds like a query which is doing something massively parallel, which is something completely different.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87314 Visits: 41113
Erland Sommarskog (10/15/2013)
ELLEN-610393 (10/3/2013)
I see that you said that the default 0 for worker threads should be good. What is the impact of having a process spin up several thousand worker threads? Today I was looking at the Activity Monitor and saw that there were two different processes that each had about 3,000 worker threads. This is a new system for me and I was thinking that might be bad


I'm not sure why Jeff thinks this is a connection leak. I assume that with "process" you mean a single spid/session_id. A connection leak is an application problems and manifests itself with lots of session_ids, all having the same client_process_id and hostname. If you have a session with 3000 workers, that sounds like a query which is doing something massively parallel, which is something completely different.


Perhaps I'm using the wrong words but I have to disagree. I don't have many examples but if you have a connection leak, for example, when using sp_OA*, you will have thousands of different spids and there will be one connection for each one. The same holds true for some of the connection leaks that you can get via an app. I'm not familiar with why the connections wouldn't automatically close when it comes from an app but we currently have a machine at work that has to be rebooted once a week (until AppDev takes the time to figure out where the leak is) because of this very problem.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ELLEN-610393
ELLEN-610393
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 102
Jeff,
Erland is correct. What I am seeing is thousands of threads for the same spid/session id [not an app that spun up thousands of spids]. Also what is showing is that while there may be several thousand threads for the same spid there will be multiple 'Execution Context IDs' . One example had about 52 different Execution Context IDs with each of those having 50 some threads.
After further investigation and looking into the formula used by SQL to determine the allowable worker threads ... it is possible that what I am seeing is legit. The server running SQL actually has 4 processors with 6 cores each and is hyper threaded. It is new for me but may be common for this configuration. The server also has 192 GB RAM. I did read in one article that the max threads maybe should be limited to 2048 but I was not clear if that would be per spid or for the server. I am doing further research.

Thank you all for your comments.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2131 Visits: 872
ELLEN-610393 (10/15/2013)
What I am seeing is thousands of threads for the same spid/session id [not an app that spun up thousands of spids]. Also what is showing is that while there may be several thousand threads for the same spid there will be multiple 'Execution Context IDs' . One example had about 52 different Execution Context IDs with each of those having 50 some threads.


You ceartinly have a parallel query - all those execution context is a sure sign of that. I am surprised that there are so many workers per execution context, but then again this is nothing I have paid attention to. As I am at PASS in Charlotte, I should be able to find someone who can explain this to me.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2131 Visits: 872
Jeff Moden (10/15/2013)Perhaps I'm using the wrong words but I have to disagree. I don't have many examples but if you have a connection leak, for example, when using sp_OA*, you will have thousands of different spids and there will be one connection for each one. The same holds true for some of the connection leaks that you can get via an app. I'm not familiar with why the connections wouldn't automatically close when it comes from an app but we currently have a machine at work that has to be rebooted once a week (until AppDev takes the time to figure out where the leak is) because of this very problem.


I'm not saying that connection leaks cannot occur, because they certainly do. With proper coding where you say

using (SqlConnection cn = new SqlConnection(connstring)) {
// code here
}



They do not happen, but if you rely on garbage collection to taking care of your connection, it can happen.

But what Ellen is seeing is something completely different, and not related to the client layer, but it happens in SQL Server alone.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 775
So if you have one SPID, but 700 rows, that means the server is trying to do parallel processing? They all show CXPacket.. So how do I track down whay this is going on? (about twice per week)?
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2131 Visits: 872
dwilliscp (10/16/2013)
So if you have one SPID, but 700 rows, that means the server is trying to do parallel processing? They all show CXPacket.. So how do I track down whay this is going on? (about twice per week)?


You and Ellen are completely unrelated, aren'y you? I am just wondering if we are trying to to solve one or two problems here.

Where do you see 700 rows? CXPacket waits token of a parallel query, where some threads have completed their job and are waiting for some other thread to complete. Typically this happens when the optimizer makes a misestimation on how to partition the data over the threads.

To see what is going on, the first step is to find which querythe process is running. My own favourite tool is beta_lockinfo, but then again I wrote it. You find it on http://www.sommarskog.se/sqlutil/beta_lockinfo.html. This gives you a snapshot on what is going on in the system, including current query and plan.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2131 Visits: 872
Erland Sommarskog (10/15/2013)
You ceartinly have a parallel query - all those execution context is a sure sign of that. I am surprised that there are so many workers per execution context, but then again this is nothing I have paid attention to. As I am at PASS in Charlotte, I should be able to find someone who can explain this to me.


I spoke with some people here, and they agreed on that 3000 workers for a single thread is excessive, and we can't understand how that occur.

When you see this condition, can you query sys.dm_os_workers directly? Just run a SELECT COUNT(*). Maybe this is an issue in Activity Monitor. Also, can you use sp_configure and report the setting for "max worker threads"?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87314 Visits: 41113
Erland Sommarskog (10/15/2013)
Jeff Moden (10/15/2013)Perhaps I'm using the wrong words but I have to disagree. I don't have many examples but if you have a connection leak, for example, when using sp_OA*, you will have thousands of different spids and there will be one connection for each one. The same holds true for some of the connection leaks that you can get via an app. I'm not familiar with why the connections wouldn't automatically close when it comes from an app but we currently have a machine at work that has to be rebooted once a week (until AppDev takes the time to figure out where the leak is) because of this very problem.


I'm not saying that connection leaks cannot occur, because they certainly do. With proper coding where you say

using (SqlConnection cn = new SqlConnection(connstring)) {
// code here
}



They do not happen, but if you rely on garbage collection to taking care of your connection, it can happen.

But what Ellen is seeing is something completely different, and not related to the client layer, but it happens in SQL Server alone.



Thanks, Erland. I appreciate the feedback. It'll be interesting to see what this actually turns out to be.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search