Thursday, March 22, 2012
Bookmark lookup takes time in SQL server 2000
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
--
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:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> > Hi All
> > I have an application with SQL Server 2000 SP4 as my RDBMS.
> > There is a table with around 1200000 records with all the necessary
> > indexes defined when i execute the query through query analyzer the no
> > of rows returned are around 300000, execution plan shows a Bookmark
> > Lookup. But the bookmark lookup taks lot of time?
> > Also if i remove the particular index it shows me a Table Scan in
> > Execution Plan.
> > Can anyone help me explain why Bookmark lookup takes the same time as
> > Table Scan?
> > Thanks & Regards
> > Vishal.
> >
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/article.asp?p=27015&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Bookmark lookup takes time in SQL server 2000
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.
I am not sure if I can but perhaps a covering index would be helpful here.
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:1159864815.076365.227450@.k70g2000cwa.googlegr oups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
Can u tell me more about covering Indexes?
As in :
What do they mean?
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/art...&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Bookmark lookup takes time in SQL server 2000
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
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:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table an
d
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/ar...5&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Bookmark lookup cost factors (SQL Server 7.0)
I am working with some large databases with several tables hosting
millions or tens of millions of records. The actual data is being
stored on a SAN, while the database host is a 4 CPU SMP machine. In
this case the SAN is often a bottleneck while the CPUs are idled.
A situation that I am consistently observing is extremely poor choices
for the uses of indexes -- or rather the lack of index use. What I am
constantly finding for these large tables is that highly unique
non-clustered indexes are being ignored, and instead a table scan is
being performed. The execution engine is (wrongly) assuming that the
bookmark lookups will be more costly than simply doing a tablescan.
The end result is that many queries run 10s or 100s of times longer
than they should without explicitly adding index hints to force it to
use the index.
In one example a table with 5,000,000 or so records has a
non-clustered index on a field, and the statistics believe that there
are around 141 records that match the requested specific criteria
(there are really less -- as a sidenote can one increase the number of
steps stored by statistics?). Instead of quickly pulling the presumed
141 index matches and then doing the corresponding bookmark lookups,
it's instead table scanning all 5,000,000 records. Indeed if I look at
the estimated execution plans, it's claiming that the forced use of
the non-clutered index, and hence forced bookmark lookups, will yield
5x the subtree cost. The end results tell the real story, though -
with the index hint it completes instantly, while without the hint it
takes hundreds of times longer.
Is there a server or database setting that governs the estimation of
the cost of bookmark lookups? I do not wish to resort to unnecessary
hardcoded index hints when it's such a basically incorrect assumption
by the query engine that affects the entire system wherever large
tables are accessed. As a sidenote I had previously heard the
statistic that a non-clustered index will only be used if the
estimated set yields less than 2% of the total set, due to the cost of
bookmark lookups, but in this case it isn't using it even when the
estimated set is ~ 0.003%... how is it choosing?
Thanks
Dennis ForbesHi Dennis,
I think that 285996 PRB: Cost of Using Non-Clustered Indexes May Be Incorrect if Data (
http://support.microsoft.com/?id=285996) is something that may be applicable
to your scenario. Unfortunately there are no 'knobs' that tune the cost es
timation for any operators inside SQL Server.
Thanks,
--R
"Dennis Forbes" <dennis_forbes@.hotmail.com> wrote in message news:32811a52.0
403190902.35fd2ef5@.posting.google.com...
Good day to you.
I am working with some large databases with several tables hosting
millions or tens of millions of records. The actual data is being
stored on a SAN, while the database host is a 4 CPU SMP machine. In
this case the SAN is often a bottleneck while the CPUs are idled.
A situation that I am consistently observing is extremely poor choices
for the uses of indexes -- or rather the lack of index use. What I am
constantly finding for these large tables is that highly unique
non-clustered indexes are being ignored, and instead a table scan is
being performed. The execution engine is (wrongly) assuming that the
bookmark lookups will be more costly than simply doing a tablescan.
The end result is that many queries run 10s or 100s of times longer
than they should without explicitly adding index hints to force it to
use the index.
In one example a table with 5,000,000 or so records has a
non-clustered index on a field, and the statistics believe that there
are around 141 records that match the requested specific criteria
(there are really less -- as a sidenote can one increase the number of
steps stored by statistics?). Instead of quickly pulling the presumed
141 index matches and then doing the corresponding bookmark lookups,
it's instead table scanning all 5,000,000 records. Indeed if I look at
the estimated execution plans, it's claiming that the forced use of
the non-clutered index, and hence forced bookmark lookups, will yield
5x the subtree cost. The end results tell the real story, though -
with the index hint it completes instantly, while without the hint it
takes hundreds of times longer.
Is there a server or database setting that governs the estimation of
the cost of bookmark lookups? I do not wish to resort to unnecessary
hardcoded index hints when it's such a basically incorrect assumption
by the query engine that affects the entire system wherever large
tables are accessed. As a sidenote I had previously heard the
statistic that a non-clustered index will only be used if the
estimated set yields less than 2% of the total set, due to the cost of
bookmark lookups, but in this case it isn't using it even when the
estimated set is ~ 0.003%... how is it choosing?
Thanks
Dennis Forbessql
Bookmark Lookup
Fellow Developers
i have a query that has joins of tables with huge data (more than 2G of records per table). the execution plan shows me the 80% of the execution is on a "Bookmark Lookup" on the biggest table. Does anyone have clue how can I optimize this query? other than using covering indexes...
best regards
Jeries Shahin wrote:
Other than using covering indexes...
Well, if you know the answer already....
Seriously, you need to understand how indexes in SQL Server work (you may already, so here is the short version.) The nonclustered index uses the key of the clustered index rather than keeping a pointer to the physical page. Of course, this is great almost all of the time, but can be a costly operation at times.
Are you using any hints? And what join operators are being used? That is a lot of data (assuming 2G = 2 GB and not 2 grand :) and I would have assumed it would do a hash join, unless this is doing a merge join. Posting the output of showplan_text would be a good place to start:
set showplan_text on
go
select ...
go
set showplan_text off
go
And what version of SQL Server are you on? The new INCLUDE clause on the CREATE INDEX statement could actually be the ticket. It gives you a covering index without the overhead of including data you aren't using for searching on in the B-Tree (only the leaf nodes are affected)
|||Why don't you want to use a covering index. Thats like saying I want my car to go but I don't want to use gas. If you want performance from a relational DB system you need to use the right indexes.
I agree that the "include" option in SQL 2005 maybe an option.
|||Using a covering index can be really costly if the index keys are really large, so it might not be a good idea in 2000 or earlier to cover an index. We don't know his usage pattern and that is a lot of data (again assuming that G means GB :) This query might only be executed once a day/week/month. There may be thousands of modifications a minute on the table.
It might even be that a reporting database/warehouse is in order.
|||I agree but to some extent if the performance isn't what is required, then something has to be done and there are a number of options covering indexing being on of them, redesign being another.
One always has to balance out their performance needs. This gets much more complex when doing DSS stuff on an OLTP system. ideally they should be mutually exclusive.
Monday, March 19, 2012
blocks hangup database when inserting in 1 specific table
We use a database with about 40 related tables. Some tables contain as
much as 30.000 records. We use Access97 as an interface to the
database. Now recently we have the problem that when we want to insert
a row in one specific table (alwasy the same) the database makes
blocks.
Details:
- about 10% of the data was inserted using copying from Excel, before
this action there was no problem, though there is no evidence that
this causes the problem.
- inserting rows via the Query Analyzer works fine, via Access causes
trouble.
- the tempdb lofile has grown to 48Mb.
Has anyone ideas about what is going on and what I can do to solve the
problem?
TAV,
Jan WillemsJan Willems (jwillems@.xs4all.nl) writes:
> We use a database with about 40 related tables. Some tables contain as
> much as 30.000 records. We use Access97 as an interface to the
> database. Now recently we have the problem that when we want to insert
> a row in one specific table (alwasy the same) the database makes
> blocks.
"Makes blocks"? You mean that the INSERT operation is blocked, and you
have to cancel the operation to continue?
When the situation occurs, use sp_who from Query Analyzer, and see if
any process has a non-zero value in the Blk column. In such case, the
process listed in Blk, blocks the spid of that row.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 7, 2012
Blocking
2 hrs and still waiting for 20 million records
But while its doing the insert i am able to do
select count(*) from table and its giving me 0
my question how cum select is not blocked by above insert
sanjaySanjay,
You might read some under this topic:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_6fhj.asp
And this topic:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_2sit.asp
SQL Server default is to use the isolation level "Read committed". This
means that it will read the rows already there, but none of those in the
insert transaction.
By the way, you are apparently inserting 20 million records in a single
transaction. This will take much longer and use way more log space than
inserting them in smaller chunks. FWIW, I usually keep the insert blocks
down to about 10,000 rows, but that is not a scientific number. :)
Russell Fields
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:03a501c34578$ce926ac0$a101280a@.phx.gbl...
> I am doing an insert into a table which is taking forever
> 2 hrs and still waiting for 20 million records
> But while its doing the insert i am able to do
> select count(*) from table and its giving me 0
> my question how cum select is not blocked by above insert
> sanjay
BLock records using UPDLOCK
here my question :
I have 2 applications.
connection 1.
one does select max(grp) from orv and one does select max(grp) from orh. orh is the historical file from orv. We did this to know which is the greather grp between these 2 files.
After having did this, we add 1 at grp field.
we insert into orv the record max(grp) + 1
connection 2.
an other application could insert at the same time record in this table orv with same parameters.
my problem is the following. I need to block record in orv table either on the select ( connection 1) or Insert ( connection 2) to avoid having select max(grp) + 1 on orv at connection 1 and Insert a record into orv at connection 2.
I believe I need to use HOLDLOCK, UPDLOCK.
but I have not the habitude to use them.
Can I do this ?
connection 1
select max(grp) from orv WITH HOLDLOCK
connection 2
what should I use to avoid lock when I need to insert into orv. ?
thanks for your quick answerCan you explain what you are trying to do from a business or "real world" perspective? It sounds to me like you've dropped into the technology so far that you've lost sight of the original goal, and are making this a lot harder than it needs to be!
-PatP