How Many Threads Can One Query Use In SQL Server?
In this video, I delve into the intricacies of parallelism and thread usage in SQL Server queries, specifically addressing a common misconception about how many threads a query can use. I demonstrate that while the degree of parallelism (DOP) controls the number of CPUs active at any given time plus one coordinator thread, a query can actually reserve more threads than its DOP suggests. By creating a query with 315 joins and forcing it to run in merge join mode, I show how SQL Server can request up to 704 worker threads for a single query, exhausting the thread pool entirely. This video is not just about the technical details but also includes some lighthearted moments, like accidentally stabbing myself with a wine opener (which explains why my hand gestures might be a bit off). I hope this deep dive into SQL Server internals helps clarify how queries handle threads and parallel execution, making your database management more efficient.
CHAPTERS
00:00:00 - Introduction
00:00:46 - Incorrect Mental Model of Parallelism
00:01:50 - Number of Threads and Query Execution
00:03:07 - Membership Link in Description
00:04:09 - Upcoming Events
00:05:30 - Opening the Query Plan
00:06:26 - Query Hints and Optimizer Errors
00:08:04 - Running sppressure Detector
00:08:42 - Worker Thread Exhaustion
00:10:09 - DOP and Parallel Workers
00:13:07 - Perfection of the Triangle
00:14:24 - Summary of the Demo
00:16:12 - Visible Dop and Threads
00:17:04 - Conclusion
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-...
SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-...
Learn T-SQL with Erik
https://training.erikdarling.com/lear...
Everything Bundle:
https://training.erikdarling.com/?cou...
━━━━━━━━━━━━━━━━━━━━━━━━━━
🛠️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlc...
━━━━━━━━━━━━━━━━━━━━━━━━━━
💬 CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/
Become a channel member
/ @erikdarlingdata
━━━━━━━━━━━━━━━━━━━━━━━━━━