Todd Robinson

I’ve uploaded my slide deck on AppFabric Cache and SQL Server 2008 as well as my demo-code that I used in my session.

Slide Deck

Demo Code

I had a really great time at SQLRally and hope to be a part of more to come. I’ve got my eyes on SQLRally Nordic, or as I’d like to call the Cold Edition. I’m guessing that shorts and t-shirts would not be appropriate attire during the month of November in Sweden.

I will be presenting my talk on Windows AppFabric Cache and SQL Server 2008 on Friday, May 13th at SQLRally.This will be a 200-level session where I will go over some of the basics of caching technologies (NoSQL), who uses them, and why. I will then go into describing Windows AppFabric Cache and how its used to help with performance in concert with SQL Server. I will have a few basic demo’s that show how easy it is to integrate into an existing applications as well as its speed. My goal is to have you after the session thinking about ways to integrate this into your environment to solve some painful issues.

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!

I have been working with Windows Server AppFabric Cache here for the past six months getting an appreciation for what it can do to augment existing applications which use SQL Server as its relational back end. I have been working with Rama Ramani, the Senior Program Manager of the AppFabric team, whom I met at the 2009 PASS conference. The past few months I’ve been testing various scenarios in our environment and gathering a bunch of statistics that compare using AppFabric Cache versus using SQL Server to store the same key-value pair information. The session, SQLCAT: Optimizing application workload & performance by leveraging AppFabric Cache along with SQL Server, will be presented by Rama and I will get a chance to show some of the findings to everyone in the session. I’m excited about this opportunity and look forward to showing folks what AppFabric can do for you.

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.

Thanks to the folks at Quest and Brent Ozar ( @BrentO ), posts on SQL Server development will now be shared with the world over on SQLServerPedia’s site. I think its important for me to start working on my professional development, and one of the best ways of doing that is sharing openly with the community at large. I’ve been involved with the SQL community in the background for years, working with PASS as a volunteer on the Program Committee, helping select the sessions that everyone gets to enjoy. I’ve met a lot of great people at PASS and I’ve been lucky to call a few of them my friends. Over the past few years I’ve witnessed a few folks in the community work just as hard on their personal development as they have on their technical skills and I can only say I’m amazed at the opportunities it has afforded them.

I only recently started blogging to the masses, mostly because Tom ( @SQLRockstar ) and Brent made me. Just kidding, but in a way its true. They have really taken the time and effort to bring their knowledge and special brand of humor out to the world, and in doing so, have elevated their status in the SQL community. Now its time for me to bring my efforts to the foreground. I hope that you’ll find my future posts informative and helpful and one day I too hope to be a Rockstar.

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.