April 6, 2009 at 9:04 pm
I have a table with about 500,000 records, and it has 8 fields, one of which is a guid as the primary key (indexed, non-clustered). I have indexes on each of the fields, and when I select one row using any one of the fields, its fast, but the index plan is 51% on the index seek, and 49% on the bookmark lookup. Isn't this ineffcient? I believe this is causing problems in larger queries in other areas.... any ideas?
April 6, 2009 at 9:49 pm
It depends a lot on the details. Can you post at least one of the queries that you are looking at, its query plan (as a sqlplan file) and the DDL for the table & indexes?
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 6, 2009 at 11:22 pm
The query is this:
select * from nodes
where macaddress = '00-13-d3-b4-47-ab'
I can't save the plan, because its sql server 2000 - but basically it is something like this:
Select (0%) <- Book Mark Lookup(49%) <- Index Seek (51%) (idx_macaddress)
Attached is the output from sp_help on the table....
April 7, 2009 at 3:20 am
MR (4/6/2009)
I can't save the plan, because its sql server 2000 - but basically it is something like this:
Please post SQL 2000-related questions in the SQL 2000 forums in the future. If you post in the 2005 forums, you're very likely to get 2005-specific solutions.
Does the query really have to be SELECT *? Do you need every single column in that table? If so, there's little that can be done to help this query.
There's not much that can really be done about bookmark lookups on SQL 2000. Do remove bookmark lookups, one would widen the index, but indexes have a 900 byte limit to them and many of your columns are larger than that (nvarchar(510), nvarchar(600))
How many rows does this query return? If it's only 2 or 3, don't worry about the bookmark lookup. Start worrying when the query returns 1% of the table or more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2009 at 6:30 am
It's a classic bookmark lookup issue. There are a number of possible solutions, but it really depends on your structure & business requirements. Do a search for solutions, but be sure to stick to 2000 because the possible solutions in 2005/2008 are different (and better).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2009 at 8:54 am
MR (4/6/2009)
The query is this:select * from nodes
where macaddress = '00-13-d3-b4-47-ab'
I can't save the plan, because its sql server 2000 - but basically it is something like this:
Select (0%) <- Book Mark Lookup(49%) <- Index Seek (51%) (idx_macaddress)
Attached is the output from sp_help on the table....
Hi,
really you need all columns (*)
So you can create nonclustered index on macaddress is key of index and this index will have inlcude other colums, try this. It can help you because optimizer will not use LOOKUP bacause other columns will in this index (inlcude)
there is help how create index with include columns:
CREATE NONCLUSTERED INDEX [name_index] ON [dbo].[nodes]
(
[maccaddress] ASC
)
INCLUDE (other_columns)
April 11, 2009 at 9:29 am
radek (4/11/2009)
Hi,
really you need all columns (*)
So you can create nonclustered index on macaddress is key of index and this index will have inlcude other colums, try this. It can help you because optimizer will not use LOOKUP bacause other columns will in this index (inlcude)
there is help how create index with include columns:
CREATE NONCLUSTERED INDEX [name_index] ON [dbo].[nodes]
(
[maccaddress] ASC
)
INCLUDE (other_columns)
Since it's SQL Server 2000, he couldn't use the INCLUDE option on the index. Instead, if you were trying to use a covering index to solve the bookmark lookup, all the columns needed to satisfy the query would have to be a part of the key of the index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2009 at 9:33 am
Grant Fritchey (4/11/2009)
Instead, if you were trying to use a covering index to solve the bookmark lookup, all the columns needed to satisfy the query would have to be a part of the key of the index.
Which, of course, will not be possible should there be more than 16 columns in the table or should the total size of any row in the table be more than 900 bytes.
Even in SQL 2005, having an index (other than the cluster) that has every single column in the table in it is usually not a great idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2009 at 9:40 am
Hmm, I don't know that sql 2000 have not include index, that's pity.
April 11, 2009 at 9:51 am
GilaMonster (4/11/2009)
Grant Fritchey (4/11/2009)
Instead, if you were trying to use a covering index to solve the bookmark lookup, all the columns needed to satisfy the query would have to be a part of the key of the index.Which, of course, will not be possible should there be more than 16 columns in the table or should the total size of any row in the table be more than 900 bytes.
Even in SQL 2005, having an index (other than the cluster) that has every single column in the table in it is usually not a great idea.
I know, that index have only 900 bytes, maybe will possible create smaller columns than 510 on computername etc..
but that know only MR.
Then it will maybe possible create "larger index"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply