Quick Testing with Fusion IO Card

Some of you have probably heard of the Fusion IO Cards.  If not you can check out more about them here.  I got one of these for a very short demo  yesterday.  I had 1 day with this card and I was only informed of this the night before as well so it made testing difficult.  I had a simple goal in mind for this card which helped me to keep testing to a minimum.

My Goal for these cards were to place Tempdb on the card.  Since every join/hash table/query execution hits tempdb I see this as a quick way to gain some performance without moving around lot’s of Databases.  All I had at my disposal for this quick test was a server with 3 drives setup in Raid 5 housing the C and basically everything else.  When life gives you lemons Call @sqlagentman and @sqlrockstar and make Margarita’s! 

So I wanted to test the write performance since that was something i could quickly setup and test.  I setup a simple loop that created 1 million row temp tables by pulling data out of another table that I had that had 12 million rows.  The loop would Create the table insert the data then drop the table.  I decided I would do this in loops of 10 and watch the disk queue length during this time. 

The First run with Tempdb on the Raid 5 with 3 Drives(2 effective).  Ran the test in 1:19 seconds hit a Max que Length of 253 and Averaged a que length of 53. 

I then Stopped SQL moved over tempdb to the Fusion drive and re-ran.  Same test same loop It took 21 seconds and only got to a max Que length of 1 and Averaged 0.  Much less of a hit.

I wanted to see if I could get higher Que lengths so I switched it to 10 million row tables and ran it again.  This time it took 2:54 seconds actually hit a Max disk que of 13 and averaged about 1 for the disk que length. 

At this point I really wish I could get some more time and setup another set of drives to do more dedicated tests but that’s just not in the cards for me right now.  I would still suggest based off these numbers having such a high level of difference that if you can look into these cards as a hardware performance tuning option.  I know I am suggesting to purchase one and when I do get it in house I'll have time to do more tests. 

If you also want to look at SSD(Solid State Drives) vs Hard Drives @paulRandal referenced a great blog post from James Hamilton on the differences you can find it here.