I am running SQL Server 2000 on a Windows 2000 server box which has a single GB CPU with 256 MB RAM. I have a problem with SQL Server (the sqlservr.exe process) consuming, at times, around 190 MB of RAM. When the SQL Server service starts, it resides around 20 MB, and after using applications that reference the SQL Server database the process typically eats up around 100 MB. The reason I'm writing is because this drastically impacts performance. The architecture of the machine cannot change - all database applications must reside on the same machine as SQL Server (stand-alone environment). I have tried to reduce the 'Maximum' amount of allocated memory - and upon doing so I get an error when trying to run the same queries I once ran before. (reducing it also detrimentally impacts performance I believe).
Open enterprise manager,
Right-click the server you are having issues with and select properties.
Click on the memory tab.
Change the "maximum" memory to somthing more appropriate.
SQL2k will hog all the resources of a server if you let it, irreguardless of how many users it may or may not have. If this comp is a dedicated SQL server, you know, the kind you through in a closet and forget about, then a RAM-HOG would be what you want.