May 21, 2015 at 5:15 am
So we have new servers that are going to be installed with SQL 2012 and I'm debating the wisdom of splitting tempdb with multiple files.
I know it's a myth that performance automatically improves if you split it into a number of files based on processors, but I'm debating the wisdom of putting a file on each of my data / log file drives.
For instance, I have a server with a C: drive (OS), D: drive (Data for system DBs and install of programs - 458 GB), an F: drive for user DB data files (767 GB), and a J: drive for log files (255 GB).
Obviously no files are going on C:. I'm debating on whether or not we should even leave system DBs on the D: drive given in our current 2k8 servers, we end up with Memory.dmp files over flowing the D: drives as well as .cabs and other install / update files that tend to collect on that drive over the years.
But if we leave the system DBs on D:, I'm wondering if adding a second tempdb file to F: and a third to J: will improve query performance or not.
Any thoughts?
May 21, 2015 at 5:48 am
Brandie Tarvin (5/21/2015)
So we have new servers that are going to be installed with SQL 2012 and I'm debating the wisdom of splitting tempdb with multiple files.I know it's a myth that performance automatically improves if you split it into a number of files based on processors, but I'm debating the wisdom of putting a file on each of my data / log file drives.
For instance, I have a server with a C: drive (OS), D: drive (Data for system DBs and install of programs - 458 GB), an F: drive for user DB data files (767 GB), and a J: drive for log files (255 GB).
Obviously no files are going on C:. I'm debating on whether or not we should even leave system DBs on the D: drive given in our current 2k8 servers, we end up with Memory.dmp files over flowing the D: drives as well as .cabs and other install / update files that tend to collect on that drive over the years.
But if we leave the system DBs on D:, I'm wondering if adding a second tempdb file to F: and a third to J: will improve query performance or not.
Any thoughts?
Hello again Brandie! Hope things are going well for you! 🙂
First, you are correct - the tempdb-file-per-core thing is SOOO bad advice for almost all systems out there. You need a stunningly capable IO subsystem to approach this number on anything more than a moderate number of cores.
Second, it doesn't matter what your "drives" are NEARLY as much as what their underlying subsystem is. You can have 20 drive letters assigned to a 3-disk RAID5, or 2 drives each backed up by half of a dedicated 1000-spindle 3PAR system. Obviously you will get vastly different IO from each construct. Having said that, as long as you have sufficient spindle count that isn't already slow, and some other section of your IO path isn't bottlenecked (single iSCSI NIC comes to mind here) then you can improve tempdb IO throughput by increasing the file count on the same or alternate LUNs/windows disks. The main thing to remember is too many files (of any type) on too few spindles means a higher fraction of "non-sequential" disk access which means slower performance. Your job is to find the sweet spot for your particular system.
Getting an IO system and the database files sitting on it optimized is a VERY deep topic obviously!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 21, 2015 at 5:57 am
Kevin,
I knew there was something regarding spindles involved. Thank you for the refresher. We'll check and see what they gave us before we make this decision.
May 21, 2015 at 6:16 am
My usual recommendation to clients is:
Split TempDB into 4 files, make sure they're all the same size, all the same growth increment. If I'm seeing obvious allocation contention I may say 8. Put them all on the same drive. If TempDB grows, I don't want it impacting my user databases, if a user DB's log grows out of control I don't want TempDB impacted.
If I'm seeing obvious TempDB IO contention, then I may suggest they look at a separate IO path and LUN for TempDB, with the understanding that the underlying SAN drive layout may invalidate that. Or I may suggest that they consider flash storage.
These are based on the idea that the client may not have the best DBAs, or any DBAs, and that the recommendation I'm making may be the only work I do for them for a couple years.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2015 at 6:18 am
GilaMonster (5/21/2015)
My usual recommendation to clients is:Split TempDB into 4 files, make sure they're all the same size, all the same growth increment. If I'm seeing obvious allocation contention I may say 8. Put them all on the same drive. If TempDB grows, I don't want it impacting my user databases, if a user DB's log grows out of control I don't want TempDB impacted.
If I'm seeing obvious TempDB IO contention, then I may suggest they look at a separate IO path and LUN for TempDB, with the understanding that the underlying SAN drive layout may invalidate that. Or I may suggest that they consider flash storage.
These are based on the idea that the client may not have the best DBAs, or any DBAs, and that the recommendation I'm making may be the only work I do for them for a couple years.
Does splitting into 4 files like this help over having 1 single file? EDIT: Well, obviously, since you're recommending it. But I'm wondering more what is the exact benefit of 4 files vs 1 file in this case.
May 21, 2015 at 6:31 am
Reducing contention on the allocation pages. That's the whole reason why TempDB gets split. It's why that old recommendation of 'files = cores' came about in the first place.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2015 at 6:39 am
Since it wasn't mentioned in the original post I had assumed that temp object allocation wasn't an issue. I will also add that I have seen this only 3 times in the wild in my almost 20 years of working/consulting on SQL Server, and one of those was a HORRIBLY stupid application and another was a HORRIBLY mis-configured VM. And if I do diagnose temp object allocation with the usual sub-optimal IO arrangement my first approach (actually probably first approach regardless) would be trace flag 1118. Then try to resolve issues with app or config (usually too little RAM on box/VM) that are causing temp object allocation.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 21, 2015 at 6:43 am
TheSQLGuru (5/21/2015)
I will also add that I have seen this only 3 times in the wild in my almost 20 years of working/consulting on SQL Server
I've seen TempDB allocation page contention more than 3 times this year already, one severe, rest moderate.
I recommend splitting the files because it's easy to do and it reduces the chance that there will be any allocation contention as the app grow, usage increases and data volumes increase.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2015 at 7:55 am
Thank you for the clarification, Gail. That gives me something to take to my boss (and something to ask our vendor about).
May 21, 2015 at 7:57 am
TheSQLGuru (5/21/2015)
Since it wasn't mentioned in the original post I had assumed that temp object allocation wasn't an issue.
These are brand new (and very beefy) servers for upgrading SQL 2008 to 2012. Our corporate servers current have tempdb split into multiple files, but then corporate installed these servers and their ways are Strange and Mysterious.
We now have our own servers and are installing them ourselves. I want to make sure we get it right, but my boss is against the idea of splitting tempdb up unless we absolutely have to. I'm hoping I can use Gail's recommendation and explanation to discuss this more fully.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply