Pb table scan

I have a query like this :
select * from table
where champs1=val1 and champs2 =val2
I have an index on (champs1,champs2).
The table has 698694 rows.
The table scan cost 17770 i/os.
If the number of rows selected is greater than 4700, the optimizer choice
the table scan. If i force the index, i have 4762 i/os ( is less than
17770 )
I don't know why the optimizer choice the table scan ( is there a traceon,
command like the dbcc traceon(302) in sybase, that help you to understand
the choice of the optimizer ? ).
Thanks and sorry for my poor english.Have you tried clustering your index?
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||Execute this from Query Analyzer and look at the query plan. It will show
you the details... Then force the index you think is best and execute again,
compare the two.
You might try to update statistics on the table,
or drop /recreate the indices on the table and try again.
Generally however if a query returns more than 3-10% of the rows in the
table, the optimizer will choose a table scan.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||One possible reason is outdated statistics. Did you try UPDATE STATISTICS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message news:1084801423.162182@.atn04...
> I have a query like this :
> select * from table
> where champs1=val1 and champs2 =val2
> I have an index on (champs1,champs2).
> The table has 698694 rows.
> The table scan cost 17770 i/os.
> If the number of rows selected is greater than 4700, the optimizer choice
> the table scan. If i force the index, i have 4762 i/os ( is less than
> 17770 )
> I don't know why the optimizer choice the table scan ( is there a traceon,
> command like the dbcc traceon(302) in sybase, that help you to understand
> the choice of the optimizer ? ).
> Thanks and sorry for my poor english.
>
>
>|||there is a primary key but not on this rows
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a écrit dans le
message de news:OIe9eACPEHA.3020@.tk2msftngp13.phx.gbl...
> Have you tried clustering your index?
> "ljbx" <pierre.lejoubioux@.nospam_atosorigin.com> wrote in message
> news:1084801423.162182@.atn04...
> > I have a query like this :
> >
> > select * from table
> > where champs1=val1 and champs2 =val2
> > I have an index on (champs1,champs2).
> > The table has 698694 rows.
> > The table scan cost 17770 i/os.
> >
> > If the number of rows selected is greater than 4700, the optimizer
choice
> > the table scan. If i force the index, i have 4762 i/os ( is less than
> > 17770 )
> >
> > I don't know why the optimizer choice the table scan ( is there a
traceon,
> > command like the dbcc traceon(302) in sybase, that help you to
understand
> > the choice of the optimizer ? ).
> >
> > Thanks and sorry for my poor english.
> >
> >
> >
> >
> >
> >
>