Just wondering how do you manage TEMPDB?

Question

Just wondering how do you manage TEMPDB?

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 1

One thing that I can tell you is that I never automatically kill a spid. If you have to do that, then you have much bigger problems such as privs being out of control or not having the proper tools available for your users.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kris Gruttemeyer Ten Centuries Points: 1215 More actions · Answer 2

My primary question would be 'why?'

Automatically killing SPIDs is never a good idea. What if these are legitimate queries that simply need to use tempdb? Maybe there's a lot of join or sorting that has to be done.

If you're worried about autogrowths, then why not just size your tempdb files larger initially? Storage is pretty inexpensive so I'm not sure what the end-game of you doing this would be.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 3

To add to what Kris stated, we have several databases that are a bit over a 3rd of a Tera-Byte on a single instance. That instance has 8 - 2GB MDF/NDF files and 1 - 2GB LDF file. We do some seriously heavy lifting in all of those databases in the form of batch files on top of one of them being the primary OLTP database. We've never needed more than 2GB of TempDB on any given run.

My point about having larger problems before is that you REALLY need to check code that causes TempDB growth over that for accidental Cross Joins in the form of many-to-many joins and, perhaps, use some "Divide'n'Conquer" methods to take control over large monolithic queries that have more than 10 joins. And it's not just for the sake of TempDB... it's for the sake of performance and the overall reduction of what is usually gross unnecessary use of resources such as memory, TempDB, CPU, and I/O.

If, as a DBA, you're just killing long running or high resource usage SPIDs, then you're not really doing your job as a DBA. Yes, I know it's because someone hasn't done THEIR job as an intelligent/skilled developer but, quite literally, "forgive them for they know not what they do" and then start teaching them a better way. Simply killing SPIDs will drive an even bigger wedge between you and the people who don't know what they're doing. [font="Arial Black"]HELP THEM[/font] instead of just sitting in an ivory tower killing SPIDs as they crawl by. It'll be good for you, good for them, and good for the company that's paying all of you to work smarter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

Imagine killing a SPI for an insert statement that was inserting a million rows the time to rollback would be huge. The only time it would be safe to kill a spid would be if its a select and even then its not a good idea and should be more of a last resort after understanding the impact.

Tempdb growing on its own it not an issue if it has freespace in it.

e.g

my tempdb started out at 20 GB and then grew to 100GB

once the transaction committed and the session disconnected all the space allocated (80GB ) is freed up back to SQL ( not the OS). and now SQL can use this space for any new Temp objects being created.

If I ever want that space back i can simply shrink the database.

Jayanth Kurup[/url]