SQL Server Memory Usage – Optimize Performance with Cache and Buffer Pool

buffer-poolcachememoryperformancesql server

How do i check memory usage by my SQL server in production box.
I am using SQL Server 2016.When ever i check task manager,it shows above 90%.
I don't think that is the real memory usage by sql server.

I have a SQL performance tool grafana which shows CPU usage very less than what i see in task manager.
I checked Resource Monitor,there is can see Average CPU value.I am confused as to which is the SQL server memory usage.
I am trying to determine if memory pressure is an issue to some of my problem.

Can someone direct to a good/proper explanation.

Best Answer

Can someone direct to a good/proper explanation.

I would start by saying Task Manager is not a correct place to gauge SQL Server memory consumption, it will not tell you correct value when SQL Server service account has Locked Pages in Memory(LPIM) privilege. This is because normally task manager tracks Process Private bytes which is pageable memory and allocated via VirtualAlloc() function but with Service account having LPIM chunk of memory allocation is done by AWE API which is NON pageable so task manager does not tracks it and this can lead to incorrect value.

It is quite normal for SQL Server to utilize memory allocated to it which often seems like it is using high memory but this is quite normal. Don`t panic if some tool is showing low CPU utilization and task manager is showing high memory this may be just normal. To know how much physical memory SQL Server is using please use below query

select
(physical_memory_in_use_kb/1024)Phy_Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(virtual_address_space_committed_kb/1024 )Total_Memory_UsedBySQLServer_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

Phy_Memory_usedby_Sqlserver_MB-- Gives total Physical memory used by SQL Server in MB Total_Memory_usedBy_SQLServer_MB-- Gives total memory(RAM+Page file) used by SQL Server in MB

To read more about why task manager should not be used see Fun with Locked Pages, AWE, Task Manager, and the Working Set…