Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I notice that pretty often my SQL server will eat up 50% of my 8GB of memory. This usually happens after I finish running some SQL queries, not while they're running. The most common query is one that is generating millions of rows of random data. In the query I use

BEGIN TRAN
  ....
COMMIT TRAN

I'm guessing this might be the cause, but I don't know nearly enough about SQL to figure out the issue.

share|improve this question

migrated from stackoverflow.com Nov 4 '13 at 20:29

This question came from our site for professional and enthusiast programmers.

    
If you are rendering rows of data anywhere, such as in Sql Server Management Studio, that requires RAM. If you are rendering millions of rows, that's a lot of RAM. –  Dan Bracuk Nov 4 '13 at 19:56
2  
Because it can. –  Dan Andrews Nov 4 '13 at 20:26
    
The funny thing is, people don't realize that it's SQL SEVER, not SQL Application. As a Server type of application, you should expect it to utilize all resources as it needs to. –  Dan Andrews Nov 4 '13 at 20:28

3 Answers 3

SQL Server uses that memory because it needs it, and typically won't release it unless there is pressure from the operating system to do so (since it assumes that you'll need that much memory again). So it is actually a good thing that it is using 4 GB of RAM, and if you don't need that memory for other things, why do you care?

You have basically two options:

  • Limit SQL Server to something lower (e.g. 2 GB). This will make your queries slower, but at least you'll still have all that precious RAM to sit idle.

  • Restart SQL Server when you're done with any high-memory operations that push it up over 4 GB. This will allow your queries to run with the memory they need, but still return that memory when finished. Not my idea of fun times, but hey...

share|improve this answer
3  
+1 for the idle comment –  Sebastian Meine Nov 4 '13 at 20:39

SQL Server will always use all the available memory is given. As soon as something is read from disk into memory, it will stay there and never be released to optimize performance in case it needs to be read again. It is not uncommon in production environments to see SQL Server instances pegged at 95% memory utilization permanently.

This is at odds with normal application use of memory which can cause some confusion. This behavior is regardless of any transaction use in your TSQL scripts and objects.

EDIT:

Here's an article from Brent Ozar on the topic which contains an absolutely fabulous quote:

"SQL Server is using all of the memory. Period." - http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

More EDIT:

If you want to limit the memory that it uses, the following article has instructions for updating the maximum memory setting which will place an upper limit on memory used.

http://technet.microsoft.com/en-us/library/ms191144%28v=sql.105%29.aspx

share|improve this answer

A log of data is cached--data, queries, tables, indexes, execution plans, etc.--to speed up future executions, and can take a fair amount of memory. If your database has a lot of data, this can be the case.

At the same time, SQL Server will take a large chunk of RAM for the transaction, and may not release it until it absolutely needs to. Holding onto it allows for future queries to be executed faster.

Now, if you want to limit the amount of memory the service can use, that's fairly easy to do.

If you're using SQL Server Management Studio, open the properties for the server (right click > Properties), and go to "Memory". You'll see settings for Minimum and Maximum server memory. Feel free to set the Maximum to a lower number, matching what your desired maximum is.

If you want to do it by SQL:

USE master
EXEC sp_configure 'show advanced option', 1;
RECONFIGURE
-- Set these to your desired values
EXEC sp_configure 'min server memory (MB)', 0;
EXEC sp_configure 'max server memory (MB)', 1024;
RECONFIGURE WITH OVERRIDE;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.