różnica w planach wykonania na serwerze UAT i PROD

Chcę zrozumieć, dlaczego miałaby być tak ogromna różnica w wykonywaniu tego samego zapytania na UAT (działa w 3 sekundy) w porównaniu z PROD (uruchamianym w 23 sekundy).

Zarówno UAT, jak i PROD mają dokładnie dane i indeksy.

PYTANIE:

set statistics io on;
set statistics time on;

SELECT CONF_NO,
       'DE',
       'Duplicate Email Address ''' + RTRIM(EMAIL_ADDRESS) + ''' in Maintenance',
       CONF_TARGET_NO
FROM   CONF_TARGET ct
WHERE  CONF_NO = 161
       AND LEFT(INTERNET_USER_ID, 6) != 'ICONF-'
       AND ( ( REGISTRATION_TYPE = 'I'
               AND (SELECT COUNT(1)
                    FROM   PORTFOLIO
                    WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                           AND DEACTIVATED_YN = 'N') > 1 )
              OR ( REGISTRATION_TYPE = 'K'
                   AND (SELECT COUNT(1)
                        FROM   CAPITAL_MARKET
                        WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                               AND DEACTIVATED_YN = 'N') > 1 ) ) 

NA UAT:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 11 ms, elapsed time = 11 ms.

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

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

(3 row(s) affected)
Table 'Worktable'. Scan count 256, logical reads 1304616, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PORTFOLIO'. Scan count 1, logical reads 84761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2418 ms,  elapsed time = 2442 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

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

W PROD:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

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

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

(3 row(s) affected)
Table 'PORTFOLIO'. Scan count 256, logical reads 21698816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 23937 ms,  elapsed time = 23935 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

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

Zauważ, że w PROD zapytanie sugeruje brakujący indeks i jest to korzystne, ponieważ testowałem, ale nie o to chodzi w dyskusji.

Chcę tylko zrozumieć, że: NA UAT - dlaczego serwer SQL tworzy tabelę roboczą, a na PROD nie? Tworzy bufor tabeli w UAT, a nie w PROD. Ponadto, dlaczego czasy wykonania są tak różne w UAT vs PROD?

Uwaga :

Korzystam z SQL Server 2008 R2 RTM na obu serwerach (już niedługo będę łatać najnowszą SP).

UAT: maks. Pamięć 8 GB. MaxDop, koligacja procesora i maksymalna liczba wątków roboczych to 0.

Logical to Physical Processor Map:
*-------  Physical Processor 0
-*------  Physical Processor 1
--*-----  Physical Processor 2
---*----  Physical Processor 3
----*---  Physical Processor 4
-----*--  Physical Processor 5
------*-  Physical Processor 6
-------*  Physical Processor 7

Logical Processor to Socket Map:
****----  Socket 0
----****  Socket 1

Logical Processor to NUMA Node Map:
********  NUMA Node 0

PROD: maksymalna pamięć 60 GB. MaxDop, koligacja procesora i maksymalna liczba wątków roboczych to 0.

Logical to Physical Processor Map:
**--------------  Physical Processor 0 (Hyperthreaded)
--**------------  Physical Processor 1 (Hyperthreaded)
----**----------  Physical Processor 2 (Hyperthreaded)
------**--------  Physical Processor 3 (Hyperthreaded)
--------**------  Physical Processor 4 (Hyperthreaded)
----------**----  Physical Processor 5 (Hyperthreaded)
------------**--  Physical Processor 6 (Hyperthreaded)
--------------**  Physical Processor 7 (Hyperthreaded)

Logical Processor to Socket Map:
********--------  Socket 0
--------********  Socket 1

Logical Processor to NUMA Node Map:
********--------  NUMA Node 0
--------********  NUMA Node 1

AKTUALIZACJA :

XML planu wykonania UAT:

http://pastebin.com/z0PWvw8m

XML planu wykonania PROD:

http://pastebin.com/GWTY16YY

XML planu wykonania UAT - z planem wygenerowanym dla PROD:

http://pastebin.com/74u3Ntr0

Konfiguracja serwera:

PROD: PowerEdge R720xd - procesor Intel (R) Xeon (E) E5-2637 v2 @ 3,50 GHz.

UAT: PowerEdge 2950 - procesor Intel (R) Xeon (R) X5460 @ 3,16 GHz

Zamieściłem na answer.sqlperformance.com

AKTUALIZACJA :

Dzięki @swasheck za sugestie

Zmieniając maksymalną pamięć PROD z 60 GB na 7680 MB, jestem w stanie wygenerować ten sam plan w PROD. Zapytanie kończy się w tym samym czasie co UAT.

Teraz muszę zrozumieć - DLACZEGO? Poza tym nie będę w stanie uzasadnić tego potwornego serwera, aby zastąpił stary serwer!

Potencjalny rozmiar puli buforów wpływa na wybór planu przez optymalizator zapytań na wiele sposobów. O ile mi wiadomo, hiperwątkowość nie wpływa na wybór planu (choć liczba potencjalnie dostępnych harmonogramów z pewnością może).

Pamięć obszaru roboczego

W przypadku planów, które zawierają iteratory pochłaniające pamięć, takie jak sortowanie i skróty, wielkość puli buforów (między innymi) określa maksymalną ilość przydzielonych pamięci, które mogą być dostępne dla zapytania w czasie wykonywania.

W SQL Server 2012 (wszystkie wersje) liczba ta jest zgłaszana w węźle głównym planu zapytań, w Optimizer Hardware Dependenciessekcji pokazanej jako Estimated Available Memory Grant. Wersje sprzed 2012 roku nie zgłaszają tej liczby w planie pokazu.

Szacowany dostępny przydział pamięci stanowi dane wejściowe do modelu kosztów stosowanego przez optymalizator zapytań. W rezultacie bardziej prawdopodobne jest wybranie alternatywnego planu, który wymaga dużej operacji sortowania lub mieszania na komputerze z ustawieniem dużej puli buforów niż na komputerze o niższym ustawieniu. W przypadku instalacji z bardzo dużą ilością pamięci model kosztów może pójść za daleko z takim myśleniem - wybierając plany z bardzo dużymi rodzajami lub skrótami, w których preferowana byłaby alternatywna strategia ( KB2413549 - Korzystanie z dużej ilości pamięci może spowodować nieefektywny plan w SQL Server - TF2335 ).

Przydział pamięci obszaru roboczego nie jest w twoim przypadku czynnikiem, ale warto o tym wiedzieć.

Dostęp do danych

Potencjalna wielkość puli buforów wpływa również na model kosztów optymalizatora dostępu do danych. Jednym z założeń przyjętych w modelu jest to, że każde zapytanie zaczyna się od zimnej pamięci podręcznej - dlatego zakłada się, że pierwszy dostęp do strony powoduje fizyczne we / wy. Model próbuje uwzględnić prawdopodobieństwo, że wielokrotny dostęp będzie pochodził z pamięci podręcznej, co zależy między innymi od potencjalnej wielkości puli buforów.

Skanowanie indeksu klastrowanego w planach zapytań pokazanych w pytaniu jest jednym z przykładów powtarzającego się dostępu; skany są przewijane (powtarzane, bez zmiany skorelowanego parametru) dla każdej iteracji połączenia częściowego pętli zagnieżdżonych. Zewnętrzne dane wejściowe do częściowego łączenia szacują 28,7874 wierszy, a właściwości planu zapytań dla tych skanów pokazują w rezultacie szacowane przewijanie do 27,7874.

Ponownie, tylko w SQL Server 2012, główny iterator planu pokazuje liczbę Estimated Pages Cachedw Optimizer Hardware Dependenciessekcji. Liczba ta zgłasza jedno z danych wejściowych algorytmu kalkulacji kosztów, który bierze pod uwagę szansę na powtarzający się dostęp do strony pochodzący z pamięci podręcznej.

Skutkuje to tym, że instalacja z wyższą skonfigurowaną maksymalną wielkością puli buforów będzie dążyła do zmniejszenia kosztów skanów (lub prób), które odczytują te same strony więcej niż raz więcej niż instalacja z mniejszym maksymalnym rozmiarem puli buforów.

W prostych planach redukcję kosztów skanowania z przewijaniem można zobaczyć porównując (estimated number of executions) * (estimated CPU + estimated I/O)z szacowanym kosztem operatora, który będzie niższy. Obliczenia są bardziej złożone w przykładowych planach ze względu na efekt połączenia częściowego i połączenia.

Niemniej jednak wydaje się, że plany w pytaniu pokazują przypadek, w którym wybór między powtórzeniem skanów a utworzeniem tymczasowego indeksu jest dość precyzyjnie zrównoważony. Na komputerze z większą pulą buforów powtarzanie skanowania kosztuje nieco mniej niż tworzenie indeksu. Na komputerze z mniejszą pulą buforów koszt skanowania jest zmniejszony o mniejszą kwotę, co oznacza, że ​​plan buforowania indeksów wygląda optymalnie nieco taniej.

Planuj wybory

Model kosztów optymalizatora przyjmuje wiele założeń i zawiera dużą liczbę szczegółowych obliczeń. Nie zawsze (lub nawet zwykle) jest możliwe śledzenie wszystkich szczegółów, ponieważ nie wszystkie liczby, których potrzebowalibyśmy, są widoczne, a algorytmy mogą zmieniać się między wydaniami. W szczególności formuła skalowania zastosowana w celu uwzględnienia szansy napotkania strony w pamięci podręcznej nie jest dobrze znana.

Co więcej, w tym konkretnym przypadku wybór planu optymalizatora i tak opiera się na niepoprawnych liczbach. Szacowana liczba wierszy z wyszukiwania klastrowanego indeksu wynosi 28,7874, podczas gdy w czasie wykonywania napotyka się 256 wierszy - prawie o rząd wielkości na zewnątrz. Nie możemy bezpośrednio zobaczyć informacji, które optymalizator ma na temat oczekiwanego rozkładu wartości w tych 28,7874 wierszach, ale bardzo prawdopodobne jest również, że okropnie się myli.

Gdy szacunki są błędne, wybór planu i wydajność środowiska wykonawczego zasadniczo nie są lepsze niż przypadek. Plan ze szpulą indeksu zdarza się działać lepiej niż powtarzanie skanowania, ale błędem jest sądzić, że zwiększenie wielkości puli buforów było przyczyną anomalii.

Tam, gdzie optymalizator ma prawidłowe informacje, jest znacznie większe prawdopodobieństwo, że opracuje porządny plan wykonania. Wystąpienie z większą ilością pamięci na ogół będzie działać lepiej na obciążeniu niż inne wystąpienie z mniejszą ilością pamięci, ale nie ma gwarancji, zwłaszcza gdy wybór planu opiera się na niepoprawnych danych.

Obie instancje zasugerowały brakujący indeks na swój sposób. Jeden zgłosił wyraźny brakujący indeks, a drugi użył buforu indeksu o takich samych cechach. Jeśli indeks zapewnia dobrą wydajność i stabilność planu, może to wystarczyć. Chciałbym również przepisać zapytanie, ale to chyba inna historia.

Paul White w doskonały sposób wyjaśnił przyczynę - zachowanie serwera SQL podczas pracy na serwerach z większą pamięcią.

Ogromne podziękowania dla @swasheck za pierwsze wykrycie problemu.

Sugerowano otwarcie skrzynki z Microsoft i poniżej.

Problem został rozwiązany za pomocą flagi śledzenia T2335 jako parametru startowego.

KB2413549 - Użycie dużej ilości pamięci może skutkować nieefektywnym planu w SQL Server opisuje to dokładniej.

Ta flaga śledzenia spowoduje, że SQL Server wygeneruje plan, który jest bardziej konserwatywny pod względem zużycia pamięci podczas wykonywania zapytania. Nie ogranicza to ilości pamięci, jaką może wykorzystać SQL Server. Pamięć skonfigurowana dla SQL Server będzie nadal używana przez pamięć podręczną danych, wykonywanie zapytań i innych konsumentów. Upewnij się, że dokładnie przetestowałeś tę opcję, zanim umieścisz ją w środowisku produkcyjnym.

Ustawienia maksymalnej pamięci i hyperthreading mogą mieć wpływ na wybór planu.

Dodatkowo zauważam, że twoje opcje „ustawiania” są różne w każdym środowisku:

StatementSetOptions on UAT:

ANSI_NULLS="true" 
ANSI_PADDING="true" 
ANSI_WARNINGS="true" 
ARITHABORT="true" 
CONCAT_NULL_YIELDS_NULL="true" 
NUMERIC_ROUNDABORT="false" 
QUOTED_IDENTIFIER="true" 

StatementSetOptions on Prod:

ANSI_NULLS="true" 
ANSI_PADDING="true" 
ANSI_WARNINGS="true" 
ARITHABORT="false" 
CONCAT_NULL_YIELDS_NULL="true"
NUMERIC_ROUNDABORT="false"
QUOTED_IDENTIFIER="true" 

SQL może generować różne plany w oparciu o opcje SET. Zdarza się to często, jeśli przechwytujesz plan z różnych sesji SSMS lub z różnych wykonań z aplikacji.

Upewnij się, że programiści używają spójnych parametrów połączenia.