Hi, 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:** -
http://sqlserverpedia.com/wiki/FTS_-_Performance **Question: What else should i do to get a faster response time ?** Any help is appreciated, Thanks.
Gustavo, 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] :
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.
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.