Brent Ozar explains why full-text search in SQL Server can be so slow:
SQL Server’s full text search is amazing. Well, it amazes me at least – it has so many cool capabilities: looking for prefixes, words near each other, different verb tenses, and even thesaurus searches. However, that’s not how I see most people using it: I’ve seen so many shops using it for matching specific strings, thinking it’s going to be faster than LIKE ‘%mysearch%’. That works at small scale, but as your data grows, you run into a query plan performance problem.
When your query uses CONTAINS, SQL Server has a nasty habit of doing a full text search across all of the rows in the table rather than using the rest of your WHERE clause to reduce the result set first.
Read on for the full impact as well as some alternatives. I agree that those alternatives come with costs (whether that be monetary or conceptual), but I’ve used both n-grams and Elasticsearch with some success.