understanding my execution plan

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

understanding my execution plan

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-07-03 : 06:22:51

select count(*)
from #table1 t1 (NOLOCK) inner join table2 t2 on t1.col1=t2.col1 and t2.status='active'

The table1 is temporary table having 5991 records.

And the table2 is perminent table having nearly 12,41,177 records

And from the table t2 only 9,86573 records satisfy the condition t2.status='ACTIVE'

if i join table1 and table2 based on t1.col1=t2.col1 and with additiional condition on t2(t2.status='ACTIVE')
i am getting 6209 records

so far very fine if i take a look into estimated execution plan for the above query

This is breif repragentation of execution plan

table1 (table scan )cost 0% estimated row count is 5991--it's looking fine

Table2 (clustered index scan)(cost 100%) estimated row count is 1--it's also looking fine

after that nested loop join is there in my execution plan "to join both tables"

Each and every row from table1(inner table) will compare with table2 based on the join condition and additional condition mentioned in the where clause and return the result this is work nested loop join will do

but the number of rows from nested loop join to stream aggregator operator is 6748 in execution plan , but i am expecting 6209 why this difference?

why nested loop join operator sending more number of rows to next operator in execution plan (stream aggregator)?

Thanks
Krishna.M

M.MURALI kRISHNA

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-07-03 : 08:51:24

Are the statistics up to date?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-07-05 : 06:34:21

yes JACKV statictics are uo to date,is there any other reason for this?

M.MURALI kRISHNA

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-05 : 06:49:07

Estimated is the clue.
Try the actual plan.

Try creating an index on Active including col1 in table2 - also try col1 including Active.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources