Things to Avoid in Your Query

LIKE ‘%xxxxxx%’

https://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/

Table Valued Functions

https://www.brentozar.com/blitzcache/tvf-join/

Functions in from/below

Implicit Conversions (convert to different data type)

Comparing the contents of 2 columns on 1 table

Table Variables

Table variable does not have stats and serial for updates.