0

I need some help interpreting this execution plan - https://www.brentozar.com/pastetheplan/?id=SJ5NYLLSL

Whenever this query is ran it uses up a huge amount of CPU and kills the server.

I have little experience in interpreting execution and am not sure what to look at/suggest to improve. I notice the amount of left joins and the size of some of the tables and would suggest adding an index somewhere but am unsure on what table and what fields. Thanks.

0
0

It is because you are joining unrelated tables, as a consequence "multiplying" the results. A "cartesian" join.

Look at the results of this sql fiddle

Do you see that I have too much data in the results? The rows of the second table are multiplied by as much as there are rows from the third table.

Not to mention the first one, the same values are repeated as many times as the rows from the second times the rows of the first table.

0

Comment out the left join to AppHisTeams as you do not use that table.

Creating indexes tends to be an art, you should not just create indexes. You may be able to adjust existing ones.

You should index AppHisScanCodes as it is not using one, maybe on scanid and include [Description] or use scanid as the clustered index.

Can you change the left joins to inner joins? Especially to AppHisActivigity, 30,000,000+ rows, as you are only using it to get to AppHisTeams.

Is MOdate a date field or is it a number?

Can you inner join to AppHisOutput, 20,000,000+ rows?

Look at the existing index IX_AppHis_ABR. Can you add the fields TitleNumber, AppType or IntakeDate with an include statement if they are not in the index already?

Add an index to AppHisOutput on MODate. Index on abr include modate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.