1> How is the data stored physically when there is now primary key as well as any index defined in the table.....?
2> How is the data stored physically when there is just a primary key defined in one of the column of the table? No INDEX defined.
Thanks,
Rahul JhaHi Rahul
As per Tom's answer in the last thread again you need to read up on this subject. You can't get an understanding of indexes by asking questions like this on a forum - or at least not without asking hundreds of questions.
Read up on this and you'll learn that indexes <> primary keys but SQL Server (and most RDMSs) enforce primary keys through unique, non-null indexes. As such, senario 2 is impossible. Also - a primary key does not determine the physical characteristics of a table. The nature of (or lack of) the indexes does.
Showing posts with label defined. Show all posts
Showing posts with label defined. Show all posts
Monday, March 12, 2012
Indexing
Hi,
Could someone please tell me the "best" way to index a table? Is the best
way (defined as quickest access to data) to create an index on every field
individually that might be queried, or is it best to create an index that
contains multiple fields if that's how they appear after the WHERE clause.
Thanks,
SteveHi Steve
Check out
http://www.sql-server-performance.com/mr_indexing.asp
http://www.sql-server-performance.c...ance_audit7.asp
http://www.sql-server-performance.c..._structures.asp
What the article doesn't specify is that it is always worth running the
index tuning wizard just to see what it comes up with, in case you may have
missed something or alternatively as a starting point. I would use this for
analysis and implement the suggestions you believe to be suitable rather tha
n
implement them verbatim.
John
"Steve Caliendo" wrote:
> Hi,
> Could someone please tell me the "best" way to index a table? Is the best
> way (defined as quickest access to data) to create an index on every field
> individually that might be queried, or is it best to create an index that
> contains multiple fields if that's how they appear after the WHERE clause.
> Thanks,
> Steve
>
>
Could someone please tell me the "best" way to index a table? Is the best
way (defined as quickest access to data) to create an index on every field
individually that might be queried, or is it best to create an index that
contains multiple fields if that's how they appear after the WHERE clause.
Thanks,
SteveHi Steve
Check out
http://www.sql-server-performance.com/mr_indexing.asp
http://www.sql-server-performance.c...ance_audit7.asp
http://www.sql-server-performance.c..._structures.asp
What the article doesn't specify is that it is always worth running the
index tuning wizard just to see what it comes up with, in case you may have
missed something or alternatively as a starting point. I would use this for
analysis and implement the suggestions you believe to be suitable rather tha
n
implement them verbatim.
John
"Steve Caliendo" wrote:
> Hi,
> Could someone please tell me the "best" way to index a table? Is the best
> way (defined as quickest access to data) to create an index on every field
> individually that might be queried, or is it best to create an index that
> contains multiple fields if that's how they appear after the WHERE clause.
> Thanks,
> Steve
>
>
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.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
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
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
Subscribe to:
Posts (Atom)