SqlServer on a Ram Drive: Fast or Not?

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#

image

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

image

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

image

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?

image

SqlServer Enterprise at client location via VPN; R#

image

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

image

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.

image

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. 

Tagged with:
Posted in Code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories
Tags
.net 3d 3d-printing 4k abc15 algorithms ames android anonymous types asp.net audio editing aws backup basecamp beatunes biorhythm bitcoin bittorrent blender blog boston marathon bpm c# caffeine campfire candycrush car carmax charity chiropractor cities-skylines clog clone codelouisville codepalousa coding coffee collaboration color run ComputerElbow ComputerVision configuration consulting cooking crash course crashplan crestwood cycling dabda dan dapper DataSet ddl diabetes dictation dotnetcore dotnetmud downtown e-cycling elite excel exercise expiration facebook feature-branching firefall flipflops Flow FL Studio focus food forecastle fortresscraft franklinplanner gadgets game-design games git github google docs google maps gopro gps grandpa greenshot hack half marathon headless health heart rate hiren ignew integration testing interop inventory ios ipad itunes javascript jobs karma kdf keyboards keys kittens lamont laptop lavalamp lego life lifehack linq linqtotwitter linux los angeles louisville mandelbulber massage therapy mastery-teaching maths merge metformin Minecraft miniature modeling monitor mud muhammad ali institute music mvc mycartracks netfabb nexus10 node nostalgia nutrition nwipe oldham county grand slam opal openjscad openscad owin pacedj paper mockup pepakura performance photoscan politics pomodoro postgresql powershell premiere prius process product-management project-management qa ReFS resharper review rmi roman road 5k RSI rubiks running samsung 700t sandals schedule scooter scribblelive selenium service shapeways sleep slic3r sneakersync snot software software-engineering solidoodle soylent spacegame speaking sql sqlite SSDT SSIS standing state-machine stayfocusd stonehearth sunset tablet teaching team teamcity teamtreehouse terraform tesla testing tfs time timelapse torque touch tracks trs80 Tuple tutor twitter ubuntu unit testing utilities video video editing visual studio vscode vsvim warp stabilizer windows 8 windows home server wordpress wpf xml
Archives
%d bloggers like this: