1 Sp_configure max memory/min memory

In MSSQL Server, the only memory option we can change the are the Minimum server memory and Maximum server memory.  General misconception is this configuration option is how much memory sql server can used. However, it is actually "How much memory SQL Server buffer pool can use”. The default value 2147483647. It is the ridiculous large number which essentially means “unlimited”. Leaving the production environment on this value is not recommend as it is likely lead the SQL Server compete the memory with other processes on the host. In my current working environment, we set to the 80% of the host physical memory ( assuming only one dedicate instance on the host).

EXEC sp_configure 'show advanced option', '1';
reconfigure;
EXEC sp_configure;

Perfmon Counter: Memory Manager Object: Total Server Memory (KB) counter

The memory manage object: Total Server Memory shows how much memory the SQL Server buffer pool is used. This is not the total memory used by SQL Server but only the buffer pool size. We can also get the information from the DM_OS_PERFORMANCE_COUNTERS.

SELECT *

FROM sys.[dm_os_performance_counters]  

where counter_name like 'Total Server%'

 

DBCC MEMORYSTATUS

DBCC MEMORYSTATUS give us more clear picture for what SQL Server memory used allocation.

CREATE TABLE #MEMORYSTATUS (ID [int] IDENTITY(1,1), counterName [varchar] (MAX), value [bigint]);

INSERT INTO #MEMORYSTATUS

EXEC ('DBCC MEMORYSTATUS');

select * from #MEMORYSTATUS;

DROP TABLE #MEMORYSTATUS;

sys.dm_os_memory_clerks

DM_OS_MEMORY_CLERKS returns all memory clerks that are currently active in the SQL Server. Every components that allocates a significant amount of memory must create its own memory clerk. This is the modified version from sqlserverpedia here.

select 

    type,

    sum(virtual_memory_reserved_kb) as [VM Reserved],

    sum(virtual_memory_committed_kb) as [VM Committed],

    sum(awe_allocated_kb) as [AWE Allocated],

    sum(shared_memory_reserved_kb) as [SM Reserved], 

    sum(shared_memory_committed_kb) as [SM Committed],

    sum(multi_pages_kb) as [MultiPage Allocator],

    sum(single_pages_kb) as [SinlgePage Allocator]

from sys.dm_os_memory_clerks 

WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

group by type

 

Reference:

• sys.dm_os_performance_counters : http://msdn.microsoft.com/en-us/library/ms187743.aspx
• Memory Object Preform counter: http://msdn.microsoft.com/en-us/library/ms190924(v=sql.105).aspx
• DBCC MEMORYSTATUS: http://support.microsoft.com/kb/271624
• SQLOS's memory manager and SQL Server's Buffer Pool: http://blogs.msdn.com/b/slavao/archive/2005/02/11/371063.aspx