Sample Header Ad - 728x90

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:
--============================================================
--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: enter image description here My question is related to 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