How to improve performance on a clustered index seek

I'm generalizing here, but...

A clustered index seek is, for the most part, the best-case scenario. The only ways I can think of to improve performance would be:

• Update the query to return fewer rows/columns, if possible;
• Defragment or rebuild the index;
• Partition the index across multiple disks/servers.

If it's only returning 138 rows, and it's that slow... maybe it's being blocked by some other process? Are you testing this in isolation, or are other users/processes online at the same time? Or maybe it's even a hardware problem, like a disk failure.

Clustered Index seeks occur when non-clustered indexes are used and aren't necessarily bad.

Consider the following query:

SELECT s.StuKey, s.Name, s.Address, s.City, s.State FROM stu s WHERE State='TX'

If there is only a clustered index on StuKey, then Sql Server only has 1 option, it must scan the entire table looking for rows where State="TX' and return those rows.

If you add a non-clustered index on State

CREATE INDEX IX_Stu_State on Stu (State)

Now Sql server has a new option. It can choose to seek using the non-clustered index, which will produce the rows where State='TX'. However, in order to get the remaining columns to return in the SELECT, it has to look up those columns by doing a clustered index seek for each row.

If you want to reduce the clustered index seeks, then you can make your index "covering" by including extra columns in it.

 CREATE INDEX IX_Stu_State2 on Stu (State) INCLUDE (name, address, city )

This index now contains all the columns needed to answer the query above. The query will do an index seek to return only the rows where State='TX', and the additional columns can be pulled out of the non-clustered index, so the clustered index seeks go away.