March 10, 2008 at 5:58 am
I have encountered the same problem now on two instances of SQL 2005 which were not present in SQL 2000. I have a problem with a search on a table with a full text index.
The search required is:
SELECT * FROM Messages WHERE Received >= '03/04/08' AND
CONTAINS(BodyText, '"alpha"') - typical example.
This search hangs, at least as long as tens of minutes.
A search by just the date parameter works OK
A search by just the CONTAINS parameter works OK
Changing the date search to "less than" rather than "greater than or equal" works OK
Rebuilding the Received index makes the search work for a short time, hours maximum. This assumes the index CAN be rebuilt, which is not always the case if the server is busy.
As I said, these databases were brought over from SQL 2000 which never had this problem, and I now have the same problem on two completely different sites.
Can anyone PLEASE advise on this as it is making my application useless!
Best Regards
Alan
March 10, 2008 at 6:51 am
Take a look at the query execution plan.
Though not always helpful, it will give you at an idea where to look for the problem.
Recently I had similar problem with a joined result: select col1, col2,... from table1 T1 join table2 T2 on T1.PKEY=T2.FKey where T1.PKey=@value
The query was taking 2 sec, plan said "89% clustered index scan T1.PKey".
I changed it to "left outer join", the plan was almost the same with CPU cost 300x lower. One of puzzles I wasn't able to figure out. So, play with join options, indexes,...
March 10, 2008 at 12:34 pm
As Robert said look at the query plan(s). Specifically, you want to compare the query plans that work to the one that does not work.
Let us know the results, if you still need help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 12:45 pm
Hi,
Thanks for replies. I think I might (fingers crossed) have an idea what it is. It looks like the two sites exhibiting the problem share the common feature of the data having been brought across from SQL 2000 using the wizard. Doing so moves the indexes that exist as part of the database, but it looks like those are the ones that then give trouble. I have several other customers who have installations 'from scratch' of the same database and all is well. Tonight I have tried removing all the index entries completely, except the primary key ones (have full text enabled) and I have recreated from scratch. Will see how that goes. Of course if that IS it, it's a nasty little gotcha, we trust these utilities to help with our upgrades, not mess the data up!
Best Regards
Alan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply