TempDB Culprit please Inspector Clouseau

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)

TempDB Culprit please Inspector Clouseau

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-04-20 : 11:43:17

The Cowboys that configured the OLTP Server put the TempDB on the 20 gig C drive (Applications Drive). They also have a E drive (Data).
Last week I stopped the Server, deleted the TempDB, and started the server - in order to reclaim C drive space.
But the TempDB grows to take up the outstanding 8 gig again.
The developers say they aren't using any #tmp naughties and they aren't using cursors (although there is still some old VB6 code running using RDO/ADO where the RDO/ADO driver converts to cursors "under the hood" - yuck).

So other than getting plans in place to move the TempDB off to its own big RAID, I need to get to the bottom of what exactly causes this file to grow. I was thinking of deleting the file again and then watching it grow and watching a Trace in Profiler and the Current Activity in Studio Manager - with the aim of spotting the cause.
Is that realistic? If so, got any other tips?
Or is the TempDB growth more of a hidden/secret operation that DBA's can't ever hope to find details out about?
Is the growth happening because of Inserts/Updates/Deletes?
Could big select statements contribute to it growing?
Could a table like this influence/contribute to the problem?: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49038
What are the main reasons why a TempDB grows and how to you find the offending SQL? (if not the way I described)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-20 : 11:57:34

How frequently are you truncating the transaction log?

e4 d5 xd5 Nf6

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-20 : 12:13:49

Do you have lot of UNION,s TOP/ORDER BY's etc in your code?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-04-21 : 05:04:09

We have quite a few UNIONs that get called all the time.
We run the db's in Simple mode and backup nightly.

I see where you are heading, just read this http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

I think some of the old VB6 code that is running is donig this:
"ODBC DSN with the option 'create temporary stored procedures'"
I'm gonna check.

We also have quite a bit of this:
"large resultsets involving unions, order by / group by, table variables, and hashing"

DBCC CheckDB & DBCC DBREINDEX run every night for all DB's

So I'll do a review and see if I can iron any of these out and report back.
Let me know if you have any other ideas.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-21 : 20:32:14

If you enabled row versioning, will use lot of tempdb space.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-04-23 : 04:02:36

Hashing
-------
It doesn't seem to me like there is much I can do about that - even if I was able to establish that that was the case.

Order by
--------
Does it make sense to try and move this client-side? Is that good practice on larger result sets? Get a .NET collection with a Comparer to do it instead?

Row Versioning
--------------
Not enabled.

ODBC DSN with the option 'create temporary stored procedures'
-------------------------------------------------------------
Apparently we don't have any of these (I mistook it for some VB6 code that we have that creates SProcs on-the-fly). Could I monitor that by looking for a tell-tale symptom of it in Profiler?

Cursors
-------
You see a lot of this in Profiler (the cursor issue I mentioned at the outset):

declare @p1 int
set @p1=2355655444
declare @p3 int
set @p3=8
declare @p4 int
set @p4=1
declare @p5 int
set @p5=6
exec sp_cursoropen @p1 output,N'SELECT Distinct CustomerNumber,Firstname,Lastname FROM Customer WHERE Firstname LIKE ''TEM%'' AND Lastname LIKE ''MAR%'' Order By Lastname, Firstname',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

and some of these

exec sp_cursorfetch 130150061,2,1,100

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-23 : 10:21:28

For row versioning, query sys.databases and check snapshot_isolation_state_desc and is_read_committed_snapshot_on columns.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-04-23 : 16:11:05

Suggestions;

Since you are running checkdb, run DBCC CHECKDB('biggestDb') WITH ESTIMATEONLY
which will show estimated tempdb usage for the checkdb command.

Index creation and alter also has a SORT_IN_TEMPDB option, that if used might make tempdb grow.

Create a trace and catch the time of the Database - Data File Autogrowth events, to spot the times at which tempdb is growing.
In order to detect any growth patterns.

Possibly run a trace catching sql events with very large Writes value.

My hunch is the nightly maintenance.

rockmoose

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-04-24 : 03:54:04

quote:
Originally posted by rockmoose

Suggestions;

Since you are running checkdb, run DBCC CHECKDB('biggestDb') WITH ESTIMATEONLY
which will show estimated tempdb usage for the checkdb command.

Index creation and alter also has a SORT_IN_TEMPDB option, that if used might make tempdb grow.

Create a trace and catch the time of the Database - Data File Autogrowth events, to spot the times at which tempdb is growing.
In order to detect any growth patterns.

Possibly run a trace catching sql events with very large Writes value.

My hunch is the nightly maintenance.

rockmoose

I've been monitoring the nightly maintenance. It bloated the tempdb to 1.5 gig the first time it ran after I deleted the tempdb. I'm fine with that. What I'm waiting for is it bloating to 8 gig again. So I've been running a trace on the tempdb File Auto Grow since yesterday.

I've also been running a trace on queries that take longer than 5 seconds against one of our eight production databases (the one I gave a link to higher up in the thread) - as I think it is most likely this excuse for a relational database that is causing the problem (probably as a result of a Business Process that runs on a Tuesday - Payroll of thousands of people.)

But I've taken your suggestion and added a trace server-wide for queries with more than 100 writes.

So hopefully, with these 3 traces running, when I spot the autogrow, I should be able to correlate that event with what happened at the same time on the other 2 threads. By the end of the day, I should have an answer.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-24 : 09:53:45

If it grows to 8gb everyday, better to put the tempdb on bigger array.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-04-24 : 11:44:08