We have been tweaking the Project Lucy datastore again this week, and have found out a couple of things we think worth sharing.  We found out the fastest way to update a lot of rows, was to do the complete opposite of what you’d think was the right thing to do.

Let me paint a scenario – we store wait stats data coming from Spotlight on SQL Server Enterprise customers in a fact table. One of the columns in the fact table is wait_type, which contains the name of the wait. This was recognized as a bad idea some time ago, but we had other fish to fry.

Recently our piscatorial problems have come back to bite us.

Since this column is an nvarchar(100) it takes up beaucoup space, and since it is indexed multiple times, multiply that by the number of indexes. This results in a performance problem, since SQL Azure has to read lots more pages because there are less rows per page. Also we have an operational problem – not only are we are using more space that we want to (and space costs money), but any DDL operations on the table are expensive because of its size.

So since we have already gone back to the nineties by eschewing the NoSQL-ness of Azure Table Services and adopting SQL Azure in all its relational glory, we thought; why stop there? Why not travel back into the eighties and start doing a bit of data modelling?

Hence, we find ourselves creating a dim_wait_types dimension table and instead of storing the wait type directly in the fact table, we store the id of the related row from the dimension table.

So far so good.

This database stuff is easy! We just create the dimension table and populate it, create a wait_type_id column in the fact table and write an update statement that updates the wait_type_id based on the value of the wait_type column. The update looks something like this:

update sqlserver_fact_waits_by_time set wait_type_id = 620, wait_category_id = 2 where wait_type = ''XE_DISPATCHER_JOIN''

The table has close to 40 million rows in it, so a single update is out of the question. SQL Azure will throttle us based on log space long before the update completes. We have to break it down, so we decide to do it per wait type, since there are 635 SQL Server wait types (that we know about).

We start running 635 update statements….

• We find each statement does a full table scan, so we get hold of the plan and create the appropriate index
• We find we cannot create the index from on-prem – the CREATE INDEX statement takes too long and the socket between us the the datacenter keeps getting closed. The solution is to remote desktop to one of the roles in the running application, install SQL Server 2008R2 Express Tools and run the CREATE INDEX from there. Don”t forget to use the WITH (ONLINE=ON) option, otherwise SQL Azure will throttle you because you use too much log space
• Now that the index has been created, running the updates can commence. Chaos ensues. Because each update is doing a bunch of physical IO, all of our queued processing (end-users upload data around the clock) starts failing with timeouts.
• We turn queue processing off, and allow the updates to continue. Each one averages 3-5 minutes and there are 635 of them which is roughly 60hrs in total.
• Realization dawns that we cannot leave processing turned off for 3 days, so we stop the updates and turn queue processing back on. We need to find a way to trade time for concurrency. In other words, we don”t care if the updates take 3 days, but we need to be able to process incoming data whilst the updates are going on

A Solution

We went back to the original query plan for the update and found that 90% of the cost was in actually doing the update, very little cost was extant in actually finding the rows. Also and most significantly, we found that the amount of physical IO required scaled somewhat worse than linearly with the number of rows updated. The answer was to keep the actual number of rows updated small. So now our update looks something like this:

update
sqlserver_fact_waits_by_time
set
wait_type_id = 620, wait_category_id = 2
from
sqlserver_fact_waits_by_time wbt
inner join
(select top(2500) upload_id, id from sqlserver_fact_waits_by_time
where wait_type_id is null and wait_type = ''XE_DISPATCHER_JOIN'') wbt_top
on wbt.upload_id = wbt_top.upload_id and wbt.id = wbt_top.id

This new update statement takes under 20 seconds to run and we have a command line application run these for all wait types in a loop until there are no rows updated. Whilst these updates are running, user processing continues normally.

What we Learned

• Execute long running DDL operations from within the data centre
• Try to look for ways that allow you to trade time for maintaining concurrency
• On SQL Azure, doing a larger number of small updates (where each update touches less rows) took less elapsed time than doing a smaller number of large updates (where each update touches more rows)

We’ve been very busy here at Project Lucy aver the last few weeks.  We are “cup-runneth-over” with ideas on how to provide really cool and valuable stuff to our users – only problem was, we were getting bogged down in performance and data storage issues.

The way we were storing our data was not conducive to providing the sorts of features we wanted to provide and something had to change.  After we re-architected our approach 4 times, with still no suitable solution, we decided our only two alternatives were between doing something stupid, or going broke.

We decided that we should store our analysed data in SQL Azure.  Now we have done this, we will be able to move much faster in providing new features that expose even more interesting things in the data that our users upload.

How we used to do it

It is useful to look at how we did things up until a week ago in order to explain the changes so let’s look at an example – SQL Workload Analysis.

When we process a trace file (this applies to Spotlight on SQL Server uploads that include SQL Analysis data as well), we have a piece of code that detects a SQL event; we will use SQL:BatchCompleted as an example.  Way back when we first went live we did some on the fly aggregation of these events but didn’t really store any event data because we had no way to display it.  Fast forward to the end of Feb 2011 and we went live with the ability to explore the workload data within your upload using a grid and some ajax magic that allowed you to do multi-level grouping on the data in real-time.

