Memory, Performance Options, Systems Cache or Programs...

[DANIEL 602]

I was looking at an article on SQL Server. Anyways, I have a client that has started to get timeout issues in an LOB accessing SQL Server on SBS 2003 Premium. I have found the problems to queries using Full Text searching. I then found an article on SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned at
http://technet.microsoft.com/library/Cc917695

It comes to my notice to imply that Windows Server 2003 has a memory settings set in: System Properties, Advanced (tab), Performance (settings) button, Advanced (tab) and a default value for Server 2003 of setting Memory usage to System cache. Although the SBS defaults to Programs, the server is quite old but had an upgrade when it was updated from SBS 2003 to 2003 and has 4gb ram. Also updated was an addon PCI (I think) RAID controller and it has 2 x SATA (raid edition) discs that replaced the old SCSI drives). It is a dual Zeon, 2.2Gz I think. I have tweaked SQL Server to use max 700Mb RAM - with the other databases (WSUS, MSFW, etc) set to 100 Mb's. The server settles down at about 3.5Gb used. Paging is fixed at 4092. I have set my test server to use System cache - but I don't know if I'll be able to tell much from it. Hope that somebody has changed this - and wondering what bad effects may result and it if can indeed help the SQL server FT queries that are timing out. Thanks for any information.

[Zachary]

I think that it is happening because SBS is a multi-purpose server that is heavily loaded. With SQL, Exchange, Sharepoint, sometimes ISA, all there on one machine, you may want the Ram use to favour those rather than anything else. Incase you have restricted your SQL memory usage, what the OS does cache wise is very unlikely to help you out. You can try to remove the caps on the SQL instance that hosts the FTS. I will also set it back, and you are more likely to cause new issues than solve existing ones.