Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

This question already has an answer here:

Sub:High memory utilization by sqlservr.exe process.

When I look into task manager -->processes or by using perfmon memory counters(Sqlserver:memory manager:Target server memory and Total server memory) I am getting high memory utilization by sqlservr.exe process nearly 8 GB (Target server memory counter) and 7.95 GB (Total server memory).

and when I restart the MSSQLSERVER service it again shoots up to the same size. I am getting this issue quite frequently.

Please help me out in identifying why sql server is using so much memory and how to find out what query , stored procedure etc is making sql server use that much memory.

* I am not using any triggers or cursors in my code.

Thanks

share|improve this question

marked as duplicate by TomTom, mdpc, Ward, MadHatter, HopelessN00b Aug 28 at 17:28

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Normal behavior. –  KCotreau Jun 14 '11 at 13:32
1  
I've just been looking at ways to trim the size of SQL Server on my local box, and I came across A Sysadmin’s Guide to Microsoft SQL Server Memory. This is a pretty useful article that explains why SQL server behaves the way it does, and also shows you how to reduce it, after telling you about the potential caveats. –  BoffinbraiN Aug 19 at 14:15
    
I ant a cent every time someone does not read the documentation on this. –  TomTom Aug 19 at 14:26

2 Answers 2

In my experience, SQL Server will attempt to preallocate as much memory as it can, as it is highly reliant on memory for performance. It usually takes our SQL Server machines about a week to get up to 32GB RAM footprint for the SQL Server process.

share|improve this answer
    
Specifically, unless you configure it to use less, SQL Server will try and use all the server's memory for cache (and therefore be faster). –  Richard Jun 14 '11 at 7:45
    
Actually it will not. Not PREALLOCATE without reconfiguration. Allocate yes, preallocate not. Minimum cache memory is not set by default. –  TomTom Aug 19 at 14:29

When you setup your SQL server one of the first steps you should take is to set your SQL Memory limit. There's a great guide at this blog. For an 8GB server the recommendation is to set MaxServerMem at 6400MB this will leave you 1.5GB for the OS and other operations. The OS will always show the SQL server process as using all of the memory allocated to it and if you don't set a limit SQL will try to use all the memory on the system which can cause you to page and have poor performance so use the limits to stop that from happening.

Another good reference for SQL server setup can be found at BrentOzar.com, guides you in some of the basic setup parameters to get the most out of your SQL server.

share|improve this answer

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