December 20, 2019 at 6:49 pm
When I go on my server the memory usage always ins about 97 - 99%.
Someone told me that the system takes all the memory and allocates it out as needed and that this high % is normal.
Is this correct or is this an indication that we need to do something.
Thank you
December 20, 2019 at 7:57 pm
From my understanding, that is correct, SQL will use all the memory you allow it to use, if you want to know if you're allocating the proper amount there are many queries out there that can provide this information here is one that was one of the first bits of code I ever wrote.. its probably written horribly but it will give you necessary information:
DECLARE @AvgWorkerTime INT,
@BufferCacheHR Decimal(6,3),
@CommitedMemory INT,
@Date SMALLDATETIME,
@MaxMemory INT,
@MinMemory INT,
@Pagelife INT,
@ReservedMemory INT,
@Server VARCHAR(30),
@Target INT
SELECT @Server = CONVERT(varchar(30), SERVERPROPERTY('MachineName'))
BEGIN
--Getting Current Date time in smalldatetime format
SELECT @Date =CONVERT(smalldatetime, getDATE())
--Average Worker Time
SELECT @AvgWorkerTime = AVG(total_worker_time) FROM master.sys.dm_exec_query_stats
--BufferCacheHitRatio
SELECT @BufferCacheHR = (a.cntr_value * 1.0 / b.cntr_value) * 100.0
FROM master.sys.dm_os_performance_counters a
JOIN (SELECT cntr_value, OBJECT_NAME
FROM master.sys.dm_os_performance_counters
WHERE counter_name = 'Buffer Cache Hit Ratio base')
b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer Cache Hit Ratio'
--Maximum and Minimum Server Memory Allocation
SELECT @MaxMemory = CONVERT(INT, value)
FROM master.sys.configurations
WHERE name like '%max server memory%'
ORDER BY name OPTION (RECOMPILE);
SELECT @MinMemory = CONVERT(INT, value)
FROM master.sys.configurations
WHERE name like '%min server memory%'
ORDER BY name OPTION (RECOMPILE);
--PageLife
SELECT @PageLife = cntr_value
FROM master.sys.dm_os_performance_counters
WHERE counter_name LIKE '%Life expectancy%' AND
object_name LIKE '%Buffer Manager%'
--Reserved memory and commited memory
SELECT @ReservedMemory = (virtual_address_space_reserved_kb/1024) FROM master.sys.dm_os_process_memory
SELECT @CommitedMemory = (virtual_address_space_committed_kb/1024) FROM master.sys.dm_os_process_memory
--Target Server Memory
SELECT @Target = (cntr_value/1024)
FROM master.sys.dm_os_performance_counters
WHERE counter_name = 'Target Server Memory (KB)'
SELECT
@Server AS Server,
@Date AS Date,
@AvgWorkerTime AS AverageWorkerTime,
@BufferCacheHR AS BufferCacheHitRate,
@CommitedMemory AS CommittedMemory_MBs,
@MinMemory AS MinimumMemory_MBs,
@MaxMemory AS MaximumMemory_MBs,
@Target AS TargetMemory_MBs,
@ReservedMemory AS ReservedMemory_MBs,
@PageLife AS PageLifeExpectancy
END
and here is an article that talks more about SQL Server Memory and why it behaves the way it does
https://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply