Todd Robinson

Agenda today:
DeWitt keynote
Query Tuning Mastery: Zen and the Art of Workspace Memory (AD-500-S)
Scaling SQL Server (DBA-302-HD)
Enterprise SQL I/O Optimization Best Practices (DBA-413-A)
SQL Server Memory Management (DBA-310)

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.

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.

Surfin’ the intertubez yesterday I ran across a NoSQL article that Brent Ozar (@BrentO) wrote last month which goes in much greater depth about NoSQL, and its relation to database administrators. I suggest that anyone looking for a brief primer on the subject, head on over.