Czy istnieje sposób, aby zapobiec skalarnym UDF w kolumnach obliczeniowych przed hamowaniem równoległości?

Wiele napisano o zagrożeniach skalarnych UDF w SQL Server. Przypadkowe wyszukiwanie zwróci mnóstwo wyników.

Są jednak miejsca, w których Skalarny UDF jest jedyną opcją.

Na przykład: w przypadku XML: XQuery nie może być użyty jako obliczona definicja kolumny. Jedną z udokumentowanych przez Microsoft opcji jest użycie Scalar UDF do enkapsulacji XQuery w Scalar UDF, a następnie użycie go w kolumnie obliczeniowej.

Ma to różne efekty i niektóre obejścia.

• Wykonuje wiersz po wierszu, gdy tabela jest sprawdzana
• Zmusza wszystkie zapytania względem tabeli do uruchomienia szeregowego

Można obejść wykonywanie wiersza po rzędzie, schematyzując funkcję i utrwalając obliczoną kolumnę lub indeksując ją. Żadna z tych metod nie może zapobiec wymuszonej serializacji zapytań trafiających do tabeli, nawet jeśli nie podano odwołania do skalarnego UDF.

Czy istnieje znany sposób?

Tak, jeśli:

• używają programu SQL Server 2014 lub nowszego; i
• są w stanie uruchomić zapytanie z aktywną flagą śledzenia 176 ; i
• kolumna obliczana to PERSISTED

W szczególności wymagane są co najmniej następujące wersje :

• Zbiorcza aktualizacja 2 dla programu SQL Server 2016 z dodatkiem SP1
• Zbiorcza aktualizacja 4 dla programu SQL Server 2016 RTM
• Zbiorcza aktualizacja 6 dla SQL Server 2014 SP2

ALE, aby uniknąć błędu (patrz 2014 i 2016 i 2017 ) wprowadzonego w tych poprawkach, zamiast tego zastosuj:

• Zbiorcza aktualizacja 1 dla programu SQL Server 2017
• Zbiorcza aktualizacja 5 dla programu SQL Server 2016 z dodatkiem SP1
• Zbiorcza aktualizacja 8 dla programu SQL Server 2016 RTM
• Zbiorcza aktualizacja 8 dla SQL Server 2014 SP2

Flaga śledzenia jest skuteczna jako –Topcja uruchamiania , zarówno w zakresie globalnym, jak i sesji, przy użyciu DBCC TRACEONi na zapytanie z OPTION (QUERYTRACEON)przewodnikiem planu.

Flaga śledzenia 176 zapobiega utrwaleniu obliczanej ekspansji kolumny.

Początkowe ładowanie metadanych wykonywane podczas kompilacji zapytania zawiera wszystkie kolumny, nie tylko te, do których bezpośrednio się odwołuje. To sprawia, że ​​wszystkie obliczone definicje kolumn są dostępne do dopasowywania, co ogólnie jest dobrą rzeczą.

Jako niefortunny efekt uboczny, jeśli jedna z załadowanych (obliczonych) kolumn używa skalarnej funkcji zdefiniowanej przez użytkownika, jej obecność wyłącza równoległość dla całego zapytania, nawet jeśli kolumna obliczeniowa nie jest faktycznie używana .

Flaga śledzenia 176 pomaga w tym, jeśli kolumna jest utrwalona, ​​nie ładując definicji (ponieważ interpretacja jest pomijana). W ten sposób skalarna funkcja zdefiniowana przez użytkownika nigdy nie jest obecna w drzewie kompilacji zapytań, więc równoległość nie jest wyłączona.

Główną wadą flagi śledzenia 176 (poza tym, że jest tylko lekko udokumentowana) jest to, że zapobiega ona również dopasowaniu wyrażenia zapytania do utrwalonych kolumn obliczeniowych: Jeśli zapytanie zawiera wyrażenie pasujące do utrwalonej kolumny obliczeniowej, flaga śledzenia 176 zapobiegnie zastąpieniu wyrażenia przez odniesienie do kolumny obliczanej.

Aby uzyskać więcej informacji, zobacz mój artykuł SQLPerformance.com, Właściwie utrwalone kolumny obliczeniowe .

Ponieważ pytanie wymienia XML, jako alternatywę dla promowania wartości za pomocą obliczonej kolumny i funkcji skalarnej, możesz również spojrzeć na użycie Selektywnego Indeksu XML, o czym pisałeś w Selektywnych Indeksach XML: Nieźle .

Oprócz doskonałego Tak # 1 autorstwa Paula, w rzeczywistości istnieje Tak 2, które:

• działa już w SQL Server 2005,
• nie wymaga ustawiania flagi śledzenia,
• ma nie wymagać, że kolumna obliczana być PERSISTED, i
• (ponieważ nie wymaga flagi śledzenia 176), nie zapobiega dopasowaniu wyrażenia zapytania do utrwalonych kolumn obliczeniowych

Jedyne wady (o ile wiem) to:

• nie działa na bazie danych Azure SQL (przynajmniej jeszcze nie, chociaż działa na serwerze Amazon RDS SQL Server oraz SQL Server w systemie Linux), i
• jest trochę poza strefą komfortu wielu DBA

Ta opcja to: SQLCLR

Zgadza się. Jednym fajnym aspektem SQLCLR Scalar UDF jest to, że jeśli nie robią żadnego dostępu do danych (ani użytkownika, ani systemu), to nie zabraniają równoległości. I to nie tylko teoria czy marketing. Chociaż nie mam czasu (w tej chwili) na wykonanie w pełni szczegółowego zapisu, przetestowałem to i udowodniłem.

Użyłem wstępnej konfiguracji z następującego posta na blogu (mam nadzieję, że OP nie uważa tego za niewiarygodne źródło 🙃):

Dżinsy Bad Idea: wiele wskazówek dotyczących indeksu

I przeprowadził następujące testy:

1. Uruchomiono pierwotne zapytanie tak jak jest ─⇾ Równoległość (zgodnie z oczekiwaniami)
2. Dodano nietrwałą kolumnę obliczaną zdefiniowaną jako ([c2] * [c3])─⇾ Równoległość (zgodnie z oczekiwaniami)
3. Usunięto tę kolumnę obliczeniową i dodano nietrwałą kolumnę obliczeniową, która odwoływała się do UDF Skalarnego T-SQL (utworzonego za pomocą SCHEMABINDING) zdefiniowanego jako RETURN (@First * @Second);─⇾ NO równoległości (zgodnie z oczekiwaniami)
4. Usunięto kolumnę obliczeniową T-SQL UDF i dodano nietrwałą kolumnę obliczeniową, która odwoływała się do skalarnego UDF SQLCLR (wypróbowanego z obydwoma IsDeterministic = truei = false) zdefiniowanego jako return SqlInt32.Multiply(First, Second);─⇾ Równoległość (woo hoo !!)

Tak więc, chociaż SQLCLR nie będzie działać dla wszystkich, z pewnością ma swoje zalety dla tych osób / sytuacji / środowisk, które są dobrze dopasowane. A ponieważ odnosi się do tego konkretnego pytania - podany przykład dotyczy użycia XQuery - na pewno by to działało (i, w zależności od tego, co konkretnie jest zrobione, może być nawet trochę szybsze 😎).