Why is this query slow?

Question

Post reply

Why is this query slow?

kfrancis25

SSC Enthusiast

Points: 182
More actions
January 16, 2020 at 5:52 pm

#3716061

#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:
And this is the new/current plan:

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

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

kfrancis25 SSC Enthusiast Points: 182 More actions · Answer 1

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

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.

kfrancis25 SSC Enthusiast Points: 182 More actions · Answer 3

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
)