3

I have a script that executes 15 bulk insert operations into a SQL Server Database sequentially, all based on the one below:

BULK INSERT M_USERS
    FROM '\\spath\users.txt'
    WITH ( 
        BATCHSIZE = 1000, 
        FIELDTERMINATOR = '|', 
        ROWTERMINATOR = '\n', 
        FIRSTROW = 2,
        ERRORFILE='\\spath\users.err'
    )
GO

The 15 text files vary in size. Some have less than 5000 rows, while the largest have over 5,000,000 rows. I am running the script 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.

Does anyone know if I could address this by revising my script, or is this an issue with SQL Server?

Edit: Eventually I get the error "There is insufficient system memory in resource pool 'internal' to run this query." and everything fails after that.

Improve this question

1 Answer 1

Reset to default
5

It's by design. Unless you cap the amount of memory that SQL Server can have access to it will take as much memory as it can. This is typically not a bad thing. SQL has its own internal OS for things like thread pooling and memory management and is VERY good at these things. Adding so much data is most likely causing SQL to put a lot in its data cache (amongst some other things) which is stored in memory.

If SQL Server is the only thing running on your machine then a general rule is to let it have all but 1.5 / 2 GB of memory, reserving that for the server OS. The more memory you can throw at SQL Server the happier it'll be.

EDIT towards your edit:
You will definitely want to cap the memory that SQL Server will use. I can't say for sure how many bytes you're working with but you're probably causing your server to swap SQL out which is bad news.

In Object Explorer in SQL Server Managment Studio:

  • Right-click on your server instance
  • Click on Memory in the navigation list at the left
  • Set Maximum server memory (in MB) to something between 10000 and 15000 (10-15 GB).
  • Click Ok

Or, you can also run this in a query window:

sp_configure 'max server memory (MB)',10000
GO
RECONFIGURE
GO

The 10000 caps SQL memory at 10GB.

Improve this answer
4
  • +1. Hate to say, but it seems reading documetnation IS SO out of favour with so called professionals in our field. THis question get asked about a couple of times per day.
    – TomTom
    Mar 25, 2011 at 14:37
  • I thought by not capping the memory, it would be better able to utilize all that it needs. But this results in the error described in my original post (edit). Would capping the memory prevent this error? Also, it still seems odd that the memory is never released, even after the process completes..
    – Michael Hornfeck
    Mar 25, 2011 at 14:40
  • 1
    @mjh41 See my edit. Also, the memory is not released because SQL has built a huge data cache. SQL tries to load as much of your data into memory as it can for optimal performance, since reading from memory is worlds faster than reading from disk.
    – squillman
    Mar 25, 2011 at 14:43
  • Thanks for the info. Your explanation makes sense and capping the memory at 10GB seems to have solved my problems. If you could point to any good documentation you know of that describes SQL Server memory management in detail, it would be appreciated!
    – Michael Hornfeck
    Mar 25, 2011 at 17:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .