We have gotten a legacy code base where quite many joins from various tables and eventually a sort on a joined table date column is performed. This whole operation takes from 20s and 90s to complete, which is unacceptable. All tables have Ids as primary keys.

We use EF Core as our ORM, so the generated SQL may look a bit odd. The SQL Server is hosted on Azure.

What we have done so far was adding an index on the OrderNode.

Create NONCLUSTERED INDEX IX_OrderNode_OrderIndex on [OrderNode] (Deleted, OrderIndex) Include (OrderId, Description, City, Zip, CountryCode, Reference, ShowDescription, IsFull)

and

CREATE INDEX IX_OrderNode_ArrivalPlan ON OrderNode (ArrivalPlan, IsMain) INCLUDE (ShowDescription, Description, CountryCode, Zip, City)

And one on the order, which was suggested by SQL Studio

CREATE INDEX IX_Order_StausId ON [Order] (StatusId) INCLUDE (OrderId)

Please find the execution plan here:

https://www.brentozar.com/pastetheplan/?id=S1_WQ3xQA