Co mówi długość życia strony o instancji?

Zainstalowałem oprogramowanie monitorujące w kilku instancjach SQL Server w środowisku. Próbuję znaleźć wąskie gardła i naprawić niektóre problemy z wydajnością. Chcę się dowiedzieć, czy niektóre serwery potrzebują więcej pamięci.

Interesuje mnie jeden licznik: oczekiwana długość życia strony. Wygląda inaczej na każdej maszynie. Dlaczego często się zmienia w niektórych przypadkach i co to oznacza?

Proszę spojrzeć na dane z ostatniego tygodnia zebrane na kilku różnych maszynach. Co możesz powiedzieć o każdej instancji?

Mocno używane wystąpienie produkcyjne (1):

Umiarkowanie używana istota produkcyjna (2)

Rzadko używana instancja testowa (3)

Mocno używane wystąpienie produkcyjne (4)

Średnio używana instancja testowa (5)

Mocno używana hurtownia danych (6)

EDYCJA: Dodam dane wyjściowe SELECT @@ VERSION dla wszystkich tych serwerów:

Instance 1: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


Instance 2: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
Oct 19 2012 13:38:57 
Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


Instance 3: Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
May 14 2014 18:34:29 
    Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 4: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 
Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


Instance 5: Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
May 14 2014 18:34:29 
Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Instance 6: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
Apr 2 2010 15:48:46 
Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Uruchomiłem również następujące zapytanie na komputerach:

SELECT DISTINCT memory_node_id
FROM sys.dm_os_memory_clerks

i zwrócił 2 lub 3 wiersze dla każdego serwera:

Instance 1: 0; 64; 1
Instance 2: 0; 64
Instance 3: 0; 64
Instance 4: 0; 64
Instance 5: 0; 64
Instance 6: 0; 64; 1

Co to znaczy? Czy na tych serwerach działa NUMA?

Zaczerpnięte z MSDN: - https://msdn.microsoft.com/en-us/library/ms189628.aspx

Oczekiwana długość życia strony - wskazuje liczbę sekund, przez jaką strona pozostanie w puli buforów bez odniesień.

SQL zawsze szuka stron danych w pamięci. Jeśli strona danych nie znajduje się w pamięci, SQL będzie musiał przejść na dysk (wykonać fizyczną operację We / Wy) w celu pobrania danych potrzebnych do spełnienia żądania. Jeśli licznik PLE jest niski, oznacza to, że strony danych w pamięci są regularnie zastępowane nowymi stronami pochodzącymi z fizycznych operacji we / wy. Fizyczne operacje we / wy są kosztowne, co oznacza, że ​​negatywnie wpłynie to na wydajność instancji SQL. Dlatego chcesz, aby twój licznik PLE był jak najwyższy.

Zignoruj ​​wszelkie porady, które widzisz w Internecie, które wspominają 300 jako dobry próg dla tego licznika

Ten próg pochodzi z dni, w których pamięć była ograniczona (pomyśl systemy 32-bitowe). Teraz mamy 64-bitowe systemy, które mogą mieć TB pamięci RAM, więc ta rada jest bardzo nieaktualna.

Po pierwsze, czy ograniczyłeś pamięć SQL? Jeśli tak, ile pozostało dostępnej pamięci? Czy można zwiększyć limit?

Druga rzecz, której bym szukał na twoich serwerach, to czy są uruchomione jakieś prace konserwacyjne? Sprawdź zadania wykonujące przebudowy indeksu, aktualizuj statystyki lub operacje DBCC CHECKDB. Wykonują one dużą liczbę odczytów i mogą być przyczyną płaskiej podszewki PLE,

Następnie, gdy korzystasz z programu SQL Server 2008 +, możesz skonfigurować sesję zdarzenia rozszerzonego w celu przechwytywania zapytań, które wykonują dużą liczbę odczytów. Oto kod, aby to zrobić: -

CREATE EVENT SESSION [QueriesWithHighLogicalReads] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
   ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
     WHERE ([logical_reads]>200000))
ADD TARGET package0.event_file(SET filename=N'C:\SQLServer\XEvents\QueriesWithHighLogicalReads.xel')
GO

Spowoduje to przechwycenie wszystkich zapytań na serwerze, które wykonują ponad 200 000 odczytów logicznych. Nie wiem, ile pamięci masz na każdym serwerze, więc możesz zmienić tę liczbę. Po utworzeniu możesz rozpocząć sesję, uruchamiając: -

ALTER EVENT SESSION [QueriesWithHighLogicalReads]
ON SERVER
STATE = START;
GO

Następnie przeprowadź sesję zapytania, uruchamiając: -

WITH CTE_ExecutedSQLStatements AS
(SELECT
[XML Data],
[XML Data].value('(/event[@name=''sql_statement_completed'']/@timestamp)[1]','DATETIME')    AS [Time],
[XML Data].value('(/event/data[@name=''duration'']/value)[1]','int')                        AS [Duration],
[XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','int')                        AS [CPU],
[XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','int')                   AS [logical_reads],
[XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','int')                  AS [physical_reads],
[XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')             AS [SQL Statement]
FROM
    (SELECT 
    OBJECT_NAME              AS [Event], 
    CONVERT(XML, event_data) AS [XML Data]
FROM 
    sys.fn_xe_file_target_read_file
('C:\SQLServer\XEvents\QueriesWithHighLogicalReads*.xel',NULL,NULL,NULL)) as v)

SELECT
[SQL Statement]     AS [SQL Statement],
SUM(Duration)       AS [Total Duration],
SUM(CPU)            AS [Total CPU],
SUM(Logical_Reads)  AS [Total Logical Reads],
SUM(Physical_Reads) AS [Total Physical Reads]
FROM
CTE_ExecutedSQLStatements
GROUP BY
[SQL Statement]
ORDER BY
[Total Logical Reads] DESC
GO

Zachowaj ostrożność podczas uruchamiania tego! Rozmiar pliku może być dość duży, dlatego najpierw przetestuj go na instancji programistycznej. Możesz ustawić maks. rozmiar pliku, ale nie podałem go tutaj. Oto link MSDN dla zdarzeń rozszerzonych: - https://msdn.microsoft.com/en-us/library/hh213147.aspx

Monitoruj tę sesję rutynowo i miejmy nadzieję, że powinna ona wychwycić wszelkie zapytania, które są płaskie na twoim PLE.

Dalsza lektura -

Blog MSDN na PLE - http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx

Film na temat konfigurowania wydarzeń rozszerzonych - https://dbafromthecold.wordpress.com/2014/12/05/video-identifying-large-queries-using-extended-events/ (pochodzi z mojego bloga, przepraszam za bezwstydną autopromocję )

Oczekiwana długość życia strony jest miarą tego, jak długo można oczekiwać, że strona, która właśnie została odczytana z dysku, zawiesi się w pamięci, zanim zostanie wypchnięta przez coś innego lub zostanie zniszczona (tj. Ta strona jest zwolniona na dysku, ponieważ nie ma potrzeby aby zachować kopię w pamięci podręcznej).

Ogólnie rzecz biorąc, im wyższa, tym szybciej przetwarzany jest wzór ładowania, ponieważ rzeczy są przechowywane w pamięci. Jeśli jest bardzo niski, może to wskazywać na problem z wydajnością spowodowany głodem pamięci.

Niski odczyt nie zawsze oznacza, że ​​istnieje problem: na przykład może być niski po ogromnym jednorazowym nadmiarze procesów, które wykorzystywały wiele stron, więc przyniosły je i spadły, aby zrobić miejsce na więcej. Twój wykres, który wydaje się spadać na przykład pod koniec każdego dnia, może być spowodowany nocnymi zadaniami administracyjnymi (tworzenie kopii zapasowych, archiwizacja danych, inne przetwarzanie z dnia na dzień).