November 21, 2011 at 2:45 pm
Hello!
I hope you can help me answer this question because I just cannot find the answer anywhere.
I'm trying to troubleshoot a slow running query. I'm looking at the execution plan and there are 2 Nested Loops (Inner Join) that are questionable. The actual number of rows of the outer table is higher that the number of executions of the inner table. For instance, in one of the Nested Loops joins -- the actual number of rows of the outer table is 721 but the number of executions of the inner table is only 699. I cannot figure out what happened to 22 rows/executions in the inner table!
Is there a way in SQL to capture and see the data itself alongside with the execution plan? This way I could compare the record sets -- mine vs optimizer's!
Thank you in advance!
Denis
November 21, 2011 at 2:54 pm
Post the plan?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2011 at 3:20 pm
This is a part of the plan where you can see the issue I'm describing. Hope it helps!
Denis
November 21, 2011 at 3:55 pm
The entire plan please. I can't load that into SSMS and I'm not overly fond of reading the XML raw.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2011 at 7:05 am
Sorry, I cannot do it per company's policy. I was hoping you'd know instances when the Nested Loop join behaves this way but anyhow, thank you for your reply.
Denis
November 22, 2011 at 8:41 am
There's probably a good reason, but I just don't have time to sit and decode the raw XML, plus I'd need to see the operators before and after as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2011 at 8:46 am
What's so secret about your plan that we could steal your company and force you to close the doors.
It's possible, but I've yet to see anything of that value ever contained in an exec plan.
November 22, 2011 at 10:26 am
Nothing secret, it's just I'm not the owner of the company! They have a policy, I follow 🙂 My boss gave me the green light to post and the plan is attached.
Node ID 19 returns 721 rows but Node ID 21 is executed only 669 times! The Nested Loop (Node ID 15) returns 8168 rows but Node ID 22 is executed only 7030 times.
Should you have any question, please ask.
Denis
November 22, 2011 at 10:54 am
The inner table of a nested loop join can return more than one row per execution (and can return no rows as well). So if rows > executions, some of the executions returned more rows than others
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2011 at 10:57 am
p.s. If you're looking to optimise this, it looks like you're missing a simple index - EnhancedBusinesses Key column: BusinessStatus, include columns BusinessId, UserGroupId, SuppressFRISK2, FriskScore
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2011 at 12:24 pm
Thank you for taking time to look at the execution plan! I understand that but where did 22 executions go? They were not qualified!? "... in the Nested Loops Join algorithm, the operator for the outer input will be executed once, and the operator for the inner input will be executed once for every row that qualifies on the outer input." -- Benjamin Nevarez "Inside the SQL Server Query Optimizer"
For example, Node ID 19 returns 721 rows but Node ID 21 is executed only 669 times -- according to the Nested Loops join definition, should've been executed 721 times! NO?
Is there a way in SQL to catch the actual record set alongside with the execution plan?
Denis
PS: I saw the missing index, no optimization needed in this case.
November 22, 2011 at 2:42 pm
DenisT (11/22/2011)
For example, Node ID 19 returns 721 rows but Node ID 21 is executed only 669 times -- according to the Nested Loops join definition, should've been executed 721 times! NO?
Bear in mind that you have parallelism as well, so that may to change the game a little. Force the query to serial and see if there's still a mismatch.
Is there a way in SQL to catch the actual record set alongside with the execution plan?
Err, the output of the query? That's the only recordset around.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2011 at 3:18 pm
I just forced the query to serial with OPTION (MAXDOP 1), compared the execution plans -- the numbers are exactly the same in the serial and parallel plans! It would be nice to see the record set at each node of the execution plan if it were possible.
Also when I'm trying to follow the steps in the execution plan by writing a query, I get 721 rows (Businesses) when I INNER JOIN the BusinessRoles table the numbers don't match. I'll continue playing around with this!
Denis
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply