restructuring tempdb sql 2014

1. I have two sql instances (named and default) on same DB server machine, server is having 12 logical processors. Do I need to create 12 secondary tempdb files (ndf) on default and named sql instance? or is it sufficient if i create 12 tempdb ndf files on only default sql instance?

2. server is having 12 logical processors and I have created 12 tempdb files with equal size. As per below article is there any changes required on number of secondary tempdb files?

"
As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.


https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d
Basava RajuAsked:
Who is Participating?
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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Since you have 12 Cores and 2 Instances of SQL Server, it would be recommended to go with
1. 8 tempdb data files for Default instance(4 more to have 12 if there are any contentions)
2. 8 tempdb data files for Named instance(4 more to have 12 if there are any contentions)
0

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do I need to create 12 secondary tempdb files (ndf) on default and named sql instance?
Yes but as the article says, when having more than 8 processors you should limit the number of files to 8. After that there's almost no gain in having more data files.

As per below article is there any changes required on number of secondary tempdb files?
No. The article is referring to a context of a single SQL Server instance no matter how many SQL Server instances are already installed in the server. Meaning that no matter the configuration in other SQL Server instances it doesn't impact the rest of existing SQL Server instances in the server.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Solutions provided.
0
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
Query Syntax

From novice to tech pro — start learning today.