October 4, 2010 at 7:44 am
I've read a few articles through the years from Microsoft and other sources saying you should have one tempdb data file for every CPU core on your server.
Was wondering if the same holds true if it is a SQL install on a virtual server? It looks to the guest OS (at least in our configuration) that the data/log/tempdb files are on different physical disks, they really are all on one RAID5 disk on the host, so I'm wondering if it really makes a difference to add more tempdb files (I almost want to think that it might hurt things if anything).
Has anyone found any articles documenting best practices for things like this on virtual servers or have any experience with this? If so I'd appreciate being pointed to them or hearing what you have found.
The Redneck DBA
October 5, 2010 at 2:18 pm
I would start by reading a blog post by Paul Randal regarding the 'one datafile per CPU' myth:
Chris
October 6, 2010 at 6:35 am
Good read. Thanks!
The Redneck DBA
October 7, 2010 at 8:32 am
1 tempdb file per CPU is DEFINITELY not appropriate for just about every server in existence. Even 1/4 files per CPU count is inappropriate for many systems. If you don't have separate spindles to put them on (that aren't already slowed down by other stuff) you can actually get WORSE throughput by having too many files on too few spindles.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 8, 2010 at 12:24 pm
In my experience if you don't have seperate spindles for each datafile it may be pointless to have a 1 to 1 ratio for cores and datafiles. I would like to know what others think?
October 8, 2010 at 12:27 pm
Twinsoft SME (10/8/2010)
In my experience if you don't have seperate spindles for each datafile it may be pointless to have a 1 to 1 ratio for cores and datafiles. I would like to know what others think?
I heavily agree with this. I do have multiple tempdb files, but typically 2-4 on a 16 logical core system..any more is completely unnecessary. There is no performance improvement whatsoever.
May 25, 2011 at 2:48 pm
I have a similar question.
I told the OS admin that tempdb needs to be off C: with the bins and put on a separate drive. I also recommended the same for data and log.
He promptly informed me that it was a VM and that it didn't matter because it was all one disk anyway. I have to admit, I didn't know how to respond (which is very rare for me). I just said "good question and I'll ask SQL Server Central. "
We are definitely disk bound (I'm seeing it in the diags). What can I tell him?
I'm relying on you all to help explain this.
May 31, 2011 at 2:50 am
If you have to, you should be able to put the data/logs/tempdb files onto separate luns on your VM (Or maybe your SAN admin or VM admin can do so?).
I separate my data files etc out onto separate disks, and only move to separate LUNs if I have a disk I/O problem.
If I hit problems, I can move the disk to a new LUN without any impact on the application.
June 1, 2011 at 12:39 pm
ckingtaylor (5/25/2011)
I have a similar question.I told the OS admin that tempdb needs to be off C: with the bins and put on a separate drive. I also recommended the same for data and log.
He promptly informed me that it was a VM and that it didn't matter because it was all one disk anyway. I have to admit, I didn't know how to respond (which is very rare for me). I just said "good question and I'll ask SQL Server Central. "
We are definitely disk bound (I'm seeing it in the diags). What can I tell him?
I'm relying on you all to help explain this.
If you are IO bound (like most SQL Server installations I come across) you have several options:
1) tune queries/indexes/etc. Reducing IO demand can open up all kinds of IO headroom
2) tune the hardware
3) get more RAM
4) put your stuff on faster IO subsystem
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 15, 2011 at 8:25 am
Thanks. Problem is my admin is still living in 1995 and probably doesn't know what a LUN is. So the weakest link in this chain is the network group. Thanks again.
(I'm forced to drive a DeLorean to work just to tolerate these archaic methods)
June 15, 2011 at 8:30 am
1) tune queries/indexes/etc. Reducing IO demand can open up all kinds of IO headroom
I can add indexes that that's about it. I can suggest changes to queries, but can't change anything.
2) tune the hardware
Ha, IF ONLY! (This 1995 admin still uses equipment that was outdated in Y2K. My home network is better)
3) get more RAM
(again, getting this admin to do anything is like pulling teeth)
4) put your stuff on faster IO subsystem
(again, admin block)
Looks like I'm SOL on this one. Just keep flying my DeLorean into work everyday.
June 15, 2011 at 8:34 am
1) tune queries/indexes/etc. Reducing IO demand can open up all kinds of IO headroom
I can add indexes that that's about it. I can suggest changes to queries, but can't change anything.
2) tune the hardware
Ha, IF ONLY! (This 1995 admin still uses equipment that was outdated in Y2K. My home network is better)
3) get more RAM
(again, getting this admin to do anything is like pulling teeth)
4) put your stuff on faster IO subsystem
(again, admin block)
Looks like I'm SOL on this one. Just keep flying my DeLorean into work everyday.
June 15, 2011 at 8:35 am
ckingtaylor (6/15/2011)
Thanks. Problem is my admin is still living in 1995 and probably doesn't know what a LUN is. So the weakest link in this chain is the network group. Thanks again.(I'm forced to drive a DeLorean to work just to tolerate these archaic methods)
Depending on your VM infrastructure..he may be right when he says it doesn't matter. If the entire raid array is all pooled together and then chunked up for each individual VM then there's going to be no performance gain by splitting it up into multiple logical partitions. The performance comes from splitting it up onto multiple physical drives so they can all be spinning and reading at the same time. In your case, they either will or wont regardless of logical drives.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply