Very Computer

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?

--
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?

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