We've developed an ASP.NET MVC application serving hundreds of concurrent users on a sql server 2016 database (running in compatibility mode 2008).
One of our user defined functions is not returning any data from a "contains" statement, which uses an FTI catalog for a table containing binary objects (mainly pdf documents). This particular table is approx 381 gb in size, the database is around 1.5 tb.
Looks like this catalogue needs to be rebuilt as the last population date is blank?
What I'd like advice on is what the implications are for rebuilding this full text index on a 1.5 tb database would be?
Could this present any resource issues that could potentially cause CPU spikes, locking issues, tempdb growth, downtime etc?
Any advice on alternative strategies would be appreciated i.e. rebuilding the catalog outside of the database to reduce any such issues?
