anwarmir
asked on
How to avoid a Costly BookMark Lookup
Hi I have the following SQL. The Quey returns around 20,000 rows. The Exhibitior_List table is being accessed via a Non clsutered index.
However all the rows are being looked up in the clustered index. The index depth is 2 for the clutered index. So I gather for each value found in the non clustred index there are2 page reads for the lookup. This appears to be costly as the lookup is costing 76% of the query. The query takes about 1min 20 secs to run. Is there any way I can aviod this lookup. I'm not sure if I can create a wide covering index for all the rows being accessed in the query. Is this sensible considering there are about 8 or 9 columns. Is there any other strategy i can use to reduce the cost of the lookup. here is the query:
select el.VAR_EL_NAME,el.VAR_EL_D
el.VAR_EL_ALPHABETICALSORT
tc.*,iOrder,vcLastName,vcF
from Exhibitor_List el WITH (NOLOCK) left outer join tblExhibitorParticipant tp (NOLOCK)
ON el.K_el_id = tp.iExhibitoriD
left outer join tblParticipant p
ON tp.iParticipantID = p.iParticipantID
left outer join tblCountries tc
ON tc.iCountryID = el.iCountryID
inner join tblContinent con (NOLOCK)
ON tc.iContinentID = con.iContinentID
where 0=0
and el.var_el_active <> 'N'
and el.K_EV_id =100458 and
p. iEventid = 100458
and con.iContinentID=8856
ORDER BY dRegistrationDate
However all the rows are being looked up in the clustered index. The index depth is 2 for the clutered index. So I gather for each value found in the non clustred index there are2 page reads for the lookup. This appears to be costly as the lookup is costing 76% of the query. The query takes about 1min 20 secs to run. Is there any way I can aviod this lookup. I'm not sure if I can create a wide covering index for all the rows being accessed in the query. Is this sensible considering there are about 8 or 9 columns. Is there any other strategy i can use to reduce the cost of the lookup. here is the query:
select el.VAR_EL_NAME,el.VAR_EL_D
el.VAR_EL_ALPHABETICALSORT
tc.*,iOrder,vcLastName,vcF
from Exhibitor_List el WITH (NOLOCK) left outer join tblExhibitorParticipant tp (NOLOCK)
ON el.K_el_id = tp.iExhibitoriD
left outer join tblParticipant p
ON tp.iParticipantID = p.iParticipantID
left outer join tblCountries tc
ON tc.iCountryID = el.iCountryID
inner join tblContinent con (NOLOCK)
ON tc.iContinentID = con.iContinentID
where 0=0
and el.var_el_active <> 'N'
and el.K_EV_id =100458 and
p. iEventid = 100458
and con.iContinentID=8856
ORDER BY dRegistrationDate
Please show me the DDL for clustered index on the Exhibitor_List table.
ASKER
clustered index is on el.K_el_id which is an identity column
And what is the fill factor on this index? That is why I wanted the full DDL.
Hi anwarmir,
You can try using a covering index. A covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn't have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.
Essentially, a Bookmark Lookup is telling you that the Query Processor had to look up the row columns it needs from a table or a clustered index, instead of being able to read it directly from a non-clustered index. Bookmark Lookups can reduce query performance because they produce extra disk I/O to retrieve the column data. So try using a covering index.
Hope this helps!
You can try using a covering index. A covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn't have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.
Essentially, a Bookmark Lookup is telling you that the Query Processor had to look up the row columns it needs from a table or a clustered index, instead of being able to read it directly from a non-clustered index. Bookmark Lookups can reduce query performance because they produce extra disk I/O to retrieve the column data. So try using a covering index.
Hope this helps!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.