Todd Robinson

Today I’ll be sitting in the electric chair for exam 71-463: Implementing a Data Warehouse with Microsoft SQL Server 2012. Hopefully I won’t have a temperamental computer which decides to have a mind of its own today and have to shuffle off to another machine. Wish me luck!

I’ve been notified that my Chalk-Talk session, AppFabric Cache and SQL Server 2008, has been selected for this years PASS summit. Here is the abstract that I submitted:

With all the hype about NoSQL going around in the SQL community its about time we looked at a Microsoft’s distributed cache-aside solution, AppFabric Cache. In this session we will briefly go over what AppFabric Cache is, what it can and cannot do, as well as some of the compelling features that make it a complimentary product to SQL Server. We will go over some real-world examples on how AppFabric Cache can co-exist with SQL Server and increase performance. In addition, we will compare the speed of the AppFabric Cache in comparison to SQL Server 2008  in both queries and inserts/updates. This chalk-talk will go more in-depth into some of the information presented in the SQLCAT Optimizing application workload & performance by leveraging AppFabric Cache along with SQL Server session.

Looks like I’ll be practicing nothing but AppFabric from now until the conference!

In my testing with AppFabric Cache I’ve been comparing its performance against SQL Server 2008 R2. In just about all use cases, AppFabric Cache is kicking SQL’s butt, except for one, in what I call the “BulkPut”.

In my testing scenario, I receive messages that I have to process which contain 1 to 100 items. There are two types of messages. One message contains all updates, and the other contains all queries. The schema of the database is similar to what gets put into the AppFabric Cache, in that I simply have a key and the object, which in this case is the serialized portion of the message. When I perform the updates in AppFabric Cache, I use the Put command, and when doing the queries, I use the BulkGet. In SQL Server 2008 R2, for both updates and queries I use a stored procedure which accepts a Table Valued Parameter (TVP). This allows me to send the 1 to 100 items in one shot to the stored procedure.

First, lets see the numbers for the queries:

As you can see, both sets of data are fairly linear, and you can clearly see the cost of making the connection to SQL, translating the SQL request to get the key and its object, then returning it. Overall, AppFabric cache is 3 to 6 times faster in retrieving the same data.

Now, if we look at the numbers for the updates:

Now we see that being able to supply the items in bulk to the TVP in SQL Server allows us to beat out the AppFabric Cache which has to do the Put command serially, or as my SQL folks out there would say RBAR (Row by agonizing row). This is why I would love to see a BulkPut command in AppFabric so I can jsut send it a bunch of changes that occur in the same region, similar to the BulkGet request.

Now I’m sure some of you could code circles around me and just use multi-threading or the parallelism in .Net 4.0 to make the updates in AppFabric Cache just as good if not better than the results I get out of the database, but that’s not the point of this exercise. I just wanted to use the best native way I knew how to get the data in and out of the cache and the database in the most efficient, single threaded way I knew possible. Hopefully, something like a “BulkPut” will be put into AppFabric Cache here sometime in the future.

On twitter today I saw some discussions about caching in regards to databases. Included in those discussions were talks about NoSQL, such as MongoDB. Here’s my take on the subject. SQL Server has a built in caching mechanism which takes data, execution plans, etc. and keeps them resident in memory. If you have much more memory than data, chances are that all the requested pages will keep themselves available. All flavors of NoSQL can be thought of as simply a way of storing data in memory, most often in key-value pairs. If you are trying to do simple key value lookups, from both a database and say my favorite flavor of NoSQL, AppFabric Cache, the response time is going to be eerily similar. The reason for that is you are looking for the key in memory in both cases, and returning the “payload”, which is the columns in the database or the value in the cache. The reason the SQL Server takes a bit longer is that it has to “translate” your SQL into a request to get the information, where as in AppFabric Cache, you are simply making a request for the value which corresponds to that key. In my testing, I found that responses from AppFabric Cache on average took .4ms and the same request from the SQL Server database took .6ms. Both of these were simple key value lookups, and the data from SQL Server had already been cached. The extra .2ms can be thought of as the overhead of translating the SQL and putting together the response.