Execution plan analysis

• sandyinfowave

  Default port

  Points: 1490

  More actions

  April 21, 2011 at 4:37 pm

  #238989

  I am basically a .NET developer and trying to troubleshoot the performance of a query

  I am noticing some database timeouts in my .NET application and on further analysis(of Actual execution plan) it seems the update statement in the stored procedure has Table Scans on a table with 33 million records.......So i created a non clustered index on the table which changed the table Scans to index seek

  But now how do i know whether the performance changed significantly....what are the factors i need to consider???

  I checked the total Execution time in Client statistics, it looked almost similar

• sandyinfowave

  Default port

  Points: 1490

  More actions

  April 21, 2011 at 7:27 pm

  #1314771

  Here is the query i was working on

  UPDATE p

  SET p.CPEReleaseID = @CPEReleaseID,

  p.DateUpdated = getdate()

  FROM Cln_ServiceInstPayer p WITH (NOLOCK)

  INNER JOIN Cln_ServiceInstance i WITH (NOLOCK) ON p.ServiceInstKey = i.ServiceInstKey

  INNER JOIN is_CPERelease cpe WITH (NOLOCK) ON cpe.CPEThresholdID = p.CPEThresholdID

  WHERE p.CPEThresholdID = @CPEThresholdID

  AND p.PayerID = 201

  AND p.DeleteStatus = 0

  AND cpe.CPEReleaseID = @CPEReleaseID

  AND i.ServiceDateBegin BETWEEN @ReleaseStartDate AND @ReleaseEndDate

  AND i.ServiceDateEnd BETWEEN @ReleaseStartDate AND @ReleaseEndDate

  AND i.Status = @StatusID

  AND i.VoidStatus IS NULL

  AND i.ServiceInstType IN (40261,40262)

  AND i.SubmitDate <= CASE WHEN cpe.ReleaseAtExecuteFlag = 0 THEN cpe.AppliedDate

  WHEN cpe.ReleaseAtExecuteFlag = 1 THEN GETDATE() END

  Also please find the attachment for the execution plan

  I created the non clustered index on InstPayer table with columns DeleteStatus , PayerID, CPEThresholdID, ServiceInstKey