There are 2 ways to go.

1. Pay to a qualified SQL developer who knows about normalisation rules and tends to follow them to develop a new, proper database to support your application.

2. Pay for a new hardware, a new version of SQL Server, move the data to in-memory storage and pray that the hardware capacity will grow faster than the scale of your database.

There is not much what can be improved in your query.

It's database design which has to be improved.