RE: Full Text Indexing Performance?

Dear Customer,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that you would liks to improve the
queries performance by setup full test index. If I have misunderstood,
please let me know.

in order to address your concern, I would like to explain the following.
1) The following is a list of recommendations that will help increase
full-text query performance:
Defragment the index of the base table by using ALTER INDEX REORGANIZE.

Reorganize the full-text catalog by using ALTER FULLTEXT CATALOG
REORGANIZE. Make sure that you do this before performance testing because
running this statement causes a master merge of the full-text indexes in
that catalog.

Restrict your choice of full-text key columns to a small column. Although a
900-byte column is supported, we do not recommend building a full-text
index that uses a key column of that size.

Combine multiple CONTAINS predicates into one CONTAINS predicate. In SQL
Server you can specify a list of columns in the CONTAINS query.

If you only require full-text key or rank information, use CONTAINSTABLE or
FREETEXTTABLE instead of CONTAINS or FREETEXT, respectively.

To limit results and increase performance, use the TOP_N_BY_RANK option of
the FREETEXTTABLE and CONTAINSTABLE syntax. Use this option you are not
interested in all possible hits.

Check the full-text query plan to make sure that the appropriate join plan
is chosen. Use a join hint or query hint if you have to. If a parameter is
used in the full-text query, the first-time value of the parameter
determines the query plan. You can use the OPTIMIZE FOR query hint to force
the query to compile with the value you want. This helps achieve a
deterministic query plan and better performance.

2 according to your description, i'm not very clear about "Create another
databaseon the same server and do a Linked Server? Keep everything in one
index? Move each "important" column to its own table?". in order to better
assist you with the issue, could you describe the above in detail?

Besides, about the Performance Tuning and Optimization (Full-Text Search),
there is an article to share with
you:http://msdn.microsoft.com/en-us/library/ms142560(SQL.90).aspx

If there is anything unclear, please do not hesitate to let me know. Have a
nice day!

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

.

Relevant Pages

  • RE: OWC 11.0 - Subtotals return incorrect values
    ... Welcome to the Microsoft MSDN Managed Newsgroup Services. ... instance and capture what the submitted MDX query is and then copy the MDX ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.olap)
  • Re: timeout expired on View
    ... Thank you for using Microsoft MSDN Managed Newsgroup. ... is not allowed for us to modify the result of the query directly. ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.tools)
  • RE: Sql Server 2005 Setup will not run
    ... Thank you for using Microsoft MSDN Managed Newsgroup. ... Is there any SQL Server instance installed on the machine? ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.tools)
  • RE: SQLXMLBULKLOAD question: Does it INSERT only, or can it UPDATE as well?
    ... Thank you for using Microsoft MSDN Managed Newsgroup. ... However you do not want to first delete the data from the table and then bulk insert the data into to the table. ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.xml)
  • RE: automating running a stored procedure
    ... Thank you for using Microsoft MSDN Managed Newsgroup. ... If you use SQL Server express edition, since the SQL Agent Service is not ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.dts)