| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2004-10-28 : 10:05:54
|
| Yesterday, we encountered a slow respond from SQL-Server. First I thought there is a lock but there wasn't. Then I noticed in EM that there are 341 users connection. Worker Threads are set to 255 (default). We have Access 2000 application as interface and SQL-Server as data source. The production server has 3.5GB RAM but SQL-Server Standard version is installed. I believe it uses only 2GM at most. Should I increase the number of Worker Threads? If yes, what is the best for my case? 350? Any disadnatage?Canada DBA |
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-10-28 : 10:19:33
|
Have you gotten "The working thread limit of 255 has been reached." error? Is your SQL Server using 2GB of ram? If not you can consider raising the max worker threads option in sp_configure. Reason I asked is that the "thread-per-connection can consume large amounts of system resources.(BOL)" Read more about the max worker threads option in books online.-JonShould still be a "Starting Member"  . |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2004-10-28 : 10:27:55
|
| Have you gotten "The working thread limit of 255 has been reached." error?How can I check that? Is the error message supposed to be in Windows Event Viewer?Is your SQL Server using 2GB of ram?I read that SQL server Standard uses at most 2GB. So, although my server has 3.5GB but it shouldn't be used by SQL entirely.Canada DBA |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-10-28 : 10:33:52
|
| That error will be in the SQL Server Error log and probably event viewer too. And standard does use 2gb at most but is your server load that heavy that it needs all 2gb now?-JonShould still be a "Starting Member" . |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-10-28 : 10:59:11
|
No my server is not that much loaded to use all the 2GB. The reason I wrote that, is that I am afraid of increasing the Workers Threads to 350 and system fall in problem because I have no idea about the right number. quote:
Originally posted by surefooted
That error will be in the SQL Server Error log and probably event viewer too. And standard does use 2gb at most but is your server load that heavy that it needs all 2gb now?-JonShould still be a "Starting Member" .
Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-10-28 : 11:27:18
|
| "The working thread limit of 255 has been reached." errorI don't see this error or similar error in SQL error log for yesterday. Does it mean that there was no problem related to Worker Threads last night and SQL Server was slow down because of something else? probably a lock on a table or something?Canada DBA |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-10-28 : 13:35:14
|
| It means it didn't reach its limit. You'll have to experiment with what number gives you teh best results as you know your server. SQL Server won't reach its max worker threads from just connections though. If it runs out of a 1-to-1, it will pool connections to have threads available for other processes.-JonShould still be a "Starting Member" . |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-10-29 : 13:41:25
|
| sp_Configure returned 255 for max worker threads on both Config_value and Run_value columns. Does this mean that I need to increas the amount?Canada DBA |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-10-29 : 13:55:03
|
You can, max worker threads can be 32767, although I wouldn't suggest going that high.  You need to find out what higher number gives you the best results and allow you server to stil do its job unadulterted.-JonShould still be a "Starting Member" . |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-10-29 : 15:13:56
|
| If you have not gotten the "thread limit reached" error message, then what makes you think that your problem is the number of worker threads? It sounds to me like you're just guessing at it and don't really understand the way worker threads work (hey, it's not my specialty either). If your only symptom is slow performance, I think there are probably 1,000 other places to check first before messing with the worker threads setting, unless you really, really know what you're doing there. Changing that sort of configuration setting on a whim is the sort of behavior that leads to other problems that you had no idea were related. I'd look elsewhere first.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
|
Worker Threads