BISQL # 66 – SQL Server Denali Feature # 4 – Columnstore Indexes –Explanation Columnstore Indexes for Fast Data Warehouse Query
Hi folks,
Continuing from last post in which I have already explained 3 feature of SQL Server Denali
- BISQL # 49 : SQL Server Denali Feature # 1–FileTables #1 – Introduction & Theory
- BISQL # 50 : SQL Server Denali Feature # 1- FileTables # 2 – Demo and Scripting
- BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number Explanation !!
- BISQL # 53 : SQL Server Denali Feature # 3 – AlwaysOn (Everything)
- Introduction and features for Columnstore index
- How Columnstore works
- Benefits Columnstore index
- How to use Columnstore Indexes
In this post we are going to cover
Introduction and features for Columnstore index
- 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.
- 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
- It does all this within the familiar T-SQL query language, and the programming and system management environment of SQL Server
- It’s thus fully compatible with all reporting solutions that run as clients of SQL Server, including SQL Server Reporting Services.
- Portions of columns are moved in and out of memory on demand.
- SQL Server Denali columnstore indexes are “pure” column stores, not a hybrid, because they store all
- 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.
- The columnstore index in SQL Server employs Microsoft’s patented Vertipaq™ technology, which it
shares with SQL Server Analysis Services and PowerPivot. - 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 :
Benefits Columnstore index
- 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),
- It’s easier to compress the data due to the redundancy of data within a column, and
- 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
SQL Server Mentalist 