Full Text Search Starts Slow
-
I've created a FullTextCatalog on one of my Databases and added a full text index on one of my text columns.
The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.
Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.
I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.
I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.
Let me know if I'm missing any pertinent information. Thanks.
wayne -
Do you have a population schedule in place? Could this be interfering with the population of the catalogue and hence slowing down your search?
I can't see any options for the Full text searching to start and stop itself.
How about, first run a search wait to get some results then leave it for the 30 minutes and then before making another search see what the status of the full text service is, is it running or stopped?
It just sounds to me like when you run your search it is starting the service first from a stopped state.
Just a few ideas.
Ed
-
Can you post your query? Are you using a CONTAINS() clause by itself, or with other conditions? If other columns are involved, you may need to create additional indexes on those columns.
It sounds as if the query plan is cached (providing the subsequent speed), and then expires and is released from the cache.
-
EIJ - I've got Track Changes turned to manual. We haven't made any changes to the data since the initial population. Since no one on the team had use FTS before, we wanted to make sure we could get it working before we started messing with the data. We'll be loading about 500,000 records a day so I want to wait until after the load to repopulate the Full Text Index. When ever we look at the Catalog it says that the Population Status is Idle.
I'll go take a look at the Service. I had thought to check that, but I need help from another user since I don't have proper privledges on the Server. I've got owner on the DB, but I'm not in any server roles.
mkeast - The same problem exists whether we're querying just on the Full Text Index or otherwise. I do have a clustered index on the other fields in my normal search. Then I have a Unique Non Clustered index on my Primary Key that is used by the FTS to link back into the main records.
At the current time this is being run mostly by AdHoc Queries, so it could be that the execution plan is changing. But it doesn't explain why it will run quickly for any period of time unless there is a 20-30 minute break in the middle. In any case here's a sample of the query:
SELECT a.*FROM tlogdata a
INNER JOIN ContainsTable(tlogdata, message_data, 'search') b
ON a.pk = b.[Key]
If you want me to post another query I can, but the speed issue happens in even this most simple case.
wayne -
Does it work fast on substequent searhces if you use different queries?
-
Yes. I can change the search term as many times as I want and it is always fast as long as there isn't a large break in between.
What is the easiest way to check to see if the Full Text Service is started? It sounds like that is the most likely issue that I'm having. It sound like it is turning itself off after a period of inactivity.
wayne -
If your using SQL Server 2005, go to the 'SQL Server Configuration Manager' which should be in your start menu group for SQL Server.
In the tree to the left there is an item for SQL Server 2005 Services, clicking on this reveals the services and their current state.
Finally, I've just been looking around the internet for you and it appears others have had this problem with little sucess. They have reported the only solution was to re-install Windows Server and SQL server and the problem goes away. Not much help I know...
-
Must you use CONTAINSTABLE (because you need the rank), or can you use Contains() like this:
SELECT a.*
FROM tlogdata a
WHERE Contains(message_data, ' "search" ')
Also, how many rows do you get when you execute this:
SELECT * FROM ContainsTable(tlogdata, message_data, 'search')
Have you examined the execution plan for clues?
-
On SQL Server 2000, the Full Text Search service is called Microsoft Search. You can look in the SQL Server Service Manager (there is usually an icon in the system tray), or go to Control Panel | Admin Tools | Services.
I can't imagine how or why the service would be stopped. It doesn't seem likely, unless it crashes. Again, it sounds like it takes SQL Server a long time to compile the execution plan, for some reason.
Have you checked the event logs for any errors related to the Microsoft Search service? If you open the service properties from Control Panel | Admin Tools | Services, and select the Recovery tab, you could set a failure action to "Run a File". Set that to a batch file (.cmd) that writes to a log file somewhere. For example:
c:\MSSLog.cmd
echo Microsoft Search service failed>>c:\msslog.txt
-
I can use either Contains or ContainsTable. It shouldn't make a difference for my current needs.
Looking at the Execution Plan for either query I get essentially the same thing. Running them side by side they each take roughly 50% of the total batch time.
When I run with '"search"' as my term I get 3 results (out of 500,000) and both Contains and ContainsTable return in the same amount of time.
When I run with '"message"' as my term I get 72000 results (out of 500,000) and Contains runs in about 5-6 seconds while ContainsTable returns in 4-5.
I've also looked at the Service and it seems to be running. I talked to the Network Admin and if it had stopped for some reason he would have been emailed about it, so it seems like that's running properly. He got SP1 installed last night, so I think I'm going to take a 30-45 minute break on running queries, and see what kind of performance I get when I try again.
wayne -
Keep us posted, I'm sure others will have this problem over time so if we find a solution it will benefit everyone.
-
I think SP1 contains the fix for this particular issue. I haven't noticed any significant delays since the install. I'm going to keep monitoring it just to make sure I'm not imagining things, but I think it's better.
Thanks for the help guys.
wayne -
I am experiencing the same exact problem. Takes about 45 seconds for the first query and then all subsequent queries run right away. I am running sp1, so I don't think that fixes the problem under 2005.
Brian
-
We are also experiencing this same issue after upgrading to SQL 2005 Standard sp1 on a Windows 2003 server. If any of you have resolved this issue since your last post please tell us about your experience. Thanks!
-
Happy days!! I'm actually still testing if this fixes it, but look to this MSDN article for the fix..
http://support.microsoft.com/kb/915850
.. sp_fulltext_service 'verify_signature', 0;
GO
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply