Sql-server – SQL Server 2008 Max memory setting not taking affect

table_cache is the most useful configuration directive to change. Each time MySQL accesses a table, it loads the table into cache. If you've got a high number of tables, it's faster to have them cached.

Take a look at your server variables by running:

show status;

and have a look for the variable open_tables. If this is the same as your table_cache value, and opened_tables keeps going up, then you need to increase the table_cache value in your configuration file. You'll find a balance by experimenting with these variables during peak times. You want to configure it so that at peak times, there are a low amount of opened_tables even after the server has been up for a long time.

key_buffer_size is also a good variable to experiment with. This variable affects the index buffer size, and making this variable bigger increases MySQL's index handling speed. You can look at the variables with the show variables; command again, and compare key_read_requests to key_reads. Ideally, you want the ratio between these two variables to be as low as possible, and you can do this by increasing the size of the key_buffer_size. If you set this variable higher, you will have less writes and reads directly to and from the disk, which was your main concern.

I found this article today which discusses in detail locked pages, AWE, and 64 bit systems. http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

One of the bullet points (#3) is title "Why is task manager not showing all of the memory allocated for SQL Server"

It's a very nice explanation. In the end the reason this doesn't show up is that sql server has "locked" those pages in memory. Locked pages are NOT part of the working set so they won't appear in Task manager or even appear as part of the applications memory set.

If you run select * from sys.dm_os_process_memory you can see the locked_page_allocations_kb field which will tell you how much RAM sql server has allocated in those pages.