March 17, 2014 at 5:13 pm
Hi all, I was wandering if somebody could advise me on whether it would be wise to stripe multiple vhds on an azure virtual machine for added write speed or what the best configuration is?
Any help would be great thanks.
March 19, 2014 at 4:42 am
I don't have personal hard tests to validate this, but yes, that's Microsoft's recommendations. However, the document I had for reference has been taken offline by Microsoft. It was here, but I'm getting the error that it was removed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 19, 2014 at 4:51 am
Hi thanks for the response,
Take a look at this: http://instadba.com/azure-vm-sql-server-disk-configuration/
and look at the section that says the following:
"Split your tables and indexes into separate database filegroups and then place those filegroups in different files (MDF + NDF) which are spread across separately attached VHDs. Note that this will not offer any benefit for transaction logs because SQL Server does not stripe across transaction log files but uses them sequentially."
That's great...... so you can increase the data file speeds but not the log file speeds. What's the point in that because the log file will become a bottleneck!
This is why I ask if placing the log file on its own striped volume is the solution to this problem?
March 19, 2014 at 4:57 am
Oops. Log. I missed that. Sorry.
No, there's no more benefit there. But I wouldn't panic until you see log latch waits indicating that you're actually seeing a log write bottleneck. Also, if you're going to 2014 in the VMs, you can use Delayed Durability if you have some tolerance for the possibility of data loss.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 19, 2014 at 5:05 am
How come there's no benefit?
I ask because I carried out testing on striping disks by starting with one disk and moving up to four disks. Using SQLIO, sequential reads on a single disks using 64k chunks gave me 31 MB/s sequential writes. The same test on a four disk volume gave me 63 MB/s sequential writes.
The log file is written to sequentially so surely placing it on a four disk volume with 63 MB/s writes would show an improvement over a single disk with 31 MB/s writes??
Am I barking up the wrong tree here?
March 19, 2014 at 5:34 am
You can do things locally with the disks that you just can't do on Azure VMs. So it's not like you get more spindles and controllers by adding more files to blob storage. You don't. So there's nothing that speeds it up. I do wish I had access to the document at Microsoft. It had some good information. I'm trying to track it down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 19, 2014 at 5:40 am
Hi sorry I should have been more clear. These tests were actually run on an Azure virtual machine.
I added four VHDs to the virtual machine and tested with SQLIO on the virtual machine.
One VHD on the virtual machine gave me 31 MB/s sequential reads where as if I striped four separate VHDs together into one logical volume on the virtual machine I got 63 MB/s write speeds off of that volume instead of 31 MB/s off of one single VHD.
The speed increase from 31 MB/s to 63 MB/s were seen on the virtual machine and not locally.
With this in mind are you saying that the 63 MB/s is not real?
March 19, 2014 at 5:53 am
Yeah, that's what Microsoft says will happen. Again, I haven't tested this type of load myself (our production systems right now are tiny). I think it's worth a shot on the logs. As long as the virtual disks are not separate files (which is what that link was referring to), but are a single volume, at least in theory, you should see a benefit. I don't think it'll be linear, but it ought to be there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 19, 2014 at 6:01 am
Yes the four separate vhds were grouped together into one logical volume. With this configuration I achieved double the sequential write speed as one disk. Really I just wanted to confirm that this was the case and that placing a log file on this volume instead of a single VHD would actually give me a performance increase for the log file.
Can I ask you a follow up question that is actually related to all of this?
In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?
I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?
I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file??
My question really is this: If the log file drive is slower than the data file drive, are data transfers limited by the speed of the log file drive or is this not the case??
Thanks
March 19, 2014 at 6:23 am
Logs can absolutely become the bottleneck for data modification operations, yes.
Information written to the log is both more complex and simpler than the T-SQL. It's not a straight copy of the T-SQL. Instead it records the page numbers and data pages of operations, data values added or removed, information about the transaction, start and stop times of the transaction and whether or not the transaction completed. Not to mention the Log Sequence Number. So, writing 'Dog' to a column, more than 'Dog' is stored in the log. But, it is an optimized process, so it's not usually the bottleneck (but certainly can be).
Funny, I'm just updating the locking and blocking chapter in my book, so I'm going through all the ACID properties and that's a big part of the transaction log use in SQL Server, supporting that aspect of relational storage.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply