[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Location for TempDB

Posted on 2014-07-14
9
Medium Priority
?
200 Views
Last Modified: 2014-07-21
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)
0
Comment
Question by:AXISHK
  • 4
  • 3
  • 2
9 Comments
 
LVL 47

Assisted Solution

by:David
David earned 1000 total points
ID: 40196215
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 40197627
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
 

Author Comment

by:AXISHK
ID: 40198631
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40199621
>> two quad-core processors <<

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

Author Comment

by:AXISHK
ID: 40200860
Not exact match with the cores, only half of them, ... correct ?
0
 
LVL 47

Expert Comment

by:David
ID: 40201075
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
 

Author Comment

by:AXISHK
ID: 40201267
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
 
LVL 47

Expert Comment

by:David
ID: 40201301
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
 

Author Closing Comment

by:AXISHK
ID: 40210813
Tks
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question