sp_prepexec (sp_execute) vs. sp_executeSQL

Najważniejsze pytanie: czy faktyczne procedury składowane są jedynym mechanizmem, który implementuje buforowanie tabeli temp, czy też systemowe procedury składowane, takie jak sp_executeSQL/, sp_executerównież z nich korzystają?

Nie jestem DBA, więc proszę, użyj słów. Nasza aplikacja wysyła przygotowane instrukcje, które z profilera widzę, że uruchamiam wszystkie SQL, przez sp_prepexecktóre jest to procedura systemowa do uruchamiania sp_preparei sp_execute. Próbuję ustalić, czy korzystam z buforowania tabeli tymczasowej.

Korzystałem z tego przewodnika z object_id () do badania zachowania

https://sqlkiwi.blogspot.com/2012/08/temporary-tables-in-stored-procedures.html

Następnie punkt 3 tego postu na blogu sugeruje, że EXEC nie może używać buforowania tabeli tymczasowej, ale pomija, czy sp_executeSQL może: http://blogs.msdn.com/b/turgays/archive/2013/09/18/exec-vs- sp-executesql.aspx

W moim zapytaniu przesłanym za pośrednictwem klienta utworzyłem prostą tabelę tymczasową.

DECLARE @foo int; -- set by JDBC, unused but required to force a prepared statement

SELECT 1 AS id
    INTO #tmp

SELECT OBJECT_ID('tempdb..#tmp');

W profilerze widzę:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 int',N'declare @foo INT = @P1 

SELECT 1 as id
    into #tmp

select Object_id(''tempdb..#tmp'');
DROP TABLE #tmp;',1
select @p1

Dostaję także cachehit. Jednak obiekt_id tabeli temp wydaje się zmieniać na mnie, co nie jest zachowaniem, które widziałbym, gdyby tabela temp została utworzona w rzeczywistej procedurze składowanej. Jednak po uruchomieniu tego samego kodu sp_executeSQLwidzę również, że identyfikator_obiektu tabeli tymczasowej zmienił się. To prowadzi mnie do przekonania, że ​​tylko „rzeczywiste” procedury przechowywane utworzone przez użytkownika korzystają z buforowania tabeli tymczasowej.

Czy faktyczne procedury składowane są jedynym mechanizmem, który implementuje buforowanie tabeli temp, czy też systemowe procedury składowane, takie jak sp_executeSQL/, sp_executerównież z nich korzystają?

Potrzebujesz prawdziwej procedury składowanej ( CREATE PROCEDURE), aby skorzystać z tymczasowego buforowania tabeli. Obejmuje to tymczasowe procedury składowane ( #procname).

Punkt 3 w tym poście na blogu sugeruje, że EXEC nie może używać buforowania tabeli tymczasowej, ale pomija, czy sp_executeSQL może.

Zauważ, że EXECUTEjest używany do uruchomienia sp_executesql.

Testowanie: Istnieje wiele sposobów sprawdzenia, czy występuje buforowanie. Niektóre z nich są wymienione w moim oryginalnym artykule, do którego odwołuje się pytanie, inne metody zostały przedstawione w moim kolejnym poście, Objaśnienie buforowania tabel tymczasowych , na przykład:

SELECT 
    DOMCC.name,
    DOMCC.pages_kb,
    DOMCC.pages_in_use_kb,
    DOMCC.entries_count,
    DOMCC.entries_in_use_count
FROM sys.dm_os_memory_cache_counters AS DOMCC
WHERE DOMCC.[type] = N'CACHESTORE_TEMPTABLES';

Wejściowe procedury przechowywane TVP są również buforowane, a począwszy od SQL Server 2012 , można je również buforować, gdy są używane z sp_executesql. Zobacz link do blogu CSS, aby uzyskać szczegółowe informacje.