Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

System\Context Switches/Sec on windows server 2008 r2 with ms sql server

Posted on 2014-03-06
5
1,866 Views
Last Modified: 2014-03-28
Hello,
   Server:
Windows Server 2008 R2 with MS SQL server 2008 R2 installed on it. 16GB RAM and 8 Cores. Machine is virtual and is running on VMware ESXi 5.5 version.

  Problem:
Our monitoring software send us alerts saying:
"System\Context Switches/Sec 10677 is over threshold value 8000"

  Question:
What we can check to find who is causing this? What settings we could change? Or is normal thing and we should just ignore it.  

Thanks,
   Tadas
0
Comment
Question by:thinkbot
  • 3
5 Comments
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39909740
If you're specifically looking hunting for things in SQL Server, you can use:

select 
	* 
from 
	sys.dm_os_tasks as OT
		left join sys.dm_exec_sessions as ES on OT.session_id = ES.session_id
		inner join sys.dm_exec_requests as ER on OT.session_id = ER.session_id
		outer apply fn_get_sql( ER.sql_handle ) as SQL
order by
	context_switches_count desc;

Open in new window


That's going to give you a lot of columns back for results, you can pick and choose what you want, but it will attempt to give you the text of the executing query that has large amount of context switches on each session thread.  Context switches are a tricky little thing, however.  What they are, is when the kernel switches the processor from one thread to another.  This is going to occur.  I would usually combine context switching with other metrics (ie. CPU Utilization, etc).  Is this alarm coming from the sql server or the esx host ?

Also, take a look at the context switching category of this fellow: http://www.sqlserverfaq.net/2011/01/31/what-perfmon-counters-should-i-monitor-and-what-each-of-them-mean/
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39910937
Is your CPU on that VM constantly high? then I would look deeper into that but if it's not then just raise the threshold in your monitoring tool.

"Understanding Context Switching"
http://technet.microsoft.com/en-us/library/ms191296(v=sql.105).aspx


"Performance impact: driving up context switches/sec"
http://sqlblog.com/blogs/linchi_shea/archive/2012/01/12/performance-impact-driving-up-context-switches-sec.aspx
0
 

Author Comment

by:thinkbot
ID: 39919884
Padawan, thank you for suggestion, will give it a try, although the same happens on SQL server which is not in use (please see bellow).

Notifications/alerts are coming from a separate monitoring system (NetCrunch). The thing is that we are observing this on all SQL servers that we have (i.e. higher values of context switching). Even on the server which is a test server and doesn't have any databases which would be in use - it still generates quite high values of context switching.

Icohan, thank you for the links. CPU load is averaging at about 10% on test SQL server VM. Context Switch delta (as observed via Process Explorer) is highest on two processes: Interupts (2000 - 3000) and System Idle Process (2000 - 10000).
0
 

Accepted Solution

by:
thinkbot earned 0 total points
ID: 39919902
Padawan, I have tried your script on test SQL server (there are few (4-5) databases on it which belong to some systems that we evaluate/test, like OperationsManager, etc., but these systems are not configured to be in use).

Findings:
Highest "context_switches_count" goes to a task (which has a RUNNING "task_state", although "status" is sleeping) with a "command" RESOURCE MONITOR (last_wait_type is PREEMPTIVE_XE_CALLBACKEXECUTE). Is this some kind of SQL system task? (If possible to determine from the information I have provided)

Thanks
0
 

Author Closing Comment

by:thinkbot
ID: 39961057
Because there was no good answer for me.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question