SQL Server – Perfmon – Memory

Background

Wanted to list the Perfmon counters to look at when evaluating SQL Server memory pressure.

Perfmon Metrics

Overview

Depending on the Operating System ( OS) and SQL Server Setting the accuracy of Perfmon Counters will vary.

If the account running the SQL Server Service has been granted “Lock Pages in Memory” Security Policy, then the following metrics do not show the full measure of Memory being used by the SQL Server Process.

Metrics:

  1. Task Manager
    • Private Working Memory
  2. PerfMon
    • Process / MS SQL Server Process  ( sqlservr.exe )
      • Working Set

 

Glossary

Category Counter Description
Paging File
%Usage The amount of the Page File instance in use in percent. See also Process\\Page File Bytes.
%Usage Peak The peak usage of the Page File instance in percent. See also Process\\Page File Bytes Peak.
Process ( sqlservr.exe )
Page Faults/sec  Page Faults/sec is the rate at which page faults by the threads executing in this process are occurring. A page fault occurs when a thread refers to a virtual memory page that is not in its working set in main memory. This may not cause the page to be fetched from disk if it is on the standby list and hence already in main memory, or if it is in use by another process with whom the page is shared.
Page File Bytes  Page File Bytes is the current amount of virtual memory, in bytes, that this process has reserved for use in the paging file(s). Paging files are used to store pages of memory used by the process that are not contained in other files. Paging files are shared by all processes, and the lack of space in paging files can prevent other processes from allocating memory. If there is no paging file, this counter reflects the current amount of virtual memory that the process has reserved for use in physical memory.
Private Bytes  Private Bytes is the current size, in bytes, of memory that this process has allocated that cannot be shared with other processes.
Virtual Bytes  Shows the current size, in bytes, of the virtual address space the process is using. Use of virtual address space does not necessarily imply corresponding use of either disk or main memory pages. Virtual space is finite, and by using too much, the process can limit its ability to load libraries.
Working Set  Shows the current size, in bytes, in the Working Set of this process. The Working Set is the set of memory pages touched recently by the threads in the process. If free memory in the computer is above a threshold, pages are left in the Working Set of a process even if they are not in use. When free memory falls below a threshold, pages are trimmed from Working Sets. If they are needed, they will be soft-faulted back into the Working Set before leaving main memory.
Working Set – Private Working Set – Private displays the size of the working set, in bytes, that is use for this process only and not shared nor sharable by other processes.
SQL Server :- Memory Manager
Stolen Server Memory Amount of memory the server is currently using for the purposes other than the database pages.
Target Server Memory Ideal amount of memory the server is willing to consume
Total Server Memory Total amount of dynamic memory the server is currently consuming

 

 

Screen Shot

DB Server – 01

 

Metric

  1. Process :- MS SQL Server
    • IO
      • IO Data Bytes :- 1020
      • IO Other Bytes : – 370, 900
      • IO Read Bytes :- 0
      • IO Write Bytes :- 1020
    • Memory
      • Page Faults
        • Page Faults/Sec :- 2
      • Page Files
        • Page Files Bytes :- 53 GB bytes
        • Page Files Bytes Peak :- 53 GB bytes
      • Private Bytes
        • Private Bytes :- 53 GB bytes
      • Thread Count
        • Thread Count :- 138
      • Virtual Bytes
        • Virtual Bytes :- 95 million bytes
        • Virtual Bytes Peak :- 95 million bytes
      • Working Set
        • Working Set :- 1.128 Million bytes
        • Working Set – Private :- 1.060 Million bytes
        • Working Set – Peak :- 1.147 Million bytes
  2. SQL Server :- Memory Manager
    • Stolen Memory
      • Stolen Memory ( KB )
        • 8 GB
      • Target Memory ( KB )
        • 58.789 GB
      • Total  Memory ( KB )
        • 51.286 GB

Explanation

  1. Process – MS SQL Server
    • IO
      • Data
        • 1020
      • Other
        • 370 K
      • Read
        • 0
      • Write
        • 1020
    • Memory
      • Pages Files
        • 53 GB
      • Private
        • 53 GB
      • Virtual Bytes
        • 95 GB
          • Only Got knows what this means???
      • Working Set
        • Only shows memory absolved from lowest 4 GB
  2. SQL Server :- Memory Manager
    • Experiencing Stolen Memory of about 8 GB
    • Our Total Memory usage is about 8 GB below Target

 

DB Server – 02

Image

 

