Tag Archives: Columnstore Index

ByHariharan Rajendran

Secrets behind Query processing Speed in Columnstore Index

Go through my previous article here, about Data storage format in columnstore Index.

There are lots of improvements in Query processing speed when we use columnstore  Index.

  1. The data are organized in a column. As a result, a much higher level compression is possible, compare than row store.
  2. It uses Vertipaq technology for data compression. The Compression rate can be achieved up to 15 to 1. Click here, to know about Vertipaq technology.
  3. Compressed data require only less IO because the data transferred between disk to memory are reduced.
  4. Reducing IO leads to improvement in query processing and response times.
  5. Amount of data transfer to memory is going to be less which leads less space is required in memory to hold the data which processed by the query.
  6. SQL Server fetches only the data which are required for the query. For example, data is maintained in a column from C1 to C10, if the required data residing only on C6 & C7 then it will fetch only those columns.
  7. It uses the advanced technology for processing the queries. The data in the columns are processed in batches using high-efficient vector technology. This processing mode is called “Batch-Mode Processing”.
ByHariharan Rajendran

Purpose of Columnstore Index in SQL Server 2012

Nowadays, data being generated and captured across many devices, applications and services. Now every organization is tasked with storing massive amounts of data to accelerate their business growth success.

Using traditional tools to capture and process the data is very challenging and it is not going to be one time activity because all data users want to see continuous growth.

We have below issues while doing this task.

  1. The Volume of data is high, so the ability of data warehouses to execute the queries within a time is not achievable.
  2. More time is required to Tune the queries.
  3. Designing and maintaining multiple indexes.

In many cases, so much of time elapsed between the time the query start executed and time to render the result.

To overcome the above issues, Microsoft SQL Server team worked on columnstore Index, which allow very large data set to be read quickly.

The performance of the columnstore index is far better than normal index. This has been introduced in Microsoft SQL Server 2012.

1