Każda partia powoduje kompilację

Mamy aplikację innej firmy, która wysyła instrukcje T-SQL partiami.

Baza danych jest hostowana na SQL Server 2016 Enterprise SP1 CU7, 16 rdzeniach i 256 GB pamięci. Optymalizacja pod kątem Ad-Hoc jest włączona.

To jest fikcyjny przykład wykonywanych zapytań:

exec sp_executesql N'
IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT

select field1, field2 from table1 where field1=@1
option(keep plan, keepfixed, loop join)

select field3, field4 from table2 where field3=@1
option(keep plan, keepfixed, loop join)', N'@1 nvarchar(6)',@1=N'test'

Kiedy monitoruję bazę danych i patrzę na partie / sek i kompilacje / sek, zauważam, że zawsze są takie same. Przy dużym obciążeniu może to być 1000 partii / sek. I 1000 kompilacji / sek. Przy średnim obciążeniu jest 150 partii / sek.

Analizuję pamięć podręczną zapytań dla ostatnio skompilowanych planów:

SELECT TOP (1000) qs.creation_time
    , DatabaseName = DB_NAME(st.dbid)
    , qs.execution_count
    , st.text
    , qs.plan_handle
    , qs.sql_handle
    , qs.query_hash 
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st
ORDER BY creation_time DESC;

Po uruchomieniu powyżej zapytania widzę tylko 10-20 nowych planów zapytań na sekundę.

To tak, jakby każde sp_executesqlwywołanie uruchamiało kompilację, ale plan zapytania nie jest buforowany.

Co może być przyczyną, że partie / s są równe kompilacjom / s?

To tak, jakby każde sp_executesqlwywołanie uruchamiało kompilację, ale plan zapytań nie jest buforowany.

SQL Server nie buforuje planu zapytań dla partii zawierających tylko sp_executesqlwywołanie. Bez planu buforowanego kompilacja zachodzi za każdym razem. Jest to zgodne z projektem i oczekiwano.

SQL Server unika buforowania partii przy niskim koszcie kompilacji. Szczegóły tego, co jest i nie jest buforowane, zmieniało się wiele razy na przestrzeni lat. Zobacz moją odpowiedź na flagę Trace 2861 i to, co tak naprawdę oznacza plan „zerowego kosztu”, aby poznać szczegóły.

Krótko mówiąc, prawdopodobieństwo ponownego użycia (w tym określonych wartości parametrów) jest niewielkie, a koszt skompilowania tekstu ad hoc zawierającego sp_executesqlwywołanie jest bardzo mały. Wewnętrzna sparametryzowana partia sp_executesqljest oczywiście buforowana i ponownie wykorzystywana - taka jest jej wartość. Sama rozszerzona procedura składowana sp_executesqljest również buforowana.

Aby zostać zbuforowane i ponownie użyte, sp_executesqlinstrukcja musiałaby być częścią większej partii, którą uważa się za wartą buforowania. Na przykład:

-- Show compilation counter
SELECT
    DOPC.[object_name],
    DOPC.cntr_value
FROM sys.dm_os_performance_counters AS DOPC
WHERE
    DOPC.counter_name = N'SQL Compilations/sec'
GO
-- This is only here to make the batch worth caching
DECLARE @TC integer =
(
    SELECT TOP (1) @@TRANCOUNT 
    FROM master.dbo.spt_values AS SV
);

