Before I start, I am proponent of FIXING MAX SERVER MEMORY always
1. I will first address SQL Server versions before SQL Server 2012. That means if you are using SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 (no I will not include SQL Server 2000)
If you are using SQL Server version before 2012 and your operating system is Windows Server 2008 R2 updated with the latest Service Pack and of course the system is a dedicated system for SQL Server database engine, then you might not need to set max server memory, or you can allow the SQL Server buffer pool to grow dynamically. Please see this link for a list of documented working set trimming issues with various Windows Server editions.
If you are using SQL Server 2005 on windows server 2003, which is not patched to the latest Service Pack, you might be a victim of SQL Server trimming by windows. This Link describes the bug.
There are a lot of third party drivers which SQL Server does not support that might be loaded into SQL Server address space. If you are using linked server query, this might cause memory leaks.
Considering all these bugs and fixes, Microsoft recommends to cap the SQL Server (before 2012) buffer pool memory to the optimum value.
A very classic reason why you should not allow SQL Server to grow memory dynamically:
You my have multiple instances. What if some rogue query starts running on one instance and starts consuming too much memory? All other applications/instances would suffer because of this, which I am sure you would not want.
Consider a two node multi-instance cluster which undergoes a failover and now both instances are on the same node and one starts taking more memory than other. The other would have to suffer and the cluster instance would become extremely slow
If you don't fix max server memory you can be a victim of working set trimming and the OS becoming unresponsive.
Consider a scenario where you are running SQL Server Enterprise, with SQL Server running with the Local System account, or with an account which has the Locked Pages in Memory privilege. You are doomed if some heavy query runs on the system when is already under load. It would try to consume all memory, and since due to LPIM memory cannot be paged out, SQL Server would try to trim as SQLOS will ask it to do. But if memory pressure is grave, it might take some time for SQL Server to react, and in that case OS processes might get paged out and eventually crash, leading to an OS reboot.
2. Starting from SQL Server 2012
Memory code has changed so that a single memory manager is used to allocate memory to both processes which require memory pages less than 8 KB and to processes which requires more than 8 KB. Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any clerk as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a non-SQL Server DLL is not controlled by max server memory.
So again, as I said all processes loaded in SQL Server address space which are not monitored by memory clerks would still take memory outside max server memory. So again you should as a best practice set the optimum value for max server memory.
Indeed, SQL server suddenly used all the assigned memory :
This is pretty much normal and expected, you found out there was low physical memory, you added physical memory and changed max server memory setting now SQL Server can see it has been allowed to use extra memory so it went ahead and grabbed all of it. This would help SQL Server in making things faster when new memory requirements comes because it has already cached memory the new process requiring memory would get that immediately.
I have a good book which say :"Once memory usage has increased beyond the min server memory setting, SQL Server won't release any memory below that amount" - Is the same thing happening here with max server memory ?
Although this is correct but not actually related to what you are seeing. What above says is min server memory is point upto which SQL Server will try to trim down its memory consumption in case of memory pressure what you are seeing is default behavior of SQL Server when more memory is available.
I'm wondering (again) how to measure how much memory SQL is really using. I'm confused with the parameter "Total server memory (KB)" ("this counter measures SQL Server's total buffer pool usage").
If you are using SQL Server 2008 and above easy way to see how much memory SQL Server is using is by querying DMV sys.dm_os_process_memory
select
(physical_memory_in_use_kb/1024)Phy_Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(virtual_address_space_committed_kb/1024 )Total_Memory_UsedBySQLServer_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory
Phy_Memory_usedby_Sqlserver_MB-- Gives total Physical memory used by SQL Server in MB
Total_Memory_UsedBySQLServer_MB-- Gives total memory(RAM+Page file) used by SQL Server in MB
I can also see you are using Task Manager to see SQL Server memory utilization, DON'T use task manager as it will not show correct memory utilized when Locked pages in memory privilege is assigned to SQL Server service account
Total Server Memory: This is simply total memory used by SQL Server BUT this does not tracks ALL memory used and might miss few MB's and this is where DMV comes into picture so always rely on query I have posted.
Now if you see the scale on which you measured Total Server memory is 0.0001 in that case whatever value you are getting you have to multiply it by 10000 to get the value on scale of 1 which is what you need. I cannot see how you got 2 MB can you show it to me.
Long Back I wrote article on SQL Server Memory and Troubleshooting, this should get you started about basics on SQL Server Memory management.
If you are using SQL Server 2012 and above you must read
Leaving enough RAM for OS and ESX in your case is important. If you want to set ideal value for max server memory here is the stackexchange Link
Best Answer
The
System.OutOfMemoryExceptionerror that you are seeing means there was a problem with SQL Server Management Studio, not with the amount of memory on the server itself. One easy way to trigger this error is to simply run aSELECTquery on a large table. If the table is too large then the results grid will take up too much memory and you'll see the error.It's not clear to me why you would experience this error when importing data, but raising max server memory is not the right fix. I recommend trying
BULK INSERTafter setting theBATCHSIZE,KILOBYTES_PER_BATCH, orROW_PER_BATCHarguments. That will limit the amount of data sent in a single transaction. You could also try importing the data through bcp. You don't even need SSMS open for that.