0

I have a performance monitoring running on Server. It recorded several counters from SQL Server, Server, Web Service Performance, Web Service Cache Performance, IIS Performance, and ASP.NET Performance. After seeing the results I can't decide whether the Server is running normal or not, because I haven't found the threshold documentation which cover all of counters from Performance Monitoring. Does any of you could give me some references ?

Any kind of help are really appreciated. Thank you

Arina
  • 70
  • 7

1 Answers1

3

Here is the breakdown of top SQL Server memory pressure counters followed by the threshold guidelines:

  • Page Life Expectancy - you can calculate the threshold based on this formula MAXBP(MB)/1024/4*300, where MAXBP represents the maximum amount of Buffer Pool memory
  • Buffer Cache hit ratio - this ratio needs to be as high as possible, as reading from memory is several orders of magnitude faster than reading from disks
  • Page reads / sec - no general guidelines
  • Page writes / sec - no general guidelines
  • Lazy writes / sec - shouldn’t exceed 20 on average systems
  • Memory Grants Pending - should always be less or equal to 1
  • Total Server Memory - should be at around 100%
  • Target Server Memory - equal to the current value of Max Server Memory setting
  • Available Mbytes - it shouldn’t drop below 2 to 4GB for today’s average production servers
  • Pages / sec - there is a wide range of acceptable values, and ideally this number would tend toward zero
  • Paging File % Usage - guideline on the values for this measurements, especially for SQL Server dedicated machines, is 0

I found these by reading an article on Top SQL Server Memory Pressure counters https://solutioncenter.apexsql.com/top-sql-server-memory-pressure-counters/ - there is a reference section at the bottom of this article if you'd like to learn more about counters and their desired values.

Turner.J
  • 31
  • 4