choice of index

[newbby]

Hi All,
I have a table EMPLOYEE_DETAIL with columns such as
EMP_ID int (primary key, non clustered index)
EMP_LNAME char(25)
EMP_FNAME char(25)
DEPT_ID int (clustered index)

The dept_ID is a foreign key from the DEPARTMENT table. I created indexes as mentioned above. Most of my queries on this table works as below:
select emp_lname, emp_fname
from employee_detail
where emp_id = @intEmpID

The execution plan leads to bookmark lookups and index scans.

Is there any way I could eliminate the bookmark lookups?
Is it a good decision to create a composite clustered index on dept_id and emp_id?
Please advise.
Thanks

 

[micha123]

Where you see the "bookmark lookup" - do you see a "WITH PREFETCH" too?
If you do - it means that the optimizer decides that using IO PREFETCH is better than going through the index.

Are the statistics updated? (if the "automatic statistics" options are off you can update statistics and try the query again to see if something changes).

Can it be that your indexes are unorganized? Can you recreate the clustered index again and see if something changes?

You can try to give the optimizer a hint: WITH (index...) and set on the statistics io. Run the query without the hint and with the hint and see if the optimizer was correct by choosing what it did (if the logical reads are less or not).


"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[vongrunt]

About exactly how many rows in a table are we speaking?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[newbby]

more than 100000 rows