Re: freetexttable query never completes nor times out?

How many hits are there to anvil? If there are many you might want to try
to limit your results set using Containstable, ie

how many is "too many"? Is there any way to query the full-text catalog to
find out?

There are only several dozen products with "anvil" in them, and less than
200 products altogether.

When I get properties for the FT catalog, it says:
"item count: 5568"
"unique key count: 16550"
"last indexed: 12/12/2006"
"catalog size: 0 MB"


SELECT DISTINCT p.p_id, p.p_name, p.p_desc, RANK from products p
INNER JOIN (SELECT Rank, [KEY] FROM FREETEXTTABLE(products,*,
'anvil',2000)) AS k
ON k.[key]=p.p_id
ORDER BY Rank DESC

The company I am currently working for is a heavy consumer of SQL FTS on
several terrabytes of data. While we do get very long queries sometimes
spanning days, for the most part the return quickly. It is difficult to
determine if your problem is related to the number of hits being returned
of a component hanging. The key it to see if you can reproduce it.

Hmmm...I have a somewhat larger dB on the same server that is a duplicate of
this one, with the same structure but different data (these are both
ecommerce stores). I just ran a similar query on that dB (this time from the
actual ASP page that passes the query to the dB) and the same thing
happened...the page timed out in the browser.

I also have SMS on the same client, and when I tried to get properties on
the FT Catalog, SMS is also timing out...no properties pane opens. A number
of taskbar alerts pop up saying "SQL SMS is busy waiting for a process to
complete" or something like that, and nothing happens. I can repeatedly try
to get "properties" for the FT Catalog but nothing ever happens...weird?

This is a testbed server that I use for development/staging before deplying
the dB to a live server. The live server is also running the same version of
SQL 2005, and I'm worried that if there's some sort of bug with FT
Catalogs/Indexing it might be happening on the live data as well (which
would be bad). I'm not seeing this behavior (yet) on the live server.

Once this happens, nothing seems to get the FreeTextTable queries working
again except a server reboot.

This feels like a FT bug, because any queries or SMS activities unrelated to
the FT Catalog or Index run immediately.


--
Hilary Cotter

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



"geek-y-guy" <noone@xxxxxxxxxxx> wrote in message
news:%232Jy$EGIHHA.1124@xxxxxxxxxxxxxxxxxxxxxxx
Hi All:

server2003 R2 standard, sp1

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00
(srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

I have a simple freetexttable query that seems to run forever without
timing out or returning results. It has worked in the past fine, so I'm
wondering if it has something to do with the full-text catalog being
corrupted or some other type of sql bug?

Only this query stalls...the SQL Server is still responding to other
queries normally.

The query is simply:

SELECT DISTINCT p.p_id, p.p_name, p.p_desc, RANK from products p

INNER JOIN (SELECT Rank, [KEY] FROM FREETEXTTABLE(products,*, 'anvil'))
AS k ON k.[key]=p.p_id

ORDER BY Rank DESC

When I run this query in SMS, I get a green circle in the bottom pane
"executing query" that never times out or returns any results? If I click
the "cancel" red box I see canceling query but it also never progresses.

At that point, I closed SMS and it "forced" the query to cancel finally.
I then attempted to restart SQL using the Service CP, saw the usual
prompt to stop Agent as well, and then Service Control timed out while
trying to stop SQL with an error 1053 ("service did not respond....")

The only way to get SQL back up was to restart the computer.

After restarting, the query works fine and executes immediately.

Is this behavior familiar to anyone, and is there a workaround other than
rebooting the computer?







.

Relevant Pages

  • Re: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... if your server doesn't have much memory ... and the "executing query" ball just kept ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Performance problems -- need guidance on scaling
    ... Thanks for the update on the FT Catalog corruption, ... will use, up to a max of 512Mb, if this memory is available. ... a server with 512 MB of RAM and a resource_usage value of 5 ... the data, you could, detach your SQL 2000 mdf & ldf files ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... SQL Server MVP ... This is a dev server so I hadn't even been ... When I get properties for the FT catalog, ... I just ran a similar query on ...
    (microsoft.public.sqlserver.fulltext)
Loading