sp_prepexec (sp_execute) 與 sp_executeSQL

問題的實質：實際儲存過程是實現臨時表記憶體的唯一機制還是系統儲存過程（例如sp_executeSQL/sp_execute也利用它們）？

我不是 DBA，所以請少說幾句。我們的應用程序發送了準備好的語句，從分析器中，我看到執行所有 SQL 通過sp_prepexec它是用於執行sp_prepare和sp_execute. 我想做的是弄清楚我是否從臨時表記憶體中受益。

我一直在使用本指南和 object_id() 來檢查行為

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

然後這篇部落格文章中的第 3 點建議 EXEC 不能使用臨時表記憶體，但忽略 sp_executeSQL 是否可以：http: //blogs.msdn.com/b/turgays/archive/2013/09/18/exec-vs- sp-executesql.aspx

在通過客戶端發送的查詢中，我創建了一個簡單的臨時表。

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');

在探查器中，我可以看到：

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

我也因此受到了打擊。但是，臨時表的 object_id 似乎在我身上發生了變化，如果這個臨時表是在真實的儲存過程中創建的，我不會看到這種行為。但是，當我通過 執行相同的程式碼時sp_executeSQL，我還看到臨時表的 object_id 已更改。這讓我相信只有“真正的”使用者創建的儲存過程才能利用臨時表記憶體。

實際儲存過程是實現臨時表記憶體的唯一機制還是系統儲存過程（例如sp_executeSQL/sp_execute也利用它們）？

您需要一個真正的儲存過程 ( CREATE PROCEDURE) 才能從臨時表記憶體中受益。這包括臨時儲存過程 ( #procname)。

此部落格文章中的第 3 點表明 EXEC 不能使用臨時表記憶體，但忽略了 sp_executeSQL 是否可以。

注意EXECUTE是用來執行的sp_executesql。

測試：有很多方法可以檢查是否正在發生記憶體。其中一些在問題中引用的我的原始文章中列出，更多方法在我的後續文章Temporary Table Caching Explained中顯示，例如：

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';

儲存過程輸入 TVP 也被記憶體，從 SQL Server 2012 開始，這些也可以在與sp_executesql. 有關詳細資訊，請參閱連結的 CSS 部落格文章。

引用自：https://dba.stackexchange.com/questions/85949