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 ?
>>
>>
>>
>>
>
