Magazyn zapytań SQL Server - co jest uważane za zapytanie „ad-hoc”?

Robiłem głębokie zanurzenie w magazynie zapytań SQL Server i często widzę odniesienia do zapytań „ad-hoc”. Jednak nie widziałem, co Query Store określa jako zapytanie ad-hoc. Widziałem miejsca, w których można by wywnioskować, że jest to zapytanie bez parametrów lub zapytanie wykonane tylko raz. Czy istnieje formalna definicja tego? Nie mam na myśli w ogóle. Mam na myśli, ponieważ odnosi się to do sklepu z zapytaniami.

Na przykład na tej stronie pokazano przykład usuwania zapytań ad-hoc ze składnicy zapytań, ale wygląda na to, że stosowane przez niego kryteria to wykonanie tylko jednego. To wydaje się być dziwną definicją zapytania ad-hoc. BTW, jeśli przejdziesz na stronę, wyszukaj „Usuń zapytania ad-hoc”.

Po krótkich poszukiwaniach nie udało mi się znaleźć konkretnego i satysfakcjonującego źródła z dokumentacji Microsoft, aby odpowiedzieć na to pytanie. Istnieje wiele dobrych opisów i definicji adhoc / ad-hoc / ad hoc stron trzecich, ale myślę, że ze względu na specyfikę tego pytania jedno blisko źródła jest idealne.

Przechodząc obok ogólnych (ale wciąż dokładnych) definicji, takich jak ten post SO (dzięki SqlWorldWide), jeśli spojrzymy na to, co mówi dokumentacja na ten temat, zgadza się z tym, co wspomniałeś o definicji opartej na liczbie wykonań Myślę, że możemy uznać to za fakt.

Artykuł na temat optymalizacji przepływów pracy ad hoc mówi:

Gdy ta opcja jest ustawiona na 1, aparat bazy danych przechowuje mały skrót skompilowanego planu w pamięci podręcznej planu, gdy partia jest kompilowana po raz pierwszy, zamiast pełnego skompilowanego planu. Pomaga to zmniejszyć presję pamięci, uniemożliwiając zapełnienie pamięci podręcznej planu skompilowanymi planami, które nie są ponownie używane. Skompilowany skrót planu pozwala aparatowi bazy danych rozpoznać, że ta partia ad hoc została wcześniej skompilowana, ale zapisała tylko skompilowany skrót planu, więc gdy ta partia zostanie ponownie wywołana (skompilowana lub wykonana), aparat bazy danych skompiluje partię ... i dodaje pełny skompilowany plan do pamięci podręcznej planu.

Wygląda więc na to, że opcja konfiguracji serwera zapytań ad hoc wykorzystuje również definicję pojedynczego wykonania jako definicję ad hoc. Jeśli zapytanie będzie nadal wykonywane i wygeneruje ten sam plan, nie będzie już tak traktowane.

Artykuł na temat najlepszych praktyk dla sklepu z zapytaniami również się z tym zgadza,

Porównaj liczbę odrębnych wartości skrótu zapytania z całkowitą liczbą wpisów w sys.query_store_query. Jeśli stosunek jest bliski 1, obciążenie ad-hoc generuje różne zapytania.

Dotyczy to oczywiście zapytań, które nie są jeszcze używane jako procedury składowane, parametryzowane itp., Ponieważ można je rozpoznać i odpowiednio potraktować od razu.

Na podstawie tego wszystkiego możemy powiedzieć, że zapytanie jest traktowane jako ad hoc, jeśli:

• Nie jest sparametryzowany
• To nie jest programowo przechowywane w bazie danych (przechowywane proc, funkcja, wyzwalacz itp.)
• To samo zapytanie jest wykonywane tylko raz LUB To samo zapytanie jest wykonywane wielokrotnie, ale generuje inny plan zapytań dla każdego kolejnego wykonania.

W przypadku zapytań ad-hoc kolumna object_id w sys.query_store_queryDMV będzie wynosić 0, jak podano w dokumentacji sys.query_store_query :

identyfikator_obiektu :

Identyfikator obiektu bazy danych, którego częścią jest zapytanie (procedura składowana, wyzwalacz, CLR UDF / UDAgg itp.). 0, jeśli zapytanie nie jest wykonywane jako część obiektu bazy danych (zapytanie ad-hoc).

Możesz identyfikować zapytania ad-hoc na podstawie tej wartości, nawet jeśli nie jest to wyraźnie określone jako „jest to definicja zapytań ad-hoc ”. :)

Termin ad-hoc jest używany w odniesieniu do zapytań, które zostały wykonane tylko raz. Jest to zgodne z definicją zastosowaną dla ustawienia bazy danych „Optymalizuj pod kątem obciążeń ad-hoc”.

Odnośny link dotyczy usuwania zapytań ad-hoc obejmuje tę konkretną definicję „Usuń zapytania ad-hoc: usuwa zapytania, które zostały wykonane tylko raz i które mają więcej niż 24 godziny”.

Ad-hoc z kolumny sys.dm_exec_cached_plans dmv objtypew docs.microsoft.com.

Adhoc: zapytanie ad hoc. Odnosi się do języka Transact-SQL przesłanego jako zdarzenia językowe przy użyciu osql lub sqlcmd zamiast jako zdalnych wywołań procedur.

Jednym z zastosowań tej kolumny jest sprawdzenie w przypadku zapytań z wieloma planami, czy przyczyną jest wiele planów Adhoc.

    SELECT  DISTINCT 
        QCP.objtype
        ,MultipleQ.PlanCount
        ,qStat.query_hash
        ,sText.text AS QueryText
FROM ( 
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY MultipleQ.PlanCount DESC