Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a problem with my SQL Server 2012 installation on my Windows 2008 R2 x64 box.

(heads up: I have no idea what I'm talking about...I'm in over my head, since I have no real DBA experience/training)

Because my memory seemed to be rapidly disappearing (according to the Task Manager graph), but I couldn't see any programs using it, I downloaded RAMMap to get a better picture of what's going on. Turns out all of my memory was being chewed up by AWE.

My memory gets eaten up by SQL Server fairly quickly, up to the maximum that I allow the instance to have (currently 3GB).

When I try to research this, however, everyone seems to say that AWE isn't enabled for Sql Server 2012 for 64 bit systems, since it isn't needed; the expanded memory that AWE would enable is directly accessible. But the system still will happily chew up memory in AWE until it reaches its limit.

Not sure what other information would be useful to help diagnose this...or even if I'm totally misinterpreting something...I'm at the limits of my current knowledge about this now. Ask any questions in the comments, and I'll try to update the question. (Please don't assume a lot of knowledge on my part...while I am tech savvy in general, I am absolutely not a DBA by any stretch.)

share|improve this question
    
what is your max memory set to and what is the physical memory on the server itself ? Any workload running ? –  Kin Aug 21 '13 at 21:39
    
@Kin Max memory for SQL server is set to 3GB. Physical memory on the Server is 10GB. Somewhat unusually, it is not really being used as a server, but as a development machine, so the DB workload tends to be quite light (though I say that without any hard metrics). –  Beska Aug 22 '13 at 2:14
    
that is very low. Sql server will need more memory. Try allocating 6GB (if possible) and see if it still causes problems. You can even run perfmon memory counters ti chech what is consuming more memory. –  Kin Aug 22 '13 at 2:29
    
I cannot give 6GB...3 is pushing it a bit. (I have a virtual machine that takes up 4GB, Visual Studio, Outlook, IIS, etc.) –  Beska Aug 22 '13 at 12:29
    
Interesting .. So you are not just running sql server, you are running Microsoft Family on the same server and then experiencing memory issues. Refer to (technet.microsoft.com/en-us/library/ms143506.aspx) to see the memory requirements ..**Minimum recommended is 4GB**. + you are running other stuff like Visual Studio, Outlook, IIS, etc (many more). –  Kin Aug 22 '13 at 13:03
show 3 more comments

1 Answer 1

SQL Server is using the AWE memory allocation API to allocate buffer pool memory if "lock pages in memory" is enabled.

If SQL Server's max server memory setting is not set, SQL Server will eventually use as much memory as it can get. That will largely show up as AWE memory in RAMMap. A little more detail can be found here and all the SQL Server memory settings are explained here.

You should set the max server memory setting to allow an adequate amount for the operating system. If not enough memory is available for the OS, paging can occur and cause performance issues. A good starting point is 3 to 4 GB reserved for the OS, but watch the appropriate counters to see if the OS is under memory pressure.

You can get more information about how to select the right max server memory setting at the following links:

http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory
http://blogs.msdn.com/b/sqlsakthi/archive/2011/03/12/importance-of-setting-max-server-memory-in-sql-server-and-how-to-set-it.aspx

Full details of how SQL Server uses AWE and Locked Pages can be found in the Microsoft CSS SQL Server Engineers article, "Fun with Locked Pages, AWE, Task Manager, and the Working Set".

share|improve this answer
    
Thanks...I'll go through this tomorrow, and evaluate...but I suspect this will be quite helpful. –  Beska Aug 22 '13 at 2:16
    
This statement is misleading: "If SQL Server's ma memory setting is not set, SQL Server will reserve as much memory as it can get." That's not what SQL Server does, it won't try to acquire memory unless there is a need to do so. –  SQLRockstar Aug 22 '13 at 3:44
    
@SQLRockstar that has also been my experience. Obviously, if there are no restrictions, and you ask a lot, it will eventually consume a lot of memory. But I've found that my memory will shoot up after some (seemingly) very simple stuff... –  Beska Aug 22 '13 at 12:27
    
@SQLRockstar, you are absolutely correct. However, unless your database is significantly smaller than your memory, SQL Server will take up all memory it can get eventually. –  Sebastian Meine Aug 22 '13 at 16:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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