SQL Server Performance Forum – Threads Archive
Tempdb is collecting objects…they need to go????
I have noticed that my brand new server seems to get slower after a few days….I have tried a few things so far. 1) on 3/17/06…copied everything from the g: drive to the H: drive for a bitthis would be all LDF’s and the TempDB MDF and LDF 2) copied these back with the TempDB going back on the drive first then the larger LDF’s and then the other LDF’s 3) restarted the server 4) ran great on Friday night and Saturday night
started to get slower on Sunday through now 5) I have found the Sysobjects in the TempDB growing with objects such as
#171B5509
#73E72765
#0269858F
#26E47B22
#16AE1359
#12DD8275
#5048500F
#47524C04
#1DE48B6F anyone know what these are? the create date is all over the place.
if I do a couple of
EXEC sp_dboption ‘Finance’, ‘auto update statistics’, ‘False’
for the different DB’s they start to go away…. do a couple of these…they come back
EXEC sp_dboption ‘Finance’, ‘auto update statistics’, ‘True’ So, I figure they have to deal with the Stats in so form or fashion but they just don’t seem to want to leave. I am running on SQL Serve Enterprise 64 bit on AMD. Anyone know how I can get these to go away to see if that is the problem?
Or so I just turn off the Auto Update Statistics and make a job update them once a day?
if you create a temp table name: #blah
there should be an entry in temp sysobjects as: #blah_______________________________xxxxx these are just temp tables, unless there huge numbers, or huge space, it should not be a problem there should not be any really old temp tables, ie, why is a temp table being from yesterday?
restarting sql will clear the temp db, but find out why there are old temp tables
if I do something to create "if you create a temp table name: #blah
there should be an entry in temp sysobjects as: #blah_______________________________xxxxx
" I see those in the list…these are different they are "#" + 8 digit hex
which is much different then a #temp
right now I have 60….the more get in there…the slower the system gets.
(actually, just got another 6 in there in the last 10 minutes) so far, I can only get rid of them with a restart of SQL Server.
Which if I have to do that every 2 days……what a pain. I can’t figure out how to drop them….it just seems to be causing fragmentation inside the TempDb because they are made, then a real Temp Table is created and more are made and then the Temp Table is dropped and leaving potholes everywhere. Seems to me that something in the background is not doing good cleanup work.
what does the sysobjects uid columns say?
ie, lookup the uid in sysusers,
then find out what action is creating it unless you already think statistics update is causing this.
i am normally ok disabling auto stats update, running a daily job,
but per MS doc, indexes on identity columns, or other columns incremented sequentially should perhaps have statistics update more frequently than daily.
now up to 81 objects or tables or whatever they are they have been created by UID = 1
which is the DBO…. my thought is either statistics of some sort is causing this
or an internal temp Table that SQL had to use to get its job done and just never dropped it. I have turned off auto stats on all DBs and they are still being created so it looks like it is probably not that…or maybe some of them were created by it but have other stuff still going on.
try running profiler, include the Object:Created class, with object name permanent temp tables are not by itself bad, something else must be going on to cause problems
After 3 hours on the phone with MS…these are internal Temp Tables.
And yes, they are not being dropped….
so, running profiler…per their directions.
Waiting for it to happen again
]]>