obulay-657245 (7/19/2010)
What is the point of having multiple files ON THE SAME DRIVE c:?
This is straight from Paul Randal's blog that another user posted a link to abover
PS To address a comment that came in - no, the extra files don't *have* to be on separate storage. If all you're seeing it PAGELATCH contention, separate storage makes no difference as the contention is on in-memory pages. For PAGEIOLATCH waits, you most likely will need to use separate storage, but not necessarily - it may be that you need to move tempdb itself to different storage from other databases rather than just adding more tempdb data files. Analysis of what's stored where will be necessary to pick the correct path to take.
Good article, but great discussion. I just worked with some Microsoft folks on thier SCOM team for a deployment of thier software and they insisted on 1 TempDB per processor and for it to take up most of the drive 80%. It is good to now know what to look for to see if the recommendations were over kill or if they were valid.
It looks likethe answer is with most things SQL Server ....It Depends
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field