sqldba_newbie (1/29/2013)
GilaMonster (1/29/2013)
If you want all queries to only use one processor, why not pull the other 23 out? That's essentially what you've done by setting maxdop to 1. It is NOT a good thing to do.If there are 10 processes running wouldnt they use 10 different core's if MAXDOP is set to 1?
Probably, but you're still throttling your server, especially a data warehouse-type server that will typically be running smaller numbers of large queries.
I do see heavy waits on PAGEIOLATCH_SH. Do you have query to find total wait on waitype PAGEIOLATCH_SH for a particular spid?
sys.dm_os_waiting_tasks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability