| 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.#1DF95FE0dbo.#4B14BA36Now 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 |
|
|
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. |
 |
|
|
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 |
|
|
|
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). |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 12:14:06
|
| I'd suggest thoroughly reading the article I linked. |
|
|
|
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. |
|
|
|
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? |
|
|
|
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 MemoryTotal Target MemoryPage Life ExpectancyBuffer Cache Hit Ratio (not an indicator of good performance, but can point out bad)Average Disk Read Queue LengthWatch PLE and see if it dramatically changes over time. |
|
|
|
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" |
|
|
|
|
Temporary tables in SSMS that won't go away!