is tempdb really a bottleneck?

Question

Post reply

is tempdb really a bottleneck?

shifty1981

Ten Centuries

Points: 1071
More actions
November 26, 2010 at 12:51 pm

#241747

I know all the best practices out there say the tempdb should be on it's own physical drive(s) and should have multiple files. However when checking ave queue depth for the volume it's on, they are well below the max we should see (.05 usually on a RAID1 set). It would appear that we should be fine leaving the tempdb on the boot volume which also has a very low queue depth value during our peak hours. our db is around 500 GB in size the tempdb is around 12GB in size before it shrinks during a maintenance window.

What performance counters should I check to see whether dedicating two spare drives to the tempdb is worth it when perhaps a table/index in our database might benefit more from being on it's own spindle?

Also when it says to break the tempdb into separate files does it mean then leave them all on the same physical drive? with 2 quad core processors, who has the extra disks to dedicate to separate tempdb files?

Thanks!

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Depending on how the tempDB is used, you have to decide if you want to split the temp DB into seperate files. It is NOT always the case.

Regarding the tempDB being in the OS drive, I am not so sure if that is a wise idea. You have a 500GB database. One small mistake by one person who has access to your DB to run a query is enough to bring the server down by blotting the tempDB.

-Roy

shifty1981 Ten Centuries Points: 1071 More actions · Answer 2

Roy Ernest (11/26/2010)

Depending on how the tempDB is used, you have to decide if you want to split the temp DB into seperate files. It is NOT always the case.

Regarding the tempDB being in the OS drive, I am not so sure if that is a wise idea. You have a 500GB database. One small mistake by one person who has access to your DB to run a query is enough to bring the server down by blotting the tempDB.

What do you mean "who has access to your DB"? the only thing that talks to our DB is applications running backups on client PCs. In any case we have monitors in place to let us know if the file grows too large, the drive is running out of space, etc.

I have no idea how the tempdb is being used. I don't think the developers know either (meaning they're not explicitly thinking "we'll use the tempdb for this part of the code". I guess I'm just wondering if tempdb performance is really a concern for everyone or only when certain counters are present. Best practices suggest putting it on it's own physical disk and possibly in a RAID1 set. but I want to make sure that's necessary before we dedicate 2 disks for it's use when those 2 could be used to offload a table/index from the DB that gets heavy load. The only counter I'm going based on as of yet is Average Queue depth which is .03 for a RAID 10 set (4 drives).

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

shifty1981 (11/26/2010)

before it shrinks during a maintenance window

Let's thing about THAT for a minute...

Why do you have to shrink it all the time? The answer is because it needs to grow to 12GB all the time. Also, the regrows happen at the worst time... when there's something that needs more TempDB. On top of that, you're refragging TempDB every time it grows and that will have some impact on performance.

Stop shrinking TempDB. It's just going to regrow. Set it to initialize as 12GB on startup so that you have one nice big slice of (usually) contiguous disk space so the read/write heads don't have to go crazy traversing multiple parts of the the disk for TempDB.

I also agree with the other thing already stated... move TempDB off the boot drive. Nothing should be on the boot drive except the operating system.

--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)
Intro to Tally Tables and Functions

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

I guess the thing to understand is what tempdb is used for. That'll help you and your developers understand whether or not it's a bottle neck.

Every temporary table, obviously, goes to tempdb. So do all the table variables. This, by extension, means that multi-statement table valued functions also go into tempd. Inside execution plans, hash operations, spool operators, sort operations, and others will write to the tempdb. Sorts are not jsut from ORDER BY statements either, if you've got Merge operations caused by missing or incorrect indexes, you can get sort operations within the execution plan that you didn't put there. While we're on it, hash operations are not just hash joins, but are usually aggregations too, and these all go into tempdb. Index rebuilding operations take place using tempdb. If you're using snapshot isolation you're putting even more load into tempdb. Large objects, VARCHAR(MAX), TEXT, XML, will use tempdb for parts of the processing. Cursors are handled within tempdb.

I'm positive I've missed quite a few uses. Tempdb is a very important part of the process. You are not wasting your time to make it as performant as you can without spending ridiculous amounts of time on it (at least until you see an issue). I wold especially take Jeff's advice to heart. If tempdb is growing to a certain size, that's because some of the operations listed above (or the one's I missed) need it to be that large. You can look at those operations to tweek the use tempdb down, but shrinking it over & over is seriously problematic.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software