SQL Tips: Does SQL server have a memory leak?

Perhaps this is more of a misconception than a tip.  Nevertheless, it was something that tripped me up the first time I used SQL server, and I’ve gotten asked about high memory utilization by SQL server quite few times by others.

Often times, after use, if you go in Task Manager, you’ll notice that SQL Server’s memory footprint has grown to take up most of the free memory on a machine.  In many scenarios for application developers, this triggers the thought “memory leak”.  Well, in this case it isn’t.

SQL server expands to use up all the memory it can on the server.  It will continually poll the server to determine if more free memory is needed for other programs and will relinquish it so as not to starve them.  SQL server relies more on itself for memory management than underlying OS.

In the end, if the company paid for all that RAM on your SQL box, why not use it all?

Of course these options are configurable.  This can be done by using the sp_configure stored procedure with the “max server memory” and “min server memory” options.

On a dedicated SQL box, I’ll set the max server memory to the size of the RAM available on the box minus 1 to 2 gigabytes. This gives some extra cushion for the OS even though it is not necessary.

Here’s an article listing the options that are available for sp_configure. http://msdn.microsoft.com/en-us/library/ms189631.aspx

Here’s a great article to read to get up to speed on SQL server and it’s memory usage and some different options to take into consideration when setting it up. http://msdn.microsoft.com/en-us/library/ms178067.aspx

Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s