Avoid Keylookups without covering index
I have a table with 20 columns. Few of those 20 columns are id, orderid, orderdetailid etc. Id is identity column and it is primary key (clustered index). Orderid, orderdetailid are part of nonclusteredindex. As part of one of the stored procedure we are retrieving 10+ columns from the table by joining few other tables on orderid,orderdetailid. The problem is – keylookups are costing more. However we can not create covering index on 10+ columns in select list. This table is huge. So is there a way performance can be improved?
So in your case, you see lots of key lookups. This shows that the optimizer estimates that using an Index Seek + Key Lookup strategy is still cheaper than the alternative, which is to just use a Clustered Index Scan. You should first verify this: check the number of logical IOs generated by the Index Seek + Key Lookup strategy and compare that to the total number of pages for the table, which is roughly equal to the number of logical IOs that a Clustered Index Scan would cost. If a Clustered Index Scan would actually have been cheaper (or rather: less expensive 😉 ), then you should investigate why the optimizer didn't go for the least expensive option and fix that. Perhaps that already makes your query fast enough.
If Index Seek + Key Lookup is actually better then Clustered Index Scan, or if Clustered Index Scan is better but still too slow, then another thing to consider is to change the type of index on your table. Make the index that supports the primary key on the identity column a nonclustered index, and change the index on OrderID, OrderDetailID to be the clustered index. (Make sure to declare it as a unique index if that combination of columns is unique; preferably by declaring a unique constraint which will automatically create the index). Make sure to list the OrderID column first when declaring the constraint or index.
Now all queries that filter on OrderID or on OrderID + OrderDetailID can use a Clustered Index Seek and no lookups are needed. However, the flip side is that all queries that filter (or join) on the identity column will now have to choose between a seek on a nonclustered index plus a lookup, or a scan of the clustered index.
(This rock / hard place type of choice is one of the reasons why I personally abhor the habit of many of my colleagues to mindlessly slap an identity column on each and every table. Yes, there are lots of tables where it is a terrific idea to have an identity column. But there are also lots of tables where it backfires. It should be a well though out decision for each individual table).
If the above suggestions do not help, then you might also want to consider using a columnstore index. If you are at least on SQL Server 2014 (2016 or higher is lots better for this!), and if your table contains at least … well, let's say 20 million rows, then you should investigate this. Note, though, that like everything else this feature has benefits but also makes you pay a price so make sure that you really understand the side effects of having a columnstore index before going this way.