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 have created a very basic SQL table as following

CREATE TABLE [dbo].[TickData](
[Date] [varchar](12) NULL,
[Time] [varchar](12) NOT NULL,
[Symbol] [varchar](12) NOT NULL,
[Side] [varchar](2) NOT NULL,
[Depth] [varchar](2) NOT NULL,
[Quote] [varchar](12) NOT NULL,
[Size] [varchar](18) NOT NULL
    ) ON [PRIMARY]

I then performed a 3 Gig Bulk Insert

    BULK
    INSERT TickData
    FROM 
    'C:\SUMO.csv'
    GO

Then RAM usage for SQL server went Skyrocking, eating up ~30Go of RAM :

enter image description here

enter image description here

I prefer to think this is an abnormal behavior and that action can be taken to avoid this.

EDIT :
Ok, This seems to be the default behavior. Fair enough.
However, why isn't memory freed up long after the Bulk Insert is finished ?

A couple of extra-considerations :
As of the comments concerning SQL server freeing the memory when it is "told to" by the OS, my hands-on experience on a 24-Core 32 Gb Xeon Server proves this to be inexact : Once a Memory-Voracious BCP extract is over I have a pool of .Net Instances of my data processing application that need to process the extracted data, and they are left choking/fighting to share the remaining memory to try to perform their jobs, which take faaaaar longer that when SQL Server is turned off and memory is available for all applications to share. I have to stop the SQL Server Agent to make everything go smoothly and prevent Apps from crashing for Articiallt caused OutOfMemmroy Exception. As to artificial Brutal Memory Capping/Limitation, if Free Memory is available, why not use it ? Ideally it would rather be dinamically set to adapt to what is available rather than just being forcibly limited "randomly". But I guess this is by-design, so case closed on this last point.

share|improve this question

migrated from stackoverflow.com Jul 23 '12 at 12:51

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

6  
Why should SQL Server free up the memory? If it needed the memory to do this operation once, it will need it again. If SQL Server released the memory, what would you use it for? Why does the memory need to be free? If you use the memory for something else, then this bulk insert runs again, what should happen? –  Aaron Bertrand Jul 23 '12 at 13:07
    
The only action you can take to avoid this is to set the max memory size. To "recover" the memory, you can restart the SQL Server service. Obviously, that will release the memory, and when the service restarts it will allocate memory normally. –  TMN Jul 23 '12 at 17:49
    
I cleaned up the last edit. If you want to have an argument/discussion about the merits or issues of how SQL Server (or any other DB engine) is coded please find a better place to do it. The original question has been answered, apparently correctly and sufficiently. If this Q keeps creeping it may get locked. –  JNK Jul 23 '12 at 18:21
    
@JNK Ok fair enough. Although I Find this rude, and I disagree with your point of view. Sharing views and asking "why are things the way they are" is what made humanity go further, though there are always people to say "That's just how it is, so Shut up or I'll cut your tongue ! " (or lock this Q :-) ) Sorry to be technically curious and willing to share views. –  Mika Jacobi Jul 23 '12 at 18:27
3  
Also, as a non moderator, I am wondering why you are running non-SQL things on your server? The box should be reserved JUST for SQL Server, this is like DBA 101. The engine was not designed for a shared resource environment. –  JNK Jul 23 '12 at 18:39
show 3 more comments

3 Answers 3

up vote 11 down vote accepted

It is normal behaviour for SQL Server to allocate as much memory as it can into it's buffer pool. Databases work best with lots of buffer. If you want to change the behaviour, you can set the 'max server memory' setting. Some good background reading on that is here.

share|improve this answer
    
Thank you. Please see edit –  Mika Jacobi Jul 23 '12 at 12:29
1  
With regard to your edit, Aaron's comment is a good one. The reason is that there's little point in a database server freeing up memory. SQL Server will respond to memory pressure and release memory if it really has to, but really that shouldn't be necessary. –  Matt Whitfield Jul 23 '12 at 13:13
    
Well I Disagree on that point :-) Please see 2nd Edit –  Mika Jacobi Jul 23 '12 at 18:04
    
@MikaJacobi Sorry, but your second edit is just wrong. From a developer mindset I understand your point of view, but there is a difference between building a bunch of applications that co-exist and a server service that really should be the only memory-consuming service on the machine. If you want SQL Server to behave like a "normal" application, then set the max memory and walk away. Your question was why does it work this way, and that's been answered. If your question is now "well I don't like that it works that way" - sorry, but that's not a question anymore. –  Aaron Bertrand Jul 23 '12 at 18:10
1  
@AaronBertrand Ok I might be wrong but as stated in my new last edit, this is not what I witnessed : Memory was not freed when other application needed it hardly, causing out of Memory Crashes (Hence my dissatisfaction...) Anyway, let's close the subject, I guess there's no real need to argue more than that. –  Mika Jacobi Jul 23 '12 at 19:02
show 2 more comments

If you really want the OS to take the memory back from SQL Server, take a big 20 GB file and copy it over the network. SQL Server will release the memory as the OS needs it. But I would watch a variety of performance counters while this is going on, and see how the performance of your BULK INSERT changes if you run it again either while the copy is going on or immediately after.

If you want to do this manually, then you should set a lower limit on SQL Server's max server memory setting, and restart the service. Now SQL Server won't use 28GB even if it needs it. But this seems to be artificially limiting SQL Server.

What you seem to expect is more flexible behavior, where you can have free memory part of the time. For what purpose? Is this like shrinking a database file to free up disk space that you can't use for other purposes because the database file is going to grow again?

It's funny, if you type a Google search for "why doesn't SQL Server" the most common auto-completion is "release memory."

share|improve this answer
1  
    
"What you seem to expect is more flexible behavior." Exactly. I want SQL server to use all avaialble memory when it is available, but when these heavy moments are over, to go back to "sleep" state and leave the other memory-needing applications do their job conmfortably (you can have a look at my new last edit on this point) –  Mika Jacobi Jul 23 '12 at 18:40
    
For a concrete case illustrating why I need to do this, you can take a look @ my last comment to JNK at the bottom of the question –  Mika Jacobi Jul 23 '12 at 18:47
add comment

This is unfortunately by design, please reference this post. However, in this post it does give you some instruction on how to control it.

http://serverfault.com/questions/251832/sql-server-bulk-insert-physical-memory-issue

Memory Allocation Edit

Memory isn't freed up because it allocates memory a lot like a .NET application. Since memory allocation is expensive it will hold on to that allocation unless the OS requests it. But, fear not, if the OS wants the memory it will get it, just like it does in a .NET application.

share|improve this answer
    
Thank you. Please see edit –  Mika Jacobi Jul 23 '12 at 12:29
    
@MikaJacobi Sure, no problem. One other thing to note, just so you know, SQL Server will take over all cores as well so make sure you limit the number of cores available. I've seen SQL Server actually destroy the operating system it runs on before and I had to hard reboot a data center SQL Server. –  mperrenoud03 Jul 23 '12 at 12:31
    
Good to know. I have 24 Cores so this ok for now. But why isn't memory freed up after the Bulk Insert is over ? –  Mika Jacobi Jul 23 '12 at 12:34
    
As long as the bulk insert has completed, if the OS needs the memory it will be given to it, but it allocates memory a lot like a .NET application so that if it needs it and nobody else does it can access it faster. –  mperrenoud03 Jul 23 '12 at 12:36
    
My hads-on experience contradicts this (seel last edit). –  Mika Jacobi Jul 23 '12 at 18:38
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.