December 10, 2009 at 5:00 am
Hey All,
We are about roll out a new a/p and are going to try having multiple tempdb files.
The machine spec is: 128GB RAM, 4* SAN volumes (sys db's, sys logs, user db's, user log's) and 4* Quad-Core processors (16 Cores).
We already have a similar cluster which only has 1 tempdb file and do not see any contention or performance issues.
Question is: Do we have 1 temdb file per physical processor (i.e., 4 quad-core cpu's) or do we have 1 file per core? There are articles out there that suggest either or but no-one has definitively said 'do it this way'.
Any suggestions or past experience advise gratefully accepted.
Cheers,
Adam.
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 10, 2009 at 5:41 am
Taken from: http://technet.microsoft.com/en-us/library/cc966534.aspx
Best practises suggest you:
Consider configuration of TEMPDB database
Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server.
Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage).
For the TEMPDB database, create 1 data file per CPU, as described in #8 below.
Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.
It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
This is especially true for TEMPDB where the recommendation is 1 data file per CPU.
Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.
There are alot of resources and some pretty good reads regarding the subject.
http://msdn.microsoft.com/en-us/library/cc966545.aspx
I personally test with one data file per cpu, and then 1 per core.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
December 10, 2009 at 5:48 am
Thanks. Will look forward to a good read 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 10, 2009 at 6:01 am
I would start with 0.25 files/core, all on the same drive then, if there is allocation contention, add more files, if there is IO contention move the files onto separate LUNs.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply