Maximun Worker Threads

Hello All,
I'm having performance problem with my database server:
My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
2003 Std Edition server, SQL 2000 Std Edition SP4.
I notice the slowness when there are more that 255 connections were made to
the SQL. Would it help if I increase the work threads?
Please Advise. Thanks.SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB of
memory MAX. So if you really have 8 dual core processors and 255 concurrent
connections I suggest you look at upgrading the edition and or at least the
version. Max worker threads is not your problem but what exactly is can not
be determined from this little bit of information.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
> Hello All,
> I'm having performance problem with my database server:
> My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
> Windows
> 2003 Std Edition server, SQL 2000 Std Edition SP4.
> I notice the slowness when there are more that 255 connections were made
> to
> the SQL. Would it help if I increase the work threads?
> Please Advise. Thanks.|||Thanks for the reply.
Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
dual-core would be 8 processors?
My question is Can I increase the number of maximun worker threads? I see
whenever it reach over maximun threads (255) I start to see the latency.
Thanks.
"Andrew J. Kelly" wrote:
> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB of
> memory MAX. So if you really have 8 dual core processors and 255 concurrent
> connections I suggest you look at upgrading the edition and or at least the
> version. Max worker threads is not your problem but what exactly is can not
> be determined from this little bit of information.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
> > Hello All,
> >
> > I'm having performance problem with my database server:
> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
> > Windows
> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> >
> > I notice the slowness when there are more that 255 connections were made
> > to
> > the SQL. Would it help if I increase the work threads?
> >
> > Please Advise. Thanks.
>|||From BOL:
The default setting for max worker threads (255) is best for most systems.
However, depending on your system configuration, setting max worker threads
to a smaller value sometimes improves performance.
When the actual number of user connections is less than the amount set in
max worker threads, one thread handles each connection. However, if the
actual number of connections exceeds the amount set in max worker threads,
SQL Server pools the worker threads so that the next available worker thread
can handle the request.
Yes, you can increase max worker threads. Most recommendations you will
find are to not mess with it. I would go up in small increments, and stay
way away from the upper limit of 32767. Also note that this setting take a
restart of sql server prior to the change taking effect.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David" <David@.discussions.microsoft.com> wrote in message
news:123172EF-8CB5-4C7A-9A59-2BDEBE529870@.microsoft.com...
> Thanks for the reply.
> Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
> dual-core would be 8 processors?
> My question is Can I increase the number of maximun worker threads? I see
> whenever it reach over maximun threads (255) I start to see the latency.
> Thanks.
> "Andrew J. Kelly" wrote:
>> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB
>> of
>> memory MAX. So if you really have 8 dual core processors and 255
>> concurrent
>> connections I suggest you look at upgrading the edition and or at least
>> the
>> version. Max worker threads is not your problem but what exactly is can
>> not
>> be determined from this little bit of information.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
>> > Hello All,
>> >
>> > I'm having performance problem with my database server:
>> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
>> > Windows
>> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
>> >
>> > I notice the slowness when there are more that 255 connections were
>> > made
>> > to
>> > the SQL. Would it help if I increase the work threads?
>> >
>> > Please Advise. Thanks.
>>|||> Would it help if I increase the work threads?
Collect the SQL wait stats, and check if there are a lot of waits on
ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
for the worker. In general, you shouldn't see any significant waits on the
worker at all.
Linchi
"David" wrote:
> Hello All,
> I'm having performance problem with my database server:
> My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> 2003 Std Edition server, SQL 2000 Std Edition SP4.
> I notice the slowness when there are more that 255 connections were made to
> the SQL. Would it help if I increase the work threads?
> Please Advise. Thanks.|||Can you increase this, yes of course you can. But you need to determine that
adding more threads will actually help. If you have a lot of waits on the
threads it can be a sign you need more. But I am willing to bet that your
system as configured can not adequately handle that many threads. If the
system (both hardware and database) was properly configured and tuned you
would most likely never need more than 255 worker threads. Try bumping it
and see if it helps but I would start to tune the system or think about
moving to SQL2005 so you can use more memory effectively.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:123172EF-8CB5-4C7A-9A59-2BDEBE529870@.microsoft.com...
> Thanks for the reply.
> Let me ask you this, SQL supports 4 "Sockets" but each sockets is a
> dual-core would be 8 processors?
> My question is Can I increase the number of maximun worker threads? I see
> whenever it reach over maximun threads (255) I start to see the latency.
> Thanks.
> "Andrew J. Kelly" wrote:
>> SQL Server 2000 Std edition only supports 4 processors (Sockets) and 2GB
>> of
>> memory MAX. So if you really have 8 dual core processors and 255
>> concurrent
>> connections I suggest you look at upgrading the edition and or at least
>> the
>> version. Max worker threads is not your problem but what exactly is can
>> not
>> be determined from this little bit of information.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:88C3FF2A-54E9-4C7C-A14E-B1479FE9CEC1@.microsoft.com...
>> > Hello All,
>> >
>> > I'm having performance problem with my database server:
>> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory,
>> > Windows
>> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
>> >
>> > I notice the slowness when there are more that 255 connections were
>> > made
>> > to
>> > the SQL. Would it help if I increase the work threads?
>> >
>> > Please Advise. Thanks.
>>|||Linchi-
SQLServer:Wait Statistic only appears in SQL2005. Where can I find it in
SQL2000?
Thanks.
"Linchi Shea" wrote:
> > Would it help if I increase the work threads?
> Collect the SQL wait stats, and check if there are a lot of waits on
> ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
> for the worker. In general, you shouldn't see any significant waits on the
> worker at all.
> Linchi
> "David" wrote:
> > Hello All,
> >
> > I'm having performance problem with my database server:
> > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> >
> > I notice the slowness when there are more that 255 connections were made to
> > the SQL. Would it help if I increase the work threads?
> >
> > Please Advise. Thanks.|||Oops! I didn't realize you were using SQL2000. In that case, just collect the
SQL wait stats via dbcc sqlperf(waitstats).
Linchi
"David" wrote:
> Linchi-
> SQLServer:Wait Statistic only appears in SQL2005. Where can I find it in
> SQL2000?
> Thanks.
> "Linchi Shea" wrote:
> > > Would it help if I increase the work threads?
> >
> > Collect the SQL wait stats, and check if there are a lot of waits on
> > ThreadPool. Also, watch perfmon counters under SQLServer:Wait Statistics\Wait
> > for the worker. In general, you shouldn't see any significant waits on the
> > worker at all.
> >
> > Linchi
> >
> > "David" wrote:
> >
> > > Hello All,
> > >
> > > I'm having performance problem with my database server:
> > > My server is a Dell PowerEdge 6850, 8 Dual-Processesors, 4GB memory, Windows
> > > 2003 Std Edition server, SQL 2000 Std Edition SP4.
> > >
> > > I notice the slowness when there are more that 255 connections were made to
> > > the SQL. Would it help if I increase the work threads?
> > >
> > > Please Advise. Thanks.

Maximum Worker Threads

SQL Server had reached it's maximum (255) worker threads during the
transaction log creation.
SQL Server was unable create to worker thread to create the transaction log
file.
The database had several blocked processes but no deadlocks.
The next time period the database transaction log file was created without a
problem.
This is the first time the maximum threads has been reached.
This system has run for 2 years know.
Should I change the Maximum Worker Theads?
Thanks,What exactly do you mean by "SQL Server had reached it's maximum (255) worker
threads during the transaction log creation" ? Was this is the error message?
And what OS and SQL server version are you using?
Thanks
GYK
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>|||Yes, I reached the default maximum worker threads of 255.
The SQL Server errorlog messages is:
2004-12-08 10:31:12.88 spid239 startsubprocess: Limit on 'Max worker
threads' reached.
Thanks,
"Joe K.." wrote:
> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>

Maximum Worker Threads

SQL Server had reached it's maximum (255) worker threads during the
transaction log creation.
SQL Server was unable create to worker thread to create the transaction log
file.
The database had several blocked processes but no deadlocks.
The next time period the database transaction log file was created without a
problem.
This is the first time the maximum threads has been reached.
This system has run for 2 years know.
Should I change the Maximum Worker Theads?
Thanks,
What exactly do you mean by "SQL Server had reached it's maximum (255) worker
threads during the transaction log creation" ? Was this is the error message?
And what OS and SQL server version are you using?
Thanks
GYK
"Joe K.." wrote:

> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>
|||Yes, I reached the default maximum worker threads of 255.
The SQL Server errorlog messages is:
2004-12-08 10:31:12.88 spid239 startsubprocess: Limit on 'Max worker
threads' reached.
Thanks,
"Joe K.." wrote:

> SQL Server had reached it's maximum (255) worker threads during the
> transaction log creation.
> SQL Server was unable create to worker thread to create the transaction log
> file.
> The database had several blocked processes but no deadlocks.
> The next time period the database transaction log file was created without a
> problem.
> This is the first time the maximum threads has been reached.
> This system has run for 2 years know.
> Should I change the Maximum Worker Theads?
> Thanks,
>

Max Worker Threads setting in X64 system.

Hi All,

As we know, the default max worker thread in SQL 2005 is 0. The default value for Max worker threads, 0, allows SQL Server to automatically configure the number of worker threads AT STARTUP.

http://msdn2.microsoft.com/en-us/library/ms187024.aspx

However, what's the actual max worker thread while the "max worker thread" option is configured as 0? Is it the one list in this table?

Number of CPUs

32-bit computer

64-bit computer

<= 4 processors

256

512

8 processors

288

576

16 processors

352

704

32 processors

480

960

This table is got form the above article. Please kindly help me confirm

This is the formula that SQL uses is it is at 0.

x86

<= 4 procs = 256

