September 20, 2016 at 5:51 am
Hi,
On our SQL 2012 physical server, we setup separate drives for the system, tempdb, users databases and user database log files. We are in process of moving our data warehouse database onto a vendor hosted SQL 2014 environment running on a virtual server. The load job time has doubled 20-25 minutes to close to an hour. Most of the time in the process is due to dropping/adding indexes. The setup is on a virtual server and everything is on the d: drive. On a virtual, I've been told having all on the d: drive is fine as I've suggested setting up the different drives like our current non-hosted environment. Can anyone confirm if you if it is still best practice to setup on the various databases/log files on separate drives on a virtual server? If this is the case, also explain why it still matters on a virtual machine so I can share this information?
Thanks in advance.
September 20, 2016 at 7:01 am
I would think first, a lot of this would depend on the underlying storage and how it's provisioned for the virtual host OS and guests.
One example would be, if the storage consists of a mix of hard disks and SSDs, you could have your data and log drives provisioned to use the SSD storage while the OS and application drives are provisioned on the hard drives. You would see performance benefits for the data access in such a situation.
As for further splitting the data and logs to different drives, again it would partly depend on the way the underlying storage is provisioned and to some extent personal preference (ex: My servers are virtual and I still request separate data, log, filestream, and audit file drives, so that I have "logical" separation, plus it makes managing the available space a little easier.)
So, to address your issue, I would have a chat with the hosting vendor to find out what sort of media your VM is on, for starters, then work with them to better provision (it's even possible they're exposing an actual drive to your VM for the D:, in which case you have all the usual issues with one drive for data and log files as you would with a physical server.)
September 20, 2016 at 9:03 am
Depends
Are you talking about separate VM drives which are just virtual hard drives on the same physical device? If so, no point (other than logical separation and easier space management).
VMs have the same IO rules as physical machines, with an extra layer of indirection to make things difficult. If your workload requires multiple IO paths, multiple different arrays to get sufficient throughput as a physical machine, then it requires the same once you've gone virtual. There's no virtualisation magic.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2016 at 4:01 pm
And it gets more and more complicated as storage becomes more advanced.
If the vendor is managing tearing/placement of the files on a drive level, then having all the files on a single logical drive can affect the placement. If placement is managed at a file level and all the files are in a single logical drive, can they move the data file that has the highest IO demand without moving all the other files? If not, then you are potentially paying for expensive faster drives to support files that have a lower IO requirement. Alternately the tearing algorithm may be averaging out the IO demands, and not escalating a drive because the average demand across all files doesn't cross the escalation threshold. So you may be sitting on intermediate disk rather than some files on lower and some on higher.
You also need to review the IO SLA. I've recently had a client move to IaaS on "default" storage with the SLA: Latency < 25ms at less than or equal to 3500 IOPS.
Nobody had taken the time to evaluate the IOPS to see if this was reasonable, and for an application server this may be fine, but not for a database server.
Scaling up to the next tier at <15ms for up to 5000 IOPS resulted an almost exactly 40% improvement, and going to the next at <5ms for up to 10000 IOPS, queries went down to almost 1/3rd again, back to the original in house server performance.
For most environments I still ask for separate logical drives. The exception to this may be Fusion IO and Xtreme IO which are doing a lot of funky thing with compression, and testing we've done indicates there is mo difference in performance, but I'll be honest I still don't like it here.
Cheers
Leo
Nothing in life is ever so complicated that with a little effort it can't be made more complicated 😀
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 20, 2016 at 5:10 pm
Thanks for the information it was very helpful.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply