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 real hardware partitioning database and placing different partitions with different tables on different hard drives can be beneficial for query performance. Or, to simplify - "more spindles is better".

When SQL Server is running in virtual environment the concept of spindles no longer applies. Does it mean that for query performance reasons it doesn't make sense to partition database anymore?

And, following the same logic, keeping database and log files on different disks doesn't make sense either in virtual environment.

I understand that there are other reasons to partition database that still apply in virtual environment (for instance large read-only tables can be in separate partition that only needs to be backed up once) but this is not a 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
add comment

closed as unclear what you're asking by Max Vernon, RolandoMySQLDBA, Mark Storey-Smith, bluefeet, Paul White Oct 26 '13 at 21:51

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

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
add comment

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
add comment

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