SQL Server Performance Forum – Threads Archive
What makes is the cuase for temdb growing
Hey There Genarally our tempdb in our QA and production databases remains small 2-4 gig, in the last 2 days 1 of our QA sql server instances seems to use 40gig of tempdb periodically. Is there anyway i can find out exactly what is causing tempdb to grow radically lately ?If i put a trace onb tempdb it will not tell me the size of the objects being created, i can also monitor the size growth of tempdb through a job that runs ever few minutes but once again i will not know what is the exact objects being created that are so huge. So bottom line how can i isolate exactly what is causing tempdb to grow so much ??? Thanx
I guess there are two aspecdts to consider.
1) The server side. There you can use Profile or a trace to see what’s going on
2) The client side. You might also need to check to applications. Especially those applications that have changed recently. When you say this happens since 2 days, I would probably start with 2) and check these apps whether they are committing or rolling-back transactions properly. Search the KB. I know there are some articles about that, however I can’t find the links right now. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Run trace against user tables also, based on your DML SQL Server will create a "worker" tables in tempdb to produce the query. http://www.aspfaq.com/show.asp?id=2446 to avoid the situation. Also check the indexes on each table. tempdb is probably being used to store the results of a sort so that a join can be performed. This may not be necessary if you have compatible indexes on each table. My guess is that tempdb may grow the same amount regardless of the number of differences
because the intermediate result is needed for the join, which most likely must occur before checking for differences.
Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanx Frank and Satya Thats just the problem, nothing has changed, i know somehting must have , but from an application point of view nothing has gone to QA this week.The only thing that has changed it that DB’s were changed to full recovery model, but i dont see how that effects tempdb, very starnge. Thanx Satya that link was very useful i am sure i can use that info to track down the problem. Thanx alot
I don’t know what changed, but you may run a trace, store results in the table and select top N rows based on descending writes order. Writting in tempdb should produce a lot of writes.
Hi Guys Thanx for all the feedback.
But i need some clarity, i have been monitoring the temp size and checking the sysindexes in tempdb when it grows. I have found that when tempdb grows to almost 40gig there is no #table in tempdb ??? So i am sure this is not a temp table problem by a big order by , or sort happening in tempdb, i am presuming these are not created as # tables in tempdb, is this the worker tables you are referring to ??? How are these tables identified in tempdb ?
If i run a trace looking for writes , it must be writing to something.
What i am trying to say it that it does not seem that these are #tables, does anyone know there format name? Or how to monitor tempdb size when it involves order or sorts not #tables ? Thanx
Hi ya, work tables don’t appear in the system tables so you can’t see tham as far as I know. Running a trace looking for any statements on that server with a lot of write activity is the only way that you may be able to track this down…? Cheers
Twan
By the time you’re looking at the objects in tempdb, they might have created and destroyed during the process of that working tables and sort conditions. As referred by Twan use a server-side trace for more information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
quote:Originally posted by [email protected]
Hi Guys
Thanx for all the feedback.
But i need some clarity, i have been monitoring the temp size and checking the sysindexes in tempdb when it grows. I have found that when tempdb grows to almost 40gig there is no #table in tempdb ??? So i am sure this is not a temp table problem by a big order by , or sort happening in tempdb, i am presuming these are not created as # tables in tempdb, is this the worker tables you are referring to ???
# tables or work-tables, whatever is written into tempdb.
But i need some clarity, i have been monitoring the temp size and checking the sysindexes in tempdb when it grows. I have found that when tempdb grows to almost 40gig there is no #table in tempdb ??? So i am sure this is not a temp table problem by a big order by , or sort happening in tempdb, i am presuming these are not created as # tables in tempdb, is this the worker tables you are referring to ???
quote:Originally posted by [email protected]How are these tables identified in tempdb ?
If i run a trace looking for writes , it must be writing to something.
What i am trying to say it that it does not seem that these are #tables, does anyone know there format name? Or how to monitor tempdb size when it involves order or sorts not #tables ? Thanx
Just find top N queries based on writes and tune them not to use tempdb as much. If i run a trace looking for writes , it must be writing to something.
What i am trying to say it that it does not seem that these are #tables, does anyone know there format name? Or how to monitor tempdb size when it involves order or sorts not #tables ? Thanx
]]>