Solved

Should I move tempdb file to own spindle?

Posted on 2010-11-19
17
686 Views
Last Modified: 2012-05-10
I just joined an org that has never put the tempdb on it's own physical drive/raid-set. They have performance issues but are convinced it's due to something other than the tempdb. Short of taking down one of the live systems to test (they don't have a test lab), what perfmon counters should I look at to see if in fact the tempdb file is a bottleneck for them? They have some systems where it's on the same drive as the OS/page file and some where it's on the same drive as the database.

Thanks!
0
Comment
Question by:MrVault
  • 9
  • 6
  • 2
17 Comments
 
LVL 4

Expert Comment

by:cmrayer
Comment Utility
Look at 'Physical disk / average disk queue length' and it should not be averaging over 1 with peaks coming right back down again.  If you look at all the disks you should get a good picture of where the issue lies and whether the disk with tempdb is the worst offender!

The main performance issue we have found across our sql servers is the log files rather than tempdb so it might be worth looking at those?

Moving tempdb is easy with just a quick restart of sqlserver for the changes to take effect.
0
 

Author Comment

by:MrVault
Comment Utility
But is there a way to associate the disk queue length to the tempdb as opposed to something else that's hitting the disk?
0
 

Author Comment

by:MrVault
Comment Utility
Actually I should clarify. They are using simple mode on the main database log file because they do full backups daily and have an SLA that is OK with 1 day's worth of data loss. To them that's worth the issue of insanely large transaction log files since the DBs are between 300 and 1.5 TB in size.

So the trans logs are often times on the same spindle as the database and index files.
0
 
LVL 4

Accepted Solution

by:
cmrayer earned 167 total points
Comment Utility
Not sure on that one, I always move a few things around and watch the aforementioned stat on the original and new disks.  

Is it an internal system or one used by external customers, I  only ask as our systems are 24/7 but internal so we can take a minute or so of downtime for the change...
0
 

Author Comment

by:MrVault
Comment Utility
used by customers. any idea on how to determine queue length attributing without putting it on a separate logical volume?
0
 
LVL 4

Expert Comment

by:cmrayer
Comment Utility
Is the queue too high (I guess it is or you would not be pursuing this avenue ;-), but just to be sure)?

What are the current spindle numbers and configuration options, should you be able to prove the tempdb is the issue?

Otherwise there is always the sql server profiler...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>They have some systems where it's on the same drive as the OS/page file and some where it's on the same drive as the database. <<
How do you know they are on the same physical drive?  What RAID are you using?
How many data files is tempdb currently using?  Have you considered just creating additional data files for tempdb whereever you see fit?  This way there is no downtime.
0
 

Author Comment

by:MrVault
Comment Utility
I just checked and for the most part the queue length is very low (.03) and that's on a RAID 1 set, so even less per disk. I'm guessing perhaps that it's not the issue that I thought it might be.

I've read that the tempdb should have 1 file per CPU core. since there are 2 quad core CPUs or 2 hex core CPUs, that would mean 8 or 12. But is there a benefit for doing that they aren't each on their own spindles? Who has that many free disks to put separate out the tempdb files like that? the max disks I can find in a Dell or HP rack mountable server is 14.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Expert Comment

by:cmrayer
Comment Utility
I think that is fairly old advice, if the queue is only 0.03 then this is very unlikely to be the issue.  When you count the cores are you counting available cores (I guess) or those that are in use?

Our main CRM SQL server has two quad core CPUs but almost never uses more than 0.5 to 1 so we have one tempdb file...

What are the queues on the other disks (ie where the user databases or system databases are)?

Cheers,
0
 

Author Comment

by:MrVault
Comment Utility
How do I determine how many cores are in use? We have 12 instances of our app running at any given time (don't ask me why, I didn't write the code) per server.

As for the queue length of the DBs? LOL you don't want to know. Something like 400 is very common. I've told them we need to find out the high IO tables/indexes and split those out onto different spindles. Amazingly they have the OS, pagefile, tempdb file, and even the transaction logs on one RAID10 set and it runs very well (.13 I think). The t-logs are in simple mode because of another DR setup they have so they don't worry about having to roll back the logs after a restore.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 83 total points
Comment Utility
>>I've read that the tempdb should have 1 file per CPU core.<<
This is a very popular myth, see here:
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
0
 

Author Comment

by:MrVault
Comment Utility
Great article! It also addresses the "1 spindle per file" question too. I had a feeling that some of these 'best practices' out there are because in 'most' situations sql server eventually hits a bottleneck with the tempdb activitiy and thus it's generally a good idea. But clearly there has to be something to measure to determine that the tempdb is actually a bottleneck. It's not so easy to rebuild raid sets without downtime so planning ahead is key and I'd rather not dedicate disks to tempdb unless I'm as sure as I can be that our app hammers it.
0
 

Author Comment

by:MrVault
Comment Utility
@cmrayer any response to my questions?
0
 
LVL 4

Assisted Solution

by:cmrayer
cmrayer earned 167 total points
Comment Utility
Sorry, out and about....

To determine the queues in perfmon you can monitor each core (identified as separate CPUs but really cores these days) or simply use task manager for a snapshot.  When you say the queue length is 400 or so for the dbs is that the average disk queue length?  If so then there is the issue, simply too much data for the physical disk to handle...  Simplest solution there would be to extend the array/RAID set that the LUN is on (I guess you are using a SAN for the data?), as that sort of queue would kill an app (or maybe I am misunderstanding your previous comment?)

Cheers,
0
 

Author Comment

by:MrVault
Comment Utility
when you say "count the cores in use" above do you mean monitor all 8 of them (2xquad core) and during peak time see if they all have values of significant or something else?

as for the queue length yes that's the average length for disk the db is on for some of our servers. we're using internal disks, not a SAN so unfortunately we can't just add more disks to the array. however new servers that are coming up I plan on getting ones with 14 internal disks and splitting up the high IO tables onto their own RAID 1 sets or possible 4 disk, RAID 10 sets.

And unfortunately, yes it appears to be killing our App.
0
 
LVL 4

Expert Comment

by:cmrayer
Comment Utility
Without a SAN you can add an external disk box (DAS) as just a few more cheap SCSI disks will help and alleviate that crippling queue...
0
 

Author Comment

by:MrVault
Comment Utility
thanks. if we can't get the performance we need out of these new servers we're building with more internal disks (and then separating out the tables with high IO onto their own disk/raid-sets, then we'll have to consider DAS or SAN.

Thanks!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I previously wrote an article addressing the use of UBCD4WIN and SARDU. All are great, but I have always been an advocate of SARDU. Recently it was suggested that I go back and take a look at Easy2Boot in comparison.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now