index, bookmark lookup

index, bookmark lookup

Post by mr bo » Sat, 17 Aug 2002 17:40:50



SQL Server 7
A certain query needs around 15% of the rows in a table
with ~10 million rows. I have a non clustered index,
containing one INT column, which exactly identifies these
rows.
However, the optimiser decides to do a clustered index
scan of the whole table. The clustered primary key
consists of 2 INT columns.
If I force the query to use the index, it runs slower with
the 'bookmark lookup' taking up 80% of the time (according
to the execution plan anyway, I don't know how reliable
this is).
Is this normal? Should I endeavour to design non-clustered
indexes so that they 'cover' queries, so that
the 'bookmark lookup' isn't necessary? For overnight jobs,
is it good practice to build a covering index, run the
query and drop the index?
 
 
 

index, bookmark lookup

Post by Andrés Taylo » Sat, 17 Aug 2002 20:36:16


Hi there,

If the 15% of the rows are mostly clustered together, in larger or smaller
ranges, the bookmark lookup will make a big difference. Whether you should
build the index, run the query and then drop it, there's only one way to
find out - test it. If the total time it takes for build index, query, drop
index is less than just querying without index, I'd say go for it.

Just remember that creating indexes might lock resourses from other users.

I think, without knowing anything about your table structure or data, that
I'd try creating a covering index for that query.

HTH,
--
Andrs Taylor


http://www.sql.nu/


Quote:> SQL Server 7
> A certain query needs around 15% of the rows in a table
> with ~10 million rows. I have a non clustered index,
> containing one INT column, which exactly identifies these
> rows.
> However, the optimiser decides to do a clustered index
> scan of the whole table. The clustered primary key
> consists of 2 INT columns.
> If I force the query to use the index, it runs slower with
> the 'bookmark lookup' taking up 80% of the time (according
> to the execution plan anyway, I don't know how reliable
> this is).
> Is this normal? Should I endeavour to design non-clustered
> indexes so that they 'cover' queries, so that
> the 'bookmark lookup' isn't necessary? For overnight jobs,
> is it good practice to build a covering index, run the
> query and drop the index?


 
 
 

index, bookmark lookup

Post by Andrew J. Kell » Sat, 17 Aug 2002 20:46:55


Most queries that return more than just a few % of the total amount of rows
the optimizer will choose a table scan over using the index.  Maybe it makes
sense to make this column the clustered index so it can do a partial scan
(assuming it is a range query).  Whether or not you can benefit overall from
adding more indexes (or larger covering indexes) can only be determined by
testing.  You don't always need all the columns from the select in one
compound index to get around a table scan.  Sever indexes may be used
together with Index Intersection to get the desired results.  You may want
to look at the Index Tuning Wizard.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:> SQL Server 7
> A certain query needs around 15% of the rows in a table
> with ~10 million rows. I have a non clustered index,
> containing one INT column, which exactly identifies these
> rows.
> However, the optimiser decides to do a clustered index
> scan of the whole table. The clustered primary key
> consists of 2 INT columns.
> If I force the query to use the index, it runs slower with
> the 'bookmark lookup' taking up 80% of the time (according
> to the execution plan anyway, I don't know how reliable
> this is).
> Is this normal? Should I endeavour to design non-clustered
> indexes so that they 'cover' queries, so that
> the 'bookmark lookup' isn't necessary? For overnight jobs,
> is it good practice to build a covering index, run the
> query and drop the index?

 
 
 

index, bookmark lookup

Post by Gert-Jan Stri » Sun, 18 Aug 2002 06:06:05


In the worst case scenario, 15% of 10 million rows selected using a
nonclustered index would require 1.5 million page reads (excluding the
index page reads). If there are (on average) 7 rows per page, then
scanning the entire table (clustered index scan or table scan) would
require at most 1.4 million page reads. If there are (on average) 20
rows per page, then the clustered index scan would at most require 0.5
million rows, etc.

So if the query optimizer tries to minimize the worst case scenario,
then it would have to select the clustered index scan.

However, if you have a high Buffer Cache Hit ratio, then it might be
(much) faster to use the non-clustered index with bookmark lookups, if
only because it using far less CPU time. This is something the query
optimizer tends to underestimate.

Gert-Jan


> SQL Server 7
> A certain query needs around 15% of the rows in a table
> with ~10 million rows. I have a non clustered index,
> containing one INT column, which exactly identifies these
> rows.
> However, the optimiser decides to do a clustered index
> scan of the whole table. The clustered primary key
> consists of 2 INT columns.
> If I force the query to use the index, it runs slower with
> the 'bookmark lookup' taking up 80% of the time (according
> to the execution plan anyway, I don't know how reliable
> this is).
> Is this normal? Should I endeavour to design non-clustered
> indexes so that they 'cover' queries, so that
> the 'bookmark lookup' isn't necessary? For overnight jobs,
> is it good practice to build a covering index, run the
> query and drop the index?

 
 
 

1. Covering index does not help to avoid bookmark lookup, help please

Hi !

 I have such a trouble. I have a query wich is using covering index.In
Execution plan i see the bookmark lookup,which is the most "heavy"
operation. AFAIK if i have covering index, SQL server have not to make BMK
cause it does not need any data from row. Can anyone tell me where i'm wrong
? Server is SQL 7.0 with SP2

SY,
  Wladzislaw

2. still having problem with executeUpdate() ...

3. are bookmark lookups bad ?

4. About Log Reader Agent

5. Bookmark lookup??

6. Is this the right way to go about it?

7. Bookmark Lookup in Est. Execution Plan

8. HELP ME (Dataflex)

9. Bookmark Lookup

10. bookmark lookup

11. Query execution plan - Bookmark Lookup