Options to Optimize your SQL 2000 performance

These are some options you can do to optimize your performance using the full text catalog in SQL 2000.

To create the catalog after you have already installed SQL 2000, follow these instructions.

1) Select the full text catalog under the database. Follow the instructions to create a full text catalog. (create a schedule that is incremental)

2) Go to the table that you need a full text catalog created in and select Define full text indexing on a table. The unique index should be the primary key. The column should be the field in the table that contains the document type. The user provides the document type in a column that contains the file name extension that the document would have had if it were stored as a file in the file system.

3) Populate the full text index by going to full-text catalogs under the database (it should change to idle when it completes)

4) Run these stored procedures for optimization

sp_tableoption 'tablename', 'text in row', 'ON'

sp_fulltext_table 'tablename','Start_change_tracking'

sp_fulltext_table 'tablename', 'start_background_updateindex'

5) Possible full text optimization solutions

a) Set the virtual memory size to at least 3 times the physical memory installed in the computer, and set the SQL Server 'max server memory' server configuration option to half the virtual memory size setting (1.5 times the physical memory).
Because working with full-text search is very resource expensive, you should have enough physical and virtual memory.

b) run index tuning wizard by selecting the database, tools from the top menu and wizards. You will see an index wizard that might give you some performance tips.

c) There should be at least 15% of disk space.

6) run: sp_help_fulltext_catalogs 'CatalogName' To see status

SQL 2000 BOL title "sp_help_fulltext_catalogs" for "6 = Incremental
population in progress" as well as possible status: "7 = Building index "
and "9 = Change tracking".

Other Full-text index population status of the catalog:

0 = Idle

1 = Full population in progress

2 = Paused

3 = Throttled

4 = Recovering

5 = Shutdown

6 = Incremental population in progress

7 = Building index

8 = Disk is full. Paused

9 = Change tracking

NULL = User does not have VIEW permission on the full-text catalog, or database is not full-text enabled, or full-text component not installed.

7)

Resource Usage specifies the amount of CPU resources that are dedicated to Full Text Search. Acceptable values are between 1 and 5, where 1 is background and 5 is dedicated; the default is 3. Changing this value sets the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0\ Gathering Manager\ PerformanceLevel. Changes become active when you next restart MSSearch Service The default of 3 is optimized for up to 1,000,000 rows, and you will not get much noticeable performance improvement until you start dealing with tables of this magnitude.

Syntax: Sp_FullText_Service ‘Resource_Usage’, 5

You can check the current settings for your Full Text Service by running the following command: Select FULLTEXTSERVICEPROPERTY (‘resourceusage’)

NOTE: SQL Server 2005 performance is several orders of magnitude faster than with SQL Server 2000 and SQL Server 7