Udostępnij za pomocą

Konfiguracja serwera: optymalizowanie pod kątem obciążeń ad hoc

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

Ta optimize for ad hoc workloads opcja służy do poprawy wydajności pamięci podręcznej planu dla obciążeń zawierających wiele partii ad hoc z jednym użyciem. Gdy ta opcja jest ustawiona na 1wartość , aparat bazy danych przechowuje mały skompilowany wycink planu w pamięci podręcznej planu, gdy partia jest kompilowana po raz pierwszy, zamiast pełnego skompilowanego planu. Ta opcja może pomóc zmniejszyć wykorzystanie pamięci, nie zezwalając pamięci podręcznej planu na wypełnienie skompilowanych planów, które nie są ponownie używane. Jednak włączenie tej opcji może mieć wpływ na możliwość rozwiązywania problemów z planami z jednym użyciem.

Skompilowany wycink planu umożliwia aparatowi bazy danych rozpoznanie, że ta partia ad hoc została skompilowana wcześniej i przechowuje tylko skompilowany wycink planu. Gdy ta partia zostanie ponownie wywołana (skompilowana lub wykonana), aparat bazy danych skompiluje partię, usunie skompilowany wycink planu z pamięci podręcznej planu i doda pełny skompilowany plan do pamięci podręcznej planu.

Skompilowane wycinki planu można znaleźć, wysyłając zapytanie do widoku wykazu i wyszukując sys.dm_exec_cached_plans ciąg "Skompilowany plan" w kolumnie cacheobjtype . Wycinkę ma unikatowy plan_handleelement . Skompilowany wycink planu nie ma skojarzonego z nim planu wykonania, a wykonywanie zapytań dotyczących uchwytu planu nie zwraca graficznego ani xml showplanu.

Flaga śledzenia 8032 przywraca parametry limitu pamięci podręcznej do ustawienia RTM dla programu SQL Server 2005 (9.x), co ogólnie pozwala na większą pojemność pamięci podręcznej. Użyj tego ustawienia, gdy często używane wpisy pamięci podręcznej nie mieszczą się w pamięci podręcznej i gdy optimize for ad hoc workloads opcja nie może rozwiązać problemu z pamięcią podręczną planu.

Ostrzeżenie

Flaga śledzenia 8032 może spowodować słabą wydajność, jeśli duże pamięci podręczne pozostawiają mniej dostępnej pamięci dla innych odbiorców pamięci, takich jak pula buforów.

Uwagi

optimize for ad hoc workloads Ustawienie opcji 1 wpływa tylko na nowe plany; plany, które znajdują się już w pamięci podręcznej planu, nie mają wpływu.

Aby natychmiast wpłynąć na już buforowane plany zapytań, pamięć podręczna planu musi zostać wyczyszczone przy użyciu polecenia ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE lub program SQL Server musi zostać uruchomiony ponownie.

Rekomendacje

Unikaj posiadania dużej liczby planów z jednym użyciem w pamięci podręcznej planu. Typowe przyczyny to:

  • Typy danych parametrów zapytania, które nie są spójnie zdefiniowane. Dotyczy to szczególnie długości ciągów, ale może dotyczyć dowolnego typu danych, który ma maksymalną dokładność lub skalę. Jeśli na przykład parametr o nazwie @Greeting jest przekazywany jako nvarchar(10) w jednym wywołaniu i nvarchar(20) podczas następnego wywołania, dla każdego rozmiaru parametru są tworzone oddzielne plany.

  • Zapytania, które nie są sparametryzowane. Jeśli zapytanie ma co najmniej jeden parametr, dla którego zakodowane wartości są przesyłane do aparatu bazy danych, może istnieć duża liczba planów zapytań dla każdego zapytania. Plany mogą istnieć dla każdej kombinacji typów danych parametrów zapytania i długości, które zostały użyte.

Jeśli liczba planów pojedynczego użycia ma znaczną część pamięci aparatu bazy danych programu SQL Server na serwerze OLTP, a te plany są planami ad hoc, użyj tej opcji serwera, aby zmniejszyć użycie pamięci z tymi obiektami.

optimize for ad hoc workloads Jeśli ta opcja jest włączona, nie można wyświetlić planów wykonywania dla zapytań z jednym użyciem, ponieważ buforowane są tylko wycinki planu. W zależności od środowiska i obciążenia możesz skorzystać z następujących dwóch funkcji:

  • Funkcja Magazynu zapytań , wprowadzona w programie SQL Server 2016 (13.x), pomaga szybko znaleźć różnice wydajności spowodowane zmianami planu zapytań. Magazyn zapytań jest domyślnie włączony w nowych bazach danych w programie SQL Server 2022 (16.x) i nowszych wersjach.

  • Wymuszona parametryzacja może zwiększyć wydajność niektórych baz danych, zmniejszając częstotliwość kompilacji zapytań i ponowne kompilowania. Bazy danych, które korzystają z wymuszonej parametryzacji, zwykle korzystają z dużych ilości współbieżnych zapytań ze źródeł, takich jak aplikacje typu punkt sprzedaży.

    Wymuszona parametryzacja może powodować problemy z wydajnością z powodu poufności parametrów. Aby uzyskać więcej informacji, zobacz Badanie i rozwiązywanie problemów z uwzględnieniem parametrów. W przypadku programu SQL Server 2022 (16.x) i nowszych wersji można również włączyć optymalizację planu poufnego parametru.

Przykłady

Aby znaleźć liczbę planów buforowanych z jedną pamięcią podręczną, uruchom następujące zapytanie:

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

Treści powiązane

  • Last updated on