full text catalog best practices? RRS feed

  • Question

  • What are the best practices for creating full text catalogs?  For example, if I have N tables of representing unique customer data and each table requires full text searching, is it best to create a full text catalog for every table that needs a full text index?  This could lead to alot of full text catalogs (and possibly file groups).  What are the pro's / con's of having fewer full text catalogs vs more full text catalogs in SQL 2008?


    Thursday, February 25, 2010 9:41 PM


  • Hi,

    In SQL Server 2008, you can create a separate text catalog for each full-text index, or you can associate multiple full-text indexes with a given catalog. However, it is important to plan the placement of full-text indexes for tables in full-text catalogs.

    Recommendations from SQL Server 2008 Books Online:
    We recommend associating tables with the same update characteristics (such as small number of changes versus large number of changes, or tables that change frequently during a particular time of day) together under the same full-text catalog. By setting up full-text catalog population schedules, full-text indexes stay synchronous with the tables without adversely affecting the resource usage of the database server during periods of high database activity.
    When you assign a table to a full-text catalog, consider the following guidelines:
    1. Always select the smallest unique index available for your full-text unique key. (A 4-byte, integer-based index is optimal.) This reduces the resources required by Microsoft Search service in the file system significantly. If the primary key is large (over 100 bytes), consider choosing another unique index in the table (or creating another unique index) as the full-text unique key. Otherwise, if the full-text unique key size exceeds the maximum size allowed (900 bytes), full-text population will not be able to proceed.
    2. If you are indexing a table that has millions of rows, assign the table to its own full-text catalog.
    3. Consider the amount of changes occurring in the tables being full-text indexed, as well as the total number of rows. If the total number of rows being changed, together with the numbers of rows in the table present during the last full-text population, represents millions of rows, assign the table to its own full-text catalog.

    Additionally, beginning in SQL Server 2008, a full-text catalog is a virtual object and does not belong to any filegroup. A full-text catalog is a logical concept that refers to a group of full-text indexes.

    If there are any more questions, please let me know.

    ***Xiao Min Tan***Microsoft Online Community***
    • Marked as answer by scott_m Monday, March 1, 2010 4:00 PM
    Monday, March 1, 2010 9:15 AM