Need help?

Our experts have had an average response time of 12.14 minutes in September 2021 to fix urgent issues.

We will keep your servers stable, secure, and fast at all times for one fixed price.

Perfmon Counters for memory usage – Top perfmon counters

by | Feb 23, 2021

Wondering how to use Perfmon Counters for memory usage? We can help you.

Memory is one of the important system resources that SQL Server uses to process their transactions. Sometimes, the server will face memory issues which in turn reduces the performance of running transactions.

We can use Windows PerfMon utility to gather current values of all given performance counters. This will help us to get a better understanding of the memory usage in the server.

Here a Bobcares, we use perfmon counters as a part of our Server Management System for our customers using SQL.

Today let’s see some top performance counters to identify SQL Server Memory pressure.

Perfmon Counters for Memory Usage – Counters Available

We can run the below command to get all performance counters and their respective values using DMV sys.dm_os_performance_counters.

Select * from sys.dm_os_performance_counters

By default, SQL Server changes its memory requirements dynamically, on the basis of available system resources.

 Some commonly used Perfmon Counters for memory usage:

1. Memory: Available Bytes

We can check the available memory with the help of counter Available Bytes under object Memory.

The low value of this counter shows the memory shortage on the server.

We can use this information to and ensure that memory bottlenecks do not happen in SQL Server.

2. Memory: Pages/sec

This counter indicates the total number of pages that are either read from the disk or written to the disk due to page fault.

When a process requests a data page that is not in memory, it will be taken from the disk to memory. This process is known as a page fault.

The high value of this counter could indicate excessive paging. The average Pages/sec value should be below 50. A high value of Pages/sec indicates intensive memory activities like reading from and writing to disk.

It means transactions are getting enough memory that is why they are using disk to process their requests.

3. Memory: Page Faults/sec

The high value of counter Memory: Pages/sec could indicate excessive paging. Next, we will monitor the Memory: Page Faults/sec counter to ensure that the disk activity is not because of paging.

This will give an exact picture of whether SQL Server is causing this paging issue or not.

4. Process: Working Set

Process: Working Set counter shows the amount of memory a process can use. This counter value shows whether we have set optimum value in min server memory and max server memory configuration or not.

If this value shows constantly below min server memory and max server memory then we can understand that we have set too much memory for the SQL Server.

5. SQL Server: Buffer Manager: Buffer Cache Hit Ratio

It is an important counter which shows the percentages of using data cache. If the value of buffer cache hit ratio for our server is 94 it means 94% of total requests for data have been processed using data cache.

The ideal value for this counter is more than 90. Higher the value better the memory health.

6. SQL Server: Buffer Manager: Page Life Expectancy

Page Life Expectancy is also an important counter for measuring memory pressure for the system. This counter indicates the average number of seconds in which a data page remains in the cache.

The ideal value is more than 300. But It is best to compare this value with the system configuration. Higher the value better the memory health.

7. SQL Server: Buffer Manager: Free List Stalls/sec

This counter helps to identify a sign of memory pressure or insufficient memory in the server. It displays the number of requests per second where data requests stall as there are no buffers available. Any value greater than two indicates that SQL Server needs more memory.

8. SQL Server: Buffer Manager: Lazy Writes/sec

This counter shows us the total number of Lazy writes performed in a second. It means the total number of times the Lazy Writer process moves dirty pages from the buffer to disk.

If the value of this counter shows a higher value continuously more than 20, we will have to increase the memory to the system or optimize memory-intensive operations.

9. SQL Server: Buffer Manager: Checkpoint Pages/sec

This counter monitors the number of dirty pages per second, which are flushed to disk when SQL Server invokes the checkpoint process. High values for this counter indicate memory pressure or insufficient memory.

10. SQL Server: Memory Manager: Memory Grants Pending

Generally counter shows the number of processes waiting for memory grants per second. A value higher than zero indicates a lack of memory.

11. Memory: Paging File(_Total)\% Usage

The percentage of the system page file that is currently in use can be seen with this counter.

12. Memory: Cache Bytes

The Memory pages that the System uses are split into two main counters, Cache Bytes and Pool Nonpaged Bytes.

The Cache Bytes counter shows the number of resident pages allocated in RAM that the Kernel threads can address without causing a Page Fault.

13. Memory: Pool Nonpaged Bytes

The Pool Nonpaged Bytes counter shows the number of resident pages in RAM that the Kernel is using that cannot be paged out.

14. Memory: Page Reads/sec

The Page Reads/sec is another counter to watch for memory issues. This counter is a complement to the Pages/sec counter since it shows how much the paging operations affect the disk.

15. Memory: Page Writes/sec

The Page Writes/sec is another counter to watch for memory issues. This counter is a complement to the Pages/sec counter since it shows how much the paging operations affect the disk.

[Need Assistance? We are happy to help you]

Conclusion

In short, we saw how to use Perfmon Counters for memory usage. Also, we saw some commonly used performance counters which our Support Engineers use to identify SQL server memory pressure.

 

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF