|With that update in the question, first thing that anybody is going to say is to remove the concatenation and use parameters in the query—it will protect you from SQL Injection.
Secondly, there are so many LIKE operators, why are you using searches on the table, and trying to match every column with the input. A quick tip would be, use a separate search for each column and then try to aggregate the overall results. This would have a little amount of WHERE clause, and the query would end quickly, yours is having multiple OR clauses, which is not letting SQL Server short-circuit the query either and is making it run on each of the records.
I would also not be so sure as to whether any indexing would speed up things, but you can try adding indexes on these columns. Read this for more on that, tsql - SQL Server: Index columns used in like? - Stack Overflow
So, you can start by:
1. Add indexers to the columns as needed. You will know where to add them.
2. Create separate stored procedures to find the query results. Then try to aggregate the result of all queries.
3. Use caching to store the results of the most recent queries.
4. Also, try using full text search capabilities of SQL Server.
Full-Text Search - SQL Server | Microsoft Docs
sql - What is Full Text Search vs LIKE - Stack Overflow
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~