Click here to Skip to main content
15,868,292 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am importing the recods of huge amoung i.e 5000000 using SQLBulkCopyInsert.writeToServer(), on a server with 12 GB of memory and a page file of 24GB. Once it starts, I notice the physical memory usage on that server steadily increases until in nears 100%. Even after the script completes (or fails due to lack of memory) the memory usage never drops. It seems that Sql Server never releases the memory resources it uses to perform the bulk inserts.ources it uses to perform the bulk inserts.
Posted
Comments
BobJanova 3-May-12 6:34am    
So, um, what's your question?
milenalukic 3-May-12 19:35pm    
I believe SQL only releases unused memory if another application requires it.

1 solution

The basic answer is that SQL Server is designed to use all of the memory available to it. It allocates the memory so that it has exclusive access to it even if it isn't using it. SQL uses sequential memory blocks in order to improve performance. That means it doesn't want to give other applications access to pieces of those blocks.

There are a couple things you can do. First, don't worry about it if you see SQL using all of the memory and things are working well. It most likely isn't a real problem. However, you should be sure that your SQL server is configured to leave some memory for the Operating System and any other applications you run on that server. You can configure the minimum and maximum amount of RAM SQL will use. The best advice I can give is that you give SQL all the memory you can. I usually reserve 2GB for the OS and give SQL the rest.

As far as SQL not releasing the memory or having a memory leak, that isn't really the case. It doesn't give the memory to the OS, but it isn't necessarily using the memory inefficiently. To learn more about how SQL uses memory and what you can do to better configure your server, read this article by Brent Ozar: http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900