sql server process Kb usage like task manager

Question

Hi all,
I try to check memory usage with some query snippets expose from MS...
it's 99% clear an fine... but the only number the not return to me is this from below...
I try to match OS sqlservr.exe but the query is always a little bit more (now is 536248 K)

SELECT    
 physical_memory_in_use_kb AS Memory_used_by_Sqlserver_KB,    
(physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB,    
(locked_page_allocations_kb/1024) AS Locked_pages_used_by_Sqlserver_MB,    
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
process_physical_memory_low,    
process_virtual_memory_low    
FROM sys.dm_os_process_memory;  

my Task manager is (now obviously)

Why is the performance counter "SQL Server: Memory Manager: Total Server Memory (KB)" [SQL Server Commited - Total, below on 2# image]
always is more of the one related to SQL SERVER process?

"sqlservr.exe" and "commited page"... do I have to think of them as separate numbers?

It's only this point non clear to me...

This is my LAB naturally...
For clarity I repeat again I thought that the process memory (.exe) was equal to the total commited memory

Thanks ALEN

Answer 1

Hi @Alen Cappelletti ,
I think your question is that the number in windows performance counter is not euqal to the number you got by T-SQL. If I misunderstood your problem pls kindly let me know. And I think the point of this problem is the WORKING SET. A Working Set is considered as physical memory that a process can use (but does not necessarily use), which means the memory that can be accessed without causing a page fault exception. A Working Set contains memory that may be shared by other programs. A DLL, for example, is a typical resource that may be shared by other programs. So the Working Set of all processes may add up to more than the actual physical memory.

"sqlservr.exe" and "commited page"... do I have to think of them as separate numbers?

I'm sorry that I don't understand this question very well. But in my opinion I don't think that they are separated cuz I can use the T-SQL to check the number of current committed:

I hope this answer can be useful.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 2

Hi! And thanks for your reply...
thanks for you explain about working set (I hadn't thought about it and this would explain the reason that sometimes the commited server memory is greater than the process memory or rather I understood your explanation like this).

I expose below the scripts with which I did the checks and which brought me this doubt.

ALEN

SELECT  
      (committed_kb)/1048576.0 as BPool_Committed_MB,  
      (committed_target_kb)/1048576.0 as BPool_Commit_Tgt_MB,  
      (visible_target_kb)/1048576.0 as BPool_Visible_MB  
FROM  sys.dm_os_sys_info;  
  
  
select  
      physical_memory_in_use_kb/1048576.0 AS 'physical_memory_in_use (GB)',  
      locked_page_allocations_kb/1048576.0 AS 'locked_page_allocations (GB)',  
      virtual_address_space_committed_kb/1048576.0 AS 'virtual_address_space_committed (GB)',  
      available_commit_limit_kb/1048576.0 AS 'available_commit_limit (GB)',  
      page_fault_count as 'page_fault_count'  
from  sys.dm_os_process_memory;