Creating FullText index on SQL Server needs a lot of time


SQL Server 2012 Express on a Win7-machine.
I have a table with 18'000 records, 10 fields with a fulltext index. After filling in that 18000 records it takes hours to complete fulltext-indexing. Is there a possibility to speed up this process? That's how I create it:
myFirstColumn KEY INDEX ixFTfirst ON ftCat 
WITH StopList = OFF

Open in new window

Second Problem: after inserting a new record it lasts minutes until I can find this record with "CONTENTS" but I should find it immediately. Thanks for your help, Peter
Who is Participating?
lcohanDatabase AnalystCommented:
Is that "SQL Server 2012 Express on a Win7-machine." a desktop/laptop with only one disk and not too much memory? If yes....not much that can be done still, you can try some of the ideas listed below but please keep in mind that Full-Text is resource intensive - CPU, Memory and IO for population and to read indexed articles:

Consider using a separate build server
Tables that are heavily updated while you're indexing can create locking problems, so if you can live with a catalog that's periodically out of date¿and an MSSearch engine that's sometimes unavailable¿consider using a separate build server.
You do this by making sure the indexing server has a copy of the table to be full-text indexed and exporting the catalog (see KB Article 240867 for the details).
Clearly, if you need real-time or near real-time updates to your catalog, this is not a good solution.

Use change tracking with the "update index in background" option
The easiest way to improve the performance of full-text indexing is to use change tracking with the "update index in background" option.
Here's why. When you index a table (FTI, like "standard" SQL indexes, works on a per-table basis), you specify full population, incremental population, or change tracking. When you opt for full population, every row in the table you're full-text indexing is extracted and indexed.

Limit activity when population is running
When population is running, don't run Profiler, and limit other database activity as much as possible. Profiler consumes significant resources. Likewise, don't run this in a continual loop, as it has a negative impact on performance:

Increase the number of threads for the indexing process
Increase the number of threads you're running for the indexing process.
The default is only five, and on quads or 8-ways, you can bump this up to much higher values. MSSearch will, however, throttle itself if it's slurping too much data from SQL Server, so avoid doing this on single- or dual-processor systems.

Another trick, if your application permits it, is to partition your data into multiple catalogs. For instance, if you split your table into two, you'll have twice as many threads working on the indexing.

Invest in the best equipment you can afford.
Invest in a quad or 8-way with the highest CPU cycles and L2 cache you can afford. There's a sweet spot using eight processors. If you can't afford an 8-way, invest in the fastest disk subsystem you can afford. (Microsoft doesn't support placing SQL Server databases or full text catalogs on RAM drives or disks, and some users have had spectacular failures using RAM drives.)

Stop any anti-virus or open file-agent backup software
If this isn't possible, try to prevent them from scanning the temporary directories being used by SQL FTI and the catalog directories¿which default to C:\Program Files\Microsoft SQL Server\MSSQL\FTData.

Try give the catalog its own controller
Place the catalog on its own controller, preferably on a RAID-1 array and separate it from temp and OS pagefile.

Be aware of maxed-out CPU utilization
Some of the word breakers have so much work to do that it's not uncommon for CPU utilization to max out at 100 percent. Although such utilization in itself isn't necessarily a bad thing, it does become a bad thing when your Server Work Queues counter climbs beyond 2 because your server will have to allocate resources to managing the queue as opposed to doing actual work.

Some "Best Practice" advice from previous versions but many they apply to SQL 2012 full text search as well:

1.        Put the full text index in its own filegroup.  This will avoid fragmenting the main data file(s).
2.       Use Varchar(max) instead of image or text for the fields that you want to use as the base for your full text index.
3.       Turn off auto updates if you see blocking on inserts and updates and do manual merges.
4.       Avoid running Master Merges and a rebuild/reorg on another index on the same table at the same time.  Make sure a Master Merge job does not overlap with a job that rebuilds a non clustered index on the same table.
5.       Sp_fulltext_Service can be used to set the block size.  Try increasing the block size to 512k.
6.       Use sp_configure to set the maximum memory for SQL Server down a bit.  How much down will depend on how many documents you intend to process simultaneously.

and lot more...

"Improve the Performance of Full-Text Indexes" -
I dont know how you are managing that as SQL Server 2012 Express doesnt include full text indexing
formiAuthor Commented:
Thanks for this explanations. I'll try some suggestions. But one last question: you write that it would be better to have an own filegroup for the FullText-indexes. So you would create one for the "Standard"-Indexes and one for the fulltext-indexes?
lcohanDatabase AnalystCommented:
"So you would create one for the "Standard"-Indexes and one for the fulltext-indexes? "
Yes I would do that and if possible split them on different disks/LUN's for better performance.
Also please note that FT catalogs are just physical files on the disk system and usually located in the default path where SQL is installed together with all system db's and page file so if you can those must be split as well. in particular temptb from the rest of database files and OS  pagefile.

And actually SQL 2012 "Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search)"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.