ARG! Bookmark lookups killin me!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

ARG! Bookmark lookups killin me!

Author

Topic

MuadDBA

628 Posts

Posted - 2004-03-16 : 13:08:05

I have a table with 65 million rows. Let's call it my CATALOG table. It has an ID field which is a nonclustered unique index.

I have another table with 3000 ids we want to look up. I join them together. It produces a table scan, then has to do a sort to match the data. Ugly. When I force it to use the index with a hint, it uses the index, and then says that the bookmark lookups are going to incur a cost of 40,000. Ludicrous! It's 3000 rows! Even when I dropped it down to 10 rows, the behavior was the same. The system insists that a tablescan is the better route to go. With 1 row, same behavior! And it's not just the query plan! If attempt to execute the query, even with 1 row in my joined table, it takes forever.

If I do a direct select statement (for the same columns), it does an index seek with a bookmark lookup that costs .062 and returns in less than a second. What the hell is going on? What should I do?

I have scheduled the table for a reindex over the weekend (it take 9 hours to complete) but is there anythign I can do at this time to try to see what is causing this behavior?

Here is what my query looks like:

Select true_name, location1, location3 from catalog, lookups
where true_name = id order by location1

only true_name is indexed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 13:09:23

Are your statistics updated?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 13:12:54

only true_name is indexed? id and location1 need to also be indexed.

Tara

MuadDBA

628 Posts

Posted - 2004-03-16 : 13:17:10

location1 has very few unique values, maybe 100 or so across 65 million rows. Didn't seem prudent to waste all that space indexing it. I took out the order by, assuming I could just sort it in excel later. Didn't help.

for whatever reason, SQL didn't deem it necessary to create statistics on my true_name column. I put them there. It didn't help.

I indexed id on the lookup table, it didn't help either. I still get a hugely whacked bookmark lookup cost whenever I attempt to run this query.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-16 : 14:04:06

With your index hint on, do a inner loop join and look at your execution plan. Sometimes on really large tables with a large amount of unique values, this can make a difference.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

MuadDBA

628 Posts

Posted - 2004-03-16 : 14:10:18

Hmmm. Well that idea was interesting. I changed my statement to:

select l.true_name, location1, location3 from catalog l (index(locator_id)) inner loop join
archsrvi a on a.true_name = l.true_name

and now my cost is even worse than before

the bookmark lookup still costs 44,000 (oops, make that 440,000) but it is only 22% of the whole cost.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-16 : 14:24:14

select l.true_name, location1, location3 from catalog l (index(locator_id)) inner loop join
archsrvi a on a.true_name = l.true_name

You have an index on a.true_name and l.true_name?

You should be able to create an index on just these two fields. Don't use a composite index. Take the index hint off and run it again. The only reason you should get a bookmark lookup is if you are using an index but your join statements or where clause use columns not in the index used by the execution plan. Make sense?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

MuadDBA

628 Posts

Posted - 2004-03-16 : 14:55:39

Yes, I have an index on l.true_name and a.true_name. If I run it without the hint, I get a table scan on 66 million rows of table "L" (catalog). If I use the index hint, I get nailed with the bookmark lookup. I need those other columns (from catalog table) or my query is useless, but they aren't in the join. And though the table scan has a cost of "only" 2800, that is still too much for something that should be able to index seek and lookup MUCH faster than what it is doing. I just don't understand it. Is it because my index is on a different filegroup from my data?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 14:58:41

What does DBCC SHOWCONTIG show for these indexes?

Tara

MuadDBA

628 Posts

Posted - 2004-03-16 : 15:03:13

DBCC SHOWCONTIG locks that table, which is something I can't afford to do during production hours.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 15:10:14

I can't find any information that say that. SHOWCONTIG just displays information. It does not modify anything. DBREINDEX is what modifies and locks tables.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-16 : 15:17:11

Isn't the index hint you are using for another index though? You have the true_name column on an index called locator_id???

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

MuadDBA

628 Posts

Posted - 2004-03-16 : 15:47:27

quote:
Originally posted by tduggan

I can't find any information that say that. SHOWCONTIG just displays information. It does not modify anything. DBREINDEX is what modifies and locks tables.

Tara

I am just posting what I know. When I run it, it blocks all the updates and inserts to the table. I know this because I see it when I run SP_WHO2.

MuadDBA

628 Posts

Posted - 2004-03-16 : 15:48:27

quote:
Originally posted by derrickleggett

Isn't the index hint you are using for another index though? You have the true_name column on an index called locator_id???

I didn't name the indexes, I just work with them :) Yes, the locator_id index is on the true_name column. Maybe because it's the ID field for the catalog table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 16:14:37

Have you considered running DBCC INDEXDEFRAG? It isn't the same as DBREINDEX, but at least it'll help if you have any fragmentation. It's an online operation, so it can be done with less impact to the system than DBREINDEX.

Tara

MuadDBA

628 Posts

Posted - 2004-03-16 : 16:26:15

Without knowing how fragmented the indexes are, I am hesitant to run it because I don't know how much transaction log traffic it is going to generate, especially on a 66 million row table.

Would that help my bookmark lookup issue? What causes bookmark lookups to use so many resources?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 16:35:05

No, it will not help your execution plan. But it could help performance of the query regardless of the execution plan.

Tara

MuadDBA

628 Posts

Posted - 2004-03-16 : 16:58:38

I don't get it. For fun, I took the results of the lookup table and simply included it in an "IN" statement.

Select true_name, location1, location3 from catalog where true_name in
('value1',
'value2',
...,
'value3800')

It returned in 5 seconds, and did an index scan (for the big table), a constant scan (of my specified values), a hash match with an inner loop join and a bookmark lookup as the last step, but the cost for the bookmark loookup is only .3875 instead of 400,000.

If I replace the IN statement with IN (select true_name from archsrvi) it does a tablescan of my catalog table and has a cost of 2900. If I try to force an inner loop join on it, well, you saw the results above.

What gives? Obviously this query isn't that intense, so why oh why is SQLServer using such an archaic execution plan?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-16 : 17:05:19

free the proccache tonight tonight and try it again. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

MuadDBA

628 Posts

Posted - 2004-03-16 : 17:16:47

ok, if you know the command off the top of your head, can you tell me? A quick search of books online only returned the dbcc proccache command and a bunch of other stuff that doesn't tell me how to free the procc cache.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-16 : 17:31:41

Of course you well get a bookmark lookup!!!! That is used to retrieve the clustered indexes data...

Can you change the syntax to use inner join for a start...

To get a clean start....
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. Don't buy them from Deckers"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources