December 16, 2008 at 8:30 am
We have recently upgraded to SQL Server 2005 and now we are facing a very High Context Switches/sec on SQL Server 2005.
It is touching 8000 all the time and sometimes higher then this.
Processor time is avg between 40-60%.
Processor Queue Length is under 2-3 most of the times.
Can anybody tells me what could be the reasons for high Context Switches/sec ?
December 16, 2008 at 11:55 am
Is "Hyper-Threading" enabled on that box? This could be a reason ...
Or do you have any special "Affinity Masks" set?
To reduce the number of "Context Switches" you could try to enable the "lightweight pooling" feature ...
Jörg A. Stryk
MVP - MS Dynamics NAV
December 16, 2008 at 7:55 pm
Thanks for the reply.
I read somewhere in the docs/forms, "lightweight pooling" implementations and requirements should
be analyzed by Microsoft first.
Is this juts a tuning configurable item which we can start ON and OFF on our requirements?
December 17, 2008 at 2:39 am
Well, I guess "lightweight pooling" should be handled with care. I found this KB article: http://support.microsoft.com/kb/319942/en-us
Lightweight Pooling
By default, SQL Server uses one thread per active SPID or user process. These threads work in a pooled configuration to keep the number of threads manageable. The advanced configuration option "lightweight pooling" (which is sometimes referred to as "Fiber mode") uses Windows NT "fiber" support to essentially handle several execution contexts with a single thread.
Based on actual production experience, you do not need to use Fiber mode except in very rare circumstances. Lightweight pooling is only even potentially useful if all of the following conditions are met. You must determine if it is actually useful through careful controlled testing.
Large multi-processor servers are in use.
All servers are running at or near maximum capacity.
A lot of context switching occurs (greater than 20,000 per second).
To look for context switching, use Performance Monitor, select the counter threads, select the object Context switches/sec", and then select to capture all SQL Server instances. SQL Mail in SQL Server 2000 or in SQL Server 2005 is not supported if you run the server in Fiber mode. SQL Mail is not supported in SQL Server 2000 64 bit. For more information, see the "Differences Between 64-bit and 32-bit Releases" topic in SQL Server 2000 (64-bit Edition) Books Online. For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
308604 (http://support.microsoft.com/kb/308604/ ) PRB: SQLMail is not supported when you run the server in fiber mode
303120 (http://support.microsoft.com/kb/303120/ ) FIX: ConnectionWrite error when you use lightweight pooling
But actually it is a ON/OFF thing; you could enable/disable it via Management Studion or sp_configure. Refer to "Books Online" for details.
Jörg A. Stryk
MVP - MS Dynamics NAV
December 18, 2008 at 8:36 am
1) what else is running on the server?
2) what is maxdop setting at the sql server level and are you seeing a lot of parallel queries?
3) Have you run track_waitstats_2005? search online for it. MS has a word document that describes wait stats and how to analyze. search for: sql server 2005 performance tuning waits queues
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 19, 2008 at 1:55 pm
mshah23 (12/16/2008)
We have recently upgraded to SQL Server 2005 and now we are facing a very High Context Switches/sec on SQL Server 2005.It is touching 8000 all the time and sometimes higher then this.
Processor time is avg between 40-60%.
Processor Queue Length is under 2-3 most of the times.
Can anybody tells me what could be the reasons for high Context Switches/sec ?
How many processors/cores does your Server have?
* Noel
December 20, 2008 at 11:29 am
4 Logical Processor.
2 Logical Processor's load are low compare to other 2
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply