Looking at External Disk Performance using USB 3.0 and eSATA with SSD

I recently did some drive upgrades in my personal laptops and as a result I had a couple of SSD drives sitting around that I wanted to make use of.  A few weeks ago I purchased a new OCZ Agility 3 240GB SATA III SSD from Newegg when it was on sale and at the same time I also purchased two MassCool USB 3 external enclosures to make use of the two 120GB OCZ Vertex 2 SSD’s that I would have sitting around.  The cost for the MassCool enclosures was only $14.99 so I figured it would be a good way to reuse the SSD’s at the time.  When the package from Newegg arrived, I immediately installed the SSD into one of my personal machines and then later I went about testing the performance of the USB 3 external enclosures with the older SSD’s.  Initially the performance was impressive, and I posted a few tweets that attracted some attention and a few people requested that I blog my results.

To make this blog post something worth looking at, I ran a number of additional tests over the last few days using SQLIO and a varying configuration of drives with external enclosures.  Specifically I tested two different USB 3 2.5” SATA enclosures, a separate eSATA 2.5” SATA external enclosure, as well as two USB 3 HDD’s that I already owned to get some performance information from each of the setups.

The external USB 3.0 HDD’s being tested are as follows:

The external enclosures being tested in these tests are as follows:

The SSD’s used with the external enclosures listed above are:

At the time that I bought the 120GB drive I paid close to $2.42 per megabyte for it, and at the time this was a good deal.  When I purchased the 240GB SSD I paid right around $1.08 per megabyte, showing how much the prices have decreased nearly 3 years later.  I happen to own a number of OCZ drives at home and they are one of my favorite brands personally because of the overall reliability I’ve had with them the last three years as well as the performance to cost ratio I’ve experienced. Initially I was just testing the MassCool USB 3 enclosures that I purchased, and I was incredibly happy with the performance that I had from them.  However, after tweeting about the results, my friend Jose Chinchilla (Blog | Twitter) mentioned that I should also try out eSATA because it performed significantly better in his own tests.  Based on this tweet, I set out to my local MicroCenter computer store to purchase a eSATA enclosure and while I was buying it, one of the sales representatives, a guy named Chris, approached me and asked what I was planning to do with the enclosure because USB 3.0 should be faster for SSD’s. The numbers he quoted didn’t match my previous testing, so he made a recommendation for a specific USB 3.0 enclosure, and I figured, for $20 it was worth testing, so I bought an extra Hornettek Panther USB 3.0 device for comparison testing along with the Vantec NexStar CX eSATA enclosure I had already selected.

For the tests, I ran a short set of tests using SQLIO that I previously blogged about on my blog post about the Powershell parser for SQLIO output.  The results from the tests are below:

image

image

The two external USB 3.0 HDD drives had very similar performance characteristics, and they beat their USB 2.0 counterparts performance wise significantly.  I’ve been incredibly happy with my USB 3.0 HDDs overall for the last few years and I only purchase USB 3.0 HDD drives based on my performance tests a few years ago.  However, when compared with the USB 3.0 external enclosures with the SSD’s, the performance difference is quite significant.  One of the problems I’ve had lately is being able to fit all of the virtual machine hard drives, VHD’s for Hyper-V when using my dual boot Hyper-V host VHD, as well as my original VMware Workstation VMDK’s for classes, and even the VirtualBox virtual disk images (VDI) from my blog series on building a completely free playground.  Using the SSD’s with USB 3.0 definitely makes storing my virtual hard disk files on an external array much more feasible performance wise, and my only real limitation from testing is the size of the external device. 

However, the performance of the eSATA external enclosure is incredibly better, with the side trade off that I can only have 1 of them attached to my laptops at a time, and for my personal laptop, the fact that the eSATA enclosure requires not only the eSATA port for throughput, but also the additional USB 2.0 port for power really limits what I can do as far as having multiple disks connected to the laptop.  To be honest, this is something that I can live with given that I also have swapped out my CD/DVD ROM bay for a replacement New Mode US second HDD conversion. This means that I can have two 240GB OCZ SSD’s in my E5420, which is a similar configuration to the dual 256GB Dell SSD configuration in my Precision M6500 for work, while being able to move VM images between the two machines using my older 120GB SSD’s with fantastic performance.

If you are looking at really high performance external hard disk configurations for scalability, I would highly recommend looking at either eSATA with an extra SSD drive or if you need multiple devices and you have USB 3.0, consider going that route.  Either will outperform your existing options significantly.  For the time being, I am going to stick with one eSATA device, which is compatible with both of my laptops, and one USB 3.0 device, which only works with my M6500 at USB 3.0 speeds.  My E5420 only has USB 2.0 onboard, so it makes more sense for me to stick with eSATA for the main shared drive.

What a year it has been!

A year ago today, Paul Randal (Blog | Twitter) first blogged about my joining the SQLskills team as the first full time employee.  I can still remember the first time that I met Paul at SQL PASS in 2008, and I am still amazed that I work with the best in the industry over a year after working out the details of my employment with SQLskills.  I remember the first time that Paul and I discussed the potential for me joining SQLskills, and how hard it was to tell him at the time that there was no way it would work out for either of us.  Joshua Harris once said, “The right thing at the wrong time is the wrong thing” and nothing could be further from the truth as far as my employment with SQLskills.  When Paul and I first discussed the possibility of me joining the SQLskills team, I knew ahead of time that my Army Reserve unit was pending a year long mobilization in early 2011 so that became the first, and as it turned out, last point of discussion about employment feasibility.  Legally there is no requirement for me to disclose this type of information when seeking employment, and further if disclosed this information can not be used as a part of the selection criteria, but I have always not only been proud of my service in the US Army Reserves, but have also been incredibly sensitive to the needs of potential employers when discussing job opportunities.  The end result was that we mutually agreed that the timing wasn’t right and we’d keep open communications about changes should they occur, such that if an employment possibility reopened we’d discuss it again further.  Fast forward a few months and the opportunity once again posed itself, but at this point it was clear the circumstances worked out to be mutually beneficial and we ultimate struck a deal that lead to me joining SQLskills full time as employee number three (it actually says this on my semi-annual review). 

For nearly six years, Paul has been a fabulous mentor of mine, dating back to before he left the product group at Microsoft, based on our forums interactions.  We actually have a very interesting background for solving problems related to SQL Server that have happened in very public venues, for example the Diskeeper 2010 issues that we figured out on Twitter late in 2010.  One of the highlights of my career as a DBA was the first time that Paul asked me to call him out of the blue, when he first broached the topic of joining SQLskills as a consultant.  I remember discussing things, and at the end of the call where we mutually agreed that it wasn’t a good fit at the time, wondering what in the world I had just done.  Looking back, that had to be one of the best decisions of my life, though I didn’t realize it at the time.  Over the ensuing months, I had the opportunity to grow as a SQL Server professional, working in one of the most challenging environments of my life, while learning more about the internals of SQL Server than I ever thought I would.  Before I ever accepted a position with SQLskills, I privately took the written exam for the new SQL Server MCM and passed, and upon passing the written exam, I privately scheduled my lab exam with Joe Sack.  The day after Paul first blogged about me joining SQLskills, I passed the Lab Exam for the SQL Server MCM and became the fourth, and youngest, SQL Server MCM under the new program.

In the year since the initial announcement I’ve made a number of changes.  First off, being a consultant is not what it might seem from the outside looking in.  This is without a doubt one of the hardest jobs I’ve ever had, and every day brings forth new challenges both technically and personally.  Additionally I’ve had to learn how to be an efficient trainer as an instructor for not only the SQLskills Immersion Events, but also the Microsoft SharePoint MCM program as well.  I definitely have the best mentors around for learning how to not only be an effective consultant, but also how to be an effective trainer as well.  Since joining the SQLskills team I have had a top rated session at SQLRally in Orlando, FL, presented at multiple Immersion Events, both public and private, as well as doing my first pre-conference seminar at PASS 2011 on Extended Events.  Along the way I’ve experienced both success and failure, but no matter what the feedback, I’ve been able to grow from the experiences in an incredibly positive manner. 

Personally I can’t imagine having a better year than the last one overall, but I am sure going to try!

Extended Events PerfObject Events

SQL Server 2008 R2 Service Pack 1 provides a new set of Events in Extended Events to collect performance counter data from the Windows OS that would be really useful to monitoring SQL Server.  The first place I can find that they were mentioned is on a blog post by Mark Weber, a PFE for SQL and SAP at Microsoft.  However, a few weeks ago a question was asked about these counters one of the forums and the question was around how to use them.  I looked at the Events and found out that they aren’t really useable in their current implementation, something that is disappointing since being able to collect the data provided by these Events would really benefit most DBA’s out there. 

If you look at the Events and columns, these events collect information about the Logical Disk, Processor, Process for the SQL instance, and System at 15 second intervals and makes the data available through Extended Events.

SELECT name, description
FROM  sys.dm_xe_objects
WHERE name like 'perfobject_%'

name

description

perfobject_process

Returns a set of counters associated with the Process performance object. The event occurs once every 15 seconds for both the SQL Server and SQL Agent processes.

perfobject_system

Returns a set of counters associated with the System performance object. The event occurs once every 15 seconds.

perfobject_logicaldisk

Returns a set of counters associated with the Logical Disk performance object. The event occurs once every 15 seconds for each hard or fixed disk drive.

perfobject_processor

Returns a set of counters associated with the Processor performance object. The event occurs once every 15 seconds for each processor in the system.

If we look at the columns, we’ll see that the columns actually represent the individual counters under the categories exposed by the Event names. For example, the perfobject_logicaldisk event returns the following columns:

SELECT
    object_name, 
    name AS column_name, 
    description
FROM  sys.dm_xe_object_columns
WHERE object_name = 'perfobject_logicaldisk'
  AND column_type = 'data'
ORDER BY object_name, name

object_name

column_name

description

perfobject_logicaldisk

average_disk_bytes_per_read

Shows the average number of bytes transferred from the disk during read operations.

perfobject_logicaldisk

average_disk_bytes_per_transfer

Shows the average number of bytes transferred to or from the disk during write or read operations.

perfobject_logicaldisk

average_disk_bytes_per_write

Shows the average number of bytes transferred to the disk during write operations.

perfobject_logicaldisk

average_disk_queue_length

Shows the average number of both read and write requests that were queued for the selected disk during the sample interval.

perfobject_logicaldisk

average_disk_read_queue_length

Shows the average number of read requests that were queued for the selected disk during the sample interval.

perfobject_logicaldisk

average_disk_seconds_per_read

Shows the average time, in seconds, of a read operation from the disk.

perfobject_logicaldisk

average_disk_seconds_per_transfer

Shows the time, in seconds, of the average disk transfer.

perfobject_logicaldisk

average_disk_seconds_per_write

Shows the average time, in seconds, of a write operation to the disk.

perfobject_logicaldisk

average_disk_write_queue_length

Shows the average number of write requests that were queued for the selected disk during the sample interval.

perfobject_logicaldisk

current_disk_queue_length

Shows the number of requests outstanding on the disk at the time that the performance data is collected.

perfobject_logicaldisk

disk_bytes_per_second

Shows the rate at which bytes are transferred to or from the disk during write or read operations.

perfobject_logicaldisk

disk_read_bytes_per_second

Shows the rate at which bytes are transferred from the disk during read operations.

perfobject_logicaldisk

disk_reads_per_second

Shows the rate at which read operations are performed on the disk.

perfobject_logicaldisk

disk_transfers_per_second

Shows the rate at which read and write operations are performed on the disk.

perfobject_logicaldisk

disk_write_bytes_per_second

Shows the rate at which bytes are transferred to the disk during write operations.

perfobject_logicaldisk

disk_writes_per_second

Shows the rate at which write operations are performed on the disk.

perfobject_logicaldisk

free_megabytes

Shows the unallocated space, in megabytes, on the disk drive. One megabyte is equal to 1,048,576 bytes.

perfobject_logicaldisk

instance_name

The logical disk drive name

perfobject_logicaldisk

percent_disk_read_time

Shows the percentage of time that the selected disk drive is busy servicing read or write requests.

perfobject_logicaldisk

percent_disk_time

Shows the percentage of time that the selected disk drive is busy servicing read requests.

perfobject_logicaldisk

percent_disk_write_time

Shows the percentage of time that the selected disk drive is busy servicing write requests.

perfobject_logicaldisk

percent_free_space

Shows the percentage of the total usable space on the selected logical disk drive that is free.

perfobject_logicaldisk

percent_idle_time

The percentage of time during the sample interval that the disk was idle.

perfobject_logicaldisk

split_io_per_second

The rate at which I/Os to the disk were split into multiple I/Os.

This all seems good, until we actually use the Events in an Event Session and take a look at the data being returned.

CREATE EVENT SESSION [XE_PerfCounters] 
ON SERVER 
ADD EVENT sqlserver.perfobject_logicaldisk 
ADD TARGET package0.ring_buffer;
GO

image

Unfortunately, the counters are returning Raw values for the Event and the necessary Base counters that are required to give these values any useful meaning have been left out of the Events data.  Looking at this in my test environment, it appears the counter values pulled for the perfobject_ Events are pulled directly from Win32_PerfRawData_PerfDisk_LogicalDisk, but if you look at the CookingType requirements for the counters in Win32_PerfFormattedData_PerfDisk_LogicalDisk the raw values have to be calculated by their base values for them to have meaning:

image

I’ve submitted Connect Item 725167 for this and I really hope that this one gets fixed in a future Cumulative Update or Service Pack.