The SQL Server Full-Text Search service can affect the overall performance of SQL Server when it is indexing text in your tables. Indexing large amounts of text uses up server resources, reducing the CPU time and memory that could be used by your users running their SQL Server-based applications. If you find that the Full-Text Search service is negatively affecting your server’s performance, and impacting your users, you can change a setting, telling the service to back off, and not use so much of your server’s resources.
To change this setting, right-click on the Full-Text Search service in Enterprise Manager and click on “Properties”. This displays the “Full-Text Search Service Properties” dialog box. Here, click on the “Performance” tab. On this tab is a “System Resource Usage” option that has a slider bar you can use to change the effect this service has on SQL Server.
The slider bar has five position, and the default setting is at the third setting. If you want to reduce the impact of this service on your SQL Server, move the slider bar to the left one or two steps. This will proportionally reduce the affect of the service on SQL Server. It will also cause the full-text service to run longer to perform the same task, but now with less impact on the server. Conversely, if you want to dedicate this SQL Server to this service, you can boost the resource usage by sliding the slider bar to the right. But don’t do this unless your SQL Server is dedicated to the Full-Text Search Service. [7.0, 2000] Updated 1-14-2005
*****
When you create a full-text index, you can choose from two different ways to keep the full-text index up-to-date after it as been created. One option is to perform a full population, and the second is to perform an incremental population. As you might guess, the full population method starts fresh and reindexes all content, which can take some time to perform. The incremental population method only indexes data that has been added or changed since the last time the index was refreshed, and because of this, it takes less time to perform.
On the other hand, queries run against a full-text index that was refreshed using the full population method run faster than queries against a full-text index that was refreshed using the incremental population method. You must decide what is more important, fast refreshes or fast queries. Note that for an incremental population refresh to work, that the table(s) being indexed must have a timestamp field. Otherwise, only a full population refresh will be done. [7.0, 2000] Updated 1-14-2005
*****
In order to create a full-text index, the table(s) to be indexed must have a unique index. For optimum performance, select a column that is both narrow and numeric, such as one created by using an Identity column. [7.0, 2000] Updated 1-14-2005
*****
In certain situations, you can dramatically improve the performance of a full text query by embedding textual codes within the text column. This will allow you to search both on your embedded textual code and the required search condition. This can reduce the number of rows returned to SQL greatly and change the performance of the full-text query by an order of magnitude. [7.0, 2000] Updated 1-14-2005
*****
If you are using the SQL Server Full-Text Search service, Microsoft recommends these two settings for optimum performance:
· The virtual memory (PAGEFILE.SYS file) setting for your operating system should be set to an amount equal to 3 times the amount of physical RAM in the server. If you have a non-dedicated SQL Server (a server running applications in addition to SQL Server) then you will want to add the virtual memory needs of these other applications to the amount calculated above.
· The SQL Server MAX SERVER MEMORY setting should be set manually (dynamic memory allocation is turned off) so that enough virtual memory is left for the Full-Text Search service to run. To achieve this, select a MAX SERVER MEMORY setting that once set, leaves enough virtual memory so that the Full-Text Search service is able to access an amount of virtual memory equal to 1.5 times the amount of physical RAM in the server. This will take some trial and error to achieve this setting.
To find out how much virtual memory is being used by SQL Server and the Full-Text Search Service, you can use the Task Manager. By default, the Task Manager does not display the amount of virtual memory used by a process. To see this number in Task Manager, you must first go to the “Processes” tab. Once there, select “View”, and then “Select Columns”. From the “Select Columns” dialog box, click on “Virtual Memory Size”, then “OK”. Now you will be able to see the amount of virtual memory size used by each process on your server using Task Manager. Use this information to help you tune your server for use with the Full-Text Search service. [7.0, 2000]
Learn more about the Task Manager at Using Windows Task Manager for Logging and DebuggingUpdated 1-14-2005
*****
If you upgrade to SQL Server 2005 from SQL Server 2000 or SQL Server 7.0, and you are running the Microsoft Search service, you should repopulate the full-text catalogs in order to enhance search performance. [2000, 2005] Updated 1-14-2005
]]>