asked on

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

Raja Jegan R

>> 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.

geek_vj

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.

ValleyENT

ASKER

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.

Raja Jegan R

Yes, just split into 16 files and then place it in a separate disk drive as 16 drives are not possible.

ValleyENT

ASKER

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?

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raja Jegan R

angelIII,

I would recommend

Accept 28503519

ValleyENT

ASKER

Thanks