For example, if 16 GB is allocated to SQL Server it should look like this 300 * (16/4) = 1.200. Therefore, one of the most known memory formulas that can be found to calculate your PLE value online is the amount of allocated memory to SQL Server divide by 4 and multiply that number with 300. These days, we can have much more RAM than that and this metric is basically memory dependent. This is an old rule, when SQL Server memory could maximum be 4 GB. You’ll probably read online that it should be more than 300. As for the expected value, this is a tricky one. This is simply because if a request comes in for that page, it can be read from the cache rather than searching on the disk and ultimately reduce I/O. So, essentially, we want the page to stay in the buffer for as long as possible because it will lead to high performance. If I’d have to choose one counter, this metric is the one that all SQL Server monitoring tools should be able to track. It represents the number of seconds a page will stay in the buffer pool without the references. SQLServer:Buffer Manager/Page life expectancy – this is probably the most popular performance counter when it comes to memory in SQL Server. We always want this to be extremely high. I’d say that if that ratio is less than 95% than the server is under memory pressure. SQLServer:Buffer Manager/Buffer cache hit ratio – this represents a percentage of how often SQL Server can find data pages in memory as opposed to fetching them from the disk. The default memory threshold value is 100 MB. Memory available megabytes – this is just a great all around counter especially if we track it for a long time because we can find out what thresholds are for memory. Moving on, let’s take a look at some counters for performance monitoring and SQL Server monitoring tools that can be used to track them: If both flags are zero, you’re in good shape.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |