Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

Should I move tempdb file to own spindle?

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
MrVault
Asked:
MrVault
  • 9
  • 6
  • 2
3 Solutions
 
cmrayerCommented:
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
 
MrVaultAuthor Commented:
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
 
MrVaultAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cmrayerCommented:
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
 
MrVaultAuthor Commented:
used by customers. any idea on how to determine queue length attributing without putting it on a separate logical volume?
0
 
cmrayerCommented:
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
 
Anthony PerkinsCommented:
>>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
 
MrVaultAuthor Commented:
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
 
cmrayerCommented:
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
 
MrVaultAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
MrVaultAuthor Commented:
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
 
MrVaultAuthor Commented:
@cmrayer any response to my questions?
0
 
cmrayerCommented:
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
 
MrVaultAuthor Commented:
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
 
cmrayerCommented:
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
 
MrVaultAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 9
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now