Multiple Virtual Disks and Application Performance
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
...three disks for SQL server (one for SQL server and the main database, one for tempDB , and one for logs.
If you're doing a SQL Server with any real load, I do one OS Disk, and One BIG Disk with partitions for SQL Data, Translogs, and Temp DB, and oversize them by at least 25% of what you expect to use.
I'll also agree with using the recommended cluster sizes as well.
-
@dafyre said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
...three disks for SQL server (one for SQL server and the main database, one for tempDB , and one for logs.
If you're doing a SQL Server with any real load, I do one OS Disk, and One BIG Disk with partitions for SQL Data, Translogs, and Temp DB, and oversize them by at least 25% of what you expect to use.
I'll also agree with using the recommended cluster sizes as well.
You didn't address the VM host aspect of his question.
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
Now, if you follow their advice of using 64K cluster sizes for the disks storing SQL data, then maybe it makes sense because the guest OS would be writing to its virtual disks differently
Is it though? Your VM storage is all formatted at whatever it's formatted at. if you create a vDisk at a different cluster size than the underlying disk - I have no idea what happens performance wise.
-
@dashrender said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
Now, if you follow their advice of using 64K cluster sizes for the disks storing SQL data, then maybe it makes sense because the guest OS would be writing to its virtual disks differently
Is it though? Your VM storage is all formatted at whatever it's formatted at. if you create a vDisk at a different cluster size than the underlying disk - I have no idea what happens performance wise.
You and I are thinking alike. It seems like there's not going to be any real difference since the underlying disk is the same for everything.
-
@dafyre said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
...three disks for SQL server (one for SQL server and the main database, one for tempDB , and one for logs.
If you're doing a SQL Server with any real load, I do one OS Disk, and One BIG Disk with partitions for SQL Data, Translogs, and Temp DB, and oversize them by at least 25% of what you expect to use.
I'll also agree with using the recommended cluster sizes as well.
If different physical disks were at play, I would agree, but does doing that separation for a VM, specifically in a situation where the VM storage is all on the same physical disks, actually make a difference?
-
I'm guessing - nothing more honest - that you need to find the IOPs requirement of your situation, and make sure you are able to provide that to the VM in question.
With SSDs you're likely able to have so many more IOPs than you really need (unless making some massively huge transaction db, etc) that this should be that hard. Just think, 10 yeas ago, we did all this on spinning disks...
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
@dafyre said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
...three disks for SQL server (one for SQL server and the main database, one for tempDB , and one for logs.
If you're doing a SQL Server with any real load, I do one OS Disk, and One BIG Disk with partitions for SQL Data, Translogs, and Temp DB, and oversize them by at least 25% of what you expect to use.
I'll also agree with using the recommended cluster sizes as well.
If different physical disks were at play, I would agree, but does doing that separation for a VM, specifically in a situation where the VM storage is all on the same physical disks, actually make a difference?
then there is always the argument for OBR10 that Scott has been saying for years - don't waste a spindle/disk on your OS - it just really shouldn't affect anything performance wise.
-
@dashrender said in Multiple Virtual Disks and Application Performance:
@dafyre said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
...three disks for SQL server (one for SQL server and the main database, one for tempDB , and one for logs.
If you're doing a SQL Server with any real load, I do one OS Disk, and One BIG Disk with partitions for SQL Data, Translogs, and Temp DB, and oversize them by at least 25% of what you expect to use.
I'll also agree with using the recommended cluster sizes as well.
You didn't address the VM host aspect of his question.
That changes little as far as the way I build it. If it has to be a physical SQL Server (groan), one RAID1 for OS, and RAID5/6 for SQL bits. Then format and go.
If it's a virtual (not as loud of a groan), one disk for OS, and one big disk for SQL bits, then format and go. I don't think I've ever actually done the 64k clusters because the SQL servers I've managed never had a large enough amount of data to warrant it.
As for the VMhosts, that is a good point about cluster sizes and the underlying host disks.
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
@dafyre said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
...three disks for SQL server (one for SQL server and the main database, one for tempDB , and one for logs.
If you're doing a SQL Server with any real load, I do one OS Disk, and One BIG Disk with partitions for SQL Data, Translogs, and Temp DB, and oversize them by at least 25% of what you expect to use.
I'll also agree with using the recommended cluster sizes as well.
If different physical disks were at play, I would agree, but does doing that separation for a VM, specifically in a situation where the VM storage is all on the same physical disks, actually make a difference?
That wouldn't be for performance, actually. That would be for ease of management. I got called in to fix an SQL Server where the OS Disk was full of translog & tempdb files more than once that totally crashed the SQL server.
By separating them out, you reduce the likelihood of that happening -- if you size things appropriately.
-
@dafyre said in Multiple Virtual Disks and Application Performance:
That wouldn't be for performance, actually. That would be for ease of management. I got called in to fix an SQL Server where the OS Disk was full of translog & tempdb files more than once that totally crashed the SQL server.
That's true for management, and on that point I would use multiple virtual disks, simply to make later storage expansion easier. I was most curious about performance implications though. Looking back at my OP, I wasn't clear about that.
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
On a system where you had different physical disks, I could see this making sense, and I suspect this guide was written assuming just that -- different physical disks. However, if you're in a virtualized environment, where your VM storage is all on the same device, I don't see where the benefit would be.
I agree that it was written thinking about the performance on separate physical arrays/disk drives.
There might be some benefit to different virtual disks/volumes in a virtualized environment if you could mix thin / thick provisioning or have different filesystems and the drives, optimized for the job at hand.
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
Neither the SCCM site nor the SQL database should share their disks with other applications
If you have a VM whose virtual disk files are all being stored on the same block device on your hypervisor, does present multiple virtual disks to your VM really make a positive impact on performance?
What they said is that they should not share disks.
But what you asked is not that at all. You asked if once they don't do that, if separating the workload by logical volume helps with performance.
The direct answer to your question is... yes, it can. BUT what you asked is unrelated to what they said.
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
On a system where you had different physical disks, I could see this making sense, and I suspect this guide was written assuming just that -- different physical disks. However, if you're in a virtualized environment, where your VM storage is all on the same device, I don't see where the benefit would be.
You are half right. It's not assuming anything. What it is doing is telling you what to do.
They are saying: you should not share disks.
You are saying: but I'm going to share disks no matter what.
Both things are fine. They have a point (it is likely wrong, but it is a point). And you are totally allowed to not follow their advice.
But you seem to be missing that they are telling you how to set up the physical storage, and you are refusing to do so, then asking if advice that no one has given is any good.
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
But it still doesn't seem to make sense to put the Configuration Manager install on a separate virtual disk than Windows.
Depends on many factors. It can in some cases, and it cannot in others. It's not as simple as better or worse.
-
@dashrender said in Multiple Virtual Disks and Application Performance:
@dafyre said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
...three disks for SQL server (one for SQL server and the main database, one for tempDB , and one for logs.
If you're doing a SQL Server with any real load, I do one OS Disk, and One BIG Disk with partitions for SQL Data, Translogs, and Temp DB, and oversize them by at least 25% of what you expect to use.
I'll also agree with using the recommended cluster sizes as well.
You didn't address the VM host aspect of his question.
Because it's not actually an aspect of the question. It's a red herring.
-
@dashrender said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
Now, if you follow their advice of using 64K cluster sizes for the disks storing SQL data, then maybe it makes sense because the guest OS would be writing to its virtual disks differently
Is it though? Your VM storage is all formatted at whatever it's formatted at. if you create a vDisk at a different cluster size than the underlying disk - I have no idea what happens performance wise.
Their advice is to make the clusters that size. Putting it on top of something different is plain and simply not following their advice.
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
If different physical disks were at play, I would agree
And they are, in any situation where you have done as they said.
-
@eddiejennings said in Multiple Virtual Disks and Application Performance:
It seems like there's not going to be any real difference since the underlying disk is the same for everything.
Correct. If you don't follow their advice and do two essentially identical things that are unrelated to their advice, then your two outcomes will be roughly identical. But neither has anything to do with whether or not their advice was good, since in neither case was anything remotely resembling their advice followed.
-
@dashrender said in Multiple Virtual Disks and Application Performance:
I'm guessing - nothing more honest - that you need to find the IOPs requirement of your situation, and make sure you are able to provide that to the VM in question.
With SSDs you're likely able to have so many more IOPs than you really need (unless making some massively huge transaction db, etc) that this should be that hard. Just think, 10 yeas ago, we did all this on spinning disks...
Yup, it's more than just IOPs, though. And the problem with IOPs is that there are "IOPs in the right situation." You can't just say "This system has X IOPs", because that's not how storage actually works. The point of their advice, right or wrong, is how to get the most IOPs for the application in question, out of a system. I think that they are generally wrong, and wrong by a lot, but that's a totally different thing.
So far, no one is taking into account what their advice actually was.
-
@dashrender said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
@dafyre said in Multiple Virtual Disks and Application Performance:
@eddiejennings said in Multiple Virtual Disks and Application Performance:
...three disks for SQL server (one for SQL server and the main database, one for tempDB , and one for logs.
If you're doing a SQL Server with any real load, I do one OS Disk, and One BIG Disk with partitions for SQL Data, Translogs, and Temp DB, and oversize them by at least 25% of what you expect to use.
I'll also agree with using the recommended cluster sizes as well.
If different physical disks were at play, I would agree, but does doing that separation for a VM, specifically in a situation where the VM storage is all on the same physical disks, actually make a difference?
then there is always the argument for OBR10 that Scott has been saying for years - don't waste a spindle/disk on your OS - it just really shouldn't affect anything performance wise.
Actually it will, but in the wrong direction. If you have 6 drives and each gets 100 IOPs (let's just use magic numbers here) and do RAID 1 + RAID 1 + RAID 1 to split things up, all in RAID 10 and assume all READ, no WRITE....
The OS gets 200 IOPS, the DB gets 200 IOPS, the log gets 200 IOPS. Of course, the OS doesn't use its allotted IOPS, ever. So that's just lost. And the DB and log are often idle or nearly idle. But each can peak at 200 IOPS, potentially at the same time.
That's splitting it into three groups. Now let's merge them into one.
There is a single 6 drive array. Same drives, just different configuration. Now the OS, DB and log can share 600 IOPS. The OS gets 600 IOPS while booting (the DB hasn't started yet) and once running the DB + logs get to share 600 IOPS (as the OS uses ~none once booted) with each getting 300 (50% faster) if both are needed at exactly the same time and either getting 600 (300% faster) if the other isn't needed at that exact moment.
So it's easy to see in that example that is quite contrived how splitting the workload might cause a dramatic drop in performance because you have to throw away most of your capacity and can't use it when you want it.
