SQLServerCentral.com / SQL Server 2008 / SQL Server 2008 Performance Tuning / Running out of workers / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 12:46:45 GMT20RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxErland ... I so appreciate that you have kept with this issue! Personally I think that for them to [i]knowingly [/i]continue to use a query that is incorrect is pathetic.Many of us are doing our best to learn how to properly manage systems for our employers. We should be able to depend on the product owners to correct mistakes when discovered. You would think at the minimum they would have corrected it in the newer versions.I put your fix into the version of the script that I now use.Thank you.Wed, 23 Oct 2013 06:52:56 GMTELLEN-610393RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxI've made some research and the query does indeed come from Activity Monitor. Furthermore, the query is the same in SQL 2012 SP1. And for that matter, SQL 2014 CTP2.I found a Connect item from 2009, [url=https://connect.microsoft.com/SQLServer/feedback/details/496162/duplicated-lines-in-ssms-2008-r2-activity-monitor]https://connect.microsoft.com/SQLServer/feedback/details/496162/duplicated-lines-in-ssms-2008-r2-activity-monitor[/url], that reported the problem. However, it was closed as Won't Fix. It was not a priority...Tue, 22 Oct 2013 15:07:10 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxErland:Thank you, I will use your replacement in the query.and now that you ask, that query may be from the Activity Monitor. I do know that one day I did capture a query that I think was from the connection for the Activity Monitor.Mon, 21 Oct 2013 06:49:06 GMTELLEN-610393RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]ELLEN-610393 (10/18/2013)[/b][hr] LEFT OUTER JOIN sys.sysprocesses p WITH (NOLOCK) ON (s.session_id = p.spid)[/quote]Here is the problem. Sysprocess does not have one per process, it has one per process and execution context id. And sometimes there can even be multiple rows for ecid = 0. Since you already have one per task, you get a complete explosion here.Does this query come from the Activity Monitor?In my [url=http://www.sommarskog.se/sqlutil/beta_lockinfo.html]beta_lockinfo[/url], I do this to avoid this problem:[code="sql"] JOIN (SELECT spid, dbid = MIN(dbid), open_tran = MIN(open_tran) FROM sys.sysprocesses WHERE ecid = 0 GROUP BY spid) AS sp ON sp.spid = es.session_id[/code]Fri, 18 Oct 2013 21:10:03 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxIf one query is generating several hundred worker threads that really sounds like you have MAXDOP set to 0 (the default). Since you know the problematic query apparently, just take that query and append OPTION (MAXDOP 4) to the end of it and fire it off in a few query windows. If it doesn't cause a couple thousand worker threads you've found your problem.Fri, 18 Oct 2013 09:18:24 GMTlnardozi 61862RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]ELLEN-610393 (10/17/2013)[/b][hr]ErlandOur 'max worker threads' is set at the default "0"Next time I see this condition I will query the sys.dm_os_workers tables directly to get the count.Since the Activity Monitor in SQL Server 2008 is so bad I usually take a quick look at it then run a couple of different queries that I found on this forum to really see what is going on.I have wondered if maybe there is a bad join in one of the queries. If you have time and are willing, I could post the two queries that I use for you to see. On the surface they both look correctly formed but there was one table/view that I am not familiar with so I took the poster's word for the correctness. For "normal" processes the number of records returned is accurate so it gave me the feeling that the number for the massive records could be correct.[/quote]Thank you [anyone] that can look at this query and if there is a flaw I welcome correction.The query that I use is: SELECT [Session ID] = s.session_id, [User Process] = CONVERT(CHAR(1), s.is_user_process), [Login] = s.login_name, [Database] = ISNULL(db_name(p.dbid), N''), [Task State] = ISNULL(t.task_state, N''), [Command] = ISNULL(r.command, N''), [Application] = ISNULL(s.program_name, N''), [Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0), [Wait Type] = ISNULL(w.wait_type, N''), [Wait Resource] = ISNULL(w.resource_description, N''), [Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''), [Head Blocker] = CASE -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1' -- session is either not blocking someone, or is blocking someone but is blocked by another party ELSE '' END, [Total CPU (ms)] = s.cpu_time, [Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024, [Memory Use (KB)] = s.memory_usage * 8192 / 1024, [Open Transactions] = ISNULL(r.open_transaction_count,0), [Login Time] = s.login_time, [Last Request Start Time] = s.last_request_start_time, [Host Name] = ISNULL(s.host_name, N''), [Net Address] = ISNULL(c.client_net_address, N''), [Execution Context ID] = ISNULL(t.exec_context_id, 0), [Request ID] = ISNULL(r.request_id, 0), [Workload Group] = ISNULL(g.name, N'')FROM sys.dm_exec_sessions s WITH (NOLOCK) LEFT OUTER JOIN sys.dm_exec_connections c WITH (NOLOCK) ON (s.session_id = c.session_id) LEFT OUTER JOIN sys.dm_exec_requests r WITH (NOLOCK) ON (s.session_id = r.session_id) LEFT OUTER JOIN sys.dm_os_tasks t WITH (NOLOCK) ON (r.session_id = t.session_id AND r.request_id = t.request_id) LEFT OUTER JOIN ( -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as -- waiting for several different threads. This will cause that thread to show up in multiple rows -- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread, -- and use it as representative of the other wait relationships this thread is involved in. SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num FROM sys.dm_os_waiting_tasks WITH (NOLOCK) ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1 LEFT OUTER JOIN sys.dm_exec_requests r2 WITH (NOLOCK) ON (s.session_id = r2.blocking_session_id) LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g WITH (NOLOCK) ON (g.group_id = s.group_id) LEFT OUTER JOIN sys.sysprocesses p WITH (NOLOCK) ON (s.session_id = p.spid)WHERE s.session_id &gt; 50ORDER BY s.session_id;Fri, 18 Oct 2013 07:23:28 GMTELLEN-610393RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]dwilliscp (10/17/2013)[/b][hr]FROM sys.dm_exec_requests erINNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid[/quote]Here is a potential problem. This join is good in most cases, but not if you use MARS (Multiple Active Result Sets) or SOAP requests, in which case there may be multiple requests for the same session id.If we ignore that, and assume that a single request is generating 700 worker threads, that is is excessive. You need to look into the query plan for that query, and investigate if you can add indexes or improve the query.There has been some other suggestions for diagnostic queries. I can offer beta_lockinfo, whicb you find at [url=http://www.sommarskog.se/sqlutil/beta_lockinfo.html]http://www.sommarskog.se/sqlutil/beta_lockinfo.html[/url]. This gives you both the query, the plan as well as a bunch of other information. And itjoins the various DMVs correctly. :--)Fri, 18 Oct 2013 07:20:17 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx..Fri, 18 Oct 2013 03:20:37 GMTr.dragoi-1095738RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxHello, You can use the query below, execute it every 30 seconds or so .. and whatch the number of connections as well !SELECT SUM(current_workers_count) current_visible_workers, SUM(active_workers_count) active_visible_workers, (SELECT COUNT(*) FROM sys.dm_exec_connections) connections FROM sys.dm_os_schedulers -- Those schedulers that have IDs greater than or equal to 255 are used internally by SQL Server -- check BOL ! WHERE scheduler_id &lt; 255 Cheers,RFri, 18 Oct 2013 03:19:36 GMTr.dragoi-1095738RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxWhen in doubt, tinker. Tinker with what you are allowed to change (which doesn't sound like much). Can you set MAXDOP to 4? MAXDOP 0 can be a killer as it'll bulk up on threads for a query on the assumption nothing else is going to run. A few milliseconds later when 100 similar queries have been executed, VOILA! Thousands of extra threads. For my dollar, even with hyperthreading MAXDOP should be set no higher than the number of physical cores - half that in AMD environments or busy Intel environments.Usually things are disk bound anyway. Sure would be nice to install a few SSDs and use them for TEMPDB - that can make a huge difference everywhere.Oh - and bad statistics. That could indirectly cause a lot of parallelism and extra threads.Thu, 17 Oct 2013 20:59:24 GMTlnardozi 61862RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]Erland Sommarskog (10/16/2013)[/b][hr][quote][b]dwilliscp (10/16/2013)[/b][hr]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)?[/quote]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 [url=http://www.sommarskog.se/sqlutil/beta_lockinfo.html]http://www.sommarskog.se/sqlutil/beta_lockinfo.html[/url]. This gives you a snapshot on what is going on in the system, including current query and plan.[/quote]Unrelated ... The following is one of the queries that I use to show what is going on... when the server is not responding, and unlike the worker query, this is the one that showed me the 700 rows with the same SPID (all generated by the same query), and I could not see one that was not CXPACKET..but maybe I should have filtered out the CXPACKET wait types after I noticed the SPID creating all the issues.USE masterGOSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECTer.session_Id AS [Spid], sp.ecid, er.start_time, DATEDIFF(SS,er.start_time,GETDATE()) as [Age Seconds], sp.nt_username, er.status, er.wait_type, SUBSTRING (qt.text, (er.statement_start_offset/2) + 1,((CASE WHEN er.statement_end_offset = -1THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2ELSE er.statement_end_offsetEND - er.statement_start_offset)/2) + 1) AS [Individual Query], qt.text AS [Parent Query], sp.program_name, sp.Hostname, sp.nt_domainFROM sys.dm_exec_requests erINNER JOIN sys.sysprocesses sp ON er.session_id = sp.spidCROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qtWHERE session_Id &gt; 50AND session_Id NOT IN (@@SPID)ORDER BY session_Id, ecidThu, 17 Oct 2013 14:55:41 GMTdwilliscpRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]ELLEN-610393 (10/17/2013)[/b]I have wondered if maybe there is a bad join in one of the queries. If you have time and are willing, I could post the two queries that I use for you to see. On the surface they both look correctly formed but there was one table/view that I am not familiar with so I took the poster's word for the correctness. For "normal" processes the number of records returned is accurate so it gave me the feeling that the number for the massive records could be correct.[/quote]Yes, it sounds like there is a join problem. Please post your queries, although it may take some days before I get the time to look at them. But maybe someone who is not at PASS can step in.Thu, 17 Oct 2013 14:46:25 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxErlandOur 'max worker threads' is set at the default "0"Next time I see this condition I will query the sys.dm_os_workers tables directly to get the count.Since the Activity Monitor in SQL Server 2008 is so bad I usually take a quick look at it then run a couple of different queries that I found on this forum to really see what is going on.I have wondered if maybe there is a bad join in one of the queries. If you have time and are willing, I could post the two queries that I use for you to see. On the surface they both look correctly formed but there was one table/view that I am not familiar with so I took the poster's word for the correctness. For "normal" processes the number of records returned is accurate so it gave me the feeling that the number for the massive records could be correct.Thu, 17 Oct 2013 06:08:02 GMTELLEN-610393RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]Erland Sommarskog (10/15/2013)[/b][hr][quote][b]Jeff Moden (10/15/2013)[/b]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.[/quote]I'm not saying that connection leaks cannot occur, because they certainly do. With proper coding where you say [code="other"]using (SqlConnection cn = new SqlConnection(connstring)) { // code here}[/code]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.[/quote]Thanks, Erland. I appreciate the feedback. It'll be interesting to see what this actually turns out to be.Wed, 16 Oct 2013 16:28:14 GMTJeff ModenRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]Erland Sommarskog (10/15/2013)[/b][hr]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.[/quote]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"?Wed, 16 Oct 2013 16:09:57 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]dwilliscp (10/16/2013)[/b][hr]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)?[/quote]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 [url=http://www.sommarskog.se/sqlutil/beta_lockinfo.html]http://www.sommarskog.se/sqlutil/beta_lockinfo.html[/url]. This gives you a snapshot on what is going on in the system, including current query and plan.Wed, 16 Oct 2013 16:05:27 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxSo 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)?Wed, 16 Oct 2013 07:20:53 GMTdwilliscpRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]Jeff Moden (10/15/2013)[/b]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.[/quote]I'm not saying that connection leaks cannot occur, because they certainly do. With proper coding where you say [code="other"]using (SqlConnection cn = new SqlConnection(connstring)) { // code here}[/code]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.Tue, 15 Oct 2013 12:19:47 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]ELLEN-610393 (10/15/2013)[/b][hr]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.[/quote]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.Tue, 15 Oct 2013 12:14:28 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxJeff, 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.Tue, 15 Oct 2013 08:54:03 GMTELLEN-610393RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]Erland Sommarskog (10/15/2013)[/b][hr][quote][b]ELLEN-610393 (10/3/2013)[/b][hr]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[/quote]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.[/quote]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.Tue, 15 Oct 2013 08:08:22 GMTJeff ModenRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]ELLEN-610393 (10/3/2013)[/b][hr]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[/quote]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.Tue, 15 Oct 2013 07:53:50 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxI 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. :-DFri, 11 Oct 2013 07:54:07 GMTJeff ModenRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]Jeff Moden (10/4/2013)[/b][hr][quote][b]ELLEN-610393 (10/3/2013)[/b][hr]Gail,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. We are using SQL Server 2008 R2 SP1, Enterprise 64-bit, 4 processors , 146 GB RAMAll opinions are welcomed.Thank you.[/quote]That, my friend, sounds like a "connection leak" somewhere in the code for those two processes or the code that controls those two processes and both will certainly need to be repaired.[/quote]Jeff [or any other wise and knowledgable person :) ] Can you point me in a direction that will help me troubleshoot what is going on? and maybe an article that will give me more in depth knowledge of worker threads? Since I am rather new here I would like to approach the people that have created these stored procedures for BO reports with some more knowledge and details.Thank you in advance for any assistance.Fri, 11 Oct 2013 07:06:37 GMTELLEN-610393RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]ELLEN-610393 (10/3/2013)[/b][hr]Gail,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. We are using SQL Server 2008 R2 SP1, Enterprise 64-bit, 4 processors , 146 GB RAMAll opinions are welcomed.Thank you.[/quote]That, my friend, sounds like a "connection leak" somewhere in the code for those two processes or the code that controls those two processes and both will certainly need to be repaired.Fri, 04 Oct 2013 14:06:48 GMTJeff ModenRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxGail,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. We are using SQL Server 2008 R2 SP1, Enterprise 64-bit, 4 processors , 146 GB RAMAll opinions are welcomed.Thank you.Thu, 03 Oct 2013 15:21:56 GMTELLEN-610393RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]Erland Sommarskog (8/9/2013)[/b][hr][quote][b]dwilliscp (8/9/2013)[/b][hr][quote]Sorry should have added.. 64bit. Any change that would effect Disaster Recover, has to be done by the App Admin team. So I put in a request, and will see if they make the change. I know when I asked to get worker threads set to 1,000 from the 800 or so that it currently is.. they wanted me to prove that the problems we are having was worker threads. Thus this posting.. on trying to track their use and what happens when they run out.[/quote]So you are the DBA, but you don't have permission to connect to the server machine directly? Well, then you have more than one problem!In the meantime, the persons who have access to the servers, will have to run the diagnostic queries for you.In this particular case, you should be able to diagnose the issue if the DAC is enabled for remote access. If SQL Server has run out of worker threads, there are of course no threads left for new connections. But there are other problems where you may find that you cannot connect, not over a remote DAC - but where any local connection works. (These problems are known as network problems, but the DBA will get the blame nevertheless.)/Erland[/quote]Yea welcome to my world... the folks that have access are the App Admin team.. not a DBA in the group. I have tried to Remote Desktop to the server and connect..using Management Studio, but it times out. (using my login) It will not let me use Admin:&lt;my login&gt;, states that DAC is not supported. I was local admin ... on our 2005 servers, but when we got the new servers.. got no access. Can not even access the drives except for my user folder.Mon, 12 Aug 2013 13:59:11 GMTdwilliscpRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]dwilliscp (8/9/2013)[/b][hr][quote]Sorry should have added.. 64bit. Any change that would effect Disaster Recover, has to be done by the App Admin team. So I put in a request, and will see if they make the change. I know when I asked to get worker threads set to 1,000 from the 800 or so that it currently is.. they wanted me to prove that the problems we are having was worker threads. Thus this posting.. on trying to track their use and what happens when they run out.[/quote]So you are the DBA, but you don't have permission to connect to the server machine directly? Well, then you have more than one problem!In the meantime, the persons who have access to the servers, will have to run the diagnostic queries for you.In this particular case, you should be able to diagnose the issue if the DAC is enabled for remote access. If SQL Server has run out of worker threads, there are of course no threads left for new connections. But there are other problems where you may find that you cannot connect, not over a remote DAC - but where any local connection works. (These problems are known as network problems, but the DBA will get the blame nevertheless.)/ErlandFri, 09 Aug 2013 13:41:07 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxI wouldn't recommend changing worker threads. 0 is a good default, it shouldn't need changing. Identifying why you're running out of worker threads should be a priority.Maybe schedule a job to run every couple minutes that runs various diagnostic scripts and inserts results into a monitoring database.Fri, 09 Aug 2013 10:28:24 GMTGilaMonsterRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]GilaMonster (8/9/2013)[/b][hr][quote][b]dwilliscp (8/9/2013)[/b][hr]We are running 2008 R2 Standard, two CPU's with 6 cores each.[/quote]32 bit or 64 bit?Have you enabled remote DAC? If not, you'll have to connect from the server directly.[/quote]Sorry should have added.. 64bit. Any change that would effect Disaster Recover, has to be done by the App Admin team. So I put in a request, and will see if they make the change. I know when I asked to get worker threads set to 1,000 from the 800 or so that it currently is.. they wanted me to prove that the problems we are having was worker threads. Thus this posting.. on trying to track their use and what happens when they run out.Fri, 09 Aug 2013 09:56:05 GMTdwilliscpRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]dwilliscp (8/9/2013)[/b][hr]We are running 2008 R2 Standard, two CPU's with 6 cores each.[/quote]32 bit or 64 bit?Have you enabled remote DAC? If not, you'll have to connect from the server directly.Fri, 09 Aug 2013 07:47:19 GMTGilaMonsterRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]John Mitchell-245523 (8/9/2013)[/b][hr]Did you follow the link I posted to watch the video? You need to use [url=http://technet.microsoft.com/en-us/library/ms178068(v=sql.105).aspx]DAC[/url].John[/quote]I am getting an error that DAC is not supported. I will put in a request to IT, but they have locked the crap out of our new servers.Fri, 09 Aug 2013 07:43:10 GMTdwilliscpRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]John Mitchell-245523 (8/9/2013)[/b][hr]Did you follow the link I posted to watch the video? You need to use [url=http://technet.microsoft.com/en-us/library/ms178068(v=sql.105).aspx]DAC[/url].John[/quote]Not yet.. still reviewing the posts.Fri, 09 Aug 2013 07:21:17 GMTdwilliscpRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]Erland Sommarskog (8/8/2013)[/b][hr]If you run out of workers, this is likely to be due to one of the following (or a combination thereof):1) There are simply too many active users.2) There are too many parallel queries.3) You have set "Max worker threads" to a low value.[/quote]Worker threads are set to zero, default.Fri, 09 Aug 2013 07:20:21 GMTdwilliscpRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxDid you follow the link I posted to watch the video? You need to use [url=http://technet.microsoft.com/en-us/library/ms178068(v=sql.105).aspx]DAC[/url].JohnFri, 09 Aug 2013 07:17:26 GMTJohn Mitchell-245523RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]GilaMonster (8/8/2013)[/b][hr][quote][b]GilaMonster (8/8/2013)[/b][hr]What version of SQL, what processor architecture, how many cores?[/quote]The DAC connection requires a sysadmin, not necessarily SA, so you should be able to use that to connect.[/quote]When the Sql Server instance stops responding.. and I try to connect using Management Studio, it times out.We are running 2008 R2 Standard, two CPU's with 6 cores each.Fri, 09 Aug 2013 07:11:32 GMTdwilliscpRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxIf you run out of workers, this is likely to be due to one of the following (or a combination thereof):1) There are simply too many active users.2) There are too many parallel queries.3) You have set "Max worker threads" to a low value.Thu, 08 Aug 2013 15:54:24 GMTErland SommarskogRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[url=http://www.sqlpassion.at/archive/2013/07/07/sql-server-quickie-6-threadpool-starvation/]This video[/url] talks about exactly the problem you're having.JohnThu, 08 Aug 2013 08:38:41 GMTJohn Mitchell-245523RE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspx[quote][b]GilaMonster (8/8/2013)[/b][hr]What version of SQL, what processor architecture, how many cores?[/quote]The DAC connection requires a sysadmin, not necessarily SA, so you should be able to use that to connect.Thu, 08 Aug 2013 08:22:52 GMTGilaMonsterRE: Running out of workershttp://www.sqlservercentral.com/Forums/Topic1482294-3387-1.aspxIT will not allow us to use the SA account, so there is no way.. that I know of .. to get into the server when it is effected. Once you can get in.. my blocking query shows no problems, and running a listing of workers shows just the normal stuff.Thu, 08 Aug 2013 08:05:08 GMTdwilliscp