August 14, 2008 at 11:24 am
Hi,
In monitoring some performance issues that we are having, I see that the SQL:User Connections counter shows on the average about 800. I read online that if this value exceeds 255 to try increasing the maximum worker threads value. I looked on our server and the value is 0 so im assuming that it is using the default of 255. Our server has 16 processors running 32-bit and the article I read shows that the automatic number of max thread workers for this combination of processors and 32-bit is 352. Has anyone increased the number to 800 and seen any performance benefit? It says the recommended maximum for 32-bit is 1024.
Thanks,
Isabelle 😀
Thanks!
Bea Isabelle
August 15, 2008 at 10:53 am
Increasing the number of worker threads may actually decrease the performace because too many threads causes context switching which could take so much of the resources that the OS starts to degrade in overall performance.
I faced a similar problem ,and the root of the problem was data contention. some queries were doing tablen scans, locking tables etc.
I hope this helps.
August 15, 2008 at 10:59 am
Thanks for the info.
I am currently looking into other areas of contention as well so I guess I will leave the default value for the worker threads. I know already that we are having some disk contention and are taking steps to correct that and then I am moving onto looking at queries and indexes. :blink:
Thanks,
Isabelle
Thanks!
Bea Isabelle
August 15, 2008 at 11:01 am
if your server is a 32bit server with 16 processors, changing it to 352 should help as this is the recommended setting.
you can check what the current settings and thread allocation using these queries.
--thread setting
select max_workers_count from sys.dm_os_sys_info
--active threads
select count(*) from sys.dm_os_threads
hope that helps
August 15, 2008 at 11:05 am
Hi,
Based on the queries, it looks like it's okay for now:
max_workers_count = 352
count from sys.dm_os_threads = 147
Thanks again! 😀
Isabelle
Thanks!
Bea Isabelle
September 15, 2008 at 9:29 am
Hi,
Can I ask a thread related question? In SQL 2000 we had to change a registry setting to increase the number of worker thread allowed for the SQL Agent as scheduled tasks were being held up due to insufficent threads.
We are migrating to 2008. Does anyone know if we have to modify the registry or whether 2008 handles the SQL agent threads differently?
Many thanks
September 15, 2008 at 10:00 am
sql 2005 automatically assigns workers calculated as per cpu/core. If you're talking 256 worker threads this sounds very much like sql 2000 as by default the worker count is set to 0 in 2005.
so .. if you're cross posting or have made changes to the out of the box setup of sql server you should state this clearly - perhaps you could clarify this?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 15, 2008 at 10:16 am
Hi,
Perhaps I should have started a new posting, but since there seemed to be some informed members with respect to worker threads I thought I would ask the question here.
In SQL 2000, SQL Agent worker threads are controlled completely different from the main SQL server and the default number is 20. So as a consquence, on systems which use a large number of scheduled tasks the tasks themselves can be held pending the availabiity of a worker thread.
The number of worker threads allocated to the agent is controlled via the registry see http://support.microsoft.com/kb/306457.
My question is, does anyone know if this also applies to SQL 2008 as we are migrating the system onto this platform.
I hope this clarifies my question.
Many thanks.
September 16, 2008 at 9:39 am
Isabelle (8/14/2008)
Hi,In monitoring some performance issues that we are having, I see that the SQL:User Connections counter shows on the average about 800. I read online that if this value exceeds 255 to try increasing the maximum worker threads value. I looked on our server and the value is 0 so im assuming that it is using the default of 255. Our server has 16 processors running 32-bit and the article I read shows that the automatic number of max thread workers for this combination of processors and 32-bit is 352. Has anyone increased the number to 800 and seen any performance benefit? It says the recommended maximum for 32-bit is 1024.
Thanks,
Isabelle 😀
1) exactly WHAT performance problems are you having, and what have you done to determine the cause and symptoms?
2) Is the server hyper threaded, or 16 physical cores?
3) What is your memory situation and are you on enterprise edition or std?
4) are these 800 connections actually active, or just sitting there? track them over time and see how many are active on average. 750 connections twiddling their digital thumbs won't have any affect on the server (other than the connection memory, which could be an issue if the RAM is very tight - but then you have worse problems than connections anyway).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 17, 2008 at 12:17 pm
I'm going to ask what I normally ask - do you have problems with waits and schedulers and are you monitoring them so that you see this problem, or did you just read about this and decided you needed this a possible solution to something else?
Sorry to sound abrupt but I often have to try to resolve problems with servers that have had ill informed changes made to them.
If you read the KB you'd note that the figure you are after is stored in msdb, not the registry with SQL 2005.
As to connections - I have 1000 connections but rarely get more than 3 or 4 active processes at a time. I've run some really busy servers and never had issues with agent processes.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 18, 2008 at 4:55 am
Hi,
Yes we have experienced the problem of SQL Agent worker threads on SQL 2000, which is why I know about the article.
I must admit I missed the bit about SQL 2005 so thank you for that, although having reviewed the table contents I am still not sure what needs to be inserted / modified to increase the sql agent worker threads.
To compound my problems we are condensing two separate instances on to one instance so the number of scheduled tasks which already exceed well over 20 of which many have long run time will almost double.
If anyone can advise I would very much appreciate it.
September 18, 2008 at 9:51 am
Hi,
Here are some answers to your questions below:
1) exactly WHAT performance problems are you having, and what have you done to determine the cause and symptoms?
we were having users calling that our ERP is running slow, printing pick lists are slow, some users experiencing time-outs
2) Is the server hyper threaded, or 16 physical cores?
We have 4 physical processors so they are hyperthreaded. they are Intel Xeon MP 3GHz.
3) What is your memory situation and are you on enterprise edition or std?
We have 32GB RAM and we are on Microsoft SQL Server Enterprise Edition 9.00.3186
4) are these 800 connections actually active, or just sitting there? track them over time and see how many are active on average. 750 connections twiddling their digital thumbs won't have any affect on the server (other than the connection memory, which could be an issue if the RAM is very tight - but then you have worse problems than connections anyway).
Of these 800 connections, I never see more than 5 -10 active at a time.
This was just one area that I was looking at when looking into our performance problems. I was just curious about the normal setting for this and in no way thought it was the primary problem to our performance problems.
Thanks,
Isabelle
Thanks!
Bea Isabelle
September 18, 2008 at 10:04 am
colin Leversuch-Roberts (9/17/2008)
I'm going to ask what I normally ask - do you have problems with waits and schedulers and are you monitoring them so that you see this problem, or did you just read about this and decided you needed this a possible solution to something else?Sorry to sound abrupt but I often have to try to resolve problems with servers that have had ill informed changes made to them.
.
Hi,
I spent alot of time monitoring different areas of our system as was able to make some changes that were causing our performance problems:
1) I used Perf Monitor and over a week's time noticed that we were having huge memory paging spikes in the morning and after further investigation saw that my full backups were running into the morning and the VERIFY portion was causing the problem. I changed my backups to do only FULL on the weekends and differential and logs during the week. That resolved that issue.
2) I took SQL Profiler tuning workloads at different times in the day and when we were experiencing slowdowns and ran them through the DTA and saw that we were missing some indexes and statistics. I also was able to determine other indexes were not being used. After implementing some of the recommendations, we saw an immediate improvement in our picking list processing.
3) After looking at the file system, we realized that there were over 1.6M files because nothing had been purged since we implemented the ERP and so after cleaning that up, our Pick Lists printed much faster and resolved that issue.
4) for the users that were experiencing time outs I did a SQL profile session for locks and deadlocks and was able to determine that a report that was being run by our ecommerce site was putting a lock on a table that the users were trying to access. This was causing the time out issues. we had the developer to modify his select to use the NOLOCK hint and that resolved that issue. we are no longer experiencing the time outs. And we also made sure that the NOLOCK hint would not cause any problems seeing that it would allow dirty reads.
So as you can see, yes I have been monitoring others things and when I came across the article about user connections, I was just checking to see if my settings were set correctly. I did not assume that it was the cause of all my performance problems. I was just trying to do an overview of all the areas that could affect performance.
Thanks,
Isabelle 🙂
Thanks!
Bea Isabelle
September 18, 2008 at 11:19 am
Isabelle (9/18/2008)
colin Leversuch-Roberts (9/17/2008)
I'm going to ask what I normally ask - do you have problems with waits and schedulers and are you monitoring them so that you see this problem, or did you just read about this and decided you needed this a possible solution to something else?Sorry to sound abrupt but I often have to try to resolve problems with servers that have had ill informed changes made to them.
.
Hi,
I spent alot of time monitoring different areas of our system as was able to make some changes that were causing our performance problems:
1) I used Perf Monitor and over a week's time noticed that we were having huge memory paging spikes in the morning and after further investigation saw that my full backups were running into the morning and the VERIFY portion was causing the problem. I changed my backups to do only FULL on the weekends and differential and logs during the week. That resolved that issue.
2) I took SQL Profiler tuning workloads at different times in the day and when we were experiencing slowdowns and ran them through the DTA and saw that we were missing some indexes and statistics. I also was able to determine other indexes were not being used. After implementing some of the recommendations, we saw an immediate improvement in our picking list processing.
3) After looking at the file system, we realized that there were over 1.6M files because nothing had been purged since we implemented the ERP and so after cleaning that up, our Pick Lists printed much faster and resolved that issue.
4) for the users that were experiencing time outs I did a SQL profile session for locks and deadlocks and was able to determine that a report that was being run by our ecommerce site was putting a lock on a table that the users were trying to access. This was causing the time out issues. we had the developer to modify his select to use the NOLOCK hint and that resolved that issue. we are no longer experiencing the time outs. And we also made sure that the NOLOCK hint would not cause any problems seeing that it would allow dirty reads.
So as you can see, yes I have been monitoring others things and when I came across the article about user connections, I was just checking to see if my settings were set correctly. I did not assume that it was the cause of all my performance problems. I was just trying to do an overview of all the areas that could affect performance.
Thanks,
Isabelle 🙂
Well done Isabelle! Boy am I glad that you are 1-in-10000 as far as how well you are doing in isolating and addressing your performance issues. Otherwise I might just be out of work!! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 18, 2008 at 11:59 am
Thanks!! 😀
I just got my MCITP certification and I have been trying to apply all the great stuff I learned to my current environment. I still have a long way to go and we still have one more area that is having performance issues, but I'm getting there! I just wish I had more time to "play" in my SQL Test Lab area to try out different things.
Isabelle
Thanks!
Bea Isabelle
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply