Zapytanie to
SELECT SUM(Amount) AS SummaryTotal
FROM PDetail WITH(NOLOCK)
WHERE ClientID = @merchid
AND PostedDate BETWEEN @datebegin AND @dateend
Tabela zawiera 103 129 000 wierszy.
Szybki plan wyszukuje według ClientId z pozostałym predykatem na dzień, ale musi wykonać 96 odnośników, aby pobrać Amount. Część <ParameterList>planu jest następująca.
<ParameterList>
<ColumnReference Column="@dateend"
ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
<ColumnReference Column="@datebegin"
ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
<ColumnReference Column="@merchid"
ParameterRuntimeValue="(78155)" />
</ParameterList>
Powolny plan wyszukuje według daty i zawiera wyszukiwania w celu oceny rezydualnego predykatu na ClientId i odzyskania kwoty (Szacowany 1 vs Rzeczywisty 7 388 383). Ta <ParameterList>sekcja jest
<ParameterList>
<ColumnReference Column="@EndDate"
ParameterCompiledValue="'2013-02-01 23:59:00.000'"
ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
<ColumnReference Column="@BeginDate"
ParameterCompiledValue="'2013-01-01 00:00:00.000'"
ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
<ColumnReference Column="@ClientID"
ParameterCompiledValue="(78155)"
ParameterRuntimeValue="(78155)" />
</ParameterList>
W tym drugim przypadku nieParameterCompiledValue jest pusty. SQL Server z powodzeniem wąchał wartości użyte w zapytaniu.
Książka „SQL Server 2005 Praktyczne rozwiązywanie problemów” ma to do powiedzenia na temat używania zmiennych lokalnych
Używanie zmiennych lokalnych w celu pokonania wąchania parametrów jest dość powszechną sztuczką, ale wskazówki OPTION (RECOMPILE)i OPTION (OPTIMIZE FOR)... są na ogół bardziej eleganckie i nieco mniej ryzykowne rozwiązania
Uwaga
W SQL Server 2005 kompilacja na poziomie instrukcji pozwala na odroczenie kompilacji pojedynczej instrukcji w procedurze przechowywanej do momentu tuż przed pierwszym wykonaniem zapytania. Do tego czasu wartość zmiennej lokalnej będzie znana. Teoretycznie SQL Server mógłby to wykorzystać do wąchania wartości zmiennych lokalnych w taki sam sposób, jak wącha parametry. Jednak ponieważ powszechne było używanie zmiennych lokalnych do pokonania wąchania parametrów w SQL Server 7.0 i SQL Server 2000+, wąchanie zmiennych lokalnych nie było włączone w SQL Server 2005. Może być włączone w przyszłej wersji SQL Server, choć jest to dobre powód, aby skorzystać z jednej z innych opcji opisanych w tym rozdziale, jeśli masz wybór.
Z szybkiego testu tego końca zachowanie opisane powyżej pozostaje takie samo w 2008 i 2012 roku, a zmienne nie są wąchane dla odroczonej kompilacji, ale tylko wtedy, gdy OPTION RECOMPILEużyta jest wyraźna wskazówka.
DECLARE @N INT = 0
CREATE TABLE #T ( I INT );
/*Reference to #T means this statement is subject to deferred compile*/
SELECT *
FROM master..spt_values
WHERE number = @N
AND EXISTS(SELECT COUNT(*) FROM #T)
SELECT *
FROM master..spt_values
WHERE number = @N
OPTION (RECOMPILE)
DROP TABLE #T
Pomimo odroczonej kompilacji zmienna nie jest wąchana, a szacowana liczba wierszy jest niedokładna
Zakładam więc, że wolny plan dotyczy sparametryzowanej wersji zapytania.
ParameterCompiledValueJest równe ParameterRuntimeValuedla wszystkich parametrów, więc nie jest to typowy parametr wąchania (gdzie plan został opracowany dla jednego zestawu wartości, a następnie uruchomić na inny zestaw wartości).
Problem polega na tym, że plan skompilowany dla poprawnych wartości parametrów jest nieodpowiedni.
Prawdopodobnie trafiasz na problem z rosnącymi datami opisanymi tutaj i tutaj . W przypadku tabeli zawierającej 100 milionów wierszy należy wstawić (lub w inny sposób zmodyfikować) 20 milionów, zanim SQL Server automatycznie zaktualizuje statystyki. Wygląda na to, że ostatnim razem, gdy były aktualizowane, zero wierszy pasowało do zakresu dat w zapytaniu, ale teraz robi to 7 milionów.
Możesz zaplanować częstsze aktualizacje statystyk, rozważyć flagi śledzenia 2389 - 90lub użyć, OPTIMIZE FOR UKNOWNaby po prostu opierać się na domysłach, a nie być w stanie używać obecnie wprowadzających w błąd statystyk w datetimekolumnie.
Może to nie być konieczne w następnej wersji programu SQL Server (po 2012 r.). Związane poz Połącz zawiera intrygującą odpowiedź
Wysłany przez Microsoft 28.08.2012 o 13:35
Zrobiliśmy ulepszenie szacowania liczności dla następnej ważnej wersji, która zasadniczo to naprawia. Sprawdzaj szczegóły, gdy tylko pojawią się nasze zapowiedzi. Eric
Poprawkę z 2014 r. Analizuje Benjamin Nevarez pod koniec artykułu:
Pierwsze spojrzenie na nowy program SQL Server Cardinality Estimator .
Wygląda na to, że nowy estymator liczności cofnie się i zastosuje średnią gęstość w tym przypadku zamiast podawać szacunek 1-rzędowy.
Kilka dodatkowych szczegółów na temat estymatora liczebności 2014 i rosnącego kluczowego problemu tutaj:
Nowa funkcjonalność w SQL Server 2014 - Część 2 - Nowa ocena liczności
