Udostępnij za pośrednictwem

  • Artykuł

Rozwiązywanie problemów z niską wydajność kwerendy: Stała składania i oceny wyrażenie podczas szacowania Kardynalność

SQL Server Niektóre wczesne wyrażenia stała ocenia aby poprawić wydajność kwerendy.To jest nazywane składanie stała.A stała is a Transact-SQL literal, such as 3, 'ABC', '2005-12-31', 1.0e3, or 0x12345678.

Wyrażenia składane

SQL Server używa stała składanie w z następujących wyrażeń:

  • Wyrażenia arytmetyczne, takie jak 1+1, 5/3*2, która zawiera tylko stałe.

  • Wyrażeń logicznych, takich jak 1=1 i 1>2 AND 3>4, która zawiera tylko stałe.

  • Wbudowane funkcje, które są uważane za składane przez SQL Server, włącznie z WYTOPU i konwersji.Wewnętrzna funkcja jest zwykle składane, jeżeli jest funkcją tylko komponenty i nie innych informacji kontekstowych, takich jak zestaw opcji Ustawienia języka, bazy danych i opcje kluczy szyfrowanie.Rodzaju funkcje nie są składane.Firma Deterministic funkcje wbudowane są składane z kilkoma wyjątkami.

Wyjątek przewidziany jest dla dużych obiektów.Jeżeli typ wyjścia procesu składane jest typem dużego obiektu (text, image, nvarchar(max), varchar(max), lub varbinary(max)), następnie SQL Server Składanie nie wyrażenie.

Wyrażenia Nonfoldable

Wszystkie inne typy wyrażenie nie są składane.W szczególności następujące typy wyrażenia nie są składane:

  • Nonconstant wyrażenie, takich jak wyrażenie, którego wynik zależy od wartości kolumna.

  • Wyrażenia, których wyniki zależą zmienną lokalną lub parametr, takich jak @x.

  • Rodzaju funkcje.

  • Funkcje zdefiniowane przez użytkownika (zarówno Transact-SQL i CLR).

  • Wyrażenia, których wyniki zależą od ustawień języka.

  • Wyrażenia, których wyniki zależą od Ustaw opcje.

  • Wyrażenia, których wyniki zależą od opcji konfiguracja serwera.

Przykłady wyrażeń stałych składane i Nonfoldable

Rozważmy następującą kwerendę:

SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00

Jeśli opcja PARAMETRYZACJA bazy danych nie jest zestaw do WYMUSZONE dla tej kwerendy (domyślnie jest proste), następnie wyrażenie 117.00 + 1000.00 jest oceniane i zastępuje jego wynikiem 1117.00, zanim kwerenda jest kompilowana.Następujące korzyści stała składanych:

  • Wyrażenie nie ma być oceniane wielokrotnie przy uruchomieniu czas.

  • Wartość wyrażenie po jest oceniany jest używany przez optymalizator kwerendy, aby oszacować rozmiar zestaw wyników kwerendy części TotalDue > 117.00 + 1000.00.

Z drugiej strony Jeśli dbo.f jest zdefiniowany przez użytkownika funkcja wartość skalarna, wyrażenie dbo.f(100) jest nie składana, ponieważ SQL Server nie składanie wyrażeniami funkcje zdefiniowane przez użytkownika, nawet jeśli są one deterministyczny.

Obliczanie wyrażenia kompilacji dla szacowania Kardynalność

Ponadto niektóre wyrażenia, które nie są stała złożony, ale których argumenty są znane czas kompilacji, czy argumenty są parametry lub stałych, oceniane są poprzez Estymator rozmiar zestaw wyników (Kardynalność), który jest częścią Optymalizator podczas optymalizacji.

W szczególności następujące funkcje wbudowane i specjalne operatory są oceniane czas kompilacji, jeśli ich dane wejściowe są znane: GÓRNA, dolna, RTRIM, DATEPART (tylko RR), GetDate, ODDANYCH i PRZEKONWERTOWAĆ.

