Queries with leading wildcards generally are slow because they are not sargable.  If by chance they pick up an index, they will usually full scan the index.  Below is one example of a typical query that you might see when using a leading wildcard.

SELECT * FROM CustomerAccount WHERE AccountNumber LIKE '%518370'

From looking at the plan you can see that there is a scan on an index for AccountNumber.

We can improve performance by creating a computed persisted column on the AccountNumber in REVERSE.  This allows us to change the leading wildcard into a trailing wildcard.

ALTER TABLE CustomerAccount ADD AccountNumberReverse AS (REVERSE(AccountNumber)) PERSISTED

After creating the persisted computed column we need to add an index on it:

CREATE INDEX IX_CustomerAccount_Reverse ON CustomerAccount(AccountNumberReverse)

The last thing is modifying the original query to reverse the input, and make the search a trailing wildcard search:

SELECT * FROM CustomerAccount WHERE REVERSE(AccountNumber) LIKE REVERSE('518370') + '%'

When looking at the plan we are now seeing an index seek on the index we just created.

So far as difference in performance is concerned, the query went from executing in 1.2 seconds to .001 seconds.

Caveats

Keep in mind this will require more storage when adding a computed persisted column.  Also keep in mind there is additional overhead on DML against this column, it might not be significant but it is something to be aware of.  Thank you for reading!