Specified SQL Server memory - what does it affect?

Vadim Rapp
Vadim Rapp used Ask the Experts™
on
I configure SQL Server 2008 to run with fixed memory size 512MB. I restart the server, and "running values" show 512MB.

Then I open Process Monitor, and SQL Server process is using ~1.5GB virtual memory - see the screenshot. I wonder, where's 512MB in this picture?

If I change the memory values from 512 to some other number, the above numbers don't change. The same is in SQL Server 2000, with the only difference being 1.7GB instead of 1.5. So I wonder, what exactly does the specified memory number affect, and where the result of specifying the memory can be seen?


Capture-10-18-00001.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
It just says in the picture you posted above "virtual size" so that would be your virtual (page file) used memory for caching purpose.

You can execute the following statement in SSMS query and check for "(max/min server memory (MB)" to see your configured values.

exec sp_configure

Please see more details at the link below:
http://www.microsoft.com/whdc/system/platform/server/pae/paedrv.mspx

According to Microsoft, for 32-bit editions of SQL Server, it is important to make sure that the /3GB and /PAE switches are set in accordance with the amount of physical memory in the system startup as follow:

• If you have 3-4 GB memory, include the /3GB switch in the startup.
• If you have 4-8GB memory, use /3GB and /PAE
• If you have 16 GB or more, use only /PAE, as /3GB will cripple memory over 8GB.

• When you have set /PAE, go into SQL Server's configuration and set the option to use AWE to ON. If you do this, however, you need to also specify a maximum memory value in SQL Server, if you do not, then SQL Server will take all but 128MB of the computer's memory if the automatic memory management is used in SQL Server.

• The user who runs the SQL Server needs to have the 'lock pages in memory' user right in the local security policy, or it will have problems allocating the memory for SQL Backup's extended stored procedure. If you have checked everything above, please check this as well.

Thanks for the response, but, I don't see how it is related to my question. I do know how to configure the memory, and how to view the running value. The question is that I don't see any impact of the values configured in sql server on the amount of memory that is actually used by the process.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
SQL limit is physical RAM that can be allocated for SQL namespace.  Virtual memory size is total allocable space which typically includes physical RAM plus pagefile space.  As to the relationship between the two, dunno as this is internally handled by the OS.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

lcohanDatabase Analyst

Commented:
That is how much Memory your SQL Server will be using including physical. I think the amount does not change much overall including from version to version of SQL if the data and the workload is the same but would be significantly different under different workloads(queries). Ideally give SQL as much physical RAM you can depending on what is running on the box and what SQL services you run in parallel on that same box.

http://www.microsoft.com/whdc/system/platform/server/pae/paedrv.mspx

"SQL Server as a process acquires more memory than specified by max server memory option. Both internal and external components can allocate memory outside of the buffer pool, which consumes additional memory, but the memory allocated to the buffer pool usually represents the largest portion of memory consumed by SQL Server."
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
A good article to explain virtual memory as opposed to physical memory...

http://blogs.technet.com/b/markrussinovich/archive/2008/11/17/3155406.aspx
@lcohan: I'm not looking for recommendations about the memory for sql server machine. I'm looking for the explanation of the memory actually consumed by sql server vs. specified in its configuration.

@Racimo: "SQL limit is physical RAM that can be allocated for SQL namespace." - then why if I specified the minimum memory = 512MB, the working set is only 58MB?

I'm pretty much familiar with what virtual memory is - and that's why I don't see any correlation between specification in sql server and actual usage.
lcohanDatabase Analyst

Commented:
I'm just trying to give you an answer - min server memory is different that what you think: it doesn't mean SQL will use MIN 512 MB of memory.

http://msdn.microsoft.com/en-us/library/ms177455.aspx

"The min server memory server configuration option can be used to ensure that SQL Server does not release memory below the configured minimum server memory once that threshold is reached."

so until you use ALL 512MB and ALL MAX memory it won't be released. Maybe I don't know how to explain it better but that's what it IS the difference between MIN and MAX memory configured values in SQL Server.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
<<I'm pretty much familiar with what virtual memory is - and that's why I don't see any correlation between specification in sql server and actual usage.>>
Why should there be one ?  

Virtual Memory is managed by the OS, the SQL memory is simply an indication of the total *physical* memory limit SQL may use as an application.  You may want to read this

http://blogs.technet.com/b/askperf/archive/2007/05/18/sql-and-the-working-set.aspx

HTH
Here's the explanations from Microsoft.

=================
Setting max and min server memory settings in SQL Server 2005/2008 governs the max and min size of the buffer pool for single page allocations. It doesn't include CLR, multi-page allocations, direct Windows allocations, process/thread overhead etc. Therefore I would expect your process working set to be larger than max server memory.
You said in the problem description that you look at the SQL Server process after setting the values. I can't tell from this whether you restarted SQL Server after making the change. You would need to. If min server memory does not appear to be working after a restart please let me know the easiest way to repro this and I'll try it.
=================

(I send the materials showing that min server memory is indeed lower even after the restart)

==================
I have an explanation for why you see the memory reducing when the server is inactive. It relates to the edition you are using, SQL Express, which is tuned for desktop and embedded usage, and is hence aggressive about trimming memory. It does this in order to work well with other applications running on the machine. There is a great article about SQL Express idle time memory usage in this blog article: http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx (this article also details some other interesting facts about Express edition).
So, for SQL Express, min server memory only represents a minimum buffer pool size for an active server. For an inactive SQL Express server the buffer pool can be significantly trimmed.
I will resolve this defect, though let me know if I can provide more info.
===================
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
A bug.
No, it's actually by design. "I will resolve this defect" means that he will close my bug report.

I replied to him however that from my point of view, there's still a bug in that "running values" don't not show the currently occupied memory - which they probably should. Even in non-Express, before the reboot, they should show the currently effective ones. And GUI should tell that new values will become effective only after restart, if that is so.
Direct (and undocumented) explanations from Microsoft.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial