Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
john.burns
Posting Yak Master
100 Posts |
 Posted - 2006-10-04 : 14:08:01
|
| Does anyone know how to lower context switching?I have Win2k3 w/8 processors16 gb of ramI following numerous performance counters on a regular basis and context switching and sql recompliations come up high consistently.Believe the high sql recompliations is due to dynamic sql.But context switching I'm not sure ... I've heard to limit sqlto lower four processors??any thoughts appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-04 : 14:20:31
|
| No you should not limit SQL Server to 4 CPUs or lower. SQL Server performs better as you add more CPUs and more memory.Context switching should only be changed if you have hardware bottlenecks. Do you have hardware bottlenecks?Tara Kizer |
 |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-10-04 : 14:22:48
|
| Out of curiosity, how high are the context switches? |
|
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-10-04 : 14:28:05
|
| do not currently have hardware/io bottleneckscontext switching at 12,000 /secthanks |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-10-04 : 14:29:02
|
| How high is CPU %Privileged Time/sec _Total? |
|
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-10-04 : 14:30:13
|
| % proc time is at 65%I'm getting priv. time |
|
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-10-04 : 14:31:40
|
| priv. time between 4 and 12 |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-10-04 : 15:09:29
|
| The main problem with Context switching is all the "bookkeeping" the CPUs have to do to keep them all straight. This is measured in Privileged or Kernel time. With Privileged time not much more than 12%, the context switching is probably not a major problem. I run 6 - 7,000 on my 4 way boxes, so twice that for twice the number of CPUs sounds about right. If I were you, I would see if I could knock the high compilations down, as those operations are almost entirely CPU dependent. If you need "low hanging fruit", you could run the index tuning wizard, and see what it says. There may be a table scan out there that you can provide an index for. |
|
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-10-04 : 15:50:50
|
| thanks much |
|
|
|
|
|
|
Context switching