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 am building an application in which I plan to embed SQL Server 2012 Express as the primary datastore. When testing on my development machine (Win7-32 with 3GB RAM), I never observed the sqlservr.exe process to use more than 1GB of RAM as I would expect from the published hardware scaling limits for the Express edition of SQL Server.

I then moved my application to a server-grade machine (Win Server 2008R2 64-bit with 16GB RAM) to evaluate its performance there and was surprised to find that the sqlservr.exe process rapidly expanded to approximately 9.5GB of RAM and stayed there. I restarted it a couple times to see if that would have any effect, but each time, the process rapidly returned to ~9.5GB. Now I am certainly pleased to have SQL Server Express make use of my RAM, but I would like to know if this is expected behavior so that I do not come to rely on performance levels that are based on RAM usage that is incorrect.

FYI, the version of SQL Server on my server machine, according to SELECT @@VERSION, is:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Edit: my 9.5GB number came from the "Private Working Set" number in Task Manager. The first table of output from DBCC memorystatus (albeit on a now idle server) is below:

PROCESS/SYSTEM COUNTS
Available Physical Memory   5543616512
Available Virtual Memory    8734902411264
Available Paging File       22471094272
Working Set                 9664200704
Percent of Committed Memory in WS   99
Page Faults                 2627510
System physical memory high 1
System physical memory low  0
Process physical memory low 0
Process virtual memory low  0

Output from spaghettidba's suggested query on sys.dm_os_memory_clerks:

MEMORYCLERK_SQLBUFFERPOOL   1410
OBJECTSTORE_LOCK_MANAGER    256
MEMORYCLERK_SQLCLR          38
MEMORYCLERK_SOSNODE         26
CACHESTORE_SQLCP            11

plus ~10 more smaller entries that sum to less than 30MB.

share|improve this question
3  
Might be interesting if you posted the output of DBCC MEMORYSTATUS –  Martin Smith Jan 17 '13 at 15:45
3  
And how are you measuring that sqlservr.exe is taking 9.5 GB of RAM? Task Manager? Can you show a screen shot of that and what Process Explorer and/or perfmon (SQLServer:Memory Manager:Total Server Memory (KB)) says? –  Aaron Bertrand Jan 17 '13 at 15:47
2  
From DBCC MEMORYSTATUS please point first the section with the header: Process/System counts - Value. –  Marian Jan 17 '13 at 15:51
    
Either it's a bug, or they're upping the limit and it isn't documented yet. Can you go past 10 GB? Here's my test on Win7 x86: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (Intel X86) Oct 19 2012 13:43:21 Copyright (c) Microsoft Corporation Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) Working Set 1631543296 –  Jon Seigel Jan 17 '13 at 19:22
3  
@JonSeigel, if it's a bug, I'm going to have to make sure I keep an un-patched copy of this SQL Express build around for my application! –  Dan Jan 17 '13 at 21:15
add comment

1 Answer

SQL Express is limited to 1GB for the Buffer Pool, but there are many other memory pools in SQL Server. What I find surprising is the excessive use of non-buffer memory pools. To find out memory usage per memory clerk, run this:

SELECT type, SUM(single_pages_kb)/1024 AS MemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC

Hope this helps

share|improve this answer
    
My test involved running a single query about 200,000 times, so I'm not sure what other 1/2 GB of memory it would be using. Maybe there's a memory leak or something. –  Jon Seigel Jan 18 '13 at 14:36
    
The output from that query is: MEMORYCLERK_SQLBUFFERPOOL 1410 OBJECTSTORE_LOCK_MANAGER 256 MEMORYCLERK_SQLCLR 38 MEMORYCLERK_SOSNODE 26 CACHESTORE_SQLCP 11 And then a few more small ones that sum to <30MB. Seems strange to me. -arg- at the comment formatting. –  Dan Jan 18 '13 at 15:03
    
Hmmm. MEMORYCLERK_SQLBUFFERPOOL alone goes beyond the 1GB limit, so we might be facing a bug. What I find weird is how it's using 9.5GB without it showing in the query. –  spaghettidba Jan 18 '13 at 15:24
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.