> 4 procs = 256+((# procs-4)*8)

x64

<= 4 procs = 512

> 4 procs = 512+((# procs-4)*16)

It is also wise to set this back to 0 if it is an upgrade from 2000.

|||Thanks a lot for your confirmation and formula!

max worker threads option

Hi,
I am working on a SQL server and the "Maximum worker threads" option is set
to zero. That seems strange to me.
What does that means? Does zero have special meaning for "Maximum worker
threads" option?
Thank you,
AlanA.M wrote:
> I am working on a SQL server and the "Maximum worker threads" option is se
t
> to zero. That seems strange to me.
> What does that means? Does zero have special meaning for "Maximum worker
> threads" option?
The default setting is 255, so how yours ended up at 0 seems odd. For
more information look at "max worker thread Option" in Books Online.
http://msdn.microsoft.com/library/e...config_09wu.asp
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Thank you for reply.
I already went through books online. I haven't see any place explain the
meaning of the value zero for "Maximum worker threads". That is what I am
looking for.
Alan
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:OPyv$g4DFHA.576@.TK2MSFTNGP15.phx.gbl...
> A.M wrote:
set
> The default setting is 255, so how yours ended up at 0 seems odd. For
> more information look at "max worker thread Option" in Books Online.
> http://msdn.microsoft.com/library/e...config_09wu.asp
> --
> Aaron Weiker
> http://aaronweiker.com/
> http://www.sqlprogrammer.org/|||A.M wrote:
> I already went through books online. I haven't see any place explain the
> meaning of the value zero for "Maximum worker threads". That is what I am
> looking for.
Glad to hear, so used to people not checking there first. It looks like
the values are only valid from 32-32767. So my guess would be that if it
is zero it defaults to either 32 or 255.
I would definitely be interested to know how it got set to 0, I would
think that there would be something internal to SQL to prevent this. And
also what it is actually using inside SQL.
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/

Max Worker Threads and User Connections

Hi

we run SQL 2005 Enterprise on W2003 Enterprise 32 bit on 8 dual core Xeon 7020 with 32Gb RAM.

The max worker thread settings is 0 now. But 3d party consultancy which monitors our servers on the POP is suggesting to make this figure to be 1200. They said - there are 1000 connections at peak time. I am going to check if it is correct...

But my question is - is it proper thing to do to set worker thread number that high? (BOL says - MS doesnt recommend more than 1024 on 32 bit systems)

Is it that useful to match number of connections with number of threads?

Thank you

P.S. that consultancy let us down several times. I dont trust then at all.. I need a real reason to change such a thing

Well, I probably wouldn't recommend it generally speaking.

First off, just because there are 1000 connections at a given time, definately doesn't imply that there are 1000 concurrent processing requests...connections are frequently idle in many types of systems processing 'think' time by end users, or just sitting idle in a connection pool (.NET or otherwise). If you are using connection pooling, I'm sure you'll find that many of your connections are idle a fair amount of the time...

In a 32-bit system, on startup Sql server will reserve enough memory to load it's own binaries in, then some to handle 8kb and greater memory requests (typically 128mb by default, but can be modified using a startup switch), then enough memory to be able to allocate stacks for the total number of threads possible to use, which is determined by your 'max worker thread' setting.

Each thread stack is uses about .5mb of memory, which means using a setting of 1200 will result in a reserved space of about 600mb for just stack allocation on a 32bit box. Bear in mind that on a 32bit box of this size (assuming you aren't using a /3gb or /USERVA switch...if you are you ain't going to get to use most of your physical memory), the maximum VAS for a user-mode process like Sql server is 2gb, so stack space is going to use over a 1/4 of that space alone.

By default, in Sql 2005 on a 32bit box, Sql Server will dynamically handle threading using a simple algorithm where with 4 procs or less, 256 max threads will be used, then an additional 8 threads per CPU...in the case of a 16-way machine (yours is an 8-way dual core, 16 schedulers), Sql will use a max thread dynamic setting of 352 on that machine. If you bump that to 1200, your allocating about 850 more than the dyamic configuration would.

Given what I mentioned earlier about the fact that many connections are typically idle a fair amount of time, connections will pool the thread use at the OS level, and typically the dynamic default configuration is hightly recommend.

I'd ask the consultants to provide you extensive documentation on why they think it would be beneficial...typically, recommending something like this would take some extensive testing, and intimate knowledge of a system...

HTH,

|||

Thank you Chad,

I have same ideas but just needed some authoritative information to argue with the consulters.

I really hate 'solutions' made from thin air. I refused to do any changes and requested concrete measures have been made before they decided on the advice. I think they have none.

Might be there is a chance they are right (that advice was given to resolve issues I addressed in my previous topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=436762&SiteID=1). Fortunately these accidents havent repeated since I went through the application and put some NOLOCKs into critical places..

MAX Worker threads ...

We just upgraded our database server to 8 processors and 24 GB RAM. With the
hyperthreading, SQL Server is recognizing 16 processors. It looks like some
of the processors are running at less than 5%. I'm wondering if turning up
the number of max threads will spread the load across the processors better.
Thanks,
Bob Castleman
DBA Poseur
Hmmm ...
Maybe not?
According to BOL, if I reach the MAX Worker Threads threshold there should
be an entry in the SQL Log. I don't see any so I don't think this is it.
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>
|||Bob,
It's unlikely you are running out of worker threads and if you do you will
see error msgs stating you are out of worker threads. Adding more threads
does not spread the load better. SQL Server already evenly divides the
worker threads across each UMS or CPU. That way each one has a pool of
threads to use.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>

MAX Worker threads ...

We just upgraded our database server to 8 processors and 24 GB RAM. With the
hyperthreading, SQL Server is recognizing 16 processors. It looks like some
of the processors are running at less than 5%. I'm wondering if turning up
the number of max threads will spread the load across the processors better.
Thanks,
Bob Castleman
DBA PoseurHmmm ...
Maybe not?
According to BOL, if I reach the MAX Worker Threads threshold there should
be an entry in the SQL Log. I don't see any so I don't think this is it.
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>|||Bob,
It's unlikely you are running out of worker threads and if you do you will
see error msgs stating you are out of worker threads. Adding more threads
does not spread the load better. SQL Server already evenly divides the
worker threads across each UMS or CPU. That way each one has a pool of
threads to use.
--
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>

MAX Worker threads ...

We just upgraded our database server to 8 processors and 24 GB RAM. With the
hyperthreading, SQL Server is recognizing 16 processors. It looks like some
of the processors are running at less than 5%. I'm wondering if turning up
the number of max threads will spread the load across the processors better.
Thanks,
Bob Castleman
DBA PoseurHmmm ...
Maybe not?
According to BOL, if I reach the MAX Worker Threads threshold there should
be an entry in the SQL Log. I don't see any so I don't think this is it.
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>|||Bob,
It's unlikely you are running out of worker threads and if you do you will
see error msgs stating you are out of worker threads. Adding more threads
does not spread the load better. SQL Server already evenly divides the
worker threads across each UMS or CPU. That way each one has a pool of
threads to use.
Andrew J. Kelly SQL MVP
"Bob Castleman" <nomail@.here> wrote in message
news:undKflbfFHA.1480@.TK2MSFTNGP10.phx.gbl...
> We just upgraded our database server to 8 processors and 24 GB RAM. With
> the hyperthreading, SQL Server is recognizing 16 processors. It looks like
> some of the processors are running at less than 5%. I'm wondering if
> turning up the number of max threads will spread the load across the
> processors better.
> Thanks,
> Bob Castleman
> DBA Poseur
>

max worker threads

We have some SQL Servers that receive high number of connections and stored
procedure calls.
Every once in a while, we may have a bad query plan and then one stored proc
blocks the other stored procs and in no time, we are out of worker threads
and no one can connect
Have you come across such situations ? If so, how did you handle it ? Is it
safe to increase the max worker threads ? What are the consequences ?
The default setting for the 'max worker threads' option is 255,But if the
number of user connections surpasses this value the thread pooling will be
used. For example, if the maximum number of the user connections to your SQL
Server box is equal to 255, you can set the 'max worker threads' options to
255, this frees up resources for SQL Server to use elsewhere. If the maximum
number of the user connections to your SQL Server box is equal to 500, you
can set the 'max worker threads' options to 500, this can improve SQL Server
performance because thread pooling will not be used.
In your case , it sounds like it's worth investigating the query plan
problem , as increasing the worker threads will take up extra resources on
your server
Jack Vamvas
"Hassan" <hassan@.hotmail.com> wrote in message
news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> We have some SQL Servers that receive high number of connections and
stored
> procedure calls.
> Every once in a while, we may have a bad query plan and then one stored
proc
> blocks the other stored procs and in no time, we are out of worker threads
> and no one can connect
> Have you come across such situations ? If so, how did you handle it ? Is
it
> safe to increase the max worker threads ? What are the consequences ?
>
|||Hi Jack
The number of connections is not related to the number of worker threads
quite as simply as you've described.
Connetions only use threads while they're actually executing. When
connections are in idle state (usually a majority of the time in typical
OLTP apps), there are no threads associated with the connection. This
asynchronous design allows any given number of threads to service a far
larger number of connections.
The way to tell if SQL Server is running short on threads is to use the
Windows Perfmon.exe & monitor the process object sqlservr instance's thread
count counter. If the process SQL Server is running has close to 255
threads, it might be worth increasing Max Worker Threads.
In my experience, this is typically when an OLTP application has thousands
of user connections running, as statistically, this translates to ~255
connections executing concurrently therefore requiring at least 255
threads..
As for the original question, it may help to increase the number of workers,
but keep in mind that this means that SQL Server will be able to schedule
that many more threads to perform concurrent work & you need to take into
account your server resources when making this decision. This is a gross
simplification, but in my experience increasing Max Worker Threads past 255
on systems with fewer than 4 CPUs has rarely helped much as these servers
usually don't have the capacity to process in increased concurrent workload
for example. It should hurt much to increase this setting & see for yourself
whether this helps SQL Server get its work done more effieciently or whether
it contributes further to the problem by increasing context switching etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jack Vamvas" <info@.nospam.com> wrote in message
news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>
> The default setting for the 'max worker threads' option is 255,But if the
> number of user connections surpasses this value the thread pooling will be
> used. For example, if the maximum number of the user connections to your
> SQL
> Server box is equal to 255, you can set the 'max worker threads' options
> to
> 255, this frees up resources for SQL Server to use elsewhere. If the
> maximum
> number of the user connections to your SQL Server box is equal to 500, you
> can set the 'max worker threads' options to 500, this can improve SQL
> Server
> performance because thread pooling will not be used.
> In your case , it sounds like it's worth investigating the query plan
> problem , as increasing the worker threads will take up extra resources on
> your server
> Jack Vamvas
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> stored
> proc
> it
>
|||I looked at the thread counter for sqlservr process and it states around
180.
What does that mean ?
Are there 180 threads running concurrently ?
If so, would that mean that when i run sp_who2 active, i should see a high
number of connections atleast around 180 or so..
Thats not the case.
Can you tell me more ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Hi Jack
> The number of connections is not related to the number of worker threads
> quite as simply as you've described.
> Connetions only use threads while they're actually executing. When
> connections are in idle state (usually a majority of the time in typical
> OLTP apps), there are no threads associated with the connection. This
> asynchronous design allows any given number of threads to service a far
> larger number of connections.
> The way to tell if SQL Server is running short on threads is to use the
> Windows Perfmon.exe & monitor the process object sqlservr instance's
> thread count counter. If the process SQL Server is running has close to
> 255 threads, it might be worth increasing Max Worker Threads.
> In my experience, this is typically when an OLTP application has thousands
> of user connections running, as statistically, this translates to ~255
> connections executing concurrently therefore requiring at least 255
> threads..
> As for the original question, it may help to increase the number of
> workers, but keep in mind that this means that SQL Server will be able to
> schedule that many more threads to perform concurrent work & you need to
> take into account your server resources when making this decision. This is
> a gross simplification, but in my experience increasing Max Worker Threads
> past 255 on systems with fewer than 4 CPUs has rarely helped much as these
> servers usually don't have the capacity to process in increased concurrent
> workload for example. It should hurt much to increase this setting & see
> for yourself whether this helps SQL Server get its work done more
> effieciently or whether it contributes further to the problem by
> increasing context switching etc.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jack Vamvas" <info@.nospam.com> wrote in message
> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>
|||And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
and saw just 2 user processes running.
Yes its a busy system with 4000 connections but did not appear i was running
under pressure or am I and dont know about it
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>I looked at the thread counter for sqlservr process and it states around
>180.
> What does that mean ?
> Are there 180 threads running concurrently ?
> If so, would that mean that when i run sp_who2 active, i should see a high
> number of connections atleast around 180 or so..
> Thats not the case.
> Can you tell me more ?
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
>
|||Hi Hassan
180 threads, means exactly that - there are 180 worker threads in SQL Server
windows process. Most would be worker threads (rather than dedicated system
threads), waiting for queries or i/o work to service. Having 180 threads
doesn't imply any system pressure on its own. It does suggest that, of your
~ 4000 connections, you might typically see peaks of ~ 180 queries actually
running concurrently.
It's possible for the # of threads to exceed connections and vice versa due
to the thread pooling model SQL Server uses in its work scheduling engine
(UMS). There's no directl correlation you can draw to assume that the number
of threads is either higher or lower than the number of connections at any
time. It's perfectly reasonable to see more threads than connections for
example where many connections may have been in use but close. The threads
will hang around in the pool for a while before SQL Server decides to trim
the worker pool. I don't have any info on exactly how or when it does this
trimming, but this is a fairly common design in multi-threaded server
programming..
It's certainly far more common to see the # of threads far lower than the #
of connections though, so if you see the # of threads > connections for
extended periods, there's possibly something wrong.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uqs1e7t8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
> and saw just 2 user processes running.
> Yes its a busy system with 4000 connections but did not appear i was
> running under pressure or am I and dont know about it
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>

max worker threads

We have some SQL Servers that receive high number of connections and stored
procedure calls.
Every once in a while, we may have a bad query plan and then one stored proc
blocks the other stored procs and in no time, we are out of worker threads
and no one can connect
Have you come across such situations ? If so, how did you handle it ? Is it
safe to increase the max worker threads ? What are the consequences ?The default setting for the 'max worker threads' option is 255,But if the
number of user connections surpasses this value the thread pooling will be
used. For example, if the maximum number of the user connections to your SQL
Server box is equal to 255, you can set the 'max worker threads' options to
255, this frees up resources for SQL Server to use elsewhere. If the maximum
number of the user connections to your SQL Server box is equal to 500, you
can set the 'max worker threads' options to 500, this can improve SQL Server
performance because thread pooling will not be used.
In your case , it sounds like it's worth investigating the query plan
problem , as increasing the worker threads will take up extra resources on
your server
Jack Vamvas
"Hassan" <hassan@.hotmail.com> wrote in message
news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> We have some SQL Servers that receive high number of connections and
stored
> procedure calls.
> Every once in a while, we may have a bad query plan and then one stored
proc
> blocks the other stored procs and in no time, we are out of worker threads
> and no one can connect
> Have you come across such situations ? If so, how did you handle it ? Is
it
> safe to increase the max worker threads ? What are the consequences ?
>|||Hi Jack
The number of connections is not related to the number of worker threads
quite as simply as you've described.
Connetions only use threads while they're actually executing. When
connections are in idle state (usually a majority of the time in typical
OLTP apps), there are no threads associated with the connection. This
asynchronous design allows any given number of threads to service a far
larger number of connections.
The way to tell if SQL Server is running short on threads is to use the
Windows Perfmon.exe & monitor the process object sqlservr instance's thread
count counter. If the process SQL Server is running has close to 255
threads, it might be worth increasing Max Worker Threads.
In my experience, this is typically when an OLTP application has thousands
of user connections running, as statistically, this translates to ~255
connections executing concurrently therefore requiring at least 255
threads..
As for the original question, it may help to increase the number of workers,
but keep in mind that this means that SQL Server will be able to schedule
that many more threads to perform concurrent work & you need to take into
account your server resources when making this decision. This is a gross
simplification, but in my experience increasing Max Worker Threads past 255
on systems with fewer than 4 CPUs has rarely helped much as these servers
usually don't have the capacity to process in increased concurrent workload
for example. It should hurt much to increase this setting & see for yourself
whether this helps SQL Server get its work done more effieciently or whether
it contributes further to the problem by increasing context switching etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jack Vamvas" <info@.nospam.com> wrote in message
news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>
> The default setting for the 'max worker threads' option is 255,But if the
> number of user connections surpasses this value the thread pooling will be
> used. For example, if the maximum number of the user connections to your
> SQL
> Server box is equal to 255, you can set the 'max worker threads' options
> to
> 255, this frees up resources for SQL Server to use elsewhere. If the
> maximum
> number of the user connections to your SQL Server box is equal to 500, you
> can set the 'max worker threads' options to 500, this can improve SQL
> Server
> performance because thread pooling will not be used.
> In your case , it sounds like it's worth investigating the query plan
> problem , as increasing the worker threads will take up extra resources on
> your server
> Jack Vamvas
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> stored
> proc
> it
>|||I looked at the thread counter for sqlservr process and it states around
180.
What does that mean ?
Are there 180 threads running concurrently ?
If so, would that mean that when i run sp_who2 active, i should see a high
number of connections atleast around 180 or so..
Thats not the case.
Can you tell me more ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Hi Jack
> The number of connections is not related to the number of worker threads
> quite as simply as you've described.
> Connetions only use threads while they're actually executing. When
> connections are in idle state (usually a majority of the time in typical
> OLTP apps), there are no threads associated with the connection. This
> asynchronous design allows any given number of threads to service a far
> larger number of connections.
> The way to tell if SQL Server is running short on threads is to use the
> Windows Perfmon.exe & monitor the process object sqlservr instance's
> thread count counter. If the process SQL Server is running has close to
> 255 threads, it might be worth increasing Max Worker Threads.
> In my experience, this is typically when an OLTP application has thousands
> of user connections running, as statistically, this translates to ~255
> connections executing concurrently therefore requiring at least 255
> threads..
> As for the original question, it may help to increase the number of
> workers, but keep in mind that this means that SQL Server will be able to
> schedule that many more threads to perform concurrent work & you need to
> take into account your server resources when making this decision. This is
> a gross simplification, but in my experience increasing Max Worker Threads
> past 255 on systems with fewer than 4 CPUs has rarely helped much as these
> servers usually don't have the capacity to process in increased concurrent
> workload for example. It should hurt much to increase this setting & see
> for yourself whether this helps SQL Server get its work done more
> effieciently or whether it contributes further to the problem by
> increasing context switching etc.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jack Vamvas" <info@.nospam.com> wrote in message
> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>|||And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
and saw just 2 user processes running.
Yes its a busy system with 4000 connections but did not appear i was running
under pressure or am I and dont know about it
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>I looked at the thread counter for sqlservr process and it states around
>180.
> What does that mean ?
> Are there 180 threads running concurrently ?
> If so, would that mean that when i run sp_who2 active, i should see a high
> number of connections atleast around 180 or so..
> Thats not the case.
> Can you tell me more ?
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
>|||Hi Hassan
180 threads, means exactly that - there are 180 worker threads in SQL Server
windows process. Most would be worker threads (rather than dedicated system
threads), waiting for queries or i/o work to service. Having 180 threads
doesn't imply any system pressure on its own. It does suggest that, of your
~ 4000 connections, you might typically see peaks of ~ 180 queries actually
running concurrently.
It's possible for the # of threads to exceed connections and vice versa due
to the thread pooling model SQL Server uses in its work scheduling engine
(UMS). There's no directl correlation you can draw to assume that the number
of threads is either higher or lower than the number of connections at any
time. It's perfectly reasonable to see more threads than connections for
example where many connections may have been in use but close. The threads
will hang around in the pool for a while before SQL Server decides to trim
the worker pool. I don't have any info on exactly how or when it does this
trimming, but this is a fairly common design in multi-threaded server
programming..
It's certainly far more common to see the # of threads far lower than the #
of connections though, so if you see the # of threads > connections for
extended periods, there's possibly something wrong.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uqs1e7t8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
> and saw just 2 user processes running.
> Yes its a busy system with 4000 connections but did not appear i was
> running under pressure or am I and dont know about it
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>

max worker threads

We have some SQL Servers that receive high number of connections and stored
procedure calls.
Every once in a while, we may have a bad query plan and then one stored proc
blocks the other stored procs and in no time, we are out of worker threads
and no one can connect
Have you come across such situations ? If so, how did you handle it ? Is it
safe to increase the max worker threads ? What are the consequences ?The default setting for the 'max worker threads' option is 255,But if the
number of user connections surpasses this value the thread pooling will be
used. For example, if the maximum number of the user connections to your SQL
Server box is equal to 255, you can set the 'max worker threads' options to
255, this frees up resources for SQL Server to use elsewhere. If the maximum
number of the user connections to your SQL Server box is equal to 500, you
can set the 'max worker threads' options to 500, this can improve SQL Server
performance because thread pooling will not be used.
In your case , it sounds like it's worth investigating the query plan
problem , as increasing the worker threads will take up extra resources on
your server
Jack Vamvas
"Hassan" <hassan@.hotmail.com> wrote in message
news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> We have some SQL Servers that receive high number of connections and
stored
> procedure calls.
> Every once in a while, we may have a bad query plan and then one stored
proc
> blocks the other stored procs and in no time, we are out of worker threads
> and no one can connect
> Have you come across such situations ? If so, how did you handle it ? Is
it
> safe to increase the max worker threads ? What are the consequences ?
>|||Hi Jack
The number of connections is not related to the number of worker threads
quite as simply as you've described.
Connetions only use threads while they're actually executing. When
connections are in idle state (usually a majority of the time in typical
OLTP apps), there are no threads associated with the connection. This
asynchronous design allows any given number of threads to service a far
larger number of connections.
The way to tell if SQL Server is running short on threads is to use the
Windows Perfmon.exe & monitor the process object sqlservr instance's thread
count counter. If the process SQL Server is running has close to 255
threads, it might be worth increasing Max Worker Threads.
In my experience, this is typically when an OLTP application has thousands
of user connections running, as statistically, this translates to ~255
connections executing concurrently therefore requiring at least 255
threads..
As for the original question, it may help to increase the number of workers,
but keep in mind that this means that SQL Server will be able to schedule
that many more threads to perform concurrent work & you need to take into
account your server resources when making this decision. This is a gross
simplification, but in my experience increasing Max Worker Threads past 255
on systems with fewer than 4 CPUs has rarely helped much as these servers
usually don't have the capacity to process in increased concurrent workload
for example. It should hurt much to increase this setting & see for yourself
whether this helps SQL Server get its work done more effieciently or whether
it contributes further to the problem by increasing context switching etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jack Vamvas" <info@.nospam.com> wrote in message
news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>
> The default setting for the 'max worker threads' option is 255,But if the
> number of user connections surpasses this value the thread pooling will be
> used. For example, if the maximum number of the user connections to your
> SQL
> Server box is equal to 255, you can set the 'max worker threads' options
> to
> 255, this frees up resources for SQL Server to use elsewhere. If the
> maximum
> number of the user connections to your SQL Server box is equal to 500, you
> can set the 'max worker threads' options to 500, this can improve SQL
> Server
> performance because thread pooling will not be used.
> In your case , it sounds like it's worth investigating the query plan
> problem , as increasing the worker threads will take up extra resources on
> your server
> Jack Vamvas
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one stored
> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ? Is
> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>|||I looked at the thread counter for sqlservr process and it states around
180.
What does that mean ?
Are there 180 threads running concurrently ?
If so, would that mean that when i run sp_who2 active, i should see a high
number of connections atleast around 180 or so..
Thats not the case.
Can you tell me more ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Hi Jack
> The number of connections is not related to the number of worker threads
> quite as simply as you've described.
> Connetions only use threads while they're actually executing. When
> connections are in idle state (usually a majority of the time in typical
> OLTP apps), there are no threads associated with the connection. This
> asynchronous design allows any given number of threads to service a far
> larger number of connections.
> The way to tell if SQL Server is running short on threads is to use the
> Windows Perfmon.exe & monitor the process object sqlservr instance's
> thread count counter. If the process SQL Server is running has close to
> 255 threads, it might be worth increasing Max Worker Threads.
> In my experience, this is typically when an OLTP application has thousands
> of user connections running, as statistically, this translates to ~255
> connections executing concurrently therefore requiring at least 255
> threads..
> As for the original question, it may help to increase the number of
> workers, but keep in mind that this means that SQL Server will be able to
> schedule that many more threads to perform concurrent work & you need to
> take into account your server resources when making this decision. This is
> a gross simplification, but in my experience increasing Max Worker Threads
> past 255 on systems with fewer than 4 CPUs has rarely helped much as these
> servers usually don't have the capacity to process in increased concurrent
> workload for example. It should hurt much to increase this setting & see
> for yourself whether this helps SQL Server get its work done more
> effieciently or whether it contributes further to the problem by
> increasing context switching etc.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jack Vamvas" <info@.nospam.com> wrote in message
> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>>
>> The default setting for the 'max worker threads' option is 255,But if the
>> number of user connections surpasses this value the thread pooling will
>> be
>> used. For example, if the maximum number of the user connections to your
>> SQL
>> Server box is equal to 255, you can set the 'max worker threads' options
>> to
>> 255, this frees up resources for SQL Server to use elsewhere. If the
>> maximum
>> number of the user connections to your SQL Server box is equal to 500,
>> you
>> can set the 'max worker threads' options to 500, this can improve SQL
>> Server
>> performance because thread pooling will not be used.
>> In your case , it sounds like it's worth investigating the query plan
>> problem , as increasing the worker threads will take up extra resources
>> on
>> your server
>> Jack Vamvas
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
>> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one stored
>> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ? Is
>> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>>
>|||And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
and saw just 2 user processes running.
Yes its a busy system with 4000 connections but did not appear i was running
under pressure or am I and dont know about it
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>I looked at the thread counter for sqlservr process and it states around
>180.
> What does that mean ?
> Are there 180 threads running concurrently ?
> If so, would that mean that when i run sp_who2 active, i should see a high
> number of connections atleast around 180 or so..
> Thats not the case.
> Can you tell me more ?
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
>> Hi Jack
>> The number of connections is not related to the number of worker threads
>> quite as simply as you've described.
>> Connetions only use threads while they're actually executing. When
>> connections are in idle state (usually a majority of the time in typical
>> OLTP apps), there are no threads associated with the connection. This
>> asynchronous design allows any given number of threads to service a far
>> larger number of connections.
>> The way to tell if SQL Server is running short on threads is to use the
>> Windows Perfmon.exe & monitor the process object sqlservr instance's
>> thread count counter. If the process SQL Server is running has close to
>> 255 threads, it might be worth increasing Max Worker Threads.
>> In my experience, this is typically when an OLTP application has
>> thousands of user connections running, as statistically, this translates
>> to ~255 connections executing concurrently therefore requiring at least
>> 255 threads..
>> As for the original question, it may help to increase the number of
>> workers, but keep in mind that this means that SQL Server will be able to
>> schedule that many more threads to perform concurrent work & you need to
>> take into account your server resources when making this decision. This
>> is a gross simplification, but in my experience increasing Max Worker
>> Threads past 255 on systems with fewer than 4 CPUs has rarely helped much
>> as these servers usually don't have the capacity to process in increased
>> concurrent workload for example. It should hurt much to increase this
>> setting & see for yourself whether this helps SQL Server get its work
>> done more effieciently or whether it contributes further to the problem
>> by increasing context switching etc.
>> HTH
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Jack Vamvas" <info@.nospam.com> wrote in message
>> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>>
>> The default setting for the 'max worker threads' option is 255,But if
>> the
>> number of user connections surpasses this value the thread pooling will
>> be
>> used. For example, if the maximum number of the user connections to your
>> SQL
>> Server box is equal to 255, you can set the 'max worker threads' options
>> to
>> 255, this frees up resources for SQL Server to use elsewhere. If the
>> maximum
>> number of the user connections to your SQL Server box is equal to 500,
>> you
>> can set the 'max worker threads' options to 500, this can improve SQL
>> Server
>> performance because thread pooling will not be used.
>> In your case , it sounds like it's worth investigating the query plan
>> problem , as increasing the worker threads will take up extra resources
>> on
>> your server
>> Jack Vamvas
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
>> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one stored
>> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ?
>> Is
>> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>>
>>
>|||Hi Hassan
180 threads, means exactly that - there are 180 worker threads in SQL Server
windows process. Most would be worker threads (rather than dedicated system
threads), waiting for queries or i/o work to service. Having 180 threads
doesn't imply any system pressure on its own. It does suggest that, of your
~ 4000 connections, you might typically see peaks of ~ 180 queries actually
running concurrently.
It's possible for the # of threads to exceed connections and vice versa due
to the thread pooling model SQL Server uses in its work scheduling engine
(UMS). There's no directl correlation you can draw to assume that the number
of threads is either higher or lower than the number of connections at any
time. It's perfectly reasonable to see more threads than connections for
example where many connections may have been in use but close. The threads
will hang around in the pool for a while before SQL Server decides to trim
the worker pool. I don't have any info on exactly how or when it does this
trimming, but this is a fairly common design in multi-threaded server
programming..
It's certainly far more common to see the # of threads far lower than the #
of connections though, so if you see the # of threads > connections for
extended periods, there's possibly something wrong.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uqs1e7t8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
> and saw just 2 user processes running.
> Yes its a busy system with 4000 connections but did not appear i was
> running under pressure or am I and dont know about it
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>>I looked at the thread counter for sqlservr process and it states around
>>180.
>> What does that mean ?
>> Are there 180 threads running concurrently ?
>> If so, would that mean that when i run sp_who2 active, i should see a
>> high number of connections atleast around 180 or so..
>> Thats not the case.
>> Can you tell me more ?
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
>> Hi Jack
>> The number of connections is not related to the number of worker threads
>> quite as simply as you've described.
>> Connetions only use threads while they're actually executing. When
>> connections are in idle state (usually a majority of the time in typical
>> OLTP apps), there are no threads associated with the connection. This
>> asynchronous design allows any given number of threads to service a far
>> larger number of connections.
>> The way to tell if SQL Server is running short on threads is to use the
>> Windows Perfmon.exe & monitor the process object sqlservr instance's
>> thread count counter. If the process SQL Server is running has close to
>> 255 threads, it might be worth increasing Max Worker Threads.
>> In my experience, this is typically when an OLTP application has
>> thousands of user connections running, as statistically, this translates
>> to ~255 connections executing concurrently therefore requiring at least
>> 255 threads..
>> As for the original question, it may help to increase the number of
>> workers, but keep in mind that this means that SQL Server will be able
>> to schedule that many more threads to perform concurrent work & you need
>> to take into account your server resources when making this decision.
>> This is a gross simplification, but in my experience increasing Max
>> Worker Threads past 255 on systems with fewer than 4 CPUs has rarely
>> helped much as these servers usually don't have the capacity to process
>> in increased concurrent workload for example. It should hurt much to
>> increase this setting & see for yourself whether this helps SQL Server
>> get its work done more effieciently or whether it contributes further to
>> the problem by increasing context switching etc.
>> HTH
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Jack Vamvas" <info@.nospam.com> wrote in message
>> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>>
>> The default setting for the 'max worker threads' option is 255,But if
>> the
>> number of user connections surpasses this value the thread pooling will
>> be
>> used. For example, if the maximum number of the user connections to
>> your SQL
>> Server box is equal to 255, you can set the 'max worker threads'
>> options to
>> 255, this frees up resources for SQL Server to use elsewhere. If the
>> maximum
>> number of the user connections to your SQL Server box is equal to 500,
>> you
>> can set the 'max worker threads' options to 500, this can improve SQL
>> Server
>> performance because thread pooling will not be used.
>> In your case , it sounds like it's worth investigating the query plan
>> problem , as increasing the worker threads will take up extra resources
>> on
>> your server
>> Jack Vamvas
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
>> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one
>> stored
>> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ?
>> Is
>> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>>
>>
>>
>

Max Woprked threads

We consistently have between 550 and 600 connections. The server has 8gb of ram and is a dediocated sql server (advanced and enterpirse editions so awe has been enabled) would performance inpro9ve if the mas threats is set to say 550?
Moses
I prefer using a default value of the max worker threads. Microsoft states
that it is best for most systems.
Before you changed the default value did you recieve the message from SQL
Server "The working thread limit of 255 has been reached."?
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?
|||You don't need anywhere near as many worker threads as you have users.
Unless you are seeing entries in the error log stating you are running out
of worker threads I would not touch that setting.
Andrew J. Kelly SQL MVP
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?

Max Woprked threads

We consistently have between 550 and 600 connections. The server has 8gb of ram and is a dediocated sql server (advanced and enterpirse editions so awe has been enabled) would performance inpro9ve if the mas threats is set to say 550?Moses
I prefer using a default value of the max worker threads. Microsoft states
that it is best for most systems.
Before you changed the default value did you recieve the message from SQL
Server "The working thread limit of 255 has been reached."?
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?|||You don't need anywhere near as many worker threads as you have users.
Unless you are seeing entries in the error log stating you are running out
of worker threads I would not touch that setting.
--
Andrew J. Kelly SQL MVP
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?

Max Woprked threads

We consistently have between 550 and 600 connections. The server has 8gb of
ram and is a dediocated sql server (advanced and enterpirse editions so awe
has been enabled) would performance inpro9ve if the mas threats is set to sa
y 550?Moses
I prefer using a default value of the max worker threads. Microsoft states
that it is best for most systems.
Before you changed the default value did you recieve the message from SQL
Server "The working thread limit of 255 has been reached."?
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?|||You don't need anywhere near as many worker threads as you have users.
Unless you are seeing entries in the error log stating you are running out
of worker threads I would not touch that setting.
Andrew J. Kelly SQL MVP
"Moses" <anonymous@.discussions.microsoft.com> wrote in message
news:1C89340C-DF46-42BC-BA9D-46427C6D1CE9@.microsoft.com...
> We consistently have between 550 and 600 connections. The server has 8gb
of ram and is a dediocated sql server (advanced and enterpirse editions so
awe has been enabled) would performance inpro9ve if the mas threats is set
to say 550?