I do not see any resolution to this problem mentioned, and have a similar
problem.
I have just implemented a database with 12 tables, one FT index on a text
column for each table. The unique key column is a uniqueidentifier.
This is under SQL Server 2005, SP1, Windows 2003 Server, on an x64 dual
processor system with 16 GB RAM. SQL Server is limited to 12 GB RAM and
nothing else runs on the box.
The query uses CONTAINSTABLE.
We have an automated process that feeds thousands of queries, one at a time,
to run FT searches.
It appears that the searches run fine for a while, then the searches take
longer and longer, until eventually the search never returns, for hours
anyway.
When we see this happening, in Windows Task Manager we see that process
msftesql shows PF Delta up over 50,000.
The Memory Usage and VM Size never increase over about 65 MB and 20MB.
Did you ever find a solution for this ?
Thanks.
Doug Funk
News Data Services
dfunk@.newsdataservice.com
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:c4366deffa728c87fe6f490db50@.msnews.microsoft. com...
> Hello KaMa,
> The maximum equates to process ~4.5GB/s thats a lot.
> Can you post you query plans and the output of statistics IO
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
Hi Hilary,
Your comment here is a bit scary. It sounds like the FTS capabilities
of Sql Server 2005 are not ready for production. Can you detail a bit
more the problems you encounter that force you to restart the sql fts
once a week? Is MS aware of that problem? What are their
recommendations? Do you know of any upcoming patch or SP that would fix
this?
Tony.
Hilary Cotter wrote:[vbcol=seagreen]
> We pound full-text search the same way you do. There are advantages to a
> multi-proc machine - a quad or eight way. We have to restart sql fts once a
> week. We find that smaller tables work better - where smaller is 50 million
> or so rows.
> We also found the following settings work well:
> setting a high resource usage to 5 and reorganize frequently.
> set ft crawl bandwidth (max) and ft notify bandwidth (max) to 0,
> set max full-text crawl range to the number of cpu's on your system,
> index text only,
> put your catalogs on the fastest disk subsystem (RAID 10) possible
> preferrably with their own controller,
> and run 64 bit.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Doug Funk" <doug.funk@.infomax-systems.com> wrote in message
> news:lAN_g.60102$OI1.44332@.newsfe15.lga...
|||Basically we find that the queries start taking longer and a bounce seems to
improve performance.
I have not communicated this to MS. You might want to open a support
incident yourself.
When we had a single table of over 300 million rows and pushing 2 terabytes
we had no end of problems with SQL FTS. After breaking the table up into 50
million row partitions we have had no real problems, but still bounce fts
weekly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tony.newsgrps@.gmail.com> wrote in message
news:1162399142.153739.316510@.e64g2000cwd.googlegr oups.com...
> Hi Hilary,
> Your comment here is a bit scary. It sounds like the FTS capabilities
> of Sql Server 2005 are not ready for production. Can you detail a bit
> more the problems you encounter that force you to restart the sql fts
> once a week? Is MS aware of that problem? What are their
> recommendations? Do you know of any upcoming patch or SP that would fix
> this?
> Tony.
>
> Hilary Cotter wrote:
>
|||Thank you for your answer. We'll look at partitioning our table if we
get into similar problems.
One quick follow up:
We keep on growing our table and the performance keeps on dropping.
With 8 million rows we had about 15 queries/sec. With 20 million rows,
we dropped to about 2 queries per sec. The server seems completely
underused though. The CPU and memory usage are very low and we see a
lot of page faults. Any idea what happened and how we could get back to
15 queries/sec? Could it be that the index needs to be re-organized or
something like that?
Our table is very simple (2 fields: 1 id, 1 plain text) and our queries
match only a very limited set of documents (100 matching records max
out of 20 millions).
Also, with your 300 millions/2TB table, what performance do you get on
queries (on average) and on what hardware?
Thanks a lot for your guidance.
Tony.
Hilary Cotter wrote:[vbcol=seagreen]
> Basically we find that the queries start taking longer and a bounce seems to
> improve performance.
> I have not communicated this to MS. You might want to open a support
> incident yourself.
> When we had a single table of over 300 million rows and pushing 2 terabytes
> we had no end of problems with SQL FTS. After breaking the table up into 50
> million row partitions we have had no real problems, but still bounce fts
> weekly.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <tony.newsgrps@.gmail.com> wrote in message
> news:1162399142.153739.316510@.e64g2000cwd.googlegr oups.com...
|||can you do this for me and post the results back here
sp_configure 'max server memory (MB)'
I don't think you have left enough memory for the OS and MSSearch.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<tony.newsgrps@.gmail.com> wrote in message
news:1162417354.892553.92200@.k70g2000cwa.googlegro ups.com...
> Thank you for your answer. We'll look at partitioning our table if we
> get into similar problems.
> One quick follow up:
> We keep on growing our table and the performance keeps on dropping.
> With 8 million rows we had about 15 queries/sec. With 20 million rows,
> we dropped to about 2 queries per sec. The server seems completely
> underused though. The CPU and memory usage are very low and we see a
> lot of page faults. Any idea what happened and how we could get back to
> 15 queries/sec? Could it be that the index needs to be re-organized or
> something like that?
> Our table is very simple (2 fields: 1 id, 1 plain text) and our queries
> match only a very limited set of documents (100 matching records max
> out of 20 millions).
> Also, with your 300 millions/2TB table, what performance do you get on
> queries (on average) and on what hardware?
> Thanks a lot for your guidance.
> Tony.
> Hilary Cotter wrote:
>
|||Hi Hilary,
thanks for the reply.
We have total 4gb ram on the server.
Currently SQLServer has max server memory (MB) set to 2048. I tried giving
it less (1024) but didn't notice any performance differences.
Any ideas?
When i allow SQLServer to use more memory (up to 3gb) i notice performance
downgrades with time probably because one SQLServer reaches its 3gb theres
not much left for OS/full-text engine.
What seems really strange is that full-text engine itself only uses about
7mb ram and has approx 100k page faults / sec.
Thanks,
Mikhail
"Hilary Cotter" wrote:
[vbcol=seagreen]
> can you do this for me and post the results back here
> sp_configure 'max server memory (MB)'
> I don't think you have left enough memory for the OS and MSSearch.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <tony.newsgrps@.gmail.com> wrote in message
> news:1162417354.892553.92200@.k70g2000cwa.googlegro ups.com...
没有评论:
发表评论