Solved

Creating FullText index on SQL Server needs a lot of time

Posted on 2013-12-10
4
427 Views
Last Modified: 2013-12-10
Hi

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:
CREATE FULLTEXT INDEX ON myTable 
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
0
Comment
Question by:formi
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
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.

http://blogs.msdn.com/b/sqlcat/archive/2008/11/06/best-practices-for-integrated-full-text-search-ifts-in-sql-2008.aspx

and lot more...
http://msdn.microsoft.com/en-us/library/ms142560.aspx

http://technet.microsoft.com/library/Cc917695

"Improve the Performance of Full-Text Indexes" - http://msdn.microsoft.com/en-us/library/ms142560.aspx
0
 
LVL 9

Expert Comment

by:QuinnDex
Comment Utility
I dont know how you are managing that as SQL Server 2012 Express doesnt include full text indexing
0
 

Author Comment

by:formi
Comment Utility
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?
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
"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)"

http://www.microsoft.com/en-ca/download/details.aspx?id=29062
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now