-- Example call we are testing
-- (use anything for the inner query, this example uses the Stack Overflow database
EXECUTE sys.sp_executesql 
    N'SELECT LT.Type FROM dbo.LinkTypes AS LT WHERE LT.Id = @id;', 
    N'@id int', 
    @id = 1;
GO
-- Show compilation counter again
SELECT
    DOPC.[object_name],
    DOPC.cntr_value
FROM sys.dm_os_performance_counters AS DOPC
WHERE
    DOPC.counter_name = N'SQL Compilations/sec'

Uruchom ten kod kilka razy. Jednak po raz pierwszy wiele kompilacji jest zgłaszanych zgodnie z oczekiwaniami. Za drugim razem nie są zgłaszane żadne kompilacje, chyba że optimize for ad hoc workloadsjest włączone (więc buforowany jest tylko Skompilowany Plan Stub ). Po raz trzeci nie są zgłaszane żadne kompilacje, ponieważ każdy kod pośredniczący jest promowany do w pełni zbuforowanego planu ad hoc.

Usuń DECLARE @TCinstrukcję, aby przekonać się, że sys.sp_executesqlinstrukcja nigdy nie jest buforowana bez niej, niezależnie od tego, ile razy zostanie wykonana.

Wyświetl powiązane wpisy pamięci podręcznej planu za pomocą:

-- Show cached plans
SELECT
    DECP.refcounts,
    DECP.usecounts,
    DECP.size_in_bytes,
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DECP.parent_plan_handle,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE 
    DEST.[text] LIKE N'%sp_executesql%'
    AND DEST.[text] NOT LIKE N'%dm_exec_cached_plans%';

Powiązane pytania i odpowiedzi: Czy wyzwalacze kompilują się za każdym razem?

Możesz przybliżać to, co widzisz w Monitorze wydajności i Monitorze aktywności dla SQL Compilations/seci Batch Requests/secpodczas uruchamiania niektórych partii w osobnym oknie zapytania jako testu, jak opisano poniżej.

Okno zapytania 1:

DECLARE @t1 datetime;
DECLARE @t2 datetime;
DECLARE @CompVal1 int;
DECLARE @CompVal2 int;
DECLARE @ReCompVal1 int;
DECLARE @ReCompVal2 int;
DECLARE @BatchVal1 int;
DECLARE @BatchVal2 int;
DECLARE @ElapsedMS decimal(10,2);

SELECT @t1 = GETDATE()
    , @CompVal1 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'SQL Compilations/sec                                                                                                            '
        )
    , @ReCompVal1 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'SQL Re-Compilations/sec                                                                                                         '
        )
    , @BatchVal1 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'Batch Requests/sec                                                                                                              '
        );

WAITFOR DELAY '00:00:10.000';

SELECT @t2 = GETDATE()
    , @CompVal2 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'SQL Compilations/sec                                                                                                            '
        )
    , @ReCompVal2 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'SQL Re-Compilations/sec                                                                                                         '
        )
    , @BatchVal2 = (
        SELECT spi.cntr_value
        FROM sys.sysperfinfo spi
        WHERE spi.counter_name = 'Batch Requests/sec                                                                                                              '
        );

SET @ElapsedMS = DATEDIFF(MILLISECOND, @t1, @t2);
SELECT  ElapsedTimeMS = @ElapsedMS
    , [SQL Compilations/sec] = (@CompVal2 - @CompVal1) / @ElapsedMS * 1000 
    , [SQL Recompilations/sec] = (@ReCompVal2 - @ReCompVal1) / @ElapsedMS * 1000
    , [Batch Requests/sec] = (@BatchVal2 - @BatchVal1) / @ElapsedMS * 1000;

W oknie zapytania 2 uruchom następujące polecenie podczas działania powyższego kodu. Kod wykonuje po prostu 100 partii T-SQL:

EXEC sys.sp_executesql N'SELECT TOP(1) o.name FROM sys.objects o;';
GO 100

Jeśli wrócisz do okna zapytania 1, zobaczysz coś takiego:

╔═══════════════╦══════════════════════╦══════════ ══════════════╦════════════════════╗
║ ElapsedTimeMS ║ Kompilacje SQL / s ║ Rekompilacje SQL / s Request Żądania wsadowe / s ║
╠═══════════════╬══════════════════════╬══════════ ══════════════╬════════════════════╣
║ 10020,00 ║ 10,07984031000 ║ 0,00000000000 ║ 10,07984031000 ║
╚═══════════════╩══════════════════════╩══════════ ══════════════╩════════════════════╝

Jeśli spojrzymy na to zapytanie:

SELECT dest.text
    , deqs.execution_count
FROM sys.dm_exec_query_stats deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
WHERE dest.text LIKE 'SELECT TOP(1)%'

Możemy potwierdzić, że wykonano 100 zapytań testowych.

W powyższych wynikach widać, że otrzymujemy kompilacje za każdym razem, gdy sp_executesqlwykonywana jest instrukcja. Plan na pewno jest buforowany, ale widzimy kompilację; co daje?

W Microsoft Docs powiedzieć o sp_executesql:

sp_executesql działa tak samo jak EXECUTE w odniesieniu do partii, zakresu nazw i kontekstu bazy danych. Instrukcja Transact-SQL lub partia w parametrze sp_executesql @stmt nie jest kompilowana, dopóki nie zostanie wykonana instrukcja sp_executesql. Zawartość @stmt jest następnie kompilowana i wykonywana jako plan wykonania odrębny od planu wykonania partii o nazwie sp_executesql.

Tak więc sp_executesql sama kompiluje się przy każdym uruchomieniu, nawet jeśli plan tekstu polecenia jest już w pamięci podręcznej planu. @PaulWhite pokazuje w swojej odpowiedzi, że większość wywołań do sp_executesql w rzeczywistości nie jest buforowana.