Azure SQL V12 full-text index creation is very slow
I am creating a full text index on 7 columns of a 30 million row table.
5 days for this request:
SELECT count(*) FROM sys.dm_fts_index_keywords( 5, OBJECT_ID('[dbo].[FormattedAddress]'))
Returns 500,000 lines and seems to be slowing down.
This database is S2 standard.
Is there anything I can do to speed up the process?
source to share
Several things you can do are
- Scale up to a higher SKU level say P1 and complete index building
- Scale to S2
Also, see resource usage in the database to see if you are hitting any resource limits. You cannot disable chanting as these configuration options are not available in SQL DB.
source to share
Let's try a few things. First, let's run the request before it completes. Second, we want to clear the buffer cache and retry the request. Perhaps this is also the problem they were talking about was fixed in 2008. Make sure signature verification is disabled. Also, another thing to check is memory allocation for FTS. The FullText service runs independently of the SQL service, so there is a chance it is starving for memory.
source to share