Re: freetexttable query never completes nor times out?
- From: Simon Sabin <SimonSabin@xxxxxxxxxxxxxxx>
- Date: Sun, 17 Dec 2006 22:40:44 +0000 (UTC)
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,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. HoweverThe server's got 1 meg of RAM...but SQL2005's the only thing on it,
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.
and it receives very light use (no active websites are accessing it).
Try the freetexttable on its own.I believe I tried that, and the "executing query" ball just kept
SELECT Rank, [KEY] FROM FREETEXTTABLE(products,*, 'anvil',2000)
spinning...I'll see if I can recreate the problem.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
How many hits are there to anvil? If there are many you might wanthow many is "too many"? Is there any way to query the full-text
to 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?
.
- 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: Document properties
- Previous by thread: Re: freetexttable query never completes nor times out?
- Next by thread: Re: SQL as a web search engine
- Index(es):
Relevant Pages
|
