sammankopplingen av två eller flera datamängder uttrycks oftast i T-SQL medUNION ALL – klausulen. Med tanke på att SQL Server optimizer ofta kan ordna om saker som kopplingar och aggregat för att förbättra prestanda, är det ganska rimligt att förvänta sig att SQL Server också skulle överväga omarrangering av sammankopplingsingångar, där detta skulle ge en fördel. Till exempel kan optimeraren överväga fördelarna med att skriva om A UNION ALL B som B UNION ALL A.

faktum är att SQL Server optimizer inte gör det här. Mer exakt fanns det ett begränsat stöd för omarrangering av sammanlänkningsinmatning i SQL Server-utgåvor upp till 2008 R2, men detta togs bort i SQL Server 2012 och har inte återuppstått sedan dess.

SQL Server 2008 R2

intuitivt betyder ordningen för sammankopplingsingångar bara om det finns ett radmål. Som standard optimerar SQL Server exekveringsplaner på grundval av att alla kvalificerade rader kommer att returneras till klienten. När ett radmål är i kraft försöker optimeraren hitta en exekveringsplan som snabbt producerar de första raderna.

Radmål kan ställas in på ett antal sätt, till exempel med TOP, en FAST n frågetips, eller genom att använda EXISTS (som till sin natur behöver hitta högst en rad). Där det inte finns något radmål (dvs. klienten kräver alla rader) spelar det i allmänhet ingen roll i vilken ordning sammankopplingsingångarna läses: varje ingång kommer i alla fall att behandlas fullständigt.

det begränsade stödet i versioner upp till SQL Server 2008 R2 gäller där det finns ett mål på exakt en rad. I denna specifika omständighet kommer SQL Server att ordna om sammanlänkningsingångar på grundval av förväntad kostnad.

detta görs inte under kostnadsbaserad optimering (som man kan förvänta sig), utan snarare som en sista minuten efter optimering omskrivning av den normala optimeringsutgången. Detta arrangemang har fördelen att inte öka det kostnadsbaserade plansökutrymmet (potentiellt ett alternativ för varje möjlig omarrangering), samtidigt som man producerar en plan som är optimerad för att snabbt returnera den första raden.

exempel

följande exempel använder två tabeller med identiskt innehåll: en miljon rader heltal från en till en miljon. En tabell är en hög utan icke-klustrade index; den andra har ett unikt grupperat index:

CREATE TABLE dbo.Expensive( Val bigint NOT NULL); CREATE TABLE dbo.Cheap( Val bigint NOT NULL, CONSTRAINT UNIQUE CLUSTERED (Val));GOINSERT dbo.Cheap WITH (TABLOCKX) (Val)SELECT TOP (1000000) Val = ROW_NUMBER() OVER (ORDER BY SV1.number)FROM master.dbo.spt_values AS SV1CROSS JOIN master.dbo.spt_values AS SV2ORDER BY ValOPTION (MAXDOP 1);GOINSERT dbo.Expensive WITH (TABLOCKX) (Val)SELECT C.ValFROM dbo.Cheap AS COPTION (MAXDOP 1);

inget Radmål

följande fråga söker efter samma rader i varje tabell och returnerar sammankopplingen av de två uppsättningar:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005;

exekveringsplanen som produceras av frågeoptimeraren är:

varningen på rotenSELECT operatören varnar oss för det uppenbara saknade indexet på heap-tabellen. Varningen på tabellen Scan operator läggs av Sentry One Plan Explorer. Det uppmärksammar I / O-kostnaden för det återstående predikatet som är dolt i skanningen.

ordningen på ingångarna till sammankopplingen spelar ingen roll här, eftersom vi inte har satt ett radmål. Båda ingångarna läses fullständigt för att returnera alla resultatrader. Av intresse (även om detta inte garanteras) märker att ordningen på ingångarna följer den ursprungliga frågans textordning. Observera också att ordningen på de slutliga resultatraderna inte heller anges, eftersom vi inte använde en toppnivå ORDER BY-klausul. Vi antar att det är avsiktlig och slutlig beställning är obetydlig för uppgiften.

om vi vänder den skriftliga ordningen på tabellerna i frågan som så:

SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 UNION ALL SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005;

exekveringsplanen följer ändringen och öppnar den grupperade tabellen först (igen, detta garanteras inte):

båda frågorna kan förväntas ha samma prestandaegenskaper, eftersom de utför samma operationer, bara i en annan ordning.

med ett Radmål

det är uppenbart att bristen på indexering på heaptabellen normalt gör det dyrare att hitta specifika rader jämfört med samma operation på det grupperade bordet. Om vi ber optimeraren om en plan som returnerar den första raden snabbt, förväntar vi oss att SQL Server ordnar om sammankopplingsingångarna så att den billiga grupperade tabellen konsulteras först.

använda frågan som nämner heap-tabellen först och använda en snabb 1-frågetips för att ange radmålet:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005OPTION (FAST 1);

den beräknade exekveringsplanen som produceras på en instans av SQL Server 2008 R2 är:

lägg märke till att sammankopplingsingångarna har omordnats för att minska den beräknade kostnaden för att returnera den första raden. Observera också att det saknade indexet och resterande I/O-varningar har försvunnit. Varken frågan är av konsekvens med denna planform när målet är att returnera en enda rad så snabbt som möjligt.

samma fråga som körs på SQL Server 2016 (med antingen kardinalitetsskattningsmodell) är:

SQL Server 2016 har inte omordnat sammankopplingsingångarna. Plan Explorer I / O-varningen har återvänt, men tyvärr har optimeraren inte producerat en saknad indexvarning den här gången (även om den är relevant).

allmän omarrangering

som nämnts är omskrivningen efter optimering som omordnar sammankopplingsingångar endast effektiv för:

• SQL Server 2008 R2 och tidigare
• ett radmål på exakt en

om vi verkligen bara vill ha en rad tillbaka, snarare än en plan optimerad för att returnera den första raden snabbt (men som i slutändan fortfarande returnerar alla rader), kan vi använda en TOP klausul med ett härledt bord eller vanligt tabeluttryck (CTE):

SELECT TOP (1) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UA;

på SQL Server 2008 R2 eller tidigare ger detta den optimala omordnade inmatningsplanen:

på SQL Server 2012, 2014 och 2016 ingen omarrangering efter optimering sker:

Om vi vill ha mer än en rad tillbaka, till exempel medTOP (2), den önskade omskrivningen kommer inte att tillämpas på SQL Server 2008 R2 även om enFAST 1 tips används också. I den situationen måste vi tillgripa tricks som att användaTOP med en variabel och ettOPTIMIZE FOR tips:

DECLARE @TopRows bigint = 2; -- Number of rows actually needed SELECT TOP (@TopRows) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UAOPTION (OPTIMIZE FOR (@TopRows = 1)); -- Just a hint

frågetipset är tillräckligt för att ställa in ett radmål på ett, medan variabelns körtidsvärde säkerställer att önskat antal rader (2) returneras.

den faktiska exekveringsplanen på SQL Server 2008 R2 är:

båda raderna som returneras kommer från den omordnade sökinmatningen, och Tabellskanningen körs inte alls. Plan Explorer visar radräkningarna i rött eftersom uppskattningen var för en rad (på grund av tipset) medan två rader påträffades vid körtid.

utan UNION ALL

detta problem är inte heller begränsat till frågor skrivna uttryckligen med UNION ALL. Andra konstruktioner som EXISTSoch OR kan också resultera i att optimeraren introducerar en sammankopplingsoperatör, som kan drabbas av bristen på inmatningsomordning. Det fanns en ny fråga om databasadministratörer Stack Exchange med exakt det här problemet. Omvandla frågan från den frågan för att använda våra exempeltabeller:

SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 ) OR EXISTS ( SELECT 1 FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 ) THEN 1 ELSE 0 END;

exekveringsplanen på SQL Server 2016 har heaptabellen på den första inmatningen:

på SQL Server 2008 R2 ordningen på ingångarna är optimerad för att återspegla den enda rad målet för semi join:

i den mer optimala planen är heap scan aldrig avrättad.

lösningar

i vissa fall kommer det att vara uppenbart för frågeskrivaren att en av sammankopplingsingångarna alltid kommer att vara billigare att köra än de andra. Om det är sant är det ganska giltigt att skriva om frågan så att de billigare sammankopplingsingångarna visas först i skriftlig ordning. Naturligtvis innebär detta att frågeskrivaren måste vara medveten om denna optimeringsbegränsning och beredd att förlita sig på papperslöst beteende.

ett svårare problem uppstår när kostnaden för sammankopplingsingångarna varierar med omständigheterna, kanske beroende på parametervärden. Att använda OPTION (RECOMPILE) hjälper inte på SQL Server 2012 eller senare. Det alternativet kan hjälpa till på SQL Server 2008 R2 eller tidigare, men bara om kravet på enstaka radmål också uppfylls.

om det finns farhågor om att förlita sig på observerat beteende (frågeplansammanfogningsingångar som matchar frågans textordning) kan en planguide användas för att tvinga planformen. Om olika inmatningsorder är optimala för olika omständigheter, kan flera planguider användas, där förhållandena kan kodas exakt i förväg. Detta är dock knappast idealiskt.

slutliga tankar

SQL Server query optimizer innehåller faktiskt en kostnadsbaserad prospekteringsregel, UNIAReorderInputs, som kan generera sammanlänkningsinmatningsordervariationer och utforska alternativ under kostnadsbaserad optimering (inte som en enstaka omskrivning efter optimering).

denna regel är för närvarande inte aktiverad för allmänt bruk. Såvitt jag kan säga aktiveras den bara när en planguide eller USE PLAN tips är närvarande. Detta gör det möjligt för motorn att framgångsrikt tvinga fram en plan som genererades för en fråga som kvalificerade sig för omskrivning av inmatning, även om den aktuella frågan inte kvalificerar sig.

min mening är att denna prospekteringsregel medvetet är begränsad till denna användning, eftersom frågor som skulle dra nytta av omfördelning av sammanlänkning som en del av kostnadsbaserad optimering anses inte vara tillräckligt vanliga, eller kanske för att det finns en oro för att den extra ansträngningen inte skulle löna sig. Min egen uppfattning är att sammanlänkning operatör input omarrangering alltid bör undersökas när en rad mål är i kraft.

det är också synd att den (mer begränsade) omskrivningen efter optimering inte är effektiv i SQL Server 2012 eller senare. Detta kan ha bero på en subtil bugg, men jag kunde inte hitta något om detta i dokumentationen, kunskapsbasen eller på Connect. Jag har lagt till ett nytt Connect-objekt här.

uppdatering 9 augusti 2017: Detta är nu fixat under spårflagga 4199 för SQL Server 2014 och 2016, se KB 4023419:

FIX: Fråga med UNION ALL och ett radmål kan köras långsammare i SQL Server 2014 eller senare versioner när det jämförs med SQL Server 2008 R2