Memory usage

Question

Memory usage

Peter Lake

SSC Veteran

Points: 233
More actions
December 1, 2008 at 5:39 am

#202160

I am using a series of stored procedures to process some data and add it to several tables. There are a total of about 500k records being added. During execution just about all system memory is taken by Sql Server, which doesn't surprise me because of the record count and complexity, but after completion the memory is never released. Any ideas on what might be wrong, or ways to remedy this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 1

Hello,

SQL Server tries to read as much as possible from memory instead of disk. For that purpose it uses a part of the memory : the DBMS buffer cache.

Immediately after starting up SQL Server service, the DBMS buffer cache is almost empty. When processing records your buffer cache gets filled with the information read from the pages on disk. These pages stay there as long as the system does not need to replace them or till SQL Server is restarted. If no memory is left in the buffer cache for new information, SQL Server reuses earlier filled up memory pages with the new information.

So, after starting up, your DBMS buffer cache grows (in a slow or fast way depending on the activity) to its limit. When reaching the limit, the size stays the same.

Regards,

Franky

Franky L.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SQL won't release memory by itself. That's by designs and it's expected behaviour. It will only release memory when the OS requests it (in cases of low memory) or when the service is shut down.

If you want to limit the memory that SQL takes (which is a good idea, especially on 64 bit) set the max memory server property.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Peter Lake SSC Veteran Points: 233 More actions · Answer 3

Thanks for the responses. I understood from researching that the memory usage grew as needed and was only released when the OS requested it back (but thanks for confirming), but in this case it doesn't seem to get released. After completion of the SP's, with Sql Server just sitting there looking back at me, if I run another app (start Outlook for instance) the new app runs v e r y s l o w l y, which I am assuming is due to not getting the memory resource it needs. Am i misunderstanding something here? (Again, thanks for taking the time to respond.)