Link to home
Start Free TrialLog in
Avatar of AXISHK
AXISHK

asked on

Location for TempDB

Want to split the TempDB to match with my SQL server configuration. I have two quad-core processors and split it with 1 .mdf file and 7 .ndf files. However, what's the best place to place this file and the size for each file ?

Current the SQL
OS ( 2 x RAID1 15000RPM)
SQL Log (4 x RAID10 15000RPM)
SQL Database ( 6 x RAID 10 SSD)
SOLUTION
Avatar of David
David
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AXISHK
AXISHK

ASKER

Current server is two quad-core processors. I have seen an article about split the TempDB to match with the physical core.
Number of IOPS can grow up to 2000 at a peak hour. Window performance counter says that the disks idle time may be 0 and build up with long disk queue....
>> two quad-core processors <<

Then start with only 2 physical tempdb files, or 4 at most.  You don't need 8.
Avatar of AXISHK

ASKER

Not exact match with the cores, only half of them, ... correct ?
There is a real easy way to see if your problem is disk I/O or CPU.  Look at the performance monitor and the I/O queue depth.  If it is > 1 then that means your application is doing nothing while waiting for the disks to get it the data it needs.

There is no reason to have to guess if it is CPU or Disk.
Avatar of AXISHK

ASKER

https://epmlivesupport.desk.com/customer/portal/articles/1357944-create-multiple-tempdb-files-per-cpu

I refer to this article for splitting the tempdb based on core...
The author is an idiot.  Think of it this way. What if you put your database on a USB stick.  How much difference would it make if you had an ancient single-core pentium or a 32 core server?
Avatar of AXISHK

ASKER

Tks