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.