Parallelism seems like it should be one of those things that the query engine takes care of for us, but it does require some care and tending from time to time. The default server configurations are no longer adequate for controlling parallelism, and DBAs need to know how to tweak the settings to make it run optimally. When parallelism goes awry, it can cause severe performance problems. Sometimes only for a specific query, sometimes it can hurt performance server-wide. To add a little complexity to the mix, parallelism can also be controlled by query hints and Resource Governor. Understanding how these three options for setting the Max Degree of Parallelism interact and who wins when they conflict is critical in being able to understand how to control it.
Robert is a SQL Server Certified Master, MVP, and has spent 17+ years honing his skills in security, performance tuning, SQL development, high availability, and disaster recovery. He served as PM for the SQL Server Certified Master Program at Microsoft Learning, and in various roles at Microsoft specializing in SQL Server administration, development, and architecture. He currently works as a Database Engineer at BlueMountain Capital Management where he spends a vast majority of his time tuning massively parallel queries. Robert feeds his passion for security by acting as co-leader of the PASS Security Virtual Chapter.