TL;DR:  Don’t bother, its not.

I’m at it again .. writing what I call integration tests, which are effectively database-friendly-data-setup-and-teardown tests.  As can be imagined, its definitely much slower than unit testing; however, I love doing it, and there’s a lot of sprocs and other stuff that its really nice to get some tests around. (most of the bugs that led to this investment in time, were in the sprocs).

Since I had the RAM available.. decided to try to have SQLSERVER (Developer Edition) run against databases that were stored in RAM.  How does that compare?

The database is about 4G in size.

SqlServer Developer, 10G RAM Drive, In a VM , Tests run by R#

SqlServer Developer, using MDF files against C: in a VM; VM is on SSD; R#

SqlServer Enterprise on VM in Azure (network lag); R#

One test failure was because this server’s copy of the invoice database was not complete; and the test was set to be readonly against this server (local sqlexpress = much happier about dropping and re-inserting records).

I could not ping all the way to the database server, but Client Statistics would indicate a ping of probably 150ms?

SqlServer Enterprise at client location via VPN; R#

This is definitely faster than our azure-hosted SQL – Looking at ping, says it’s a 50ms round trip.

Pinging taacasql01.triaa.local [10.120.0.10] with 32 bytes of data:
Reply from 10.120.0.10: bytes=32 time=50ms TTL=127
Reply from 10.120.0.10: bytes=32 time=44ms TTL=127
Reply from 10.120.0.10: bytes=32 time=45ms TTL=127
Reply from 10.120.0.10: bytes=32 time=45ms TTL=127

SqlServer Enterprise local network (client site); Teamcity

This is almost on-par with local SqlServer.  However, I don’t know if the machines are faster.

Summary

Ram-Disk Sql-Server didn’t help.  Or maybe its that, even on a regular hard drive, it was able to load everything into RAM, and got quite fast.

Local Sqlserver vs LAN SqlServer were close enough; I’d use one as a substitute for “how would it perform” on the other.   Helps that the customer is (probably) running both VM’s on a Hypervisor, and network communication between the two machines is … superfast.

WAN SqlServer was definitely the dumps; however, that’s good for posing artificial limits on ourselves, to make sure we’re not doing too many round trips, etc.   Nevertheless, our main cloud sql server is slower than a VPN into our client; that doesn’t seem right. Or, our client is that awesome.  It could be the latter. 

Not shown above, but if you drill in to some of the tests, you can see the cost of setting up an Entity Framework context the first time.  It seemed to take about 8 seconds against my local server.  Once set up, subsequent tests were less than a second each.   However, the set-up would happen again for every other test fixture – apparently whatever its doing to cache things, got dropped between fixtures.  Possible optimization, hang onto it in a static?  *food for thought*

Methodology Notes:

I ran each full suite twice.  Sometimes three times, till I got what seemed to be a stable number.

I used SoftPerfect for the RAM disk.  Its set to sync ramdisk to disk every hour or so.  After seeing that it didn’t really improve things, I deleted it.

I drank 3 glasses of Tea, 1 Spicy Hot V8, and ate 3 pieces of chocolate, and 1 cream cheese snack, while running all these tests.