Sql-server – High context switches on SQL Server box

Upping the number of workers won't affect your deadlock scenario at all if it is related to parallelism as in Bart Duncan's blog post. If it is truly a parallel deadlock, your quick fix is to OPTION(MAXDOP n) the offending query while you are working on tuning it, and restrict it back to the point where the deadlocking ceases. You may not need to go back to DOP 1 necessarily, I've seen DOP 4 fix it before.

Another thing to look at is if hyperthreading is enabled on the server, disabling it. The SQLOS creates a user scheduler for each logical CPU available to SQL Server. With hyperthreading, you get 8 logical CPU's which means that you have 8 user schedulers. Your query may be running at DOP 8 when you really have 4 CPU's which could be leading to your problem. You can tell if this is part of the problem by counting the number of process nodes in the deadlock XML graph. If you have 8 process nodes then you should try disabling hyperthreading on the server and see if that solves the problem.

As you are running SQL 2005 you can take your SQL Profiler data and compare it against the Perfmon data to see if you can see a correlation. This is done by saving your trace data and perfmon data to files using the normal techniques. Then open the SQL Profiler trace within profiler, and then one of the options in the file menu will be Import Performance Data. This will let you select a query and see what the counters were doing at that time (or close to it depending on your perfmon collection interval).

Disk queue spikes are never good. Especially that high. What's the IO that you are pushing to the disk when the queue gets that high? Basically you don't want a disk queue any higher than (2*n) where n in the number of disks in the array. Since you are using a 2 disk RAID 1 n=1 in your case (since you only get the speed of a single disk).

In perfmon there's a counter which is the seconds per read and seconds per write. What do these counters look like when the queries start taking a long time to run. What about normally? (Anything over .02 seconds is bad.) What's the estimated page life expectancy? (Anything under 300 seconds is usually bad, but this can vary.) What's the SQL Server cache hit ratio? (Anything below ~97% is usually bad. I like mine above 99.9%.)