SQL Server Performance Forum – Threads Archive

query not using the right index

Hello, I am analysing a query which is performing bad even though it has the right indexes on the table.The problem is that sql server is not using the right index. i have 2 indexes on the emp table: 1. clustrered index on pk (id column) 2. Another index on id,name,dept. Analyse the following 2 queries: select name, id, dept from emp where id=1 and name=’xyz’ and dept=’abc’ The above query uses the right index (second index on id, nmae, dept) where as if I add any column which is not part of the index in select stmt, it uses the index created on pk!!! select name, id, dept, msg from emp where id=1 and name=’xyz’ and dept=’abc’ please let me know whats going wrong here, how can i make my second query use the right index?

---

Rocky, If SQL server has to use the index on id,name,dept for the second query (which referes to additional tables), then SQL server has to do an index seek on the nc index and a bookmark lookup to the clustered index to fetch the additional column. Bookmark lookup is a costly operation and that’s why the optimizer decide to use a clustered index scan instead. Anyways, you can force SQL server to do a bookmark lookup by specifying an index hint, as in select name, id, dept, msg from emp WITH(INDEX=index_name)
where id=1 and name=’xyz’ and dept=’abc’
Also, you can try whether the following version, which effectively use the index, gives you better performance. SELECT a.name, a.id, a.dept, b.msg
FROM (select name, id, dept from emp where id=1 and name=’xyz’ and dept=’abc’) a
INNER JOIN emp b ON a.id = b.id This approach will perform two seeks instead of a seek and a bookmark lookup.
Roji. P. Thomas
http://toponewithties.blogspot.com

---

If the id column is already the PK, then it should not be in your index on (name, dept).

---

Thanks mate.

---

You are right. But practically its the same. The clustered index key will be implicitly or explictly part of the non-clustered index. Roji. P. Thomas
http://toponewithties.blogspot.com

---

If ID is already the PK, there is no need to filter on both ID, and name or department. If you have the ID, you already have the row you need.

---

Why not? You are using a narrow index which covers all the columns you need. Roji. P. Thomas
http://toponewithties.blogspot.com

---

Sorry for the confuasion: Rocky’s query specified criteria for id and name and dept. If you already now the PK value to filter on, you don’t need any other criteria.

---

Adrian, I understand your point. What I am trying to say is, by supplying additional filter criterias you are improving your chances of using a covering index, which might give better performance, since its narrow compared to the heap or clustered index. Roji. P. Thomas
http://toponewithties.blogspot.com

---

]]>