Jan 11 2007
It’s Not a Bug … It’s a Feature!
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.
Jan 13, 2007 @ 07:26:12
This is exactly why I restart SQL Server every morning via a script.
[Code]
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
NET START MSSQLSERVER
NET START SQLSERVERAGENT
[/Code]
The code above in a batch file and linked to a Scheduled Task is all you need to restart it as often as you want. The only downside is while it’s restarting all connections to it are lost and denied. Most often this is only for 15 seconds or so.
Jan 13, 2007 @ 10:48:31
That script is a good idea. I’m already restarting other services every 2 hours due to complaints that e-mails aren’t being sent occasionally. Restarting sends all pending mail.
I’m letting SQL Server grab as much memory as possible this weekend. I want to see how close to 2 GB it will get.
I still would like to put the database on a separate server.