Optymalizacja planów za pomocą czytników XML

Wykonanie zapytania stąd, aby wyciągnąć zdarzenia impasu z domyślnej sesji zdarzeń rozszerzonych

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

wykonanie na moim komputerze zajmuje około 20 minut. Zgłoszone statystyki to

Table 'Worktable'. Scan count 0, logical reads 68121, physical reads 0, read-ahead reads 0, 
         lob logical reads 25674576, lob physical reads 0, lob read-ahead reads 4332386.

 SQL Server Execution Times:
   CPU time = 1241269 ms,  elapsed time = 1244082 ms.

Powolny plan XML

Jeśli usunę WHEREklauzulę, wypełnia się ona w mniej niż sekundę, zwracając 3782 wiersze.

Podobnie, jeśli dodam OPTION (MAXDOP 1)do pierwotnego zapytania, które również przyspiesza, statystyki pokazują teraz znacznie mniej odczytów lob.

Table 'Worktable'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0,
                lob logical reads 6767, lob physical reads 0, lob read-ahead reads 6076.

 SQL Server Execution Times:
   CPU time = 639 ms,  elapsed time = 693 ms.

Szybszy plan XML

Więc moje pytanie brzmi

Czy ktoś może wyjaśnić, co się dzieje? Dlaczego pierwotny plan jest tak katastrofalnie gorszy i czy istnieje jakiś niezawodny sposób na uniknięcie problemu?

Dodanie:

Przekonałem się również, że zmiana zapytania do INNER HASH JOINpewnego stopnia poprawia (ale nadal zajmuje> 3 minuty), ponieważ wyniki DMV są tak małe, że wątpię, czy sam typ łączenia jest odpowiedzialny i zakładam, że coś innego musiało się zmienić. Statystyki dla tego

Table 'Worktable'. Scan count 0, logical reads 30294, physical reads 0, read-ahead reads 0, 
          lob logical reads 10741863, lob physical reads 0, lob read-ahead reads 4361042.

 SQL Server Execution Times:
   CPU time = 200914 ms,  elapsed time = 203614 ms.

(I plan)

Po napełnieniu bufor pierścieniowy wydłużone zdarzenia ( DATALENGTHz XMLbyło 4,880,045 bajtów i zawierał 1,448 zdarzeń.) I prób ściętego wersji oryginalnego zapytania z i bez MAXDOPśladu.

SELECT COUNT(*)
FROM   (SELECT CAST (target_data AS XML) AS TargetData
        FROM   sys.dm_xe_session_targets st
               JOIN sys.dm_xe_sessions s
                 ON s.address = st.event_session_address
        WHERE  [name] = 'system_health') AS Data
       CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE  XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

SELECT*
FROM   sys.dm_db_task_space_usage
WHERE  session_id = @@SPID 

Dał następujące wyniki

+-------------------------------------+------+----------+
|                                     | Fast |   Slow   |
+-------------------------------------+------+----------+
| internal_objects_alloc_page_count   |  616 |  1761272 |
| internal_objects_dealloc_page_count |  616 |  1761272 |
| elapsed time (ms)                   |  428 |   398481 |
| lob logical reads                   | 8390 | 12784196 |
+-------------------------------------+------+----------+

Istnieje wyraźna różnica w alokacjach tempdb w tym, że szybciej pokazuje 616strony przydzielone i cofnięte. Jest to ta sama liczba stron, która jest używana, gdy XML jest również wstawiany do zmiennej.

W przypadku powolnego planu liczba przydziałów stron jest wyrażona w milionach. Sondowanie dm_db_task_space_usagepodczas działania zapytania pokazuje, że wydaje się ono stale przydzielać i zwalniać strony tempdbz dowolnym miejscem między 1800 a 3000 stron jednocześnie.

Przyczyną różnicy wydajności jest sposób, w jaki wyrażenia skalarne są obsługiwane w silniku wykonywania. W takim przypadku wyrazem zainteresowania jest:

[Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0)

Ta etykieta wyrażenia jest zdefiniowana przez operatora obliczeń skalarnych (węzeł 11 w planie szeregowym, węzeł 13 w planie równoległym). Operatory obliczeń skalarnych różnią się od innych operatorów (SQL Server 2005 i nowsze) tym, że zdefiniowane przez nich wyrażenia niekoniecznie są oceniane w miejscu, w którym pojawiają się w widocznym planie wykonania; ocena może zostać odroczona do momentu, gdy wynik obliczeń będzie wymagany przez późniejszego operatora.

W niniejszym zapytaniu target_dataciąg jest zwykle duży, co powoduje, że konwersja z ciągu na ciąg jest XMLdroga. W wolnych planach ciąg do XMLkonwersji jest wykonywany za każdym razem, gdy późniejszy operator wymagający wyniku Expr1000jest odbijany.

Ponowne wiązanie następuje po wewnętrznej stronie zagnieżdżonego połączenia, gdy zmienia się skorelowany parametr (odniesienie zewnętrzne). Expr1000jest zewnętrznym odniesieniem dla większości złączeń zagnieżdżonych pętli w tym planie wykonania. Do wyrażenia odwołuje się wiele razy kilka czytników XML, zarówno Stream Aggregates, jak i filtr początkowy. W zależności od rozmiaru XML, liczba konwersji łańcucha XMLmoże z łatwością być liczona w milionach.

Poniższe stosy wywołań pokazują przykłady target_dataciągu, który jest konwertowany na XML( ConvertStringToXMLForES- gdzie ES jest usługą wyrażania ):

