max worker threads Options

What does it do?

The max worker threads option controls the number of worker threads available to SQL Server.  One thread typically handles one connection, and it used to handle a batch of SQL statements as the arrive from the client.

Viewing current settings:

Run EXEC sp_configure, and look at the ‘max worker threads’ option.  If you only see a limited list of options, you need to enable the ’show advanced option’ setting, followed by RECONFIGURE.

EXEC sp_configure ’show advanced option’, ‘1′;
RECONFIGURE;
EXEC sp_configure;

As long as the number of connections is less than or equal to the number of threads available, each connection will get a single thread.  After that,  SQL Server will create a pool of worker threads to handle a larger number of clients.

Suggested settings:

For a 32 bit system, Microsoft recommends:

<= 4 processors = 256 max worker threads
8 processors = 288 max worker threads
16 processors = 352 max worker threads
32 processors = 480 max worker threads

Index Rebuild versus Reorganize: What’s the difference?

In a nutshell, you should rebuild when an index is over 30% fragmented and reorganize when an index is between 10% and 30% fragmented. If fragmentation is below 10%, you can probably just leave that index alone. Rebuilding takes more server resources (and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions), so reorg when possible. Syntax below to rebuild or reorg all indexes on a table named tTable follows…

REBUILD

ALTER INDEX ALL ON dbo.tTable REBUILD
GO

REORGANIZE

ALTER INDEX ALL ON dbo.tTable REORGANIZE
GO