How can i avoid the Index seek to scan 19K rows

sqlmega 2014-08-13 22:05:26

How can i avoid the Index seek to scan 19K rows on the index seek as the nested loop only needs to scan 584 rows ?

sqlmega 2014-08-14 14:59:04
link text

Aaron

Thank you for the quick response. i have attached the actual plan from plan explorer please find on the attachment.

Any suggestion will help a lot to me

Aaron Bertrand 2014-08-13 22:53:10
This is a classic "kitchen sink" procedure, where you have a bunch of optional parameters and SQL Server must try to optimize for all of them to potentially be populated, and often you get stuck with a plan.

One way to avoid this is to throw OPTION (RECOMPILE) at the end of the query – this will force SQL Server to evaluate the query, parameters and underlying cardinality every time, at the cost of compiling the plan every time. From the looks of things compilation is not a bottleneck here at all, but it would be easier to see if you generated an actual execution plan from within Plan Explorer (the one you provided was generated in SSMS first, and is missing a boatload of runtime metrics as a result).

If that does not work out better overall, you could try dynamic SQL.

-- get rid of @OfficeNIDS table variable
 
DECLARE @sql NVARCHAR(MAX) = N'SELECT PrecertNID
  FROM dbo.tblATNPrecertSummary WITH (NOLOCK)
  WHERE LastModifiedDTM > @ExpirationDate'
 
+ CASE WHEN @OfficeNidList IS NOT NULL THEN ' AND OfficeNID IN 
  (SELECT OfficeNID FROM dbo.udfTPSplitString(@OfficeNidList, '','')'
  ELSE '' END
+ CASE WHEN @MemberID IS NOT NULL THEN ' AND MemberID = @MemberID' 
  ELSE '' END
+ CASE WHEN @MemberDOB IS NOT NULL THEN ' AND MemberDOB = @MemberDOB' 
  ELSE '' END
+ CASE WHEN @MemberNameLast IS NOT NULL THEN 
  ' AND MemberNameLast LIKE @MemberNameLast + ''%''' ELSE '' END
-- ... fill in the rest ...
 
DECLARE @params NVARCHAR(MAX) = N'@ExpirationDate DATETIME,'
  + N'@OfficeNidList VARCHAR(MAX),@MemberID INT,@MemberDOB DATE,'
  + N'MemberNameLast NVARCHAR(64), ... fill in the rest ...';
 
 
INSERT @PrecertNIDS EXEC sp_executesql @sql, @params,
  @ExpirationDate, @OfficeNidList, @MemberID, @MemberDOB,
  @MemberNameLast, ... fill in the rest ...;

This will work best if you have optimize for ad hoc workloads enabled, preventing single-use plans from filling up the plan cache.

sqlmega 2014-08-14 22:22:33
Aaron

I have attached the actual execution plan from sql sentry on my previous post could you please take look on it.

Thank You for your support

Aaron Bertrand 2014-08-14 22:45:46
So looking at the query in question with the big index seek, it took 161 milliseconds to run. Is this really the query on your system that you want to invest man-hours improving? How much faster do you think you can get it? Did you try using either of the methods I suggested above?