July 16, 2010 at 1:03 pm
Quick question to the community -
I have a virtual server (windows 2003 R2 std) with SQL Server 2005 SP3 Std installed on it. OS is on C, Data and some logs on E and a few more logs on F.
I have a high writelog wait type, and I cannot change the physical RAID structure. I have other virtual machines that perform fine on virtual but this one seems to have issues.
I didn't set it up initially, but am trying to tune it to get better performance.
It is dedicated db server for SCCM (microsoft's tool for pushing updates, scripts, and inventory clients).
It is using CLR.
My question - if I move ALL user db log files to F: will this improve performance on E at all?
I would say no, because the underlying RAID structure doesn't change but maybe my knowledge is limited...
Any input would be helpful!
July 16, 2010 at 4:40 pm
Yes. Perf should improve.
Thanks
SQLBuddy
July 16, 2010 at 9:27 pm
Are the data and log devices configured with the same RAID configuration?
I can't see it making a big difference- but it could if the log device is on a LUN that's not as heavily used...
July 16, 2010 at 9:30 pm
You could run a tool like SQLIO that can benchmark the read/write performance of the drives (before moving) to see if it can achieve faster IO.
July 19, 2010 at 9:25 am
Thanks for your replies.
We are using Blades on VMWare which is a little different than a LUN but the same in theory. The Blade can have several virtual servers on it. It uses what resources are given it from the SAN. We have about 20 blades and 3 to 4 virtual servers on each one.
Logically I don't see separating them making a bit of difference, but it seems that my virtual servers where I have them separated perform a bit better.
The RAID is the same so that makes no sense unless the way that VMWare handles the logical disks is different?
I guess that's my question - does VMWare balance processing on logical disks or is it merely separating/reserving space for each logical drive and only that?
Thanks again for your thoughts on this 🙂
July 19, 2010 at 9:29 am
SQLIO is a good idea - don't know why I hadn't thought of that. It will be odd to see if it better!
July 20, 2010 at 7:51 am
+1 to SQLIO, but without a fuller understanding of the true physical level, it's insufficient.
Forgive me if I missed it, but I didn't see any information at all on the physical disk structure. Drive letters and UNC paths and mounts points are all logical reference points. LUNs and Metaluns and so on are also logical reference points. Virtual disks are simply files that are stored "on" one of these logical reference points.
To do performance tuning at the storage level, one much start at the physical level; with spindles, and platters, and SSDs/EFDs, and RAMdisks, and so on.
With VM's, you should also check to see if the storage can move arbitarily; if so, you need to be in communication with the SAN admins about that.
Perhaps what the SQL Server 'machine' sees as E and F share the same three physical spindles on 15k RPM drives; one will be a little faster than the other, because it's closer to the outer edge, but they will definitely contend with each other.
Perhaps what the SQL Server 'machine' sees as E and F do not, in fact, share physical spindles... but those sets of physical spindles are, or will be, shared with other users, in which case some other very random IO heavy process sharing those spindles could significantly impact SQL performance.
This is not a trivial investigation.
July 20, 2010 at 9:49 am
I apologize I didn't put the physical structure in my post.
The SAN is a set of 8 SAS and 8 SATA drives, 2 controllers, not sure how much RAM anymore I lost count.., and yes, the servers CAN move from blade to blade without me knowing, trust me not the surprise you want!
I have little actually - almost no control over this configuration so I'm stuck doing the best I can with tuning, etc.
The RAID is a special RAID 6 that is faster than a traditional RAID 6. Sorry I cannot recall what it is named. I usually get very good throughput on it even with high oltp servers it is less than 10 ms.
So.. since I cannot change the physical - my question, for which I'm sure the answer is "no", is can I see a benefit from separating the data and log files if only at the logical level. I know that this usually implies ONLY reserving X amount of space on those "drives" for the files, but I do wonder if the virtual OS treats it different and sends the work to the underlying hardware differently than if it were all on one drive letter.
again - I'm sure the answer is "no" but I'm hopeful I don't know the answer! LOL
Thanks again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply