BUG: freetexttable query never completes nor times out?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geek-y-guy - 15 Dec 2006 15:52 GMT

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?

Reply to this Message

Hilary Cotter - 15 Dec 2006 20:10 GMT

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

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.

Signature

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

> Hi All:
>
[quoted text clipped - 41 lines]
> Is this behavior familiar to anyone, and is there a workaround other than
> rebooting the computer?

Reply to this Message

geek-y-guy - 16 Dec 2006 19:52 GMT

> 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,*,
[quoted text clipped - 7 lines]
> 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.

>> Hi All:
>>
[quoted text clipped - 41 lines]
>> Is this behavior familiar to anyone, and is there a workaround other than
>> rebooting the computer?

Reply to this Message

Simon Sabin - 17 Dec 2006 01:05 GMT

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.

Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

>> How many hits are there to anvil? If there are many you might want to
>> try to limit your results set using Containstable, ie
[quoted text clipped - 101 lines]
>>> Is this behavior familiar to anyone, and is there a workaround other
>>> than rebooting the computer?

Reply to this Message

Simon Sabin - 17 Dec 2006 01:07 GMT

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

Reply to this Message

geek-y-guy - 17 Dec 2006 18:19 GMT

> Hello geek-y-guy,

Thanks for the feedback, Simon.

> Are your statistics up to date.

How can I tell? Is there a maintenance plan I should be running to regularly
update them? This is a dev server so I hadn't even been backing up either of
these databases regularly.

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

The server's got 1 meg of RAM...but SQL2005's the only thing on it, and it
receives very light use (no active websites are accessing it).

> Try the freetexttable on its own.
>
> SELECT Rank, [KEY] FROM FREETEXTTABLE(products,*, 'anvil',2000)

I believe I tried that, and the "executing query" ball just kept
spinning...I'll see if I can recreate the problem.

> Simon Sabin
> SQL Server MVP
[quoted text clipped - 105 lines]
>>>> Is this behavior familiar to anyone, and is there a workaround other
>>>> than rebooting the computer?

Reply to this Message

Simon Sabin - 17 Dec 2006 22:40 GMT

Hello geek-y-guy,

Have a look at the Sys.stats try

select object_name(object_id),name , stats_date(object_id,stats_id)
from sys.stats
order by stats_date(object_id,stats_id) desc

I assume you mean 1Gb of RAM.

How big is your database?

If you look at task manager what are the top 10 processes by memory and how
much do they each have?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

>> Hello geek-y-guy,
>>
[quoted text clipped - 136 lines]
>>>>> Is this behavior familiar to anyone, and is there a workaround
>>>>> other than rebooting the computer?

Reply to this Message