How Vertical Partitioning and Deep Joins Kill Parallelism

Query plans with deep joins trees are often the result of high levels of normalisation in the data model. There are large advantages to normalising data as it minimizes the amount of data that must be written when a change happens. In traditional OLTP systems, this can be a boon.

However, normalisation is not without its costs – especially not in read intensive workloads like data warehouses.

How do Column Stores Work?

In this blog, I will provide you with some basic information about column stores. Nothing I am writing here is vendor specific IP, but merely taken from the papers published throughout history. One of the best papers that serves as an introduction is by Stonebraker:

• Stonebraker et a, Proceedings of 31st VLDB Conference, 2005: C-Store: “C-Store: A Column-oriented DBMS”

The idea is older than that though, with the first papers published in the 1970’ies.

Shamefully standing on the shoulders of giants, I will walk you through a simple example which illustrate one of the key principles of column stores: Run Length Encoding (RLE).

The Effect of CPU Caches and Memory Access Patterns

In this blog, I will provide you with some “Grade of the Steel” background information that will help you understand CPU caches and their effects better.

As we move towards a future where power consumption in the server room begins to make a big dent in the balance sheet – it becomes important for programmers to fully understand hardware and make the best possible use of it, instead of floating around in the leaky abstractions of the virtual systems. Even when we take the old enemy of I/O out of the equation, there are other bottlenecks we need to worry about: DRAM access time being one of the most important.

Correction of old Post: Lock Pages in Memory

James Rowland Jones kindly pointed out that my old post is in need of an update. The Lock Pages in Memory Privilege is now available in SQL Server 2008 as per this post:

SQL Server, Locked Pages, and Standard SKU

Thanks James.