Tuesday, December 25, 2012

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

image

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

image

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.

image

SELECT *
FROM sys.[dm_os_performance_counters]  
where counter_name like 'Total Server%'

image

 

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;

image

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

image

 

Reference:

1 comments:

  1. Bluehost is ultimately one of the best web-hosting company for any hosting plans you need.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates