SQL Server - Memory Dangerously Low or Max Memory Too High

By default, SQL Server’s max memory is 2147483647 – a heck of a lot more than you actually have. Trivia time – that’s the max number for a signed 32-bit integer. SQL Server will just keep using more and more memory until there’s none left on the system. If the operating system has no memory available, it will start using the page file instead of RAM. Using the page file in place of memory will result in poor system performance – operations that should be fast and in memory will read and write to disk constantly.


/********************************************************** 
*   top procedures memory consumption total 
*   (this will show more operational procedures) 
***********************************************************/ 
SELECT TOP 100 * 
FROM
( 
    SELECT
         DatabaseName       = DB_NAME(qt.dbid) 
        ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) 
        ,DiskReads          = SUM(qs.total_physical_reads)   -- The worst reads, disk reads 
        ,MemoryReads        = SUM(qs.total_logical_reads)    --Logical Reads are memory reads 
        ,Total_IO_Reads     = SUM(qs.total_physical_reads + qs.total_logical_reads) 
        ,Executions         = SUM(qs.execution_count) 
        ,IO_Per_Execution   = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) 
        ,CPUTime            = SUM(qs.total_worker_time) 
        ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) 
        ,MemoryWrites       = SUM(qs.max_logical_writes) 
        ,DateLastExecuted   = MAX(qs.last_execution_time) 
 
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
    GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) 
) T 
ORDER BY Total_IO_Reads DESC



Microsoft: 
If the same value is specified for both min server memory and max server memory, then once the memory allocated to the SQL Server Database Engine reaches that value, the SQL Server Database Engine stops dynamically freeing and acquiring memory for the buffer pool.

Fix:

1) Decide what to set your max server memory (MB) to. Our simple “starter” rule of thumb is to leave 4GB or 10% of total memory free, whichever is LARGER on your instance to start with, and adjust this as needed.

2) Execute the change. We’ve got detailed steps on how execute your change below. This does not require restarting your SQL Server instance, but it will cause execution plans to recompile.

Option 1: Use THE GUI

Right click!

Once you get to the properties…

This is 12GB. Only use this number if you are also on a laptop.

Remember that 1GB is 1024MB, so calculate accordingly

Command Line

GB	MB	Recommended Setting	Command
16	16384	12288	EXEC sys.sp_configure ‘max server memory (MB)’, ‘12288’; RECONFIGURE;
32	32768	29491	EXEC sys.sp_configure ‘max server memory (MB)’, ‘29491’; RECONFIGURE;
64	65536	58982	EXEC sys.sp_configure ‘max server memory (MB)’, ‘58982’; RECONFIGURE;
128	131072	117964	EXEC sys.sp_configure ‘max server memory (MB)’, ‘117964’; RECONFIGURE;
256	262144	235929	EXEC sys.sp_configure ‘max server memory (MB)’, ‘235929’; RECONFIGURE;
512	524288	471859	EXEC sys.sp_configure ‘max server memory (MB)’, ‘471859’; RECONFIGURE;
1024	1048576	943718	EXEC sys.sp_configure ‘max server memory (MB)’, ‘943718’; RECONFIGURE;
2048	2097152	1887436	EXEC sys.sp_configure ‘max server memory (MB)’, ‘1887436’; RECONFIGURE;
4096	4194304	3774873	EXEC sys.sp_configure’max server memory (MB)’, ‘3774873’; RECONFIGURE;