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_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_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 http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
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.