Another execution plan question

SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
The total number of rows in Table1 are just 100. But the estimated row count
shows 75,000.
Its a production environment and probably cannot drop any procedure caches .
How can I make the estimated row count to show me the actual count or
atleast drop it to within the 100s range. Table1 has only 1 clustered index
on (col2,col3) . IF i have to use hints, what hints can i use and if not,
what else can i do to reflect the actual count"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> The total number of rows in Table1 are just 100. But the estimated row
count
> shows 75,000.
> Its a production environment and probably cannot drop any procedure caches
.
> How can I make the estimated row count to show me the actual count or
> atleast drop it to within the 100s range. Table1 has only 1 clustered
index
> on (col2,col3) . IF i have to use hints, what hints can i use and if not,
> what else can i do to reflect the actual count
How many rows are there in table 2? If there is a one to many relationship
then a single row in table 1 will be counted for every row in table 2 it is
paired with.
If you only want a row to appear once from table 1 then change the query to
SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003|||You didnt follow my question Bob..
When i talk about estimated row count, I mean when i click on the operators
in the execution plan under the clustered index scan of table1 which only
has 100 rows but the estimated row count shown there is around 75000.
Probably at one point in time it was that much and hence the adhoc query
plan is reflecting that. I would like to change it to show me atleast in a
100s range...
"Bob Simms" <bob_simms@.hotmail.com> wrote in message
news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > t1.col3=t2.col3
> >
> > The total number of rows in Table1 are just 100. But the estimated row
> count
> > shows 75,000.
> > Its a production environment and probably cannot drop any procedure
caches
> .
> > How can I make the estimated row count to show me the actual count or
> > atleast drop it to within the 100s range. Table1 has only 1 clustered
> index
> > on (col2,col3) . IF i have to use hints, what hints can i use and if
not,
> > what else can i do to reflect the actual count
> How many rows are there in table 2? If there is a one to many
relationship
> then a single row in table 1 will be counted for every row in table 2 it
is
> paired with.
> If you only want a row to appear once from table 1 then change the query
to
> SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2
and
> t1.col3=t2.col3
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
>|||Did you try updating statistics with fullscan. I'm not sure it'll help, but worth a try...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> The total number of rows in Table1 are just 100. But the estimated row count
> shows 75,000.
> Its a production environment and probably cannot drop any procedure caches .
> How can I make the estimated row count to show me the actual count or
> atleast drop it to within the 100s range. Table1 has only 1 clustered index
> on (col2,col3) . IF i have to use hints, what hints can i use and if not,
> what else can i do to reflect the actual count
>|||I did all that... As I was hoping the plan might change...but no luck...
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> Did you try updating statistics with fullscan. I'm not sure it'll help,
but worth a try...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > t1.col3=t2.col3
> >
> > The total number of rows in Table1 are just 100. But the estimated row
count
> > shows 75,000.
> > Its a production environment and probably cannot drop any procedure
caches .
> > How can I make the estimated row count to show me the actual count or
> > atleast drop it to within the 100s range. Table1 has only 1 clustered
index
> > on (col2,col3) . IF i have to use hints, what hints can i use and if
not,
> > what else can i do to reflect the actual count
> >
> >
>|||On Thu, 2 Oct 2003 21:16:28 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
>t1.col3=t2.col3
>The total number of rows in Table1 are just 100. But the estimated row count
>shows 75,000.
>Its a production environment and probably cannot drop any procedure caches .
>How can I make the estimated row count to show me the actual count or
>atleast drop it to within the 100s range. Table1 has only 1 clustered index
>on (col2,col3) . IF i have to use hints, what hints can i use and if not,
>what else can i do to reflect the actual count
I seldom even look at the estimated numbers, and even more seldom am
able to make any sense out of them. I suspect many, many bugs, as
well as unexplained complexities, in the numbers displayed.
When you actually run the query, what kind of statistics do you get?
And, how many rows get returned?
Joshua Stern|||you have indexes on the columns in both t1 and t2 AND you have updated
statistics with FULLSCAN on both tables? And you still are getting widely
off estimated row counts showing in the graphical showplan? I just want to
make sure I understand...
that would be bit wierd to be off SOO much on such simple queries if the
columns are indexed and you did FULLSCAN...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e9cqgmXiDHA.1796@.TK2MSFTNGP10.phx.gbl...
> I did all that... As I was hoping the plan might change...but no luck...
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> > Did you try updating statistics with fullscan. I'm not sure it'll help,
> but worth a try...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > t1.col3=t2.col3
> > >
> > > The total number of rows in Table1 are just 100. But the estimated row
> count
> > > shows 75,000.
> > > Its a production environment and probably cannot drop any procedure
> caches .
> > > How can I make the estimated row count to show me the actual count or
> > > atleast drop it to within the 100s range. Table1 has only 1 clustered
> index
> > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> not,
> > > what else can i do to reflect the actual count
> > >
> > >
> >
> >
>|||On Thu, 2 Oct 2003 21:16:28 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
>t1.col3=t2.col3
>The total number of rows in Table1 are just 100. But the estimated row count
>shows 75,000.
>Its a production environment and probably cannot drop any procedure caches .
>How can I make the estimated row count to show me the actual count or
>atleast drop it to within the 100s range. Table1 has only 1 clustered index
>on (col2,col3) . IF i have to use hints, what hints can i use and if not,
>what else can i do to reflect the actual count
You've got no where clause.
Actually, if it's a clustered index, and the 100 rows remaining are
spread over a space that used to have 75,000 rows, maybe it's telling
you that it has to physically scan the space that would normally take
75k rows? Maybe someone here with more experience with these plans
and clustered indexes can confirm this.
J.|||Dropped all indexes and recreated them to resolve this.. It was weird. The
adhoc query plan was still being cached I guess.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:OBan2MeiDHA.1200@.TK2MSFTNGP09.phx.gbl...
> you have indexes on the columns in both t1 and t2 AND you have updated
> statistics with FULLSCAN on both tables? And you still are getting widely
> off estimated row counts showing in the graphical showplan? I just want to
> make sure I understand...
> that would be bit wierd to be off SOO much on such simple queries if the
> columns are indexed and you did FULLSCAN...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e9cqgmXiDHA.1796@.TK2MSFTNGP10.phx.gbl...
> > I did all that... As I was hoping the plan might change...but no luck...
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> > > Did you try updating statistics with fullscan. I'm not sure it'll
help,
> > but worth a try...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > > t1.col3=t2.col3
> > > >
> > > > The total number of rows in Table1 are just 100. But the estimated
row
> > count
> > > > shows 75,000.
> > > > Its a production environment and probably cannot drop any procedure
> > caches .
> > > > How can I make the estimated row count to show me the actual count
or
> > > > atleast drop it to within the 100s range. Table1 has only 1
clustered
> > index
> > > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> > not,
> > > > what else can i do to reflect the actual count
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Hassan.
I think that Bob is probably on the money - I've carefully re-read your
original post, what Bob's responded with & the other posts too, but I think
Bob most likely right.
Does table2 have 75000 rows? If so, I think that you won't get the estimated
rowcount down below this because, critically, your query is selecting
t2.col1 which is from the right hand side (table2) of the join statement.
This means that the estimated rows will be however many rows are in table2 &
as you have no where clause (either on table1 or table2) there is no way for
the optimizer to short cut a full scan of table2.col1. If table2 has 75000
rows, then the only valid estimate is 75000 rows.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e#IhICXiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> You didnt follow my question Bob..
> When i talk about estimated row count, I mean when i click on the
operators
> in the execution plan under the clustered index scan of table1 which only
> has 100 rows but the estimated row count shown there is around 75000.
> Probably at one point in time it was that much and hence the adhoc query
> plan is reflecting that. I would like to change it to show me atleast in a
> 100s range...
>
> "Bob Simms" <bob_simms@.hotmail.com> wrote in message
> news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > t1.col3=t2.col3
> > >
> > > The total number of rows in Table1 are just 100. But the estimated row
> > count
> > > shows 75,000.
> > > Its a production environment and probably cannot drop any procedure
> caches
> > .
> > > How can I make the estimated row count to show me the actual count or
> > > atleast drop it to within the 100s range. Table1 has only 1 clustered
> > index
> > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> not,
> > > what else can i do to reflect the actual count
> >
> > How many rows are there in table 2? If there is a one to many
> relationship
> > then a single row in table 1 will be counted for every row in table 2 it
> is
> > paired with.
> >
> > If you only want a row to appear once from table 1 then change the query
> to
> > SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2
> and
> > t1.col3=t2.col3
> >
> >
> >
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
> >
> >
>|||Table T2 has 20 millions rows and the estimated row count for that shows
around the same. But T1 has only 100 rows and the estimated row count for T1
showed 75000. I dropped and recreated all indexes and it worked fine now.
Just some plan being cached I guess.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%234MVFakiDHA.2536@.TK2MSFTNGP10.phx.gbl...
> Hi Hassan.
> I think that Bob is probably on the money - I've carefully re-read your
> original post, what Bob's responded with & the other posts too, but I
think
> Bob most likely right.
> Does table2 have 75000 rows? If so, I think that you won't get the
estimated
> rowcount down below this because, critically, your query is selecting
> t2.col1 which is from the right hand side (table2) of the join statement.
> This means that the estimated rows will be however many rows are in table2
&
> as you have no where clause (either on table1 or table2) there is no way
for
> the optimizer to short cut a full scan of table2.col1. If table2 has 75000
> rows, then the only valid estimate is 75000 rows.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e#IhICXiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> > You didnt follow my question Bob..
> >
> > When i talk about estimated row count, I mean when i click on the
> operators
> > in the execution plan under the clustered index scan of table1 which
only
> > has 100 rows but the estimated row count shown there is around 75000.
> > Probably at one point in time it was that much and hence the adhoc query
> > plan is reflecting that. I would like to change it to show me atleast in
a
> > 100s range...
> >
> >
> > "Bob Simms" <bob_simms@.hotmail.com> wrote in message
> > news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > > t1.col3=t2.col3
> > > >
> > > > The total number of rows in Table1 are just 100. But the estimated
row
> > > count
> > > > shows 75,000.
> > > > Its a production environment and probably cannot drop any procedure
> > caches
> > > .
> > > > How can I make the estimated row count to show me the actual count
or
> > > > atleast drop it to within the 100s range. Table1 has only 1
clustered
> > > index
> > > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> > not,
> > > > what else can i do to reflect the actual count
> > >
> > > How many rows are there in table 2? If there is a one to many
> > relationship
> > > then a single row in table 1 will be counted for every row in table 2
it
> > is
> > > paired with.
> > >
> > > If you only want a row to appear once from table 1 then change the
query
> > to
> > > SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on
t1.col2=t2.col2
> > and
> > > t1.col3=t2.col3
> > >
> > >
> > >
> > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
> > >
> > >
> >
> >
>|||On Fri, 3 Oct 2003 17:14:09 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>Dropped all indexes and recreated them to resolve this.. It was weird. The
>adhoc query plan was still being cached I guess.
Did you drop a clustered index on table1?
J.