Metric

  1. Process
    • IO
      • IO Data Bytes :- 4800
      • IO Other Bytes : – 22,000
      • IO Read Bytes :- 0
      • IO Write Bytes :- 4800
    • Memory
      • Page Faults
        • Page Faults/Sec :- 1
      • Page Files
        • Page Files Bytes :- 27 GB bytes
        • Page Files Bytes Peak :- 27 GB bytes
      • Private Bytes
        • Private Bytes :- 27 GB bytes
      • Thread Count
        • Thread Count :- 104
      • Virtual Bytes
        • Virtual Bytes :- 87 GB
        • Virtual Bytes Peak :- 88 GB
      • Working Set
        • Working Set :- 454 MB
        • Working Set – Private :- 383 MB
        • Working Set – Peak :- 465 MB
  2. SQL Server : Memory Manager
    • Stolen Memory (KB)
      • Stolen Memory (KB) :-  5 GB
    • Target Server Memory
      • Target Server Memory (KB) :-  26 GB
    • Total Server Memory
      • Total Server Memory ( KB) :- 26 GB

Explanation

  1. Process :- SQL Server
    • IO
      • Data
        • 5 K
      • Other
        • 21 K
      • Read
        • 0
      • Write
        • 4 K
    • Memory
      • Pages Files
        • 53 GB
      • Private
        • 53 GB
      • Virtual Bytes
        • 95 GB
          • Only Got knows what this means???
      • Working Set
        • Only shows memory absolved from lowest 4 GB
  2. SQL Server :- Memory Manager
    • Stolen Memory
      • Stolen Memory ( KB )
        • 5 GB
      • Target Memory ( KB )
        • 26 GB
      • Total  Memory ( KB )
        • 26 GB

 

DB Server – 03

Image

Image – #01

Image – #02

 

Metric

  1. Process
    • IO
      • IO Data Bytes :- 251 KB
      • IO Other Bytes : – 362 KB
      • IO Read Bytes :- 24 KB
      • IO Write Bytes :- 226 KB
    • Memory
      • Page Faults
        • Page Faults/Sec :- 28
      • Page Files
        • Page Files Bytes :- 19.716 GB bytes
        • Page Files Bytes Peak :- 21.229 GB bytes
      • Private Bytes
        • Private Bytes :- 19.716 GB bytes
      • Thread Count
        • Thread Count :- 123
      • Virtual Bytes
        • Virtual Bytes :- 26.415 GB
        • Virtual Bytes Peak :- 27 GB
      • Working Set
        • Working Set :- 19 GB
        • Working Set – Private :- 19 GB
        • Working Set – Peak :- 20.5 GB
  2. SQL Server : Memory Manager
    • Stolen Memory (KB)
      • Stolen Memory (KB) :-  
    • Target Server Memory
      • Target Server Memory (KB) :-  18.43 GB
    • Total Server Memory
      • Total Server Memory ( KB) :- 18.43 GB

Explanation

  1. Process :- SQL Server
    • IO
      • Data
        • 251 K
      • Other
        • 362 K
      • Read
        • 24 K
      • Write
        • 226 K
    • Memory
      • Pages Files
        • 19.716 GB
      • Private
        • 19.716 GB
      • Virtual Bytes
        • 26.940 GB
          • Only Got knows what this means???
      • Working Set
        • 19 GB
  2. SQL Server :- Memory Manager
    • Stolen Memory
      • Stolen Memory ( KB )
        • N/A
      • Target Memory ( KB )
        • 18.432 GB
      • Total  Memory ( KB )
        • 18.432 GB

Summary

How memory is acquired and tallied depends on whether the Account running SQL Server has been granted “Lock pages in Memory“.

In later editions of SQL Server, there are more memory performance counters such as :-

  1. SQL Server :- Memory Manager
    • Stolen Memory

Coincidentally, our third sample has “Locked Pages in Memory” off for the Account running SQL Server.  And, hence, it accurately reflects Working Set numbers.

 

References

  1. Microsoft
    • Developer Network
      • Job Object Details
        Link
    • Technet
      • Mark Russinovich
        • Pushing the Limits of Windows: Paged and Nonpaged Pool
          Link
  2. Perforce
    • Troubleshooting Performance Issues – Windows
      Link
  3. SQL Shack
    • Milena Petrovic
      • SQL Server memory performance metrics – Part 2 – available bytes, total server, and target server memory
      • Link
  4. Public MPWiki
    • Management Pack : Systems Management Server ( SMS )
      • SMS 2003 Perf Threshold: Paging File – %Usage > 98 over 3 hours Monitor
        Link

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s