Performance Analysis for SQL Server is showing the memory utilisation incorrectly on our server m (4274444)

Performance Analysis for SQL Server (PA) seems to be showing the memory utilisation incorrectly on our server machine with AWE memory. The details for AWE is showing as other rather than as SQL Server memory usage.

Performance Analysis for SQl Server shows the AWE memory utilisation as on the basis that Non SQL Server Resident Memory Usage (% CPU Busy) is defined as overall RAM consumption not associated with the monitored SQL Server instance (background and foreground processes).

From the PA perspective, we show only memory consumption by the SQL Server physical process as SQL Server memory in PA. Thus any non-transaction related work initiated by DB is possibly handed over to OS processes and the memory consumed by such processes will be shown under non-SQLServer Memory usage which is what we are showing within PA.

We get the memory utilisation figure by running the following query with the SQL Server instance:

select object_name,counter_name,CAST(cntr_value as decimal(11,2))/1024 MB from sys.dm_os_performance_counters where counter_name = Total Server Memory (KB)


The above SQL is showing the memory utilisation if your SQL Server instance is SQL2005 or if your SQL Server instance is SQL2000 you may want to run the query below:

select *
from master..sysperfinfo
where counter_name = Total pages
object_name
counter_name
instance_name
cntr_value cntr_type

You will find that both queries will give the exact figure that we are showing within Real Time within the PA GUI for memory utilisation.

PA mayshow a high non-SQL Server memory consumption value whileTask Manager indicates that there are no other applications running on system that are taking up a lot ofmemory. What is thereforeconsuming high memory? Can it be SQL Server process? And the answer is yes. Two reasons for this: (a) Total SQL Server memory is a dynamic metric and this shows the memory SQL server process is using at any given time so it is the SQL Server process consuming high memory.

The next logical question is why does it not show up in SQL Server resident memory usage?
This is becauseon Windowx x64 machines, theOS is capable of allocating additional memory via locked pages to SQL Server process. This added memory does not show up as being consumed by SQL Server.exe process and hence PA shows a low value for the actual SQL Server resident memory usage. But based on the high value for the metric Total SQL Server memory,it can besafely concluded thatthe SQL Server processis consuming more memory than what theTask Manager shows. Since PA sees this high memory consumption outside of sqlserver process, it will show as non-SQL Server memory.

Thus, rely on SQL Server Total Memory metric to derive the real memory consumption by SQL Server process.