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_DESCRIPTION,el.K_EV_ID,el.K_EL_ID,el.VAR_EL_ADDRESS1,el.VAR_EL_ADDRESS2,el.VAR_EL_ADDRESS3,el.VCADDRESS4,el.dRegistrationDate,              
  el.VAR_EL_ALPHABETICALSORT,el.VAR_EL_FAX,el.VAR_EL_TEL,el.VAR_EL_WEB,              
  tc.*,iOrder,vcLastName,vcFirstName, 0


 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
anwarmirAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> The Exhibitior_List table is being accessed via a Non clsutered index. <<
Then you must have an index on el.K_EV_id (right?).

>> Is there any way I can aviod this lookup[?] << 
No; the only way to get from a non-clus idx to a row in a clus tbl is thru the clus idx.

>> [Is a] covering index [sensible] for all the rows being accessed in the query <<
No; too many columns and too wide.

>> Is there any other strategy i can use to reduce the cost of the lookup[?] <<
Unfortunately, probably not, other than general tuning on the base table itself (make sure fragmentation is low, table is reorg'ed periodically, etc.).
0
 
btuttCommented:
Please show me the DDL for clustered index on the Exhibitor_List table.
0
 
anwarmirAuthor Commented:
clustered index is on el.K_el_id  which is an identity column
0
 
btuttCommented:
And what is the fill factor on this index? That is why I wanted the full DDL.
0
 
Sowmya_KCommented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.