Jan 11 2007
I maintain a Windows server running IIS and SQL Server. Normally, it runs without problems and I periodically check to make sure everything is ok.
A couple of days ago, people were complaining that they had problems uploading documents to the server. I checked it and saw that SQL Server was using about 1 GB of RAM. The server has 2 GB installed memory, but in spite of that, the server was running extremely slow. You could actually watch dialog boxes being painted on the screen!
I restarted the SQL Server service and it was back to using 37 MB of RAM, but I watched it take more and more memory throughout the day. Eventually, it was using around 400 MB of RAM even though the computer had light usage that day.
The first thought was that this must be a bug in SQL Server … a memory leak to be more precise. After some research, it was discovered that this isn’t a bug in the software, but its a feature.
SQL Server by design grabs as much RAM as it can. The more memory it has, the larger its cache will be which will allow it to respond faster to more queries without disk access. If other applications request memory, SQL server will release it to them. The problem is that the database server may not release the memory fast enough and could slow down performance of the entire computer. This is why its a good idea to have a dedicated database server.
You could limit the amount of memory that SQL Server can use, but it should be the only application running on your server. IIS and SQL Server should not share the same computer for commercial web hosting.