Sample Header Ad - 728x90

Gather Streams plan operator bad performance

0 votes
1 answer
89 views
How to explain and troubleshoot the "final" Gather Streams query plan operator, which takes seconds, while the previous operators make it within one second? These are the left-most plan operators. I can't share the full plan for security reasons (sorry about that) enter image description here The plan metrics I follow don't look suspicious to me: - Threads are pretty evenly distributed - No prevalent wait type (as far as the plan info goes) - Duration captured via Extended Event fits the Duration inside the plan stats enter image description here Is it that the gathering itself is so time-consuming on that number of rows ("just" 100k+)? **Additional info:** Here is the obfuscated query plan SQL Sentry Plan Explorer gives a different view of the problematic operator with Costs by CPU, so this could be a wrong interpretation of numbers (which numbers(?)) by SSMS. enter image description here **Additional info #2:** The obfuscated query plan I provide comes from the *query_post_execution_showplan* XE. The query typically (in most executions) runs for about 1 second. Only some executions go beyond >5 seconds (my XE is filtered for Duration > 5s).
Asked by jericzech (977 rep)
Jun 17, 2025, 11:40 AM
Last activity: Jun 20, 2025, 07:06 PM