Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Running out of workers Expand / Collapse
Author
Message
Posted Friday, October 11, 2013 7:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1504020
Posted Tuesday, October 15, 2013 7:53 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1504763
Posted Tuesday, October 15, 2013 8:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1504775
Posted Tuesday, October 15, 2013 8:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:15 AM
Points: 36, 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.
Post #1504797
Posted Tuesday, October 15, 2013 12:14 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1504898
Posted Tuesday, October 15, 2013 12:19 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1504902
Posted Wednesday, October 16, 2013 7:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 311, Visits: 538
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)?
Post #1505180
Posted Wednesday, October 16, 2013 4:05 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1505432
Posted Wednesday, October 16, 2013 4:09 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1505434
Posted Wednesday, October 16, 2013 4:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1505440
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse