June 20, 2018 at 8:54 am
I've heard that when SQL is installed on a SAN that the TempDB files no longer need to be spread out over multiple files. Is that correct?
There is an exception to every rule, except this one...
June 20, 2018 at 10:07 am
The idea here from my understanding is to remove page contention. This is a bottleneck for threads trying to access those pages in memory, not I/O. Thus, splitting the temp into multiple files even on the same spindle may still be beneficial because once the page is allocated, it could still be dropped if there is just one file with multiple queries doing things with TempDB.
I'm no expert there, but I did see improvements when I split mine up on my virtual disks.
June 20, 2018 at 12:03 pm
Thanks xsevensinzx! Any other ideas/experiences out there about this?
There is an exception to every rule, except this one...
June 20, 2018 at 2:13 pm
SQLHeap - Wednesday, June 20, 2018 12:03 PMThanks xsevensinzx! Any other ideas/experiences out there about this?
Just as xsevensinzx said, the multiple files are to reduce allocation contention. There are quite a few article on the internet about this.
Recommendations to reduce allocation contention in SQL Server tempdb database
Correctly adding data files to tempdb
You don't want to confuse that concept with people asking if having separate drives for databases, files will improve performance when the drives are from same pool of disks. I would guess what you heard before is likely more related to that.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply