The meat of the question: are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as `sp_executeSQL` / `sp_execute` also take advantage of them? I am not a DBA, so please use little words. Our application sends over prepared statements that, from the profiler, I see run all SQL through `sp_prepexec` which is a system procedure for both running `sp_prepare` and `sp_execute`. What I'm trying to do is figure out if I am benefiting from temp table caching. I've been using this guide with object_id() to examine behavior https://sqlkiwi.blogspot.com/2012/08/temporary-tables-in-stored-procedures.html Then point #3 on this blog post suggests that EXEC cannot use temp table caching, but leaves out whether sp_executeSQL can: http://blogs.msdn.com/b/turgays/archive/2013/09/18/exec-vs-sp-executesql.aspx In my query sent over via the client I have created a simple temp table. 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'); In profiler, I can see: 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 I also get a cachehit from this. However, the object_id of the temp table appears to be changing on me, which is not the behavior I would see if this temp table were created in a real stored procedure. However, when I run this same code through `sp_executeSQL`, I'm also seeing that the object_id of the temp table has changed. This leads me to believe that only "real" user created stored procedures take advantage of temp table caching.
Paul White (imported from SE)
>*Are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as `sp_executeSQL` / `sp_execute` also take advantage of them?* You need a real stored procedure (`CREATE PROCEDURE`) to benefit from temporary table caching. This includes *temporary* stored procedures (`#procname`). >*Point #3 on this blog post suggests that EXEC cannot use temp table caching, but leaves out whether sp_executeSQL can.* Notice that `EXECUTE` is used to run `sp_executesql`. Testing: There are many ways to check whether caching is occurring. Some of them are listed in my original article referenced in the question, some more methods are shown in my follow-up post, [Temporary Table Caching Explained], for example: 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'; Stored procedure input TVPs are also cached, and [starting with SQL Server 2012], these can also be cached when used with `sp_executesql`. See the linked CSS blog post for details. : https://sqlkiwi.blogspot.com/2012/08/temporary-object-caching-explained.html : http://blogs.msdn.com/b/psssql/archive/2013/02/26/temp-table-caching-improvement-for-table-valued-parameters-in-sql-server-2012.aspx