Loading...

SQL Server Performance Tuning - Controlling Parallelism

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 DBA’s 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.

 

In this session Robert will cover:

  • Best Practices for setting Max Degree of Parallelism and Cost Threshold for Parallelism
  • Why we would want to adjust these numbers up or down
  • How to view the parallelism details of a query plan
  • What CXPacket waits are and what we should do about them
  • How to determine effective degree of parallelism
  • Methods for controlling parallelism

 

About the Speaker

Robert L Davis is Principal Database Architect at DB Best Technologies and is a SQL Server Certified Master, MVP, and experienced DBA, evangelist, speaker, writer, and trainer. He has worked with SQL Server for 15+ years. He recently worked for Outerwall, Idera Software, and Microsoft. He served as PM for the SQL Server Certified Master Program at Microsoft Learning, and as a production DBA at Microsoft and Outerwall. Robert is co-founder and leader of the PASS Security Virtual Chapter.

 

Robert blogs at http://www.sqlsoldier.com/ and can be found on Twitter as @SQLSoldier.

 

The Session slide deck and demo scripts are available here ...

 

Scroll to Top