November 3, 2011 at 7:02 am
Best practice states you should add as many data files to your tempdb as their are cores in the server .. so my question is .. On a Cluster we have multiple instances (up to 8) each with their own tempdb.. does the best practice still apply in this scenario, if our servers have 8 cores, should each of the instances tempdb's still have up to 8 datafiles sized the same? Or because we have 8 instances, each having their own tempdb should we limit the number of tempdb files per instance? Is their a performance hit in someway doing it either way?
November 3, 2011 at 7:16 am
jspatz (11/3/2011)
Best practice states you should add as many data files to your tempdb as their are cores in the server...
Nope
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
November 3, 2011 at 7:32 am
Very good article, and thank you for the reply, but even in that article if you follow the advice there is still a recommendation for 1/4 or 1/2 instead of 1:1.. that being said the clusters we are managing were all setup with 1 tempfile per instance... I am contemplating adding a datafile or 2 to the tempdb's on each instance, but being we have 8 instances and thus 8 tempdb's on a server with 4 cores (these servers are old and probably overtaxed as it is but we deal with what we have 🙂 ) And I was curious of the impact of multiple datafiles on multiple sql server instances tempdb's on the same server.
November 3, 2011 at 7:50 am
The recommendation is per-instance. The fact that there are multiple instances on the server don't make the slightest difference to the number of files a tempDB should have.
If all the tempDB data files (regardless of how many per DB) share the same drive, you likely have IO contention.
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
November 3, 2011 at 7:57 am
Again .. thank you for the reply, as far as sharing the disk that we are ok with each instance gets a different slice from the san with data files and log files also getting their own mount point.. but you answered my question so thank you again!
November 4, 2011 at 8:16 am
jspatz (11/3/2011)
Again .. thank you for the reply, as far as sharing the disk that we are ok with each instance gets a different slice from the san with data files and log files also getting their own mount point.. but you answered my question so thank you again!
Your "own slice" very likely comes off of the same set of physical disks. Too many files actually leads to POOR IO performance due to excessive head thrashing and seek times. In 15 years of sql server relational engine consulting I can count on ONE HAND the number of clients that actually needed more than ONE tempdb file to avoid allocation contention and on just TWO hands the number of clients that needed more than one tempdb file to achieve adequate tempdb throughput. I have lost track of the number of clients that benefited from me REDUCING the number of tempdb files they had in place already. 1-to-1 is a ridiculous recommendation now.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply