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:
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.
asked Jul 22, 2010 at 01:53 PM in Default
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
answered Jul 23, 2010 at 12:20 AM
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.
answered Sep 04, 2012 at 04:49 AM
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.
answered Sep 04, 2012 at 07:53 AM