Problem
We’ve mentioned in previous tips that the process of creating, building, and maintaining full-text search capabilities is quite a daunting task. During the installation of Full-Text Search for SQL Server 2000, one of the folders gets installed in a not-so-conspicuous location that could potentially be troublesome as the server gets busier. To get a good start on building and maintaining an efficient Full-Text Search process, move the temporary folder used by the Microsoft Search service.
Solution
One of the easiest things you can do to optimize your full-text process is to change the location of the Gatherer Service’s temporary folder. This folder is created in one of two locations during the installation of SQL Server:
- The Temporary folder of the user account that was used to install SQL Server (i.e., C:\Documents and Settings\timcullen\Local Settings\Temp\gthrsvc)
- C:\WINDOWS\TEMP\gthrsvc
To find out where this folder was installed, choose the server in Enterprise Manager, expand Support Services, right-click Full-Text Search, and choose Properties:
As you can see by the location, it is easy to overlook this location when specifying exception folders in anti-virus software. To fix this you can either include the location exclusion in your server build documentation to make sure it’s not missed, or move the folder. The latter option is likely the best option, since writing to this folder will compete for I/O with all of the other operating system requests. If possible, move the folder to its own physical disk array; if not possible, then move it to another physical disk array, since the process of building and populating full-text catalogs is I/O intensive.
To move the full-text temporary folder, use the SetTempPath.vbs file. This file is most often in the C:\Program Files\Common Files\System\MSSearch\Bin folder. Once you’ve decided where to move the folder, open a command prompt and navigate to the folder where the .vbs file is located and type cscript SetTempPath.vbs “newfolderlocation“:
Once you successfully change the location of the temporary folder you must re-start the service for it to take effect. Also, you cannot put the folder directly on a drive (i.e., C:\-it must be one level down), and you cannot put it in the same folder location as the pagefile.
Next Steps
- Review your current use of Full-Text Search, what hardware it is running on, and where the temporary folder is located
- Read other ways to Optimize SQL Server Full-text Indexing on MSDN
- Look at more Full-Text Search tips on MSSQLTIPS.com