In order to allow you to do grouping in real-time, we couldn’t store aggregates any more – we had to store every event.  We could have stored each event as an entity in Table Services if we were prepared to live with the latency of fetching these entities.  But we weren’t.  You see, some of Project Lucy’s users upload trace files with hundreds of thousands of SQL events and pulling 200,000 entities out of table services is two orders of magnitude slower than a wet week.  Another solution was required.

We found that when we stripped all of the text data (SQL, application name, login name, database name) out of the event data, it really wasn’t that big – certainly small enough to fit in memory.  So that is what we did; as we were processing the data we put all of the text columns in look up tables and stored the even data in a big list.  What we ended up with was a binary structure that had one big list for the event data, and numerous smaller hash tables for the category (SQL, application, database etc) data.  This we then streamed out into a blob.

When a user came to the workload page and started interacting with it, we would check to see if we had the blob that contained the data we wanted to render for the user on the web server.  If the file did not exist we would go get it from blob storage and cache it locally.  Once the blob was local to the web server, we would load it into memory and perform the query user had requested using Linq. This worked surprisingly well and the response times even from blob files that contained many hundreds of thousands of events were acceptable.

The downside of this approach was that the data was stored  one blob per upload.  This meant that a few things were out of reach.  Because a user can upload files from multiple servers in an upload, we had no easy way of getting data from just one server.

Also, we had no easy way of querying across time.  We wanted to give users the ability to look at performance characteristics over time.  In order to do this with our blob approach we would have had to load each blob in turn, get the rows we needed from it (after reading all the rows in it) and then merge those rows at the end.  So we decided to invent our own database.

No seriously. We did.  We spent some hours basking in warm delusions of our own grandeur, scribbling madly on a whiteboard.

We then came to our senses and decided that inventing our own database was somewhat foolish, and might distract us from our core objective of providing value to our users.  So we decided to use SQL Azure to store our data.   Now instead of a binary file, we store the event data in a big fact table and we store all the “category” (application, database etc) data in dimension tables.  This now means that we can start to build features that allow querying across time – which is what we are working on right now.

What we learnt

As part of our migration to SQL Azure we learnt a few things:

• The database size limit is there to encourage you to think scale out, instead of scale up.  This is a good thing – with scale out, you are not constrained by being on one machine. You need to design your scale out strategy from the start.  We plan to use SQL Azure Federations when it ships, so our schema is optimised for this
• For tables that you are going to federate, you need to make sure there are no database scoped functions used.  This means no identity columns.  We used uniqueidentifier with a newid() default constraint
• Make sure you are religious about putting clustered indexes on tables.  Operations against tables without clustered indexes will work in the development environment with a local database, but when you push into the cloud it will break
• Using table services for bulk inserts or updates involves high latency. You can only insert/modify 100 entities (rows) at a time and this takes 500ms – designs which involve big fetches of many rows from Table Services will be unsatisfactory
• If you are going to tempt fate and use Table Services, in order to fetch data from table services quickly enough (it takes 500ms per 1000 entities fetched) you have to be very careful about the partition and key values used when storing the data. (The partition+key must be unique for each entity; and you select multiple rows by partition.) The trick is to define your partition key so you can fetch multiple rows at a time, but not too many rows. Invariably this mean the same data will need to be stored multiple times, via different partition values. For example, for SQL Server wait statistics, you might need to store data partitioned by time (to service a trend over time chart) and store the same data partitioned by wait-name so you can compare different users with each other.  As a consequence of storing the data multiple times, you end up writing a lot more (serialization-deserialization) code than you really feel you should need to
• Tables in Table Services store entities. An entity is an object with arbitrary properties.  Just because you can store different entities in the same table doesn’t mean you should. We got clever with C# generics and got this to work and then decided it was just a rod for our own backs
• SQL Azure is expensive (we hope this changes) make sure you are indexing just enough.  You can double your storage requirements by just creating an index – make sure that the index is useful
• High concurrency MERGE SQL statements that update multiple rows were problematic for us in SQL Azure. We had deadlocks, no matter what you try. The only way we were able to stop the deadlock was with a WITH TABLOCKX locking hint – not really a solution.  Until Microsoft provide a way to get the deadlock graph from SQL Azure so you can really troubleshoot the problem rather than guess, we will be left with a cloying sense of dissatisfaction
• We’ve seen really impressive IO rates from our SQL Azure database (7,500 writes per second) – when you consider that this involves replication to two slaves, color us extra impressed
• Keep the persistence layer of your software simple. i.e. Where feasible, perform simple SQL select statements from multiple tables and then use something akin to LINQ in C# to merge and project that data. This will make life easier in future – when you decide to locate those tables within different Azure databases, or use federations. We encountered this issue. Our database grew to over 43Gb in size (50Gb is the limit, as at August 2011). We had to move one of our tables into a different database. Consequently, any SQL statements joining this table to other tables had to be achieved via LINQ instead. (There are no cross-database joins in SQL Azure)
• Long running SQL Statements like CREATE INDEX have to be run from within the data centre.  This will drive you nuts.  The internet will conspire against you to ensure that the socket will get closed while you wait for the query to finish

In general, our experience with SQL Azure has been a positive one, and now having taken the leap, we look forward to building out the new features we have planned in Project Lucy.