Why is this query slow?

  • #pastetheplan https://www.brentozar.com/pastetheplan/?id=SJF21m0lU

    I'm trying to figure out what's causing this query (part of a stored procedure) to be so slow. The NOLOCK hints were an earlier attempt to stop deadlocks, I'm guessing they are likely no longer necessary.

    I did add an index that helped a ton, but this is still pretty slow and I'm not sure what direction to go in next.

    This was the old plan before I added the index it craved:

    Plan Hash_ 0xD9B010BBE52B310C and 11 more pages -

    And this is the new/current plan:

    Plan Hash_ 0xFB148C7A32121D3D and 11 more pages -

  • Gah! Sorry, didn't mean to post twice - site timed out during the original post.

  • Here are some initial observations based on the execution plan:

    • There seems to be a large number of reads on the ClaimPaid table, and there is a residual IO warning here.  Is there an index that has the ServiceDate column as the first column?  This seems to be the main filtering criteria.
    • How many rows would there be in the @CaTs table variable?  SQL Server won't use statistics for table variables and you can see in the plan it estimates 1 row.  If this has lots of rows in it, SQL Server could have picked a poor execution plan.
    • Is there an index on the ClaimPaidDetail table that has ClaimID as the first column?  Since you are joining ClaimPaid to ClaimPaidDetail on this column it would help to have an index on this foreign key.


  • There is an index for ServiceDate on that table, although that table has a ton of indexes on it. We're very likely trying to do too much here (indexes with sensible names = us, the two new ones at the bottom are Azure performance indexes it's added and is validating):

    SQLQuery4.sql - tcp_csscancentral.database.windows

    No, I don't think that @CaTs (categories) has many rows. This query is part of a larger that that's attempting to validate new claims against a set of rules of various categories. We're first determining what categories might apply, then finding relevant paid claims that might be relevant in the evaluation.

    Yes, absolutely that index exists:

    CREATE NONCLUSTERED INDEX [IX_ClaimPaidDetail_ClaimID] ON [Claims].[ClaimPaidDetail]
    [ClaimID] ASC


Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply