Re: FTS query performance on SQL 2005

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: FTS query performance on SQL 2005
    ... road and see is you still have optimal performance. ... effects of cached query results. ... queries, the memory usage was steadily increasing. ...
    (microsoft.public.sqlserver.fulltext)
  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Cluster synchronize
    ... queries per unit time. ... CPU is the ONLY bottleneck. ... increase in query capacity. ... queries that perform sequential I/O or queries performing random I/O. ...
    (microsoft.public.sqlserver.clustering)