Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over separate physical disks and RAID arrays is also an option. Simply put, more spindles is better.

When SQL server is running in a virtual environment, the concept of spindles applies differently. You often have little to no direct control over what physical disks your virtual disks are mapped to. Does this mean that it doesn't make sense to partition a database, or split the data and log files over separate virtual disks, the same way we do with physical ones?

I understand that there are other reasons to partition databases that still apply in virtual environments. For instance, large read-only tables can be in a separate partition that only needs to be backed up once, but this is not the subject of this post.

share|improve this question
2  
I didn't realize that a virtual environment no longer had separate disks? We have some new virtual environments being created where they will have one disk that uses SSDs and the others are not. To me that means you can have different physical disks for different drives even in a virtual environment. –  Kenneth Fisher Oct 22 '13 at 2:09
    
Virtual environment can have logical disks (as in different drive letters). However, these logical discs are created by an array of physical discs, and you really have no way of knowing how many physical discs are behind the logical disc. That being said, it is probably possible to have one logical disc based on mechanical array and another - on SSD array. –  Joe Schmoe Oct 22 '13 at 2:38
    
@Joe I'm not really sure what you're talking about here. You seem to be talking about data/log file organization and management, but the word you keep using is "partitioning." This wouldn't be such an issue, but you slip into a mild discussion on table partitioning in the last paragraph (and yes, I understand the notion of filegroups, etc). –  swasheck Oct 22 '13 at 14:47
    
He's really confused, we might want to close this as it's going to confuse others. He believes there are no physical disks in a virtualized environment and is asking how to store data across logical partitioned disks for performance.... –  Ali Razeghi Oct 24 '13 at 19:27

3 Answers 3

I can attest that disks DO matter in virtual and non virtual environments, at the end of the day it's physics whether or not there is a Hypervisor layer added. Double true for writes, as they eventually have to persist to disk.

IF the data that is needed is not in memory within SQL Server (the base tables), and your query needs to read it, it will absolutely go to disk and read it.

For example, turn on your perfmon counters for disk sec/read & write as well as disk read bytes/sec & write bytes/sec. This will show you much how much data SQL Server is requesting. Thus, the matter of spindles always matters.

Separating transaction logs and data disks was important for 2 main reasons:

1-ACID compliance. If your data disks fail you can still take a 'tail of the log' backup, and restore the DB.

2-Transaction logs are heavy write and low read, data drives can be mixed or just heavy read. Separating them based on workload would be very helpful.

share|improve this answer

Partition-elimination is an awefully good reason to still partition even in a virtual environment, if your queries are such that they can use take advantage of it.

share|improve this answer
    
Hi Dave, I know the question was confusing but he was asking about separating physical disks, not partition elimination. I was confused too. Just a note, I didn't -1 your answer, someone else did. –  Ali Razeghi Oct 22 '13 at 0:26
    
I was not asking about separating physical discs. There are no physical disks in virtual environment, hence the reason for my post. –  Joe Schmoe Oct 22 '13 at 2:01
    
Actually I would say this answer would apply to the Does it mean that for query performance reasons it doesn't make sense to partition database anymore? portion of the question as an additional performance reason in a virtual environment. –  Kenneth Fisher Oct 22 '13 at 2:07
    
Joe Schmoe there is ALWAYS a physical disk... ALWAYS. We don't store data in air yet. Where do you get the idea there is no physical disk? How will it even read the data if it is not in memory then? It seems like you have a lot of foundation work to cover. –  Ali Razeghi Oct 24 '13 at 19:25
    
Of course there are physical disks but you are not accessing them directly. Behind your drive C: you can have pieces of multiple discs from some kinds of network connected storage. And placing you database on C: drive and log on D: in virtual environment doesn't mean they will actually end up on different spindles. Does this make it clearer? –  Joe Schmoe Oct 25 '13 at 20:31

I think you might be a bit confused about virtualization. Which virtualization platform are you using? I havent come across one yet that doesnt allow you to map the drives however you choose. There are default configs and settings that allow the hypervisor to decide how to best make use of the available host's resources, but I have never seen an offering that doesnt allow you to override and say "Map virtual drive C:/ to physical drive C:/, D:/ to D:/", etc.

To answer your question, no, if you have 2 virtual drives, C:/ and D:/, but they both map to physical drive C:/, you wont gain any advantage by splitting up your database files across the virtual drives. If youre also bringing in networked drives, unless you have an incredible network config, youre actually probably going to lose performance since writes to external drives are expensive and time consuming. If you need that performance gain, I would recommend creating a new virtual drive and mapping it to a different, non-networked drive on the physical hardware.

You will probably also want to make sure that you dont put another high-load db on that virtual server with the same config, because if theyre both trying to access the physical drive at the same time, you will lose performance waiting for read/write time on that drive while the other DB is busy using it.

Overall, if you are worried about performance to the point of adding additional physical drives and splitting your mdf and tlog files across 2 physical pieces of hardware (which indicates youre probably already having resource constraints), i would probably advise against virtualizing if at all possible. Highly transactional, resource hungry DB's are not the best candidate for virtualization as you are inherently losing performance by splitting already scarce machine resources across multiple virtual clients and the hypervisor. The current setup I admin virtualizes everything except for the DB's for exactly this reason.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.