We did a test on SQL Server 2014 when we had 10 databases, 100 different schemas in each database, 10 of small (~50 rows) tables in each schema (so 10K tables in total) and we created full-text indexes on all these tables in all these databases simultaneously.
In several minutes we found that SQL Server stopped to accept any connections (except
ADMIN:. connection). If we restart the server, we can connect, but in some time it hangs again. After some investigation we found that it is caused by consuming all working threads, and
dm_os_waiting_tasks showed us that there is a lot
FT_MASTER_MERGE waits in
SUSPENDED state. We googled that "Full-text is waiting on master merge operation", but found no more actual info about it.
We tried different full-text catalog configurations: one catalog per DB, one catalog per schema, one catalog per index. Anyway the server hangs with all these suspended tasks.
What's the root cause of the waits, how this can be fixed/mitigated?
And what is the recommended way to enable full-text on such a large amount of tables?