Specified SQL Server memory - what does it affect?

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.

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.

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."

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.

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.

<<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

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.