1

Is there any system catalog views or DMVs that can be used to query (in SSMS for example) below performance counters from SQL Server:Memory Manager ?

Free Memory (KB)
Target Server Memory
Total Server Memory
Maximum Workspace Memory (KB)
Database Cache Memory (KB)
Granted Workspace Memory (KB)
Lock Memory (KB)
Log Pool Memory (KB)
Optimizer Memory (KB)
Connection Memory (KB)
SQL Cache Memory (KB)
Reserved Server Memory (KB)
Stolen Server Memory (KB)
4

The information you seek is available in the DMV sys.dm_os_performance_counters.

select object_name, 
       counter_name, 
       instance_name, 
       cntr_value, 
       cntr_type
  from sys.dm_os_performance_counters
 where 1=1
   and [object_name] = 'SQLServer:Memory Manager'
| improve this answer | |
0

Updated Kevinwhat's query, it will output values in Megabytes in descending order, only those counters that I am interested in:

select
    replace(counter_name,' (KB)','')    [counter_name],
    cntr_value / 1024                   [MB]    
from sys.dm_os_performance_counters
where   [object_name] = 'SQLServer:Memory Manager'
    and counter_name not in ('External benefit of memory','Lock Blocks Allocated','Lock Owner Blocks Allocated','Lock Blocks','Lock Owner Blocks','Memory Grants Outstanding','Memory Grants Pending')
order by [MB] desc
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.