what sql server is holding in the used memory?
0
votes
1
answer
99
views
I have a query that shows me how much memory sql server is using, the settings like max memory and min memory, cpu, etc.
here is the query:
My question is related to
--============================================================
--checking current memory settings and usage
--marcelo miorelli
--16-feb-2014
--============================================================
SELECT R.[instance],
R.[Logical CPU Count],
R. [Hyperthread Ratio],
R.[Physical CPU Count],
R.[Physical Memory (GB)],
k.[Max server memory (GB)],
FOO.Memory_usedby_Sqlserver_MB,
FOO.Locked_pages_used_Sqlserver_MB,
k.[Min server memory (GB)],
k.[optimize for ad hoc workloads],
R.affinity_type_desc,
FOO.process_physical_memory_low,
FOO.process_virtual_memory_low,
R.virtual_machine_type_desc,
R.sqlserver_start_time
FROM (
SELECT [instance]=@@servername,
cpu_count AS [Logical CPU Count],
hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
CONVERT(decimal(12,2),physical_memory_kb/1024.00/1024.00) AS [Physical Memory (GB)],
affinity_type_desc,
virtual_machine_type_desc,
sqlserver_start_time
FROM sys.dm_os_sys_info WITH (NOLOCK)
) R
INNER JOIN (
SELECT [instance] = @@servername,
[Max server memory (GB)] = CONVERT(decimal(12,2),CAST(p.[max server memory (MB)] AS DECIMAL(12,2))/1024.00),
[Min server memory (GB)] = CONVERT(decimal(12,2),CAST(P.[min server memory (MB)] AS DECIMAL(12,2))/1024.00),
[min memory per query (MB)]= CONVERT(decimal(12,2),CAST(P.[min memory per query (KB)] AS DECIMAL(12,2))/1024.00),
p.[optimize for ad hoc workloads]
FROM ( SELECT name, [value_in_use]
FROM sys.configurations) t
PIVOT (MAX([value_in_use])
FOR name IN (
[min server memory (MB)],
[min memory per query (KB)],
[max server memory (MB)],
[optimize for ad hoc workloads]
)) p
) K
ON R.instance = K.instance
INNER JOIN (
SELECT [instance] = @@servername,
CONVERT(decimal(12,2),CAST(physical_memory_in_use_kb AS DECIMAL(12,2))/1024.00) AS Memory_usedby_Sqlserver_MB,
CONVERT(decimal(12,2),CAST(locked_page_allocations_kb AS DECIMAL(12,2))/1024.00) AS Locked_pages_used_Sqlserver_MB,
CONVERT(decimal(18,2),CAST(total_virtual_address_space_kb AS DECIMAL(18,2))/1024.00/1024.00) AS Total_VAS_in_GB,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory t
) FOO
ON R.instance = foo.instance
OPTION (RECOMPILE);
this query gives me this - currently on a not busy dev server:

physical_memory_in_use_kb
and locked_page_allocations_kb
from the dmv sys.dm_os_process_memory
.
How can I find what objects or whatever sql server is holding in that memory?
I can see that in my case there is a minimum memory set to 2 Gb but the memory in use is over 5Gb.
It might as well be that I actually do not need to know as there is probably no memory pressure at the moment, but still, is there a way to find out what sql server is holding in there memory?
Asked by Marcello Miorelli
(17274 rep)
Feb 18, 2024, 07:35 PM
Last activity: Feb 19, 2024, 10:33 AM
Last activity: Feb 19, 2024, 10:33 AM