Max Worker Trhead
-
I'm getting this message in the log and modified my values from 255 to 500 via enterprise manager.
I then went into QA and ran sp_configure, it showed my configuration as 500, but my running value is still 255.
max worker threads 32 32767 500 255
So I took the manual route and ran the following
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max worker threads', 500
GO
RECONFIGURE
GO
EXEC sp_configure
Still get
max worker threads 32 32767 500 255
I checked BOL and it says that it you don't need a SQL restart..
Why is my running value still showing 255?
Thanks
Susan
-
You run the following query.
sp_configure 'show advanced options', 1
GO
RECONFIGURE with override
GO
sp_configure 'max worker threads', 512
GO
RECONFIGURE with override
GO
EXEC sp_configure
You didn't get the value since you have used reconfigure statement. If you use reconfigure the you need sql restart for particular options on sp_configure.
If you use reconfigure with override this will avoid sql restart for those options on sp_configure.
Try the above query that will help you.
Regards..Vidhya Sagar
SQL-Articles -
Always make a point to use the RECONFIGURE with OVERRIDE option when you change any configuration options that comes under the advanced options category few may require restart few might not but still they require the SQL Server to realise that there is a configuration change which can be accomplished by override option.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
I ran the script, it now shows my config_value = 213, but my run_value is still 255
sp_configure 'show advanced options', 1
GO
RECONFIGURE with override
GO
sp_configure 'max worker threads', 512
GO
RECONFIGURE with override
GO
EXEC sp_configure
-
I am not sure what version of BOL you are using but mine says:
max worker threads (A, RR) 32 32767 255
RR: Options that require a server restart before taking effect.
* Noel -
Here's what I have in my BOL
max worker threads Option
Use the max worker threads option to configure the number of worker threads available to Microsoft® SQL Server™ processes. SQL Server uses the native thread services of the Microsoft Windows NT® 4.0 or Windows® 2000 operating system so that one or more threads support each network that SQL Server supports simultaneously; another thread handles database checkpoints; and a pool of threads handles all users.
Thread pooling helps optimize performance when large numbers of clients are connected to the server. Usually, a separate operating system thread is created for each client connection to consume fewer system resources. However, with hundreds of connections to the server, using a thread-per-connection can consume large amounts of system resources. max worker threads enables SQL Server to create a pool of worker threads to service a larger number of client connections, which improves performance.
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.
When the maximum number of worker threads is reached, SQL Server returns the following message:
The working thread limit of 255 has been reached.Because Windows 98 does not support thread pooling, the option has no effect on those systems.
max worker threads is an advanced option. If you will be using the sp_configure system stored procedure to change the setting, you can change max worker threads only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).
To configure the maximum number of worker threads
See Also
©1988-2000 Microsoft Corporation. All Rights Reserved.
-
Here is what Mine says:
max worker threads Option
Use the max worker threads option to configure the number of worker threads available to Microsoft® SQL Server™ processes. SQL Server uses the native thread services of the Microsoft Windows NT® 4.0 or Windows® 2000 operating system so that one or more threads support each network that SQL Server supports simultaneously; another thread handles database checkpoints; and a pool of threads handles all users.
Thread pooling helps optimize performance when large numbers of clients are connected to the server. Usually, a separate operating system thread is created for each client connection to consume fewer system resources. However, with hundreds of connections to the server, using a thread-per-connection can consume large amounts of system resources. max worker threads enables SQL Server to create a pool of worker threads to service a larger number of client connections, which improves performance.
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.
When the maximum number of worker threads is reached, SQL Server returns the following message:
The working thread limit of 255 has been reached.
Because Windows 98 does not support thread pooling, the option has no effect on those systems.
max worker threads is an advanced option. If you will be using the sp_configure system stored procedure to change the setting, you can change max worker threads only when show advanced options is set to 1. The system must be stopped and restarted in order for the new setting to take effect.
To configure the maximum number of worker threads
Enterprise Manager
How to configure the maximum number of worker threads (Enterprise Manager)
To configure the maximum number of worker threads
Expand a server group.
Right-click a server, and then click Properties.
Click the Processor tab.
In the Maximum worker threads box, type or select a value from 32 through 32767.
The default value is 255.
Transact-SQL
SQL-DMO
See Also
RECONFIGURE
Setting Configuration Options
sp_configure
SQL Server Task Scheduling
©1988-2004 Microsoft Corporation. All Rights Reserved.
* Noel -
I am using the Latest BOL for SQL Server 2000... I hope
* Noel -
Note that my page says at the end:
©1988-2004 Microsoft Corporation. All Rights Reserved.
and your page says:
©1988-2000 Microsoft Corporation. All Rights Reserved.
Cheers,
* Noel -
Thanks Noel!
Time to update my BOL.....
Scheduling restart of server.
Thanks for all your help
Susan
-
Thats a valid note.
Enterprise Manager
Transact-SQLViewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply