SMP & KB article Q254321

SMP & KB article Q254321

Post by Michael Philli » Tue, 13 Aug 2002 02:37:06



Bill,

Thanks for your reply.

Your answer did cover an issue that I do need to address, i.e.
"Maximum worker threads".

We have an application called Gentran that uses SQL Server 2000 as its
database and this setting (along with most others) is currently the
default value. Now, Gentran is the only "user" that connects to or
uses this instance of SQL Server and the value of "Maximum worker
threads" seems overly high for a database that is only serving one
user, especially as it isn't a multi-threaded app.

Is it still not worthwhile reducing this setting? Reducing this
setting will, I assume, clear some more memory for SQL Server.

Also, are there any other settings that can improve system performance
for a situation such as this? (ignoring indexes, filegroups etc.)

Thanks,

Michael

 
 
 

SMP & KB article Q254321

Post by Bill Hollinshead [M » Wed, 14 Aug 2002 02:21:33


Hello Michael,

This is the first anyone has ever asked me about lowering max worker
threads; every other customer has wanted to increase it <g>. Lowering max
worker threads may help your situation (where an application is single
threaded/has just open connection to SQL Server). SQL Server reserves a
pool of memory (based upon the max worker threads, available RAM, etc) at
startup, and yet SQL Server doesn't commit memory for a worker thread until
it's needed. However, SQL Server (internally) still must reserve that
memory (for configured worker threads) even if other memory uses (mostly
the buffer pool) need to commit more memory but cannot. Thus lowering max
worker threads may allow SQL Server to better utilize that memory, likely
for the buffer pool (i.e., cache).

When starting SQL Server in minimal configuration mode (see the -f
parameter in the still-mostly-applicable-to-2000 article
http://support.microsoft.com/support/kb/articles/q200/1/03.asp) we reserve
memory for 32 max worker threads. If you do wish to lower it, a
quantitative analysis of its effect will require a controlled series (5-10)
of tests where max worker threads is changed by a percentage of it's range
(between the minimum of 32 and the current setting of 255) for each test,
and you will need to use a replayable trace (to repeatedly test the app/SQL
Server performance under the same representative load). Keep an eye on the
"SQL Server: Buffer Manager: Buffer Cache Hit Ratio" performance monitor
counter as it is the metric you should likely use to determine whether
altering the worker threads setting will help. I used "likely" because
there could be other memory resource bottlenecks that have a more important
need for that memory than buffer hits, such as using a large number of OLE
DB connections/linked servers which could instead theoretically benefit
from the use of that memory (changed via a SQL Server startup with the -g
parameter).  Anyway, if the number of cache hits increased when the number
of worker threads was decreased, then it can be worth lowering (or it may
be worth buying more memory in order to further increase the cache hits
<g>). I suspect you will find the effects of lowering max worker threads to
be on the order of a percentage point difference in the cache hits per test
(if not less), but the effects do depend upon the app design and the memory
resources of the box <g>. One warning: If that replayable trace didn't
catch certain situations (such as the app suddenly and briefly opening more
than 255 connections - let's say this happens once a week) then (during
that weekly event)  the connections could be queuing for one of those fewer
worker threads, and thus SQL Server may appear to become 'inexplicably
slow' while the time to run the query remains fast <g>.

One final word about lowering the max worker thread setting: If you ever
have a problem (say with performance) where you need to call Product
Support, and if the max worker threads is set to some value other that it's
default (255), then you may first wish to change it back to 255 (and see if
the performance problem still occurs) before calling. Product support will
typically and strenuously request that set such configuration options be
set back to their defaults before Support dives into troubleshooting a
problem. This does not mean you shouldn't be using the non-default
setting<g>. It only means support's experience has been that a notable
number of  performance problems are rooted in non-default configuration
settings, and thus setting such configuration options back to their
defaults simply helps Support quickly narrow down the possibilities for the
root cause of an issue (whether root cause is due to a bug, or whether root
cause is designed behavior). Having a series of tests (as the previous
paragraph suggested) which "justify" a lowered setting will also help you
justify your  need to keep the worker thread setting low (if,
theoretically, increasing max worker threads avoided the performance
problem for which you had called Support).

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

SMP & KB article Q254321

Post by Bill Hollinshead [M » Wed, 14 Aug 2002 03:29:44


Hi Michael,

Re: "Also, are there any other settings that can improve system performance
for a situation such as this? (ignoring indexes, filegroups etc.)"

In general, the sp_configure settings should be kept at their defaults
(many of which are now auto-tuned by SQL Server). In the past (6.5 days,
which was before auto-tuning was added) many customer cases involved
setting these configuration settings back to their defaults. With 6.5,
these settings were much like the "fuel/air" and "idle speed" set screws on
a carburetor: Adjusting one set*may be better, but setting both to
the maximum would stall then engine <g>, the main difference being that SQL
Server had about 30-40 such set screws, and this increased the likelihood
that the engine would stall at some point, not always at startup ("ease of
use"  is one of the main reasons we introduced auto-tuning in 7.0). And our
experience has been that the sp_configure settings may improve performance
by an order of XX%, yet adjusting indexes, filegroups etc may improve
performance by an order of XXX%.  This indicates that one can obtain a far
more efficient use of time by analyzing the performance options that I will
not discuss within this post <g>.

If you do wish to alter sp_configure settings, then it becomes a matter of
quantifying the effects of such alterations using a realistic load (much
like my previous post mentioned). The counters are mostly mentioned within
the performance White Papers that are upon both
http://www.veryComputer.com/
http://www.veryComputer.com/(the
7.0 perftuning.doc is still applicable even though it doesn't cover 2000's
distributed partitioned views, etc.). With that replayable trace, you can
keep an eye on the counters and trace events (mentioned in those White
Papers) while changing the configuration settings (but no more than 10% to  
20% of a configuration setting's allowable range should be changed between
each test).

Even so, I have no knowledge of any setting change that would be
useful/applicable to all situations, and keep in mind that I would still
feel far more comfortable after seeing the results of tests (even if I were
to know the particulars of a situation <g>).

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://www.veryComputer.com/