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: enter image description here

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.TypeDR = 718990 
  AND
    (PER1.[Code_ID] IS NULL OR ( PER1.[Code_ID] = '6516' 
                             AND PER1.[OptionsCode_ID] = '6522')
    )
  AND 
    ( PER2.[Code_ID] IS NULL OR ( PER2.[Code_ID] = '6516' 
                              AND PER2.[OptionsCode_ID] = '6522')
    )
ORDER BY PER1.[LastName] ASC,
        PER1.[FirstName] ASC

How long does the query take? Why do you think there is a problem? — Tomalak
the query is taking around 14 seconds ... the requirement is that it should be complete with in 3 seconds — arun.v1
That's not very useful. Please add the SQL and the entire execution plan. — Tomalak
From this we can see that the seek is executed 10424 times but not why so no one can tell you what to do about it. The query and the XML version of the execution plan would be most helpful. — Mikael Eriksson

GregHNZ GregHNZ · Accepted Answer · 2012-04-19T07:23:53

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

1 Answers