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)
AXISHKAsked:
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.

DavidPresidentCommented:
TempDB on the fastest random I/O you have.   Redo the SSDs and give yourself a pair of SSDs in a RAID1. use that for TempDB.

It is a waste to put the entire DB on SSD unless this is a really bizarre dataset that is almost pure random I/O and scratch tables and lots of joins and such.  SSDs pay off in random I/O.  The bulk of I/O on the database itself will be sequential 64KB I/O.  That works quite well in 15KB RPM disks in RAID10.
0
Scott PletcherSenior DBACommented:
How many physical CPUs do you have?  Why did you decide on 8 data files?

It's an interesting decision on whether to put logs or data on SSDs.  Logs are more critical, since they must write before SQL continues, but since they're (almost) all writes, they will wear out the SSD drives faster.  Data is more read-intensive, saving wear on the SSD hardware, but less critical to performance (if properly indexed).

Tempdb can be most critical, but it depends too on what specifically you do on that instance.  Do you use replication?  Change Tracking or Change Data Capture?  Snapshots?  Snapshot isolation?  If not to all, tempdb might not be quite so critical on your instance.

[Btw, fwiw, I think the .mdf/.ndf thing is dopey and just name all my data files .mdf.  Honestly, what is the point of adding that meaning to the physical file name??]
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
AXISHKAuthor Commented:
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....
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Scott PletcherSenior DBACommented:
>> two quad-core processors <<

Then start with only 2 physical tempdb files, or 4 at most.  You don't need 8.
0
AXISHKAuthor Commented:
Not exact match with the cores, only half of them, ... correct ?
0
DavidPresidentCommented:
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.
0
AXISHKAuthor Commented:
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...
0
DavidPresidentCommented:
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?
0
AXISHKAuthor Commented:
Tks
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.