I've inherited a server/application. This query seems to be my biggest problem child.

Steve Malcolm 2017-11-02 14:07:31

The is the link to the Paste A Plan site https://www.brentozar.com/pastetheplan/?id=HJklGsOR- .
SDyckes2 2017-11-02 14:30:11

While the Paste the Plan is a great way to make a plan available for everyone to see, it is not the same as getting an Actual Plan with Plan Explorer and uploading it to this site.

Your biggest cost is obviously the SORT, does the data need to be sorted by SQL Server? Or could the application perform the sort?

I would also investigate the Key Lookup on the OrdMain table. Initially, it does an index seek on the OrderNumber index but then it must use the PK to return the BillingDate, HSTTaxAmount, and TotalOrder columns data. I would consider adding the three columns to the OrderNumber index as INCLUDE columns.