SQL Server Data Warehouse Database Performance: L1 L2 and L3 caches

I recently upgraded the OS on my laptop to windows 8 and there are things that I like and things that I don’t like about it. I think that most of the things that don’t like have more to do with some of the new navigation features and should become second nature over time.

One of my favorite surprises in win8 is the new Task Manager. When I open it up, I am presented with a much cleaner interface that also provides more information than the previous versions of Task Manager. One these new goodies is now you can see the sizes of the L1, L2, and L3 caches:

clip_image001

These CPU caches act like stepping stones for data as it travels from main memory (RAM) to the CPU and the closer the cache is to the CPU the faster the data can be processed by the CPU. For example L1 and L2 caches are orders of magnitude faster than the L3 cache and as you can tell from the above image the closer the cache is to the CPU (L1) the smaller the cache size is.

If you have an application that you want to process faster on the SQL Server 2012 CPU then a good way to do it would be to have the application divide up its instructions and data into batches that would ideally fit in the L1 or L2 caches. Then magically your application would run faster (there is more to it than just that but I am keeping things simple).

Well that is great but what do L1, L2, and L3 caches have to with SQL Server databases and data warehousing? Well in a word ALOT! The new SQL Server 2012 Columnstore Indexes not only compress and store data (tables and indexes) in a column-wise format but this columnar data is also broken down into “batches” that just so happen to match the sizes of the L1 and L2 caches on the SQL Server 2012 Data Warehouse Database Server. This means that all things being equal, the same data set will actually process faster on the SQL Server 2012 Data Warehouse Database Server CPU using Columnstore Index’s data “batches” than had the same data been processed without Columnstore Indexes.

And guess what, you can actually see this happen in SQL Server Management Studio SSMS when you run query on a tables that has a SQL Server 2012  Columnstore Index and view the Execution Plan.

Example:

clip_image003

The table dbo.CSI_100m_Fact has a SQL Server 2012 Columnstore Index and at execution time the query uses Actual Execution Mode = BATCH for the SQL Server 2012 Columnstore Index Scan operator which mean that it is doing its L1 and L2 magic behind the scenes and your queries now run much faster!

Note: If you create a columnstore index on your SQL Server 2012 database and your SQL 2012 data warehouse database query does not use the columnstore index then that means that your data model is wrong (fact and dimension tables) or your query does not match typical data warehouse workloads. See: Columnstore Indexes on MSDN.

Advertisements

3 Responses to “SQL Server Data Warehouse Database Performance: L1 L2 and L3 caches”

  1. Bill Anton Says:

    Hi Garrett,

    Quick question…is the “SQL Server 2012 Data Warehouse Database Server” referenced in your post (specifically the quote below) an appliance of some sort?

    >> “this columnar data is also broken down into “batches” that just so happen to match the sizes of the L1 and L2 caches on the SQL Server 2012 Data Warehouse Database Server”

    Or is the implication that the columnstore index batch sizing is dynamic such that it matches the L1/2 cache on whatever hardware its running on?

    • Garrett Edmondson Says:

      Good question…
      by using “SQL Server 2012 Data Warehouse Database Server” I meant any server with SQL Server 2012 installed on it. Although the fastTrack Appliances are awesome!!! more on that later…
      No configuration needed to get the right batch size to fit into the L2 cache, SQL Server 2012 does that all for you automatically. Conor Cunningham went into a lot detail about it here Inside SQLServer ColumnStore . He built it so I think that he knows what he is talking about 😉

      • Bill Anton Says:

        well that’s freaking amazing! I had *assumed* the batch-mode designation (in the execution plan details) was simply indicating that the “work” was being pushed down into the compressed chunks of data…sparing the resources from having to first decompress the data before doing the “work”.

        will definitely watch the video – thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: