October 2014

Volume 29 Number 10

SQL Server : Use Updatable Tables for Responsive Real-Time Reporting

Don Mackenzie | October 2014

Business systems often include challenging reporting requirements. A business user needs access to historic transactions and current activity. He also needs to view data in many ways. A user will make specific requests such as:

• Dollars by month by customer for the last year
• Units by week by product for the last six months
• Units and dollars by order and product for the last 10 days (including today and right now)

Satisfying these varied requests can be a challenge for system designers, especially for businesses with a high transaction volume. Cox Digital Solutions (CDS) is a good example. The company processes about 20,000 transactions per second.

My system design for CDS supports up-to-the-hour reporting with a year’s history in one SQL Server database table with updates every 10 minutes. The solution blends two SQL Server features—partitioning and columnstore indexing—to achieve this response time when querying such a massive amount of data.

First Impressions

CDS provides Internet advertising services. “Free” television shows are supported by advertising and “free” Web sites are supported by the ads displayed on those pages. CDS helps Web site publishers display ads properly. The company records information about every display of every ad (called an impression). It also records other information such as clicks on ads. This generates nearly 2 billion records (1.5TB compressed) per day.

Each impression, or transaction, involves numerous components. There are two customers—the advertiser and the Web site publisher. It also involves the product, which is the ad. Advertisers place orders, so there’s also an order id as part of a record. There’s a price collected from the advertiser and a price paid to the publisher. The transaction record contains many fields that reflect numerous small details about the transaction.

More than a year’s transactions are archived so a user can compare the present month to a year ago, or Thanksgiving (a big advertising day) this year to last year. CDS generates reports to bill advertisers and pay publishers. These reports also help the staff monitor advertising-order delivery, they help advertisers monitor the effectiveness of their ads, and the publishers can track activity and revenue.

The business users often can’t wait for each new day’s data. They’ll make adjustments and updates as ads are delivered throughout the day. This led me to the design I’ll describe here, which you can adapt for many applications where a continuous stream of new data is added to an already large dataset.

Data Overload

You wouldn’t want to put 500 billion rows (and 500TB decompressed) in a SQL Server table. The raw transactions are kept in a Hadoop Distributed File System (HDFS). Hadoop is a good tool for storing and analyzing massive amounts of data, but has terrible response time for queries (learn more at hadoop.apache.org).

The queries against this data take from five minutes to 20 hours, depending, of course, on the amount of data, the time window and the complexity of the query. Making queries directly against HDFS is too slow for the Web application users.

Most users’ needs can be met with aggregated data. Transactions are summarized by hour (and by advertiser, Web site, ad and other key attributes) and placed in a SQL Server database table. The hours are important because advertising activity is quite different at noon than at 2 a.m. This process summarizes 70 million transactions to 50,000 rows per hour.

These rows are placed in a SQL Server table called Revenue. An interactive Web application lets the user query this table to check on the performance of an order, see how an ad performs on different sites, review budgets and so on. This table is updated and an hour’s data (50,000 rows) is replaced every 10 minutes.

Partitioning a SQL Server Table

SQL Server 2005 introduced a good level support for partitioned tables, which has continued with subsequent versions. Partitioning breaks a large table into several (or many) smaller internal tables. SQL Server stores and indexes each small table, or partition, separately (see Figure A).

Figure A Partitioned Table in SQL Server

A Partition Function (essentially a list of boundary values that separate partitions) tells SQL Server how to separate the table into partitions. There’s often a datetime column (as in my Revenue table) that’s useful for separating partitions. The table has monthly partitions for data more than a month old, daily partitions for newer data more than a week old, and hourly partitions for very recent data. All these small partitions have a single table name, a single schema and appear to be one large table to the application.

When SQL Server processes a query with a WHERE clause including the date column, it determines which partitions are needed and ignores the rest. All of the queries include the date in the WHERE clause. This improves query performance by restricting the query to one or a few smaller data stores.

Organizing a Large Database Table

That’s still a massive table, having 500 million rows and consuming 60GB of storage. An ordinary table that large would be slow to update and slow to query. Adding indexes can help, but they further slow updates. As tables become larger, it creates multiples layers of indexes (index depth), which reduces their helpfulness.

Multidimensional OLAP Cubes are one approach to querying large amounts of data, but they require lots of design and planning before deployment. And once deployed, you can’t update them. OLAP Cubes are typically rebuilt daily, or even less frequently.

My approach to managing this massive table combines two techniques: partitioning and columnstore indexing. A significant limitation of columnstore indexed tables in SQL Server 2012 is that you can’t update them while the index is active. You must disable the index during update and completely rebuild it after the update. That’s a cumbersome process when dealing with a large table. I’ll describe a technique for overcoming this limitation. These restrictions have been relaxed in SQL Server 2014 (see “Partitioning a SQL Server Table”).

The various partition sizes—month and day and hour—match the common query patterns of the business users. They also match the common update and maintenance patterns. Your application may need different partition sizes and values other than “date” to be most useful for partitioning. In other applications, it might be better to keep just hourly or daily partitions. SQL Server 2012 supports up to 15,000 partitions per table, allowing hourly partitions for 20 months. Refer to the SQL Server documentation at bit.ly/1mtZkfl for more authoritative and in-depth explanation of partitioning.

Columnstore Indexes

The most exciting new feature SQL Server 2012 introduced is columnstore indexes. They’re a powerful tool for improving query performance. For massive tables like mine, I’ve seen a 100x performance increase.

The serious issue in SQL Server 2012 is that a columnstore indexed table, like an OLAP Cube, is read-only. You can’t update it without dropping (or at least disabling) the index and rebuilding it after the update. After verifying the performance improvements, I looked for a way to overcome the read-only limitation and found partitioning was the answer.

While I think of partitioning as slicing a table into horizontal slices (based on time in my case), I think of columnstore indexes as slicing a table into vertical slices by column. Like all Nonclustered indexes, the index is stored separately from the main table data. Each columnstore indexed column is stored individually in its own internal blob container. This is quite different from a composite index with multiple indexed or included columns together.

Because the base table is read-only, the data in the columnstore blobs don’t need to handle updates and they can be compressed using one of several algorithms chosen by SQL Server to match the data. So the index is often substantially smaller than the original data (see Figure 1).

Figure 1 Use Columnstore Indexes to Manage Large Data Stores

My columnstore index includes every column in the table. Each column is stored separately. A query that references only a few columns will only read those columns and ignores the rest. Queries don’t need to read the rows in the base table. All the data values are in the index and the data for each column in the index is compressed to minimize disk reads.

Remember my table is also partitioned, so each partition and each column in the partition has its own storage (see Figure 2). SQL Server is smart enough to only read the data it needs (partitions and columns) for a specific query with impressive query-response time improvement compared to scanning a “normal” table row by row, or even searching through normal indexes for needed data.

Figure 2 Using Partitions and Columnstore Indexes to Parse Data

An important positive side effect of all this is there’s no performance penalty for denormalizing the table. The former implementation suffered because queries against the precursor to the Revenue table often included JOINs to other tables to get secondary attributes. At that time, I wanted to keep the table rows skinny and avoid reading unnecessary data from disk every time the large table was queried.

Sometimes the JOINs caused SQL Server to do a full table scan for a query, especially when the secondary attribute was in the WHERE clause. Now, many of those secondary (and some tertiary) attributes are stored in the much wider Revenue table knowing that with columnstore indexing, they won’t be accessed unless they’re needed by that specific query. The Revenue table I’ve designed has 25 columns. Refer to the SQL Server documentation at bit.ly/1zbsju1 for more authoritative and in-depth understanding of columnstore indexes.

Updating

The combination of partitioning and columnstore indexing has broken the massive table into manageable modest-sized segments. However, because of the columnstore index, updates are forbidden in SQL Server 2012. Even though each partition is stored separately, they’re all treated as one table, so disabling, dropping or rebuilding the index involves the whole table. Rebuilding the columnstore index can take a half hour and the table isn’t available for queries during that time. The table needs to be updated with new data every 10 minutes and be available all the time.

The key to solving this problem is a partition-related statement: ALTER TABLE ... SWITCH PARTITION ... This DDL statement moves a partition of data from one table to another. It doesn’t copy the data, but just rearranges internal schema information so the partition storage that used to belong to one table now belongs to a different table. There are some rules governing this process, but they’re manageable. Read the TechNet Library article, “Transferring Data Efficiently by Using Partition Switching” (bit.ly/1ts04Xv), for a thorough description of partition switching.

Let’s revisit the original problem. There’s a torrent of data (20,000 transactions/second) from the ad serving machines going to Hadoop 24x7. I can use Hadoop to summarize the current hour’s data to about 50,000 SQL Server rows for that one hour. That Hadoop query takes about 5 minutes. I can’t update my main Revenue table, but I can put those rows into a new table (partitioned and columnstore indexed) I call RevenueIn. It has the same schema as the Revenue table, but it’s empty and its columnstore index is disabled, so I can insert rows.

After inserting the hour’s rows, I can rebuild the columnstore index on the RevenueIn table. The hour’s data fits precisely into one partition. I’m only indexing 50,000 rows, so that takes less than a minute. Now I can use SWITCH PARTITION to move that one partition, already indexed, into the main Revenue table and it’s instantly available for report queries. Note that the SWITCH PARTITION statement only works if the destination partition is empty. To solve that problem, I use a third table called RevenueOut, which is empty.

Existing data from the Revenue partition is SWITCHed to RevenueOut (leaving the Revenue partition empty) and then the RevenueIn partition (with the new data and its index) is SWITCHed into Revenue. The two SWITCH statements take less than 5 ms in my environment. The old data in RevenueOut is then truncated (see Figure 3). This is a variation on the sliding window partition problem described at bit.ly/1wgPVkR.

Figure 3 Refreshing a Partition’s Data

Details, Details

There are always details. The application repeats this process six times or more per hour (as fast as the Hadoop query can run). Early in the hour, there are only a few minutes of data and each update during the hour replaces that with fresh data. Shortly after the top of the hour, there’s a full hour of data in the huge Revenue table and it can begin on the next hour. The user has the luxury of fresh, current data and more than a year’s history at his fingertips in one table that’s easy and fast to query.

Developers also win with a single database table. The previous solution used one table for “current” data and another for historic data. C# code had to decide which tables to use and combine the results of database queries if a query used both tables. The single table and minimal use of JOINs (because of the denormalized columns enabled without penalty by columnstore indexing) simplifies the database queries for reporting.

Another detail is merging. I keep older data in monthly partitions. This simplifies maintenance as I can drop a month that’s beyond the requirements. Fewer partitions also simplify the SQL Server internal logic to select partitions for queries, as most queries for old data include whole months. The columnstore index also gains some efficiency with larger partitions due to its compression algo­rithms. I use a technique similar to inserting data for rolling up small partitions into large partitions without rebuilding the data.

There is some SQL Server plumbing involved. An accompanying online article at msdn.microsoft.com/magazine/dn800596 provides step-by-step instructions and sample T-SQL for all the necessary SQL Server database objects. Check out that article and copy the code from there.

Don Mackenzie is director of software architecture at Cox Digital Solutions, the Internet advertising arm of Cox Media Group and Cox Enterprises. He enjoys applying new technologies to business applications. Reach him at don@coxds.com.

Thanks to the following Microsoft technical expert for reviewing this article: Remus Rusanu