TempDB question

Question

Post reply

TempDB question

sunny.tjk

SSCertifiable

Points: 5041
More actions
October 11, 2013 at 2:42 pm

#278849

Does having multiple tempdb files on different drives help with contention?

Viewing 6 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: 1003943 More actions · Answer 1

sunny.tjk (10/11/2013)
Does having multiple tempdb files on different drives help with contention?

Yes... especially if they're on different drives.

--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)

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 2

Jeff Moden (10/11/2013)
sunny.tjk (10/11/2013)
Does having multiple tempdb files on different drives help with contention?
Yes... especially if they're on different drives.

Thanks Jeff.

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

sunny.tjk (10/14/2013)
Jeff Moden (10/11/2013)
sunny.tjk (10/11/2013)
Does having multiple tempdb files on different drives help with contention?
Yes... especially if they're on different drives.
Thanks Jeff.

Another way to reduce contention is to turn on Trace Flag 1118. Please see the following article where one of the world's leading authorities on the subject talks about it.

http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/

--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)

george sibbald SSC Guru Points: 104210 More actions · Answer 4

if you are seeing latch contention on the SGAM pages in tempdb (waitresource 2:1:3) just creating multiple files on the same disk will be enough to alleviate this.

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 5

george sibbald (10/15/2013)
if you are seeing latch contention on the SGAM pages in tempdb (waitresource 2:1:3) just creating multiple files on the same disk will be enough to alleviate this.

I agree. They don't have to be on different disks. Instituting Trace Flag 1118 sometimes makes even that unnecessary although I generally do both automatically now, even when no such wait problem currently exists. It's one less thing to have to worry about in the future.

--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)