or
J T imported from SE
sql-server
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.
Top Answer
Paul White
>*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][1], 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][2], these can also be cached when used with `sp_executesql`. See the linked CSS blog post for details.

  [1]: https://sqlkiwi.blogspot.com/2012/08/temporary-object-caching-explained.html
  [2]: http://blogs.msdn.com/b/psssql/archive/2013/02/26/temp-table-caching-improvement-for-table-valued-parameters-in-sql-server-2012.aspx
Are actual stored procedures the only mechanism that implements temp table caching?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.