Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Temporary tables in SSMS that won't go away!

Author  Topic 

holster100
Starting Member

13 Posts

Posted - 2010-08-25 : 07:51:27
We're having a few performance issues, and we've noticed our TempDB buffer pool is very high, at around 1300 MB.

On further investigation, we seem to have a lot of temporary tables that simply won't go away. When you look in the tempdb using SSMS, under the "temporary tables" list, we have a number of tables named like:

dbo.#1DF95FE0
dbo.#4B14BA36

Now these aren't the temp tables we user (and always drop) in our stored procedures, but if we run the following script:

USE [TEMPDB]

SELECT * FROM syscolumns WHERE id = ( SELECT id FROM sysobjects WHERE [Name] LIKE '#1DF95FE0')

Each one shows a structure identical to a temp table created in our SPs. (not all the same temp table, they vary)

Some of these objects are gowing in size, and we have seen have 1mil+ pages and be over 100MB.

We've double checked the SPs to make sure we are dropping the temp tables (and they're named differently anyway).

Any ideas what these #323232323 temp tables are, or why they are appearing?

Thanks everyone in advance.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-25 : 08:39:32
SQL Server creates them.

http://technet.microsoft.com/en-us/library/cc966545.aspx

by the way, 1 million pages is a LOT more than 100MB -- it's 7.63GB
Go to Top of Page

holster100
Starting Member

13 Posts

Posted - 2010-08-25 : 09:06:53
Hmm... so why are they not removed after the query has run? They are sitting there taking up lots of memory.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-25 : 09:33:34
what isolation level are you using? are these frequently referenced tables? how much RAM available to SQL Server? I wouldn't call 1GB a lot of memory
Go to Top of Page

holster100
Starting Member

13 Posts

Posted - 2010-08-25 : 10:23:08
we are using the default Isolation Level. They are frequently referenced tables yes - they are searched by users maybe 10-20 times a minute. The box has 4 gigs memory, and we're using the 3gb switch. SQL server 2005 workgroup edition.

Weirdly, when we execute a very simple test script (create temp table, drop table) in the console window of SSMS it doesn't create a #etcetcetc table variable in the "temporary tables" section of tempbdb, but if we execute the exact same script as a stored procedure it does. Why is this? If it's just caused by the engine running an SP, why do they remain for ages, and why do the most executed SPs keep growing and growing (by about 100MB in the buffer pool per hour).
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-25 : 12:14:06
I'd suggest thoroughly reading the article I linked.
Go to Top of Page

holster100
Starting Member

13 Posts

Posted - 2010-08-25 : 12:25:33
Thanks russell. I have read that article, but unfortunately not everyone who posts on these forums for help is in a position to fully understand microsoft's technical articles! I was kind of hoping for a little more advice and opinion on my problem specifically.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-25 : 13:20:53
A couple of things are happening. One, SQL will copy data into tempdb to avoid concurrency problems (deadlocks) depending on your isolation level. That's why I asked about that.

Additionally, it used to be (in sql 2000) that tables were dropped in tempdb as soon as either they were explicitly dropped, or went out of scope. Beginning with SQL 2005, this is no longer true. Drops are deferred until a cleanup thread comes along and drops 'em. This enhances performance, but leaves objects laying around for a while.

In short, I wouldn't worry about it, unless you're experiencing problems -- which these tables are almost certainly not causing.

Are you seeing memory pressure? Or just curious about these? The memory utilized by tempdb may not be related to the persistence of these tables at all.

How many processors in your system and how many files is your tempdb spread accross? What is the size of these files?

When was the last service restart?
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-25 : 13:24:19
Also, in perfmon, what are you seeing for these:

Total Server Memory
Total Target Memory
Page Life Expectancy
Buffer Cache Hit Ratio (not an indicator of good performance, but can point out bad)
Average Disk Read Queue Length

Watch PLE and see if it dramatically changes over time.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 14:30:52
PLE = Page Lifetime Expectancy



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

- Advertisement -