Re: freetexttable query never completes nor times out?
- From: Simon Sabin <SimonSabin@xxxxxxxxxxxxxxx>
- Date: Sun, 17 Dec 2006 01:07:34 +0000 (UTC)
Hello geek-y-guy,
Are your statistics up to date. With 2005 Full text provides estimations to the optimiser. However this means the optimiser may choose a nested loop to the full text index when it believes the rows in the other tables are small.
Full text does not perform with a nested loop because the search is done for each iteration (I believed), if your server doesn't have much memory you will have a huge IO issue.
Try the freetexttable on its own.
SELECT Rank, [KEY] FROM FREETEXTTABLE(products,*, 'anvil',2000)
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
How many hits are there to anvil? If there are many you might want tohow many is "too many"? Is there any way to query the full-text
try to limit your results set using Containstable, ie
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 pHmmm...I have a somewhat larger dB on the same server that is a
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.
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?
.
- Follow-Ups:
- Re: freetexttable query never completes nor times out?
- From: geek-y-guy
- Re: freetexttable query never completes nor times out?
- References:
- Re: freetexttable query never completes nor times out?
- From: geek-y-guy
- Re: freetexttable query never completes nor times out?
- Prev by Date: Re: freetexttable query never completes nor times out?
- Next by Date: Re: freetexttable query never completes nor times out?
- Previous by thread: Re: freetexttable query never completes nor times out?
- Next by thread: Re: freetexttable query never completes nor times out?
- Index(es):
Relevant Pages
|
