January 9, 2014 at 10:56 am
I'm attempting to configure a calculation to accurately represent a baseline value for Page Life Expectancy based on the amount of memory actually in the BufferCache.
My question, is the Database pages counter the correct counter I should be comparing to?
Other thoughts?
IF OBJECT_ID('tempdb..#stats_PLE') IS NOT NULL
DROP TABLE #stats_PLE
Declare @8k Decimal(25,8)
SET @8k = 8
--We Only get the size of all pages allocated to databases. This is the memory that the Page Life expectancy counter (is)? compared to.
--The adapted minimum is based on the Microsoft recommendation that a page of memory should stay resident for at least 5 minutes on a system with 4GB of RAM
--1 page = 8KB
SELECT (cntr_value * @8k) AS [BufferPoolKB]
,((cntr_value * @8k)/1024) AS [BufferPoolMB]
,(((((cntr_value * @8k)/1024)/1024)/4)*300) AS [AdaptedMinPLE]
,(Select cntr_value from sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND [counter_name] = 'Page life expectancy') AS [PLE]
INTO #stats_PLE
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database pages'
Select * From #stats_PLE
January 9, 2014 at 11:28 am
PLE is not a very useful metric on modern hardware. And the 5 minute guideline is exceptionally inappropriate these days. I would look elsewhere for your baselining/tuning needs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 9, 2014 at 11:35 am
I understand that the old rule of 300 is not appropriate for current hardware, which is why I'm creating an adaptive formula.
January 9, 2014 at 11:36 am
I wouldn't throw the metric out entirely, but rather use it and others with a knowledge of the workload and your environment, to determine if you have a problem.
January 13, 2014 at 10:06 pm
http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/
Jonathan Kehayias says:
Summary: don’t use the value 300 as any kind of Page Life Expectancy threshold. Anyone that continues to recommend doing so is doing you a disservice. Use an adaptive formula like (DataCacheSizeInGB/4GB*300). Even better – monitor the steady-state value of PLE and react when it dips *and stays* below your steady-state value.
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
January 14, 2014 at 6:12 am
Correct, if you look at my query that's what I'm doing. My question is getting an accurate number for the size of the datacache.
January 14, 2014 at 6:26 am
Just use max server memory (and if it's not set, set it to a sensible value)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply