Full Text Search Optimization


I am having a hard time trying to optimize a Full Text Search ( FTS ) on a ~1 billion rows table ( around 18Gb of reserved space used ).

The main problem seems the amount of rows returned and not the search itself. For example, when searching for "sales" i got 1811766 rows and it takes 22secs, when searching for "pineapple" i got 9 rows and it takes 0.1secs

So far i tryed the following tips and optimizations:

  • Separeted Disk with its own raid system ( low IO concurrency )

  • clustered index on the FTS key.

  • timestamp column to enhance incremental update ( even if i usually do full updates at night )

  • daily table index and FTS rebuild

  • using CONTAINSTALE instead of CONTAINS

  • config: max full-text crawl range set to the number of cpu cores ( 4x4 = 16 )

  • config: resource_usage set to 5 ( from 3 )

  • config: ft crawl bandwidth (max) set to 0 ( unlimited )

  • config: ft notify bandwidth (max) set to 0 ( unlimited )

  • Config: max server memory set to 44Gb ( 4Gb left to SO + fdhost.exe )

I am running on a win2003 with 4x quad-core CPUs, 48GbRam. SQL 2005 SP3, with all cumulative updates ( except last one or two ).

I have very few bottlenecks right now, cpu never allways under 80%, our new fiber channel solved most IO problems, and Memory usage also seems ok ( Page Life Expectancy never goes too down, and buffer cache hit ratio is 98+ ).

These are some of my sources of information so far:

Question: What else should i do to get a faster response time ?

Any help is appreciated, Thanks.

more ▼

asked Jul 22, 2010 at 01:53 PM in Default

avatar image

592 4 6 11

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first


What does the system look like when you run the slower query (I/O, CPU etc.)?

What does your storage subsystem look like (RAID Levels, # of physical disks per volume)?

Have you ensured the partitions are aligned? This is a common problem, as the default behaviour can cause performance problems - Technet Whitepaper

more ▼

answered Jul 23, 2010 at 12:20 AM

avatar image

26.2k 18 38 48

Hi William, Thanks for answering.

When i reach a lot of simultaneous requests cpu goes up and 'page life expectancy' goes down. Meaning that i will have problems to scale this in the future, but right now i am able to handle them without a clear bottleneck.

I have two separate raid system, both aligned and build of 12 + 12 Hds( raid 10 ). FTS is on the second raid ( usually used for failover only ).

The Problem seems to be the number of returned rows, since searching for "pineabple" is instant, but for "sales" takes ~22secs.

Jul 23, 2010 at 06:17 AM Gustavo


it sounds like the FTS is working as it should (as I understand it). The smaller search is faster, because there really is less data to plough through to get your results - it may even still be in cache with sub-second speeds.

The larger query is slower because you are looking for more data (probably off disk). What I am not too sure about with FTS is the behaviour in terms of caching. If Page Life Expectancy is dropping, this would suggest that the cache is being emptied to allow the data from the FTS engine to be cached. This would be pumping data off disk and into your RAM and be the reason for 20+ second queries.

Can you check that your I/O subsystem is up to the task when you issue such a large search (or two)? I would not be surprised to see full whack on that array whilst it tries to pull all the data off disk.

Jul 23, 2010 at 06:54 AM WilliamD


Indeed, while searching for "sales" a lot more of IO and memory resources are used, the behavior is completly different when searching for "pineapple".

But that amount of resources needed should be supported by the actual hardware and the setups i have made.

Is hardware scaling my only viable solution in this case ?

Jul 26, 2010 at 07:25 AM Gustavo

Gustavo, one possibility would be to try partitioning your table (if logically possible and you have enterprise edition).

Once you have your partitions, you could then create indexed views on these and Full Text Indexes on these partitioned views.

How does that sound?

Jul 27, 2010 at 12:41 AM WilliamD

Its worth a try thanks.

I have also request some more RAM, upgrading from 48Gb to 64Gb might give some benefits too.

I will write next time with the results.

Jul 28, 2010 at 11:02 AM Gustavo
(comments are locked)
10|1200 characters needed characters left

SQL 2005 IS THE BOTTLENECK. Move to 2008 R2 or better 2012. The FTS is dramatically faster and diffrent there. 2012 is ~1.5 faster than 2008 R2.

Also: cache in application the one word top N results for all possible prefixes. In my ~80M words case it loaded only 1Gb of memory.

more ▼

answered Sep 04, 2012 at 04:49 AM

avatar image


(comments are locked)
10|1200 characters needed characters left

The problem is, as mentioned in the comments, most likely to be the sheer volume of data being returned - there's a limit to how fast traditional HDDs can shift data off disk, through the server, and back out to the client application.

An alternative to sheer hardware scaling might be (if that sort of query is run often enough) to partition the data so that this particular table & Full Text Index data are on SSD rather than a normal HDD/array.

more ▼

answered Sep 04, 2012 at 07:53 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 22, 2010 at 01:53 PM

Seen: 4171 times

Last Updated: Sep 04, 2012 at 07:53 AM

Copyright 2018 Redgate Software. Privacy Policy