September 7, 2010 at 9:03 am
Hello,
We have 2 environments: test and production. Of course production environment is a much better server. I try to run the following query: select * from containstable (TableName, (FieldList), Keywords) on both systems (query is simplified for this post). The result set for the specific keyword is 10,000 records. Our test system returns results instantly, within 1 second (all 10,000 keys). Our production system might take up to 15 seconds to return the same result set of 10,000 keys. When I run this query on the console window I notice that results start showing up in the results window instantly and then sql server continues to pull data from the full text catalog until the query completes execution. Again, on the test system, all 10,000 records are pulled instantly. Can someone suggest what I should be looking at in our production environment to find out why it takes so long to pull data from the full text catalog. The only suggestion (that seemed logical to me) I found so far was to make sure that System cache is favored and it is in our system. Second question I have is whether it's better to use Clustered Unique index or Nonclustered Unique index for the basis of full text index?
Thank you in advance for your help.
Eugene.
September 8, 2010 at 12:44 pm
Are you running scheduled REORGANIZE or REBUILD on the full text catalog?
From BOL (http://msdn.microsoft.com/en-us/library/ms176095(v=SQL.90).aspx):
"Tells SQL Server to perform a master merge, which involves merging the smaller indexes created in the process of indexing into one large index. Merging the indexes can improve performance and free up disk and memory resources. If there are frequent changes to the full-text catalog, use this command periodically to reorganize the full-text catalog."
September 8, 2010 at 2:36 pm
We REBUILD catalog every week.
September 8, 2010 at 2:55 pm
And how is the response just after the catalog has been rebuilt? Just as bad as at the end of the week (just before the rebuild)?
September 8, 2010 at 2:56 pm
How is memory configured on the dev and prod system? Is there enough memory for the full text engine on prod?
September 8, 2010 at 3:00 pm
elayevskiy (9/7/2010)
When I run this query on the console window I notice that results start showing up in the results window instantly and then sql server continues to pull data from the full text catalog until the query completes execution.
Sounds like it's either firehosing the data (giving you the partial result set while it completes) or you've got network traffic. Nils seems to have a better grasp of the Full Text index side so I'll stay out of that, but other items to look at are network traffic/connection traffic differences between dev/prod and possible CPU maxing for communications traffic. Probably not your specific problem but it doesn't hurt to check.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 8, 2010 at 3:34 pm
The response is the same right after the REBUILD and at the end of the week (we don't get too many updates at this time). As far as memory configuration, I am not sure what to say. Maybe you can ask me specific question (maybe this is where the problem is). We didn't do any specific memory configuration. Dev system has 2GB. Prod has 4GB. Did you mean we can allocate memory for full text engine directly?
Thanks,
Eugene.
September 9, 2010 at 2:08 am
I was more interested in how memory was configured for SQL Server. Is min/max memory set? What is target server memory for prod and dev? A busy prod SQL Server tends to eat all memory it is allowed to. Perhaps the full text engine is starved on memory on the prod server. Just a guess.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply