Max Worker Threads option

Question

Max Worker Threads option

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

but none of this directly relates to issues with agent worker threads which have little to do with the actual user connections.

If you have hyperthreading enabled you should turn it off, have you monitored waits and schedulers - this will tell you if you have a problem here. I can't directly remember which CU is which version but you should be using CU6 or above as there are memory problems prior. Personally I think you following the wrong route, if backups are a problem then the medium you are backing up to is too slow and /or your database subsystem is too slow. How much memory do you have allocated to SQL Server? I wrote some reports which allow you to analyse a number of performance issues, you can download them from my web site - instructions etc. on are the page.

http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

colin Leversuch-Roberts (9/18/2008)
but none of this directly relates to issues with agent worker threads which have little to do with the actual user connections.
If you have hyperthreading enabled you should turn it off, have you monitored waits and schedulers - this will tell you if you have a problem here. I can't directly remember which CU is which version but you should be using CU6 or above as there are memory problems prior. Personally I think you following the wrong route, if backups are a problem then the medium you are backing up to is too slow and /or your database subsystem is too slow. How much memory do you have allocated to SQL Server? I wrote some reports which allow you to analyse a number of performance issues, you can download them from my web site - instructions etc. on are the page.
http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm

Hi,

Yes, I did monitor the waits and schedulers and did not see any problems there. I ran queries against sys.dm_os_schedulers and it was returning 0. I also monitored batches using the most CPU, excessive compilations or recompilations, memory and inefficient query plans. As I said, I do still have alot to do but most of our performance issues have been resolved with the changes that I mentioned before. Our database has grown to 140GB and because of other processes that we have running and the number of other databases I have to back up, a full backup every night was just not working. I agree that we are not on the most recent CU and I do have the latest versions and installed in our test area but due to 'busy season' for our company, they have been working 24/7 and I haven't had any window for downtime.

Thank you for the link. I will check it out!

Isabelle

Thanks!
Bea Isabelle

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Colin brings up a good point. Have you verified that your memory configuration is correct given that you have 32 bit sql server EE with a large-ram box? Thing like /PAE, AWE, lock-pages-in-memory, max memory setting.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 4

TheSQLGuru (9/18/2008)
Colin brings up a good point. Have you verified that your memory configuration is correct given that you have 32 bit sql server EE with a large-ram box? Thing like /PAE, AWE, lock-pages-in-memory, max memory setting.

Hi there,

Here is what I have setup on my server:

-Windows 2003 SP1

- AWE is enabled

- Max server memory for SQL is set to 24GB

- we have the SQL server service account added in the Lock-pages-in-memory local security setting

Isabelle

Thanks!
Bea Isabelle

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

run my rdl files and see how the memory is being used ( it's easier in a picture ) You should really get to sp2 on w2k3 and there are some critical fixes/patches which affect sql server.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 6

Thanks Colin. I will do that.

Isabelle

Thanks!
Bea Isabelle