Another technique to try ...

On tables where you have columns that you'd otherwise build a FTS index, instead create a trigger that populates a single table where you string together exactly what you want. I.e. if searching has a set of filter criteria as well as the text, add in 'keywords' for the filters.

E.g. you want all people whose name are like 'Fred Smith' that live in California and were born in 1969. You'd create a FTS string like 'Fred Smith zClfrnz zDOB19690501' where zClfrnz and zDOB19690501 are the keywords created by the trigger from attributes that are probably already in memory.

When you search your FTS contains clause looks like this: 'Fred AND Smith AND zClfrnz AND zDOB1969*'. This is blisteringly fast.

Obviously, this technique falls down when a range search is also required. But not anything like as bad as you'd think. Say you want to search on 'Fred Smith's in California whose age is between 45 and 50. You'd add a column to your new FTS table that inlcuded the DOB, possibly with a specific index or a covering index that has some of the most common range value searches if they are commonly used in conjunction. Another approach where the number of items in a rang is small is simply to string together the range of interest, e.g. 'Fred AND Smith AND zClfrnz AND (zDOB1967* or zDOB1968* or zDOB1969* or zDOB1970* or zDOB1971*)'

Another thing that works well is 'joining' FTS searches. In this situation you still have just one FTS table but have triggers on multiple entity tables populating it. I.e. you want to find all sales people whose name is 'Joe Patel' who work in California and who are servicing a 'Fred Smith'. The salesPerson table creates a 'Joe Patel zslsprsnz zClfrnz' and the fred smith row is extended to include this relationship, i.e. red Smith zClfrnz zDOB19690501 zslsprsnzJoeSmith'.

At any time to want to add in new search keywords or searchable attributes, you run a dummy update on the source table and the trigger regenerates the search string value. Note you need to get a bit funky finding the existing search value if you're doing updates rather than deletes and inserts.

A 2 vCPU, 8GB SQL Server was easily handling 100 AJAX trns per second over a DB with 750k rows in the search table. Two different search application types too. One for building products and one for real estate.

The above won't work for all situations but if your searchable data is in database columns it generally works a treat as you can combine the best of relational DB with the best of FTS.