question

vivekchandra09 avatar image
vivekchandra09 asked

Xplan for a query: Performance Issue

I have a query where the xplan read like this: RID Lookup (Heap) [Table Name] Cost 59% What is happening here. How to fix this query?
queryheap
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
Are you able to attach the plan and are you able to change the existing indexes?
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
I expect there are other operations as well. It sounds like you have a lookup operation going on. There's a nonclustered index on a heap table. The nonclustered index has enough data to assist in filtering the query, but it doesn't have all the data needed by the query. So an additional lookup operation, the RID Lookup, is going against the heap to retrieve the other necessary columns. To "fix the query" is hard to say without seeing the query, the execution plan, or your structure. I can give you some general advice. Most tables should have a clustered index. If the index being used in this query is the most frequent path to the data, it's possibly a candidate for being a clustered index. A clustered index stores all the data, so it won't need an additional lookup operation. Another possible solution is to use the INCLUDE operation to add additional columns to the leaf level of the nonclustered index. That will eliminate the lookup operation too. Which of these is a good solution for you? I just don't know without more information.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.