11

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.

7
  • 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? Jul 23, 2012 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, 2012 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, 2012 at 18:21
  • There's nothing wrong with wanting to know answers and discuss methodologies, just not here on a Q&A site. You can definitely have this discussion in chat or a forum somewhere, but Stack Exchange is a little more structured and discussion-y questions will get shut down very quickly.
    – JNK
    Jul 23, 2012 at 18:37
  • 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, 2012 at 18:39

3 Answers 3

12

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.

5
  • 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. Jul 23, 2012 at 13:13
  • @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. Jul 23, 2012 at 18:10
  • Fair enough. My point of view is just that there should be an option to put this on or off, and the assumption that SQL server is the only Application on a Server is really abusive (might be true for big companies with large networks but that's not the only possible nor the most common setup) Jul 23, 2012 at 18:30
  • I still don't understand why you need SQL Server to free up the memory. If other applications will need it, they will take it back from SQL Server, and SQL Server will comply. Until those other applications need it, what purpose does freeing the memory serve? Jul 23, 2012 at 18:53
  • 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. Jul 23, 2012 at 19:02
7

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."

3
  • 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) Jul 23, 2012 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 Jul 23, 2012 at 18:47
4

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

https://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.

4
  • @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, 2012 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, 2012 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, 2012 at 12:36
  • My hads-on experience contradicts this (seel last edit). Jul 23, 2012 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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