Archive

Archive for October, 2011

BISQL # 66 – SQL Server Denali Feature # 4 – Columnstore Indexes –Explanation Columnstore Indexes for Fast Data Warehouse Query

October 30, 2011 Leave a comment

Hi folks,

Continuing from last post in which I have already explained 3 feature of SQL Server Denali

    In this post we are going to cover

  • Introduction and features for Columnstore index
  • How Columnstore works
  • Benefits Columnstore index
  • How to use Columnstore Indexes
  • Introduction and features for Columnstore index

  1. The SQL Server 11.0 release which is known as “Denali” have introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore.
  2. This new index, combined with enhanced query optimization and execution features,improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speed up for a broad range of queries fitting the scenario for which it was designed
  3. It does all this within the familiar T-SQL query language, and the programming and system management environment of SQL Server
  4. It’s thus fully compatible with all reporting solutions that run as clients of SQL Server, including SQL Server Reporting Services.
  5. Portions of columns are moved in and out of memory on demand.
  6. SQL Server Denali columnstore indexes are “pure” column stores, not a hybrid, because they store all
  7. SQL Server columnstore indexes don’t have to  fit in main memory, but they can effectively use as much memory as is available on the server.
  8. The columnstore index in SQL Server employs Microsoft’s patented Vertipaq™ technology, which it
    shares with SQL Server Analysis Services and PowerPivot.
  9. Data for separate columns on separate pages. This improves I/O scan performance and buffer hit rates.

How Columnstore works ? 

A columnstore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored. We use the term “row store” to describe either a heap or a B-tree that contains multiple rows per page.  The difference between column store and row store approaches is illustrated following figure :

image

Benefits Columnstore  index 

  1. Only the columns needed to solve a query are fetched from disk (this is often fewer than 15% of the columns in a typical fact table),
  2. It’s easier to compress the data due to the redundancy of data within a column, and
  3. Buffer hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, while infrequently used parts are paged out.
    How to use Columnstore Indexes
    We have to give columnstore index on the fact tables in a data warehouse. Also

    If we have extremely large dimensions then also we may wish to build a columnstore index on those dimensions also .After that, you simply submit queries to SQL Server, and they can run much, much faster .

    Syntax for creating Columnstore Indexes

     CREATE COLUMNSTORE INDEX cstore on [dbo].[<Table Name>]
                                         (<Column Name>) 
    

So as we can see the columnstore index and associated query processing capabilities in SQL Server Denali are

breakthrough technologies that give unheard-of performance benefits for data warehouse query  processing

Hope this explanation is useful for you !!

Thanks for visiting my blog !!

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource Website

Categories: Query, SQL
Follow

Get every new post delivered to your Inbox.

Join 1,115 other followers

%d bloggers like this: