temp DB - add files

Hi experts

i have this server:
Nombre Servidor:      BDSIS02      
Sistema Operativo:      Windows Server 2008 R2 Enterprise 64-bit (6.1, Build 7601) Service Pack 1 (7601.win7sp1_gdr.140303-2144)      
Marca:      IBM      
Modelo del sistema:      System x3650 M3 -[7945AC1]      
Procesador:      Intel(R) Xeon(R) CPU    X5680  @ 3.33GHz (24 CPUs)      
Memoria:      65536 MB RAM      
Memoria disponible SO:      65524 MB RAM

I have the following storage units
SQL_CT2(M:)
SQL_CT1(K:)
SQL_CT1(J:)
SQL_CT3(I:)
SQL_CT4(P:)
Total TB: 12

If I want to increase my tempdb:
Currently I occupy space purely bd totaling 6564.84 (including database system) would be 20% (6564.84) -> 1.3 TB data files that you create must be different is to better performance units or simply to this in a different unit E as would their recommended size.
When the tempdb should be added only data files (* .ndf)
enrique_aeoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
So...  Here are the general recommendations for TempDB (mileage may vary):

8 TempDB Data files - they must ALL be the same size 4096MB or less (this has to do with the fact that the round robin calculation does not round robin PFS/GAM/sGAM intervals, just files).  The best advice I have seen for this is 1:1 TempDB file:Logical Processor up to 8 files.  After that point add additional files to TempDB as needed and add them in groups of 4.
Enable T1118 - this forces the first data page allocation to be on a uniform extent instead of a mixed extent - this helps to mitigate a lot of the sGAM contention.  Also, this does impact internal objects that leverage the TempDB, such as worktables. (bonus!)
Enable T1117 - this forces data files in a filegroup to grow by their autogrowth units (make sure that the auto grow increments are the same for all files in a filegroup).  Also be aware that this is instance-wide.  This behavior will be for ALL databases, not JUST TempDB.
TempDB should be on it's own LUN - depending on the SAN solution, this may just be for organization purposes, but I still like to do this from a stylistic/organization perspective
enrique_aeoAuthor Commented:
Hi,

20% of all my bd user is 1.3 TB, then that should divide by 8, so the size of each file must be 162.5 GB, is that correct?
enrique_aeoAuthor Commented:
or the maximum size is 5GB for each of the files (8 in this case)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
TempDB as the name says is used for temporary operations. I don't know why you came with the 20% value but minimum size should be the same size of the largest table that you have in the instance so it will handle a full copy of that table if necessary.
Also, 8 files are the maximum recommended by Microsoft if you have 8 or more processors. Otherwise should be a file per processor.
enrique_aeoAuthor Commented:
I do not remember where I read that article to calculate the size of the temporary bd should be 20% of the sum of all user Bd, if not, then as calculate the minimum and maximum size of Tempdb bd. Thank you.

Scenario 1: If all bd would add 200MB to 1000MB then the tempDB so serious 25MB for each file, right?

Scenario 2: If all bd would then add 10TB 2TB for tempDB so serious 0.25TB for each file, right?

If I am wrong please tell me how to calculate the size of each file bd tempDB is both scenarios. thanks
Vitor MontalvãoMSSQL Senior EngineerCommented:
If I am wrong please tell me how to calculate the size of each file bd tempDB is both scenarios.
I don't think there's a wrong or right way. It just you should justify the solution that you're using.

For example, imagine that in a SQL Server instance you have 10 DBs with 100MB size each (1GB total) and then you have a database with 4TB where exists a table with 2TB. So, all together you have around 4TB of data, so 20% of that will be 800GB. What's going to happen if for example you'll need to run a reindex or an update in the 2TB table? How will the engine handle it if you don't have 2TB of available RAM? Will swap on the 800GB tempdb, right?

With the method I told you above, you'll need to have a 2TB tempdb (size of the largest table). And you'll have a good justification to have it.
PadawanDBAOperational DBACommented:
If you are looking to automate the logic that Vitor properly provided you with around sizing of the TempDB, I would encapsulate this in logic.  I don't necessarily know if this is really that important, assuming you have Instant File Initialization enabled and T1117 enabled.  Regardless, if you wanted to automate it, you would essentially be iterating all of your databases, looking for the largest table (based on page allocations) and divide that by the amount data files you have created for your TempDB and there's your value.  I have seen the 20% recommendation in the past, but I usually let Vitor's guidance be my starting point, to be honest.

Also, with regard to the data files for TempDB.  8 is where you should stop without evidence that you are still seeing contention on allocation resources.  So if you still see a wild amount of PFS, GAM and sGAM contention with 8, you would add more, but this is purposeful and *not* done as a part of best practices, you should have supporting evidence for this - and you add data files to TempDB above 8 in groups of 4, there's some weird logic around that.
enrique_aeoAuthor Commented:
I agree.

then
2TB tempdb = size of the largest table. Therefore 8 files 0.25TB each, correct?
Vitor MontalvãoMSSQL Senior EngineerCommented:
2TB tempdb = size of the largest table
That was my example. I don't know if in your case it's really 2TB :)
Therefore 8 files 0.25TB each, correct?
If you're counting on 2TB, then yes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.