Filtr uruchamiania

Czytnik XML (wewnętrzny strumień TVF)

Stream Aggregate

Konwertowanie ciągu za XMLkażdym razem, gdy którykolwiek z tych operatorów zostanie ponownie powiązany, wyjaśnia różnicę wydajności obserwowaną w planach zagnieżdżonych pętli. Jest to niezależne od tego, czy równoległość jest używana, czy nie. Zdarza się tak, że optymalizator wybiera sprzężenie mieszające, gdy MAXDOP 1podpowiedź jest określona. Jeśli MAXDOP 1, LOOP JOINjest określony, wydajność jest niska, podobnie jak w domyślnym planie równoległym (w którym optymalizator wybiera zagnieżdżone pętle).

To, o ile wzrośnie wydajność po połączeniu mieszającym, zależy od tego, czy Expr1000pojawia się po stronie kompilacji, czy po stronie sondy operatora. Następujące zapytanie lokalizuje wyrażenie po stronie sondy:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_sessions s
    INNER HASH JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

Odwróciłem pisemną kolejność złączeń od wersji pokazanej w pytaniu, ponieważ wskazówki dotyczące łączenia ( INNER HASH JOINpowyżej) również wymuszają kolejność dla całego zapytania, tak jakby FORCE ORDERto zostało określone. Odwrócenie jest konieczne, aby zapewnić Expr1000pojawienie się po stronie sondy. Interesująca część planu wykonania to:

Po wyrażeniu zdefiniowanym po stronie sondy wartość jest buforowana:

Ocena Expr1000jest nadal odraczana, dopóki pierwszy operator nie potrzebuje wartości (filtr początkowy w powyższym śladzie stosu), ale obliczona wartość jest buforowana ( CValHashCachedSwitch) i ponownie wykorzystywana do późniejszych wywołań przez czytniki XML i agregaty strumieniowe. Poniższy wykres stosu pokazuje przykład buforowanej wartości ponownie wykorzystywanej przez czytnik XML.

Gdy wymuszona jest kolejność łączenia, tak że definicja Expr1000występuje po stronie kompilacji sprzężenia skrótu, sytuacja wygląda inaczej:

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st 
    INNER HASH JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Sprzężenie mieszające odczytuje całkowicie dane wejściowe kompilacji, aby utworzyć tabelę mieszania, zanim rozpocznie sprawdzanie zgodności. W rezultacie musimy przechowywać wszystkie wartości, a nie tylko jedną dla każdego wątku, nad którą pracujemy od strony planu sondy. Łączenie skrótów wykorzystuje zatem tempdbtabelę roboczą do przechowywania XMLdanych, a każdy dostęp do wyniku Expr1000późniejszych operatorów wymaga kosztownej podróży do tempdb:

Poniżej przedstawiono więcej szczegółów ścieżki wolnego dostępu:

Jeśli wymuszenie łączenia jest wymuszone, wiersze wejściowe są sortowane (operacja blokowania, podobnie jak wejście kompilacji do łączenia mieszającego), co skutkuje podobnym układem, w którym tempdbwymagany jest powolny dostęp za pośrednictwem zoptymalizowanego sortowania stołu roboczego ze względu na rozmiar danych.

Plany manipulujące dużymi elementami danych mogą być problematyczne z różnych powodów, które nie są widoczne w planie wykonania. Użycie łączenia mieszającego (z wyrażeniem na poprawnym wejściu) nie jest dobrym rozwiązaniem. Opiera się na nieudokumentowanym wewnętrznym zachowaniu bez żadnych gwarancji, że zadziała w ten sam sposób w przyszłym tygodniu lub na nieco innym zapytaniu.

Przesłanie jest takie, że XMLmanipulacja może być trudna do zoptymalizowania dzisiaj. Zapisanie XMLzmiennej lub tabeli tymczasowej przed niszczeniem jest znacznie bardziej solidnym obejściem niż cokolwiek powyżej. Jednym ze sposobów na to jest:

DECLARE @data xml =
        CONVERT
        (
            xml,
            (
            SELECT TOP (1)
                dxst.target_data
            FROM sys.dm_xe_sessions AS dxs 
            JOIN sys.dm_xe_session_targets AS dxst ON
                dxst.event_session_address = dxs.[address]
            WHERE 
                dxs.name = N'system_health'
                AND dxst.target_name = N'ring_buffer'
            )
        )

SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
FROM @data.nodes ('./RingBufferTarget/event[@name eq "xml_deadlock_report"]') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

Na koniec chcę dodać bardzo ładną grafikę Martina z poniższych komentarzy:

To jest kod z mojego artykułu opublikowanego tutaj tutaj:

http://www.sqlservercentral.com/articles/deadlock/65658/

Jeśli czytasz komentarze, znajdziesz kilka alternatyw, które nie powodują problemów z wydajnością, jedną z nich jest modyfikacja tego oryginalnego zapytania, a druga z wykorzystaniem zmiennej do przechowywania kodu XML przed przetworzeniem, co się sprawdza. lepszy. (patrz moje komentarze na stronie 2) Przetwarzanie XML z DMV może być powolne, podobnie jak parsowanie XML z DMF dla celu pliku, który często lepiej jest osiągnąć, najpierw czytając dane do tabeli tymczasowej, a następnie przetwarzając. XML w SQL jest powolny w porównaniu do używania takich rzeczy jak .NET lub SQLCLR.