Improving the performance of non clustered Index Seek

Question

I have a query where a index seek (non clustered) is taking more time about 93% of the total execution time. the execution plan of the query, the estimated number of rows for the index seek is 1 and actual number of rows is 209. whether this is the problem? How to improve the performance of a non clustered index seek. A generic answer will be helpful. Execution Plan: And following is the query, ``` SELECT TOP 11 DVPR1.IncidentID, DVPR2.IncidentID, Rel.ID, PER1.[LastName], PER1.[FirstName] FROM DV_PHPersonalRecord DVPR1 INNER JOIN Relationship Rel ON Rel.source_Id = DVPR1.RowId AND Rel.typeCode = 'RPLC' INNER JOIN DV_PHPersonalRecord DVPR2 ON DVPR2.RowId = Rel.target_Id INNER JOIN [T_Attribute] (nolock) ON [T_Attribute].[ActRelationship_ID] = Rel.[ID] AND [T_Attribute].[name] = 'MergeFlag' AND ( [T_Attribute].[valueString_Code] = 'pending') INNER JOIN [Person] PER1 (nolock) ON DVPR1.[PersonDR]=PER1.[RowID] INNER JOIN [Person] PER2 (nolock) ON DVPR2.[PersonDR]=PER2.[RowID] WHERE DVPR1.T…

Accepted Answer

When I see such a discrepancy between expected rows and actual rows, I would first look at updating the statistics of all involved tables.

The query optimizer is supposed to do this automatically, but... sometimes it brings benefit.

This usually requires DBA type permissions.

Check out the Microsoft page on update statistics.

http://msdn.microsoft.com/en-us/library/ms187348.aspx

Improving the performance of non clustered Index Seek

Solution

Problem