January 29, 2007 at 9:23 am
Is anyone using virtual drives for log files and data files?
We have done this on one server in a SAN configuration but I don't see a benefit in performance.
Now I'm migrating to a new server to install SQL 2005 and I plan on using the default installation settings to just put the data and log files on the C: drive not creating virtual drives via vmware.
Any comments or suggests would be appreciated. Thanks.
January 30, 2007 at 3:17 am
Hi
You will not see any performance increase in this case, especially for a single user. You would probably find that performance is worse. Even if the SAN has faster disks this is more than compensated by a local disk because of the complexities of communications and the fact that the disks are physically further away.
If you use drive C then this disk will be shared by the operating system the temporary database and you main database. The physical disk head can only be working on one part of the disk at a time, so if you need to write to the TEM DB it will not be reading you database; if the operating system is doing something on the disk it is not working with your database.
But as soon as you have more than one user then you need the database files spread over as many spindles (disk drives) as possible. The disk head can only be in one place at a time so the more disks the better. You want your SAN formatted with RAID 1+0 with as many disks as you can get. I would personally have the OS and programs on drive C: have the temp DB on another Local drive D: and the database on the SAN
January 30, 2007 at 5:57 am
my experience with vmware has generally shown performance degredation. I personally don't believe virtual drives are good for databases, however, we've all been using virtual drives for years ( it just wasn't an in term!! ) When you partition your home PC to have three partitions you create virtual drives.
The physical spindles are the important consideration fro performance and dedicated spindles are a must for production systems. There are technically lots of advantages to SAN's , however my feeling is that many are badly designed and implemented and I include the vendors and vars in this.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 30, 2007 at 6:31 am
The cost of SAN's has gone down, but it's still a lot more expensive than just a SCSI RAID container. I like the performance a SAN offers, but its offset by complexity - wiring, setup, maintenance. In most cases you'll pay an annual maintenance agreement to have someone that works on them all the time come in to do changes you need.
The partitioned drive = virtual drive comment is interesting. And accurate I think, but somehow it SEEMS different!
January 30, 2007 at 10:22 am
Thanks everyone and I agree.
C:\ - os, sql server system files - RAID-1+0 (direct-attached storage, not SAN)
D:\ - Tempdb - RAID-1 (direct-attached storage, not SAN)
E:\ - Data files - RAID-1+0 (SAN)
F:\ - Log files - RAID-1 (SAN)
Currently, it’s a production database with RAID 0 for the C drive and RAID 5 for E drive; I don’t believe in slapping it all on one san like we did with the other. Plus you wouldn’t be able to utilize I/O parallelism at the table level.
It’s probably a cost issue.
January 30, 2007 at 12:44 pm
I honestly can’t tell you why there was this illusion that virtual vmdrives have some magical performance by vmware. At the time, I thought it was no different than partitioning a hard drive.
January 31, 2007 at 8:51 am
you'll not find any advantage to placing tempdb locally, especially on a raid 1.
I find the biggest mistake made with splitting out tempdb is to not match the spindle count to data. ( and of course making sure tempdb is actually heavily used )
Anyway, you say raid 10 for data so that means more than 1 available spindle, let's say you have 6 in total giving 3 available ( the other 3 being the mirror ) with tempdb now on 1 spindle you've introduced a 3 to 1 bottleneck on performance ( 1 disk only supports one third the io of the raid 10 ) + on most modern raid controllers ( e.g. SAN ) raid 10 supports split reads thus increasing the read throughput to equal 6 spindles.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply