April 1, 2011 at 6:51 am
I was troubleshooting an issue in production and stumbled upon an issue I have not seen before. I found a view that is joining 6 tables to a base table (1 inner join and 5 outer joins). The last outer join is causing a "No Join Predicate" warning in the "Nested Loop", yet there is obviously a join predicate in the code. Here's an example of the FROM clause in the view (table names and field names have been replaced)
FROM
dbo.TableS s
INNER JOIN dbo.TableC c ON s.SID = c.SID
LEFT OUTER JOIN DMDB.dbo.vw_1 u1 ON u1.UID = s.DCBUID
LEFT OUTER JOIN DMDB.dbo.vw_2 u2 ON u2.UID = s.FBUID
LEFT OUTER JOIN dbo.TableSM sm ON sm.LSID = s.SID
LEFT OUTER JOIN DMDB.dbo.TableDIPs dips ON dips.DID = sm.RDID
LEFT OUTER JOIN dbo.TableCMI cmi ON cmi.DID = s.SD
The last line
LEFT OUTER JOIN dbo.TableCMI cmi ON cmi.DID = s.SD
is the issue.
After adding an index to fix a scan on TableCMI and still seeing the same issue, I did some reading online and found that this case is often caused by using functions inside of views. None of the views being referenced are complicated or contain functions and the FROM clause above is pretty straight forward.
Just on a whim I moved the last line to the third line like this...
FROM
dbo.TableS s
INNER JOIN dbo.TableC c ON s.SID = c.SID
LEFT OUTER JOIN dbo.TableCMI cmi ON cmi.DID = s.SD
LEFT OUTER JOIN DMDB.dbo.vw_1 u1 ON u1.UID = s.DCBUID
LEFT OUTER JOIN DMDB.dbo.vw_2 u2 ON u2.UID = s.FBUID
LEFT OUTER JOIN dbo.TableSM sm ON sm.LSID = s.SID
LEFT OUTER JOIN DMDB.dbo.TableDIPs dips ON dips.DID = sm.RDID
And that fixed the issue. The query plan actually changed and there was no longer a warning on the "Nested Loop" operation saying there was "No Join Predicate"
I'm baffled. Can anyone explain this to me?
Thanks!
April 1, 2011 at 8:03 am
On a guess, the optimizer timed out trying to unpack the views and the "best" plan at the time of the timeout was missing the predicate because the optimizer hadn't been able to get to it yet.
Take a look at the SELECT operator properties. It will tell you if the optimizer stopped because it found the best plan or because it timed out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2011 at 8:26 am
Grant Fritchey (4/1/2011)
On a guess, the optimizer timed out trying to unpack the views and the "best" plan at the time of the timeout was missing the predicate because the optimizer hadn't been able to get to it yet.Take a look at the SELECT operator properties. It will tell you if the optimizer stopped because it found the best plan or because it timed out.
Which Grant posted about HERE [/url]....
Unfortunately the links to the pictures have gone bad.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 1, 2011 at 8:56 am
David Benoit (4/1/2011)
Grant Fritchey (4/1/2011)
On a guess, the optimizer timed out trying to unpack the views and the "best" plan at the time of the timeout was missing the predicate because the optimizer hadn't been able to get to it yet.Take a look at the SELECT operator properties. It will tell you if the optimizer stopped because it found the best plan or because it timed out.
Which Grant posted about HERE [/url]....
Unfortunately the links to the pictures have gone bad.
Crap. I moved the blog twice & it really messed things up. I'll go fix that post now. Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2011 at 9:11 am
Fixed it. Sorry about that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2011 at 9:13 am
Grant Fritchey (4/1/2011)
Fixed it. Sorry about that.
No worries here. That is a great post to cover the topic though. Thanks for fixing it. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 1, 2011 at 9:15 am
That's awesome, thanks! I'm going to look this over and let you know what I find.
April 4, 2011 at 7:03 am
I read through the article and tried checking the properties of the SELECT statement, however I do not see the Reason for Early Termination property. I'm running SQL Server 2005 Standard Edition. Is this only for SQL Server 2008?
April 4, 2011 at 7:11 am
JohnnyDBA (4/4/2011)
I read through the article and tried checking the properties of the SELECT statement, however I do not see the Reason for Early Termination property. I'm running SQL Server 2005 Standard Edition. Is this only for SQL Server 2008?
No it works in 2k5 too.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply