tempdb data file per processor

Hello All,
I was reading through the configuration settings recommended to us by our software vendor and I didn't understand the  part below in quotes:

"Limit to one tempdb data file per processor". Set tempdb data files to 100
MB with 10 percent autogrowth in size.

I have quad dual core processors, does this mean i need 16 tempdb's? sounds crazy, but how do I control this and where on SQL server 2005?

Background, Server 2003 Enterprise R2 32-Bit, SQL 2005 Standard
Who is Participating?

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

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:
>> I have quad dual core processors

You have a total of 4 * 2 = 8 processors and hence you can split your tempdb into 8 files for better performance with Initial size as 100 MB and Autogrowth set to 10 or 20%.

And steps to split your tempdb here:


Note: Run the third step statement 8 times since you have 8 processors and don't forget to restart your SQL Server instance.
This would improve performance if it is placed in a separate disk dedicated only for tempdb files.
Agree with rrjegan17 on this. Also, make sure that you place all the 8 files on as many different drives/disks as possible to get the best performance.
ValleyENTAuthor Commented:
I just realized I typo'd we have 4 quad core's. Does this mean I will need to split tempdb into 16 files? Also, I do not have the resources to place the tempdb on it's own raid.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, just split into 16 files and then place it in a separate disk drive as 16 drives are not possible.
ValleyENTAuthor Commented:
Should I place it on my existing Raid 1 which runs the OS and SQL.EXE or on the RAID 1+0 which contains the database and log files for SQL?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Placing it on your RAID 1 would add more IO to your disk with OS and SQL.exe..
And placing it on your RAID 1 + 0 with database and log files would significantly improve performance.
But make sure that you have separate disk spindles available for this tempdb to improve performance.

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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:

I would recommend

Accept 28503519
ValleyENTAuthor Commented:
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.