Indexes ineffective in MSSQL Server 2000

Indexes defined on my tables seems to be ineffective.
Eventhough execution plans shows a Index Seek or Index Scan it takes up
the same time as the query would take when it shows a Table scan in
Execution plan.
This behaviour seems to occur when the number of rows returned by the
query are > than 1/10 th of the total no of rows in the table.
If the number of rows is less than 1/10th of the total rows, the time
difference between IndexSeek and Table Scan seems to be alright.
Is this behaviour (To take up same time for Index Seek and Table Scan
when number of rows returned are more) shown by SQL server expected?
Also the Recompute statistics on my indexes are on still i would like
to know how i can recompute statistics of individual indexes defined on
the table?
Thanks
Vishal.
can you run update statistics tablename and see if this solves the problem.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegrou ps.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>
|||Hi
In additon, please take a look at
http://www.sql-server-performance.co...te_indexes.asp
http://www.sql-server-performance.co...statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegrou ps.com...
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
> Also the Recompute statistics on my indexes are on still i would like
> to know how i can recompute statistics of individual indexes defined on
> the table?
> Thanks
> Vishal.
>
|||On 03.10.2006 10:53, Vishal wrote:
> Indexes defined on my tables seems to be ineffective.
> Eventhough execution plans shows a Index Seek or Index Scan it takes up
> the same time as the query would take when it shows a Table scan in
> Execution plan.
> This behaviour seems to occur when the number of rows returned by the
> query are > than 1/10 th of the total no of rows in the table.
> If the number of rows is less than 1/10th of the total rows, the time
> difference between IndexSeek and Table Scan seems to be alright.
> Is this behaviour (To take up same time for Index Seek and Table Scan
> when number of rows returned are more) shown by SQL server expected?
If you pull more than a certain percentage of rows from a table a table
scan is more efficient. The amount of that percentage differs depending
on DB product, schema, query etc. So, yes it is expected and typically
optimizers take this into account when creating the execution plan.
Kind regards
robert
|||Thanx for this info Robert...
Robert Klemme wrote:
> On 03.10.2006 10:53, Vishal wrote:
> If you pull more than a certain percentage of rows from a table a table
> scan is more efficient. The amount of that percentage differs depending
> on DB product, schema, query etc. So, yes it is expected and typically
> optimizers take this into account when creating the execution plan.
> Kind regards
> robert