SQL Server Performance Forum – Threads Archive

Sql server context switches/sec

Hi all Here I am doing some analysis in sql server performance monitoring. From development team I got context switches/sec counter maximum values is like 175461
Is there any possibility to get maximum values is 175461 ?. what is the maximum limit for contextswitches counter ?. While I am running, I am getting maximum up to 10,000. please give your suggestion . regards
sultan Sultan Sultan

This occurs when threads are switched between CPUs in your SQL Server, and excessive context switching eats up CPU resources. In some cases, context switching can be reduced by turning on Lightweight Pooling. http://www.sql-server-performance.com/qdpma/inst_3_pmlogs.asp</a> refers <blockquote id="quote">quote:<hr height="1" noshade id="quote"> The System object, Context Switches/sec counter should be logged but is usually not an issue. Context Switches/sec should almost always track with SQL Server<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />QL Statistics -> Batch Requests/sec. The Context Switch rate counter is of concern if it increases sharply without commensurate increase in the Batch Request/sec counter, and when the cause is not traced to other intended activity. There is some Microsoft documentation suggesting the use of fiber mode for high context switching. Do not follow this instruction without first consulting an expert on the matter of fiber mode and very real negative side affects. A competent consultant should advise the importance of resolving the cause of high context switching or other means rather than resorting to fiber mode.<hr height="1" noshade id="quote"></blockquote id="quote"> Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

thanks satya for your immediate reply Is there any possibility to get maximum values is 175461 ?.
My application is running in sql 2005 environment. please reply me the maximum limit for context switchessec Sultan

I think you haven’t gone through the link above, how about other counters such as physical disk, memory etc. looks like. Based on my 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). Are you using or getting these values whenever a report is executed from reporting services? A high number for this counter may indicate either high contention in locks or many switches between user and kernel mode by the thread. Further investigation you need to perform by using PROFILER or server side trace to see what queries are causing this issue. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

i got this maximum values from my testing team. They have tested the application with 12 users and gave the results. weather is it possible for the values 175461 ?. you are also not recommanding for lightweight pooling. Then how can i reduce this context switches/sec. This is for our new release. In my older version the maximum value less than 10000 (sql 2000). But i am getting 175461 in my new version(sql 2005). why the big difference is comming ?. surely sql 2005 will give better performance comparing to sql 2000. please reply Sultan

Yes SQL 2005 is by far better than 2000 to tackle the performance issues, but if such query is not optimized to use better indexes then you will find this problem. Refer tohttp://sqlserver-qa.net/blogs/perft…shoot-slow-running-queries-in-sql-server.aspx to identify the problem. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>