SQL SERVER – Fundamentals of Columnstore Index

There are two kind of storage in database. Row Store and Column Store. Row store does exactly as the name suggests – stores rows of data on a page – and column store stores all the data in a column on the same page. These columns are much easier to search – instead of a query searching all the data in an entire row whether the data is relevant or not, column store queries need only to search much lesser number of the columns. This means major increases in search speed and hard drive use. Additionally, the column store indexes are heavily compressed, which translates to even greater memory and faster searches. I am sure this looks very exciting and it does not mean that you convert every single index from row store to columnstore index. One has to understand the proper places where to use row store or column store indexes. Let us understand in this article what is the difference in Columnstore type of index.

Column store indexes are run by Microsoft’s VertiPaq technology. However, all you really need to know is that this method of storing data is columns on a single page is much faster and more efficient. Creating a column store index is very easy, and you don’t have to learn a new syntax to create them. You just need to specify the keyword “COLUMNSTORE” and enter the data as you normally would. Keep in mind that once you add a column store to a table, though, you cannot delete, insert or update the data – it is READ ONLY. However, since column store will be mainly used for data warehousing, this should not be a big problem. You can always use partitioning to avoid rebuilding the index.

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. The difference between column store and row store approaches is illustrated below:

SQL SERVER - Fundamentals of Columnstore Index columnstore

In case of the row store indexes multiple pages will contain multiple rows of the columns spanning across multiple pages. In case of column store indexes multiple pages will contain multiple single columns. This will lead only the columns needed to solve a query will be fetched from disk. Additionally there is good chance that there will be redundant data in a single column which will further help to compress the data, this will have positive effect on buffer hit rate as most of the data will be in memory and due to same it will not need to be retrieved.

Let us see small example of how columnstore index improves the performance of the query on a large table.

As a first step let us create databaseset which is large enough to show performance impact of columnstore index. The time taken to create sample database may vary on different computer based on the resources.

USE AdventureWorks
GO
-- Create New Table
CREATE TABLE [dbo].[MySalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
-- Create clustered index
CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail]
( [SalesOrderDetailID])
GO
-- Create Sample Data Table
-- WARNING: This Query may run upto 2-10 minutes based on your systems resources
INSERT INTO [dbo].[MySalesOrderDetail]
SELECT S1.*
FROM Sales.SalesOrderDetail S1
GO 100

Now let us do a quick performance test. I have kept STATISTICS IO ON for measuring how much IO following queries take. In my test first I will run query which will use regular index. We will note the IO usage of the query. After that we will create columnstore index and will measure the IO of the same.

-- Performance Test
-- Comparing Regular Index with ColumnStore Index
USE AdventureWorks
GO
SET STATISTICS IO ON
GO
-- Select Table with regular Index
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail]
GROUP BY ProductID
ORDER BY ProductID
GO
-- Table 'MySalesOrderDetail'. Scan count 1, logical reads 342261, physical reads 0, read-ahead reads 0.
-- Create ColumnStore Index
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore]
ON [MySalesOrderDetail]
(UnitPrice, OrderQty, ProductID)
GO
-- Select Table with Columnstore Index
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail]
GROUP BY ProductID
ORDER BY ProductID
GO

SQL SERVER - Fundamentals of Columnstore Index columnstore2

It is very clear from the results that query is performance extremely fast after creating ColumnStore Index. The amount of the pages it has to read to run query is drastically reduced as the column which are needed in the query are stored in the same page and query does not have to go through every single page to read those columns.

SQL SERVER - Fundamentals of Columnstore Index columnstore1

If we enable execution plan and compare we can see that column store index performance way better than regular index in this case.

Let us clean up the database.

-- Cleanup
DROP INDEX [IX_MySalesOrderDetail_ColumnStore] ON [dbo].[MySalesOrderDetail]
GO
TRUNCATE TABLE dbo.MySalesOrderDetail
GO
DROP TABLE dbo.MySalesOrderDetail
GO

In future posts we will see cases where Columnstore index is not an appropriate solution as well few other tricks and tips of the columnstore index.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – TRACEWRITE – Wait Type – Wait Related to Buffer and Resolution
Next Post
SQL SERVER – How to Ignore Columnstore Index Usage in Query

Related Posts

38 Comments. Leave new

  • you are the MAN

    Reply
  • Why not just create a normal index?

    CREATE NONCLUSTERED INDEX [IX_MySalesOrderDetail_ColumnStore]
    ON [MySalesOrderDetail]
    (ProductID) INCLUDE (UnitPrice, OrderQty)

    What are the benefits of an columnstore index over an simple index (in this example)?

    Reply
  • Nice article, I didn’t knew this before.

    Reply
  • Chirag Satasiya
    October 31, 2011 10:44 am

    Hi pinal sir,
    Thank you for such a useful article.

    From this article it is very much clear that CloumnStore index are better solution for non clustered index. So can we avoid using non clustered and start using column store index?

    Thank$
    Chirag Satasiya

    Reply
  • In which version of SQL Server?
    I have never read before in the books about columnstore index?
    It is in Denali?

    Please advice, Virul

    Reply
  • Virul,
    This is new feature in SQL 2012.

    Reply
  • vishal patwardhan (@vishalpatwardha)
    November 1, 2011 10:37 am

    Awesome Experiment Thanks

    Reply
  • Is This the New Feature in Sql 2012

    Reply
  • Can i know if there s a view created on the base table which has a column store index, why does the execution plan not reflect “Batch” mode. When i query the table directly the execution mode is “Batch”. through the view it is “Row”.

    Reply
  • sir i have a problem that i created a index but when i select this index for display records then it shown that invalid object name .i ckecked all things as database name but i was understanding what is the reason.please give me the solution of this problem

    Reply
  • remember this is read only

    Reply
  • Vinay Kumar Chella
    April 2, 2012 10:20 am

    Does order of the columns in columnstore index matters to performance?

    Reply
  • How is the data stored on the page in column store index.Is it physically stored or logically stored because Column Store index doesnt support Clustered index.

    Reply
  • nice artical :)

    Reply
  • Pinal, tried your script during the SQL Server Launch Demo and its works perfectly. The simplicity of the script helps everyone to connect to it. Thanks a lot.

    Reply
  • Nice Article Sir….thank you so much..
    I am using column store index on a table having large number of records.
    I just fired select query to fetch millions of record on two different tables on having normal index and another having column store index.Both query takes almost same time. When i see the execution plan of both the query nearly both are same…i am doing something wrong??

    Reply
  • Neeraj Kumar
    June 11, 2012 1:15 pm

    Sir i am following the same process as explained by u above on a table having 200000 records.i am running the following query

    Set statistics IO On
    Select PortfolioID,StrategyID,SignalID,SecID,NoTrds=count(*),SUM(PL) from
    (
    SELECT [TradeID]
    ,[PortfolioID]
    ,[StrategyID]
    ,[SignalID]
    ,[SecID]
    ,PL=case when LongStatus=1 then (EntryPrice-ExitPrice)* EntryQty else (EntryPrice-ExitPrice)* EntryQty*-1 END

    FROM [PST_BANK]
    ) As A group by PortfolioID,StrategyID,SignalID,SecID

    It gives the following message

    Table ‘PST_BANK’. Scan count 18, logical reads 22580, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Now i create the columnstore index as follows:

    create nonclustered columnstore index[Neeaj_Index]
    ON [pst_Bank] (portfolioID,strategyId,SignalID,SecID)

    Set statistics IO On
    Select PortfolioID,StrategyID,SignalID,SecID,NoTrds=count(*),SUM(PL) from
    (
    SELECT [TradeID]
    ,[PortfolioID]
    ,[StrategyID]
    ,[SignalID]
    ,[SecID]
    ,PL=case when LongStatus=1 then (EntryPrice-ExitPrice)* EntryQty else (EntryPrice-ExitPrice)* EntryQty*-1 END

    FROM [PST_BANK] with (INDEX (Neeaj_Index))
    ) As A group by PortfolioID,StrategyID,SignalID,SecID

    it gives following message

    Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘PST_BANK’. Scan count 6, logical reads 4461739, physical reads 0, read-ahead reads 1515, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    The number of logical read increases….why??

    Reply
  • Hi,
    I found some weird problem today with column store index table.

    Not able to truncate data from a table with column store index even after disabling the index. We can delete the data but not truncate. If you ever aware of this, can you please suggest any work around on it other than dropping and recreating column-store index.

    Reply
  • Hi Dave,

    Absolute gem of an article and a very significant feature for SQL Server in the Big Data world! Thanks

    Reply
  • nrevanthRevanth
    October 22, 2012 10:18 pm

    Thanks Pinal,

    Reply

Leave a Reply

Menu