August 26, 2004 at 12:24 pm
Hi Folks,
I was reading an article on "SQL Server Full-Text Search Performance Tuning and Optimization:
URL:
http://www.sql-server-performance.com/full_text_search.asp
Scroll down the page....there is a paragraph that I dont understand. Can some one please explain this.
"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. "
Thanks
Sri
August 27, 2004 at 6:28 am
Yes, that is rather confusing. What it should say is give as much memory as possible to SQL Server, but leave 512 Mgs for the OS and MSSearch.
MSSearch will consume as much memory as possible (up to 512 Mgs) if the resource usage is 5.
ie sp_fulltext_service @action = 'resource_usage', @value = '5'
--
Hilary Cotter
Looking for a book on SQL Server replication?
August 27, 2004 at 8:40 am
To further clarify... The memory requirements of MSSearch are controlled by the resource_usage parameter of the stored proc sp_fulltext_service, but not the OS memory usage. Specificly, the MSSearch resource usage level can be modified via the following SQL Server 2000 system stored procedure:
EXEC sp_fulltext_service 'resource_usage', <value>
where valid <value> parameters are from 1 to 5 with 3 being the default and these values relate to proportionally allocate more memory within min/max boundaries for the given physical memory available on the server. The Min/Max values relate to the number of word lists kept by the Microsoft Search engine until Shadow Merge:
1: 20/20
2: 20/30
3: 20/40
4: 20/50
5: 30/60
For example, on a server with 512Mb of RAM and a resource usage value of 5 will get 60 word lists, and machine with 128Mb will get 30 word lists. For more information on word lists, see Platform SDK Indexing Service in the Additional Resource section. The actual values are computed by determining the available physical memory (RAM) on the server. The number of rows maintained in memory by the Microsoft Search service before the Master Merge is 250000 or 500000 depending on the available physical memory (RAM) on the server. In addition to the memory allocated to SQL server, Microsoft recommends that a minimum of 15Mb of RAM be reserved for MSSearch to a maximum of 512 Mb (RAM) of memory be allocated for the Microsoft Search service when Full-Text indexing multi-million row SQL tables.
Regards,
John T. Kane
August 27, 2004 at 9:30 am
Hilary and John
Thanks a lot for the clarification. Appriciate your help with examples.
-Sri
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply