Read Gather Streams and other parallel exchanges, inspect rows per thread and spill evidence, and relate exchange waits to actual work.
MAXDOP Query Hint: Override the Server Setting for One Query
The MAXDOP query hint sets how many processors one query can use, whatever the server setting says. This post proves it in both directions on a test table, shows that the hint can’t force a parallel plan and adds the database scoped setting between the two. Every step leaves the server setting alone.
NonParallelPlanReason: Why a Query Refuses to Go Parallel
Read NonParallelPlanReason in actual and cached plans, then check MAXDOP, scalar functions, table variables, and query cost.
SQL SERVER – Active Parallel Requests and Cached Parallel Query History
I Identify parallel running requests separately from parallel queries recorded in the cache. Those lists answer different performance questions.
Do Stream Aggregate Operator Always Need Sort Operator? – Interview Question of the Week #240
Question: Do Stream Aggregate Operator Always Need Sort Operator?
Answer: Absolutely No.Before I explain this question a bit more in detail.
SQL SERVER – Is Stream Aggregate is Same as Gather Streams of Parallelism?
Is Stream Aggregate is Same as Gather Streams of Parallelism? – This was one of the brilliant questions I had received during my recent engagement.
ENABLE_PARALLEL_PLAN_PREFERENCE Limits: Rows Per Thread
The ENABLE_PARALLEL_PLAN_PREFERENCE limits are easy to miss: the hint can’t make every query parallel, and it can’t spread rows evenly over the threads. This post proves three limits from the last actual plan of demo queries, with the rows each thread processed and the reason a plan stays serial.