April 30, 2015 at 7:59 am
I've recently started a new position and our production box
Contains a procedure that uses 30 + temp tables. I'm currently not in a position to change this as it's production and I would have to be granted a window
to re-design.
However the tempDb is showing some strange activity.
If a table is created #CarrierService (CarrierServiceID,DeliveryZoneID,CollectionZoneID) for example
Once the procedure is called It will appear in the tempDB with the session info appended as expected
#CarrierService________________________________________________________2C78E45A
However once the session has ended the above table will get dropped and a new one created
#2C78E45A, I now have 7000 of these different Tables in the TempDB
When I Interogate this using
SELECT o.name, o.create_date,o.modify_date , c.Name,C.Column_Id
FROM tempdb.sys.Objects o
Inner join tempdb.Sys.Columns c
ON o.object_id =c.Object_ID
WHERE o.type ='U'
I get the Following results
name create_date modify_date object_id name
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CarrierServiceId
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CollectionZoneId
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282DeliveryZoneId
Notice How It's getting Modified today.
Can anyone explain what's happening?
Regards,
April 30, 2015 at 8:29 am
SimonH (4/30/2015)
I've recently started a new position and our production boxContains a procedure that uses 30 + temp tables. I'm currently not in a position to change this as it's production and I would have to be granted a window
to re-design.
However the tempDb is showing some strange activity.
If a table is created #CarrierService (CarrierServiceID,DeliveryZoneID,CollectionZoneID) for example
Once the procedure is called It will appear in the tempDB with the session info appended as expected
#CarrierService________________________________________________________2C78E45A
However once the session has ended the above table will get dropped and a new one created
#2C78E45A, I now have 7000 of these different Tables in the TempDB
When I Interogate this using
SELECT o.name, o.create_date,o.modify_date , c.Name,C.Column_Id
FROM tempdb.sys.Objects o
Inner join tempdb.Sys.Columns c
ON o.object_id =c.Object_ID
WHERE o.type ='U'
I get the Following results
name create_date modify_date object_id name
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CarrierServiceId
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CollectionZoneId
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282DeliveryZoneId
Notice How It's getting Modified today.
Can anyone explain what's happening?
Regards,
Not an expert on this, but I seem to remember hearing that temp tables don't really get dropped in tempdb but the structure actually gets cached and reused when the "same" temp table is created again. I'll try and get someone more knowledgeable on this to drop by.
April 30, 2015 at 8:33 am
Hi Lynn,
Thanks, sounds about right.
I clearly need to find some time to re-write this procedure.
Regards,
Simon
April 30, 2015 at 8:50 am
Lynn's seeming remembrance is spot-on. 🙂
A nice read on temporary object caching and what does/does not prevent it can be found here: http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx
Cheers!
April 30, 2015 at 8:57 am
Thanks Jacob,
That's exactly what I'm looking for.
Regards
May 1, 2015 at 3:39 pm
SimonH (4/30/2015)
I clearly need to find some time to re-write this procedure.
Not for the reason you posted.
Temp table caching is a feature, it's designed to reduce the overhead when creating new temp tables. They're 'cached' with only two pages so they don't take up tonnes of space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2015 at 4:01 pm
GilaMonster (5/1/2015)
SimonH (4/30/2015)
I clearly need to find some time to re-write this procedure.Not for the reason you posted.
Temp table caching is a feature, it's designed to reduce the overhead when creating new temp tables. They're 'cached' with only two pages so they don't take up tonnes of space.
Since you are mentioning temp table caching, is there value in doing checkDB on tempdb?
I thought that temp tables were dropped after losing scope and so thought checkdb would be pointless.
Maybe put another way, if I did not do checkdb on tempdb, what could go wrong?
May 1, 2015 at 4:12 pm
As I understand it, you probably should run CHECKDB against tempdb, since corruption can occur, and the files aren't zeroed out on restart, so the corruption can stick around.
Here http://www.sqlservercentral.com/Forums/Topic1025006-266-1.aspx, Paul Randal suggests that you should check it, albeit less frequently.
It's also recommended in this article[/url] to check tempdb.
Cheers!
May 1, 2015 at 4:15 pm
Jacob Wilkins (5/1/2015)
As I understand it, you probably should run CHECKDB against tempdb, since corruption can occur, and the files aren't zeroed out on restart, so the corruption can stick around.Here http://www.sqlservercentral.com/Forums/Topic1025006-266-1.aspx, Paul Randal suggests that you should check it, albeit less frequently.
It's also recommended in this article[/url] to check tempdb.
Cheers!
Was a bit off topic so thanks for info.
May 2, 2015 at 11:01 am
MadAdmin (5/1/2015)
Maybe put another way, if I did not do checkdb on tempdb, what could go wrong?
You could need to restart SQL at an inopportune time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2015 at 1:28 am
Agreed,
However it still needs re-writing as it has a massive overuse of temp tables.
7,000 of them cached in the tempdb.
It was causing a performance issue.
I freed the proc cache and saw a drastic performance increase.
Regards,
Simon
May 6, 2015 at 7:31 am
Can't this be overcome by explicitly dropping the temp tables in the connection. Yes, in theory it goes away, but my understanding is that it doesn't go away in temp immedidately unless explicitly dropped.
May 6, 2015 at 7:35 am
RonKyle (5/6/2015)
Can't this be overcome by explicitly dropping the temp tables in the connection.
Why do you want to bypass a feature that's designed to reduce contention on the TempDB allocation pages and reduce the overhead in creating temp tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 6, 2015 at 7:36 am
From the Paul White article I referenced earlier in the thread:
Dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS. None of these things prevent temporary table caching (so it does not matter whether you explicitly drop a temporary table at the end of a procedure or not).
(emphasis mine)
Cheers!
May 6, 2015 at 7:36 am
Hi,
The tables are getting explicitly dropped in the procedure.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply