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.googlegroups.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.c...ate_indexes.asp
http://www.sql-server-performance.c..._statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.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
Showing posts with label scan. Show all posts
Showing posts with label scan. Show all posts
Wednesday, March 7, 2012
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.googlegroups.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.com/ma_finding_duplicate_indexes.asp
http://www.sql-server-performance.com/nb_execution_plan_statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.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:
> > 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
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.googlegroups.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.com/ma_finding_duplicate_indexes.asp
http://www.sql-server-performance.com/nb_execution_plan_statistics.asp
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159865591.049181.67820@.e3g2000cwe.googlegroups.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:
> > 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
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
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
Friday, February 24, 2012
Indexes :-
hi,
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
--
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
--
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
Indexes :-
hi,
one small ques on indexes, i added one composite index on table to avoud clu
s index scan(cost 70%) , when i checked executioin plan it is showing me lik
e clus index seek using the above defined index + bookmark lookup operator a
lso..
so which soln should i go for clus index scan or bookmark operator using clu
s index seek (for composit index0
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
one small ques on indexes, i added one composite index on table to avoud clu
s index scan(cost 70%) , when i checked executioin plan it is showing me lik
e clus index seek using the above defined index + bookmark lookup operator a
lso..
so which soln should i go for clus index scan or bookmark operator using clu
s index seek (for composit index0
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
Indexes :-
hi,
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
Sanju
Run the query both ways in Query Analyzer and look at the Total Plan cost
.... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
Sanju
Run the query both ways in Query Analyzer and look at the Total Plan cost
.... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju
Subscribe to:
Posts (Atom)