Total server memory specifies the amount of memory the server has committed using the memory manager. Target Server Memory Indicates the ideal amount of memory the server can consume.
Total Server Memory divided by Target Server Memory ratio should be close to 1. If the Total Server Memory value is significantly lower than the Target Server Memory value, it can mean that there’s memory pressure on the server.
Upon staring SQL Server its total memory will be low and it will grow throughout the warm-up period while SQL Server is bringing pages into its buffer pool and until it reaches a steady state. When this steady state is reached the total server memory will ideally be around the value of the target memory.
Note : Total Server Memory Performance Counter is also not the total memory used by SQL Server because it shows only the memory allocation through Buffer Pool.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @Total as Float DECLARE @Target as Float DECLARE @Ratio as Float DECLARE @Min as Float DECLARE @Max as Float SET @Total=(SELECT ROUND(((CAST([cntr_value] AS float )/1024)/1024),2)FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] = 'Total Server Memory (KB)') SET @Target=(SELECT ROUND(((CAST([cntr_value] AS float )/1024)/1024),2) FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] ='Target Server Memory (KB)') SET @Ratio=(SELECT ROUND(100.0 * ( SELECT CAST([cntr_value] AS FLOAT) FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] = 'Total Server Memory (KB)' ) / ( SELECT CAST([cntr_value] AS FLOAT) FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Memory Manager%' AND [counter_name] = 'Target Server Memory (KB)') , 2)) SET @Min =(SELECT CAST(value AS float )/1024 FROM sys.configurations WHERE name ='min server memory (MB)') SET @Max =(SELECT CAST(value AS float )/1024 FROM sys.configurations WHERE name ='max server memory (MB)') SELECT @Min AS [Min server memory (GB)], @Max AS [Max server memory (GB)], @Total AS [Total Server Memory (GB) in use],@Target AS [Target Server Memory(GB) in use],ROUND(@Total/@Target,4)*100 AS [Ratio (Total/Target)] |
Leave a Comment