Parallelism and Query Tuning

 

...Adam Machanic presentation on query parallelism.  Just skip to 148:00 or so.  The beginning is fluff. 

http://pass.bethereglobal.com/demand/day2p1.mp4

other coolness:

177:00            table vars vs temp tables.  I learned some stuff here I did not know (table vars never go parallel, even when INSERTing into them). 

200:00              the "parallel APPLY pattern".  This trick is just f'ing cool. 

I didn't mark the time but he answers a question about what the algorithm should be for serverwide DOP.  He answers it well, but mentions that when it still doesn't work he bumps the cost threshold for parallelism.  He said he sets his default CTP to 40 or 80!!  Wow.