Learning Performance Counters : Memory/Available Mbytes and Pages/Sec.

Introduction

I firmly believe  a DBA is only as good as how well he/she knows Performance Counters. I have been wanting to blog/learn on them for a long time and thought would start with the list of important counters as listed in Grant Fritchey/Sagal Dam’s great book - 'SQL Server  2008 Performance Tuning  Distilled'. This book is a bible for troubleshooting query heavy SQL Servers and has a section devoted to baselining using perfmon counters. There are two counters listed as important for monitoring Memory – Available Mbytes and Pages/Sec.

Available Mbytes

Definition: Available Mbytes stands for free unallocated RAM and displays the amount of physical memory, in MB, available to processes running on the computer.

Interpretation

1. This counter only displays the last value and is not an average.
2. If the value is less than 20/25 percent of installed RAM it is an indication of insufficient memory.
3. Less than 100 MB is an indication that the system is very starved for memory and paging out.
4. Fluctuations of 100 MB or more can indicate that someone is logged in remotely into the server.

Pages/Sec

Definition:  Pages/sec is the number of pages read from the disk or written to the disk to resolve memory references to pages that were not in memory at the time of the reference.

Interpretation

1. This is the sum of two counters - Pages Input/sec and Pages Output/sec.
2. The threshold is normally 20 pages/sec, although one has to investigate activity on the server before concluding paging is the problem.
3. Spikes in pages/sec are normal and possible due to backups, big files/data being written to disk and after reboot.

Reads more: Beyond Relational

Posted via email from .NET Info