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
LVL 4
ValleyENTAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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:

http://sqlsolace.blogspot.com/2007/11/moving-tempdb.html

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.
0
 
geek_vjCommented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes, just split into 16 files and then place it in a separate disk drive as 16 drives are not possible.
0
 
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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
angelIII,

I would recommend

Accept 28503519
0
 
ValleyENTAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.