September 2, 2009 at 12:15 pm
Nested loop join does not work if the outer table is a heap? I am not sure why..
when I put a clustered index on the outer table(base table) then the join works as I would expect, it does a table scan on the outer table followed by an index seek on the inner table, which is good 30 rows on the outer and 30 rows on the inner, PERFECT, no complaints
now I am trying to understand why when the outer table is heap why it does a index scan on the inner table instead of an index seek.
I am guessing that the the outer table needs to be sorted(with a clustered index) for an index seek to occur(on the innner table) but I don't know why?
thanks
September 2, 2009 at 12:45 pm
Is it possible it doesn't have stats on the heap? That might make a scan less expensive than a set of seeks, if it has to generate stats on-the-fly. Creating a clustered index automatically creates stats, so that might make a difference.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 1:12 pm
Doubled checked that...but no luck although the table is a heap the it has a PK and a (unique) non clustered index on the PK...my only thought is the sort is so expenses the optimizer decides that a hash is more efficient. There is 17,000 rows on the base table and is finding 30 rows. seems like it just needs to eithere create an index on the fly or sort it then the index scan on the inner table would turn into a seek?
I am just guessing
September 2, 2009 at 1:12 pm
Can you post the two execution plans? Might be able to help more if I see them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 1:21 pm
Can you also post the table definitions and index definitions?
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
September 2, 2009 at 1:55 pm
I found the problem...thanks folks
As I was getting ready to post the table info I noticed the both the inner and outer tables are both heaps. Both have unique non-cluster PK.
It seems that the outer table may get away with a help but not the inner and having both means one or both of the tables need to be put into order so the hash join is a must!
****
one more ? though, this query is my highest CPU usage qry according to my sys.dm_exec_query_stats....
****
I fixed the CPU usage from 63 ms to 0 ms but by doing so I increased the time of the qry 60 ms to 93ms -- the time is still short I know but this qry is used VERY OFTEN.
I solved one problem but may have created a new one.
My SERVER CPU is very low average around 20% not so sure I should fix this?
THANKS AGAIN FOR EVERYONE HELP
before tuning here are my stats-----
------------------------------------
Table 'Accounts'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Accounts_Users'. Scan count 1, logical reads 1392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'IPAccess'. Scan count 1, logical reads 1996, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(8 row(s) affected)
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 60 ms.
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 61 ms.
---------------------------------------
after tuning here are my stats
(1 row(s) affected)
Table 'Accounts'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Accounts_Users'. Scan count 28, logical reads 114, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'IPAccess'. Scan count 1, logical reads 1965, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(6 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 92 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 93 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
September 2, 2009 at 2:08 pm
I'd still need to see at least the execution plans to be able to say much about this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 2:11 pm
Please read through this - http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
September 2, 2009 at 2:17 pm
here are both plans..hope it helps
September 2, 2009 at 2:19 pm
by the way good article Gail ....thanks
September 4, 2009 at 1:34 pm
Unfortunately, and unintuitively, you have to save each section of the plan. All those files have is the initial variable assignment.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply