THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on SQLperformance.com

Temporary Table Caching Explained

Published Friday, August 17, 2012 11:51 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Roji Thomas said:

Thanks Paul. Excellent as usual.

August 16, 2012 8:33 PM
 

tobi said:

This is such a thorough treatment of the subject, just like the last post. It's the final word.

August 17, 2012 7:26 AM
 

Paul Randal said:

More good stuff - better go update my post... :-)

August 22, 2012 5:02 PM
 

Patrick Ge said:

Thanks Paul. Great stuff. I've learned a lot from these two posts about which I never thought this deep before.

I was reading "The runtime contents of a temporary object (table or variable) are obviously specific to a particular execution, so it makes sense for the cached object to be associated with execution contexts rather than the parent plan" and trying to simulate this by simply making a little change to your script. Below is the updated script. When I ran it in one query window and ran "EXECUTE dbo.Demo;" in another query window simultaneously. The result is perfect. Different exectutions don't interfere with each other's data in the temporary table and there is just one #demo table. However, after I put the insert statement into a transaction and let it wait for 5 seconds and did the same test again. I have two temporary tables. I can't think of any reason why another #Demo is created. It's like the 2nd query window created a new #Demo rather than using the exsiting cached one from 1st query window. Am I missing any points here?

I am just trying to get my head more confused ^_^. My apology if my question doesn't make sense or demonstrate I've completedly understood your posts.

[code moved to http://pastebin.com/5jpYWLDM]

August 22, 2012 9:30 PM
 

Paul White said:

Hi Patrick,

I'm not sure I follow exactly what you did, but I'm going to take a crack at it anyway.  I uncommented the delay in your procedure and ran it in two sessions, ensuring the executions overlapped.  In that case, as expected, two execution contexts were required (derived from the one cached plan) so two cached tables are seen.  If I have misunderstood your test, please feel free to correct me!

Paul

August 22, 2012 11:17 PM
 

SomewhereSomehow said:

Hi, Paul! Thanks for the post.

I saw no words about parameters issue, have you done any inverstigations of that, was it approved or disproved?

August 28, 2012 3:32 AM
 

Paul White said:

Hi Dima,

Sorry I forgot all about that.  I'll take a look at the thread and get back to you.

Paul

August 28, 2012 4:16 AM
 

SomewhereSomehow said:

Thanks, appreciated. As I understood cached object is "linked" to the plan of proc, to have multiple cached tables we should have multiple plans for one proc, and it is possible for example if there are different set options. But due to different params... I took a short look at our dbs to find out, if there are any plans, with the same cache keys, but multiple for one proc - and found nothing. But I didn't do deep investigations, so have no confirmation or disproof. If you find something, you are welcome to share it.

August 28, 2012 5:59 AM
 

Jason Yousef said:

Thanks Paul, great article...I'm overwhelmed!!

October 1, 2012 8:35 AM
 

mjswart said:

I would love to be able to match a query plan to a tempdb table.

It might be possible given that when a query plan is removed from cache, those tempdb tables are cleaned up.

So SQL Server knows how to map a table to a query plan, is that possible for us end users? It would help me go beyond number of temp tables and help me identify which queries are creating temp tables most often.

select * from sys.dm_os_performance_counters

where instance_name like '%Temporary Tables & Table Variables%'

or counter_name like '%temp table%'

September 10, 2013 11:11 AM
 

Eugene Karpovich said:

Hi Paul, thank you for the great article.

Have you ever come across an issue (which might be temp table caching related), when SQL Server issues "Invalid column name" exception in case two stored procs are involved in a query, each of which creates a temp table with the same name but different columns?

The repro script is below:

USE tempdb

GO

SET NOCOUNT ON

GO

IF EXISTS( SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID( N'dbo.p_inner'))

 DROP PROCEDURE dbo.p_inner

GO

CREATE PROCEDURE dbo.p_inner

AS

BEGIN

 SELECT the_string = '' INTO #temp

 SELECT the_string FROM #temp

END

GO

---------------------------------------

IF EXISTS( SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID( N'dbo.p_outer'))

 DROP PROCEDURE dbo.p_outer

GO

CREATE PROCEDURE dbo.p_outer

AS

BEGIN

 SELECT the_date = GETDATE() INTO #temp

 EXEC p_inner

END

GO

---------------------------------------

EXEC dbo.p_outer    -- ***** this statement raises the "Invalid column name 'the_string'." exception

GO

---------------------------------------

July 13, 2014 4:58 PM
 

Paul White said:

Hi Eugene,

Yes, I am familiar with that behaviour, which is weird but apparently "by design". Books Online CREATE TABLE (http://msdn.microsoft.com/en-us/library/ms174979.aspx) contains this statement:

"A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against."

The scoping rules for #tables are a bit of a mess, frankly.

July 13, 2014 7:40 PM
 

Eugene Karpovich said:

Thank you for the information, Paul. Apparently, I have missed that piece of the documentation. However, this is weird behavior I agree.

July 13, 2014 8:25 PM
 

Steve Hood said:

I'm seeing a lot of buffer pool space being used by TempDB that is linked to unallocated pages.  This space takes up to 1/3 of the buffer pool, and the only way I've found to get most of the space back is to "right-size" the TempDB data files instead of having them presized to fill the LUN.  

A reader on my blog mentioned that what you're discussing here appears to be the root cause, but I have my doubts.  Specifically, I'd expect that if this was the cause then freeing the proc cache would cause the memory to be reclaimed.  

Do you know if these two things are related?

My post, if you're interested, is at http://simplesqlserver.com/2016/01/05/tempdb-memory-leak/

January 6, 2016 4:46 PM
 

Paul White said:

Hi Steve,

Temporary table caching might well be a factor, but I have no specific knowledge to address the question your blog post raises definitively.

If you want my opinion though, I would only regard it as a bug or cause for concern if the buffer pool space used in this way were not quickly available for other uses.

SQL Server often keeps stuff in memory (e.g. plans that have been invalidated) simply because it is not worth the effort needed to locate such entries and remove them in advance of the space actually being required for something.

So, when memory pressure is detected, I would expect memory use like this to be freed up for reuse in pretty short order, by design. If it is not, and bad things happen because memory for unallocated tempdb is not released/reused, that would be a bug.

January 7, 2016 12:35 AM
 

Steve Hood said:

Thanks.  What I'm seeing is this memory building up over time to be somewhere around 1/4 the size of TempDB's data files, and it's not released at all when the instance is under pressure.  

I've decided to open a connect item on this: https://connect.microsoft.com/SQLServer/feedback/details/2215297

January 7, 2016 11:11 AM
 

Paul White said:

Steve, have an up vote :)

January 7, 2016 11:45 AM
 

sunil Kumar said:

Great article Paul.Thanks

I have a query here i am getting error DBCC SHRINKFILE:6:9719968 Page could not be moved because it is a work table page. I don't want to clear my cache just want to drop cache plan located on the page.using above queries unable to find any temp cache plans. CACHESTORE_TEMPTABLES showing 0 entry in use Can anyone guide here Please ?

August 22, 2016 7:46 PM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement