How to change the SQL Max worker threads to improve performance
posted by Jason Carreiro in Industry News on Oct 20, 2011
A “normal” SQL installation on a standard Windows Server will usually run without performance or bottleneck issues. However, a 32-bit server with 4 processors or less uses only 256 worker threads, which is predefined by the SQL installer.
But if your system will be generating a fairly large load of data, the number of Max worker threads should be changed. The standards are different for 32- and 64-bit operating systems, and the graph below provides guidelines to properly configure your SQL.
Pay attention to the caution section: even though the default setting on a 32-bit machine is 256 threads, it is recommended to increase the number to 1024 threads.
To change the number, follow these instructions as outlined in MSDN:
- Open the SQL Server Management Studio
- In Object Explorer, right-click a server and select Properties.
- If you’re not able to see Object Explorer, go to the View menu and select it.
- Click the Processors node.
- In the Max worker threads box, type or select a value from 32 through 32767.
- Stop and restart the SQL server agent service for the change to take effect.
For more details and instructions for configuring other versions of SQL Server, see: http://msdn.microsoft.com/en-us/library/ms187024.aspx
Share this article
|
|
|||
| Share |
|
|
Comments
More Recent News
Writing technical doc
Writing technical documentation should not be a complicated procedure. Remember, you’re not writing a novel. You want to communicate information and procedures in a clear and concise fashion. Sometimes basic short... (May 15, 2012)
Go Postal on Spam
You get it at the office. You get it at home. Postal spam a.k.a. junk mail. Since the late 1800’s flyers, ad-mail, unsolicited magazines and pamphlets have been cluttering up people’s mailboxes and, now, people’s... (May 09, 2012)
Single installer to deploy
With the advent of 64-bit hardware, Microsoft has released a 64-bit version of Windows to leverage the capabilities of these hardware platforms. The new Version has the advantages of addressing more than 4 GB of... (May 03, 2012)