Następujące operatory również są oceniane czas kompilacji, jeżeli wszystkie ich dane wejściowe są znane:

  • Operatory arytmetyczne: +,-, *, jednoargumentowy-,

  • Operatory logiczne: I, LUB NIE

  • Operatory porównania: <, >, <=, >=, <>, Jak, jest równa NULL, nie jest NULL

Nie inne funkcje lub Operatorzy są oceniane przez optymalizator podczas szacowania kardynalność.

Przykładami wyrażenia kompilacji

Należy wziąć pod uwagę to Transact-SQL procedura składowana:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1;

Podczas optymalizacji instrukcja SELECT w procedurze Optymalizator próbuje ocenić Kardynalność oczekiwanego zestaw wyników dla warunku OrderDate > @d+1.Wyrażenie @d+1 nie jest składana stała, ponieważ @d jest parametr.Jednak przy optymalizacji czas, wartość parametru jest znana.Dzięki temu Optymalizator dokładnie oszacować rozmiar zestaw wyników, który pomaga w wybierz plan dobre kwerendy.

Teraz Rozważmy przykład podobny do poprzedniego, z wyjątkiem zmiennej lokalnej @d2 zastępuje @d+1 w kwerendzie i wyrażenie jest oceniane w instrukcja SET zamiast w kwerendzie.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END;

Gdy SELECT instrukcja w MyProc2 jest zoptymalizowana w SQL Server, wartość @d2 jest znana.Dlatego optymalizator używa szacowania domyślne selektywności OrderDate > @d2, (w tym przypadek 30 procent).

Najważniejsze wskazówki: Generowanie optymalnej kwerendy przy użyciu stałej składanie i kompilacji wyrażenia plany

Aby wygenerować planów kwerend optymalnej, najlepiej projektować kwerendy, procedury przechowywane i instancje tak, aby optymalizator kwerendy można dokładnie oszacować selektywności warunków w kwerendzie, oparte na statystyki dotyczące dystrybucji danych.W przeciwnym razie Optymalizator należy użyć szacowania domyślny do szacowania selektywność (na przykład 30 procent w poprzednim przykładzie).

Aby upewnić się, że zwierzęcym Kardynalność Optymalizator zapewnia dobre szacunków, należy najpierw upewnij się, że opcje zestawu AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS bazy danych są (ustawienie domyślne) lub że utworzono ręcznie statystyki na wszystkie kolumny w warunku kwerendy.Następnie Projektując warunki w kwerendach, wykonaj następujące czynności, gdy jest możliwe:

  • Należy unikać używania zmiennych lokalnych w kwerendach.Natomiast używać parametrów, literały lub wyrażenia w kwerendzie.

  • Ogranicz używanie operatory i funkcje osadzone w kwerendzie zawiera parametr do tych wymienionych w Compile-Time Expression Evaluation for Cardinality Estimation.

  • Upewnij się, że tylko do stała wyrażeń w warunku kwerendy są albo stała składane, lub może zostać oceniony przy kompilacji czas.

  • Jeśli trzeba użyć zmiennej lokalnej oszacować wyrażenie używane w kwerendzie, należy rozważyć oceny go w innym zakres niż kwerendy.Na przykład mogą być pomocne dla wykonaj jedną z następujących czynności:

    • Przekazać wartość zmiennej do procedura składowana, zawierający kwerendę, którą chcesz oszacować i mają zastosowanie kwerendy parametr procedury w zmiennej lokalnej.

    • Skonstruować ciąg, który zawiera kwerendę w części na podstawie wartości zmiennej lokalnej, a następnie wykonać ciąg przy użyciu języka SQL dynamic (EXEC lub sp_executesql).

    • Parameterize kwerendy i ją wykonać za pomocą sp_executesqli jako parametr przekazać wartość zmiennej do kwerendy.