Re: FTS query performance on SQL 2005

Its hard to say what is going on here. repeat this a couple of days down the
road and see is you still have optimal performance. I would recommend at
that point setting SQL Max Memory to 2000-512 mgs=1500.

--
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



"Bahama Joe" <someone@xxxxxxxxxxxxx> wrote in message
news:e4h6C$K$GHA.3260@xxxxxxxxxxxxxxxxxxxxxxx
I have achieved a breakthrough in this performance testing, whereby now all
of my queries return in under 1/2 second. All of my testing was being
performed right after a server reboot in an attempt to eliminate any
effects of cached query results. However, I also found that sqlservr.exe
was using very little memory during this time, and during those long
queries, the memory usage was steadily increasing. I also found that once
sqlservr.exe settled out at around 740MB of memory usage, that all queries
responded very quickly. So, before doing any testing, I rebooted the
server, allowed all processes to fully startup, then issued a query that
returned nearly all rows in the database, such as "select count(1) from
containstable(Item, ItemText, 'is')", since the word "is" is in almost
every row in my database. (BTW, I have blanked out my "noise file", since I
need all those words to be searchable.) That query for "is" takes several
minutes, but at the end of that query, my sqlservr.exe process has reached
about 740MB of memory usage. From that point on, the sqlservr.exe process
memory usage does not increase any more, and all queries (including those
that were taking several minutes) now return in under 1/2 second.

Can anyone out there provide an under-the-covers explanation for the
behavior that I'm seeing? Is it that the full-text index (with 1.4 million
unique keys) is being cached in memory, after which the performance
improves? If that's the case, why wouldn't that memory be used by the
msftesql.exe process instead of the sqlservr.exe process?

Thanks for any insights.



.

Relevant Pages

  • Re: How to clear memory usage after executing queries ?
    ... got scared when the memory usage reached 1.7 gig (the machine has 2 gig of ... select distinctfrom packet, then do query ... > Columnist, SQL Server Professional ... > The only way to clear up the memory usage is by stopping SQL Server Service ...
    (microsoft.public.sqlserver.programming)
  • Re: Full Text Search Performance
    ... >> The simplest query that uses the smiplest form of FT on the first table FT ... >> very powerful SAN system. ... Memory usage on the server is not very high so I ... >> SQL Server. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS query performance on SQL 2005
    ... I have achieved a breakthrough in this performance testing, ... of my queries return in under 1/2 second. ... of cached query results. ... memory usage was steadily increasing. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: cancel during asyncfetchnonblocking
    ... >The problem with this seems to be, that the query is not really cancelled ... >AdoDS.Close - looking at my memory usage it climbs incredibly. ... Brian Bushay (TeamB) ...
    (borland.public.delphi.database.ado)
  • Re: Memory Hog
    ... George Christoforakis wrote: ... > queries, will get your memory in order. ... Yes, that's a good ideea, will do. ... I tried a lot of property combinations, none of them reduced my memory usage. ...
    (borland.public.delphi.database.ado)