gotchasqlModerate
Why does SQL Server 2012 Express use 9.5GB of RAM on my server?
Viewed 0 times
whyexpress2012sql5gbdoesserveruseram
Problem
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
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
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
My 9.5GB number came from the "Private Working Set" number in Task Manager. The first table of output from
Output from spaghettidba's suggested query on
```
MEMORYCLERK_SQLBUFFERPOOL 1410
OBJECTSTORE_LOCK_MANAGER 256
MEMORYCLERK_SQLCLR 38
MEMORYCLERK_SOSNODE 2
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 (Build 7601: Service Pack 1)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 0Output from spaghettidba's suggested query on
sys.dm_os_memory_clerks:```
MEMORYCLERK_SQLBUFFERPOOL 1410
OBJECTSTORE_LOCK_MANAGER 256
MEMORYCLERK_SQLCLR 38
MEMORYCLERK_SOSNODE 2
Solution
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:
Hope this helps
To find out memory usage per memory clerk, run this:
SELECT type, SUM(pages_kb)/1024 AS MemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESCHope this helps
Code Snippets
SELECT type, SUM(pages_kb)/1024 AS MemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESCContext
StackExchange Database Administrators Q#33088, answer score: 12
Revisions (0)
No revisions yet.