Hallo Dennis,
hiermit kannst Du mal schauen, ob RAM wirklich ein Thema ist:
/*
https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/
What’s Wrong about Page Life Expectancy >= 300?
Aside from being a performance metric that was recommended (1998) by Microsoft that has never been updated to reflect the changes that have occurred in hardware over the last years,
there is nothing wrong with this recommendation (i.e. sarcasm… :-)). The problem with this fixed value is that it was determined when servers generally had 4GB of RAM installed
in them, and servers with 16GB+ of RAM installed in them were extremely expensive and fairly rare.
This performance counter tells you the number of seconds, at the current point in time, a page will remain in memory without being referenced (so a value of 300 means your buffer
pool is flushing every 5 minutes). So 10 years ago when you were reading anywhere from 1.7GB up to 12GB of data (depending on your server’s memory) from disk into the buffer cache
every 5 minutes it was a sign of memory pressure on the server and something you needed to investigate.
Fast forward to today, where it is not uncommon for a SQL Servers to have anywhere from 48-144GB+ RAM installed in them. These RAM values equate to 32-132GB of buffer cache size
depending on the ‘max server memory’ sp_configure option setting and the amount of memory being used for the plan cache, but you probably get the point by now. If reading 1.7GB-12GB
of data every 5 minutes was bad, how bad would it have to be to read 32GB-132GB of data from disk every 5 minutes consistently?
Today the value 300 is ridiculously small as a threshold for when to start worrying about buffer pool pressure.
Now from my own experiences as a DBA and over the past 6 months as a consultant, the I/O subsystem is one of the most undersized components in a majority of SQL Server implementations,
so the last thing I want to be doing is hammering the disks in my server because I relied on a performance counter that was out of date years ago but never updated. For the last 3-4 years
I have relied on the amount of memory being used by the data cache in SQL Server to determine when Page Life Expectancy was a sign of impending problems. This means I replaced the old
300 threshold with a more reasonable value of (DataCacheSizeInGB/4GB *300) as a basis for when to begin investigating things.
Keep in mind that short drops in Page Life Expectancy can be the result of a large query executing and are not a sign of problems in the system (though it may be a sign that you have a tuning opportunity).
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.
https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/
Page Life Expectancy isn’t what you think…
The Buffer Manager:Page Life Expectancy counter is calculated by adding the PLE of each mini buffer pool and then calculating the mean. But it’s not the arithmetic mean as we’ve all thought forever,
it’s the harmonic mean (see Wikipedia here), so the value is lower than the arithmetic mean. (5/11/2015: Thanks to Matt Slocum (b | t) for pointing out a discrepancy from the arithmetic mean on a
large NUMA system and making me dig into this more, and my friend Bob Dorr from CSS for digging into the code.)
-- Beispiel CPU1 5021, CPU2 5038 (geringe Rundungsfehler)
Select (5021 + 5038) / 2 as mean, Round(2 / (1/(1000 * 5021.0) + 1/(1000 * 5038.0) ) / 1000, 0) as harmonic_mean;
-- Beispiel CPU1 3000, CPU2 6000 (deutliche Abweichung der verschiedenen Berechnungsarten)
Select (3000 + 6000) / 2 as mean, Round(2 / (1/(1000 * 3000.0) + 1/(1000 * 6000.0) ) / 1000, 0) as harmonic_mean;
-- Beispiel CPU1 1480, CPU2 9524
Select (1480 + 9524) / 2 as mean, Round(2 / (1/(1000 * 1480.0) + 1/(1000 * 9524.0) ) / 1000, 0) as harmonic_mean;
*/
-- Der Wert 300 Sekunden wäre für 4 GB gut, damit nicht zu viel IO gemacht wird. Berechne jetzt mal, welche Zeiten für den aktuell konfigurierten Speicher akzeptabel wären.
Declare @DataCacheSizeInGB decimal(10,2),
@Nodes smallint;
-- Wieviel Speicher gibt es?
Select @DataCacheSizeInGB = cast(value_in_use as int)/1024.0
from sys.configurations
where name = 'max server memory (MB)';
-- Wieviele NUMA-Nodes gibt es?
SELECT @Nodes = COUNT(*)
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Node%'
AND counter_name = 'Page life expectancy';
-- Kein Max Server Memory eingetragen
If @DataCacheSizeInGB >= 2097152.00
begin
-- Hole den aktuell verwendeten Wert
SELECT @DataCacheSizeInGB = physical_memory_in_use_kb/1024.0/1024.0
FROM sys.dm_os_process_memory WITH (NOLOCK);
end;
-- Da der PLE-Wert für die Instanz der harmonische Mittelwert ist, muss hier auch der verfügbare Speicher durch die Anzahl Nodes geteilt werden
-- Der PLE für die Instanz kann nie größer werden, als der größte PLE eines Nodes
Select @@SERVERNAME as Instance, @Nodes as #Nodes, @DataCacheSizeInGB as Data_Cache_Size_In_GB,
cast(@DataCacheSizeInGB/4*300 as bigint)/ @Nodes as Minimum_Allowed_PLE_For_Instance_or_Node, @DataCacheSizeInGB/4*300/60/@Nodes as Minutes_Pages_Would_Remain_In_Cache;
-- monitor the steady-state value of PLE and react when it dips *and stays* below your steady-state value
-- Aktuellen Wert auslesen
SELECT [object_name], 'All' as Node, cntr_value as PLE_For_Instance_Or_Node, cntr_value/60.0 as Minutes_Pages_Remain_In_Cache,
case when cast(@DataCacheSizeInGB/4*300 as bigint) / @Nodes < cntr_value then 'Okay' else 'Zu klein' end as Status
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND counter_name = 'Page life expectancy'
UNION ALL
-- Pro NUMA-Node wird auch nur der Speicher geteilt durch die Anzahl Nodes berücksichtigt
SELECT [object_name], instance_name as Node, cntr_value as PLE_For_Each_Node, cntr_value/60.0 as Minutes_Pages_Remain_In_Cache_Per_Node,
case when cast(@DataCacheSizeInGB/4*300 as bigint) / @Nodes < cntr_value then 'Okay' else 'Zu klein' end as Status
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Node%'
AND counter_name = 'Page life expectancy'
order by [object_name], Node;
Dann würde ich neben den Indizes (wie bereits angemerkt wurde) auch auf die CPUs schauen.
- Wie viele CPUs hast Du?
- Was ist der "Cost Threshold for Parallelism"? Ich nehme gerne 50.
- Was ist der "Max Degree of Parallelism"? Ich nehme gerne 4 für OLTP oder 6 für OLAP.
- Was unterscheidet die beiden Datenbanken von den anderen? Größe, Indizes, Nutzung?
BTW SQL Server 2012 ist seit 2017 aus dem Mainstream-Support
Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu
