SQL Server, columnstore indexes: A bit of theory and some examples (when to use columnstore indexes and when not)

Hi guys,
Luca Biondi for sqlserverperformace.blogspot.com

 
Today we speak about columnstore index. 
I wrote a summary, a list of things to know about and some examples in order to learn how to use (or not) this types of indexes.

Are you Ready? Yes! 

 

What is a columstore index

Yes but what is a columnstore index?
 
Microsoft introduced, in SQL Server, Columnstore indexes that are the standard for storing and querying large tables, let's think to tables with million of records.
 
The difference is that data is stored inside page in a different way.
  • In a row-based index data is stored in one or more data pages.
  • In a column-based index data is stored in a separate pages for each column of the table.
Rowstore vs. ColumnStore storage
A columnstore is data that's logically organized as a table with rows and columns, and physically stored in a column-wise data format.
 
A rowstore is data that's logically organized as a table with rows and columns, and physically stored in a row-wise data format. 

Just as there are clustered and non-clustered indexes, we can have clustered and non-clustered columnstore indexes.

No other details in this post just let's see their history for a moment ..
 

The evolution of the columstore indexes

Columnstore indexes are not a new entry infact them will turn 10 years old next year. They were introduced with SQL Server 2012 and so SQL Server 2008 indeed have no clusterindexes at all.
 
With SQL Server 2008 you can only create "classics" rowstore indexes, one clustered Index (CI) and n nonclustered indexes (NCI)
 
As sayd before SQL Server 2012 was the first release that supports a Columnstore, albeit in a very limited way:  you can only create a read-only non clustered Columnstore Index (NCCI).

An NCCI can be created on most data types with some limitations: e.g. the data type DECIMAL is supported only up to a precision of 18 digits.
 
Starting from SQL Server 2014 microsoft introduced a writeable Clustered Columnstore Index (CCI). The CCI must be the only index on a table, so you can't add additional CI or NCI.
 
Finally SQL Server 2016 has come.With this release all Columnstore indexes are writeable.
Furthermore you can have both Rowstore and Columnstore on the same table.

Today, even through some examples, we will see when use columnstore index.

When to use them and when not to

Before show any example must be say that Columnstore indexes are the preferred data storage format for large data warehoue and analytics workloads.
 
However thet can be added also in OLTP queries. I think for example that queries with group by and order by can benefit from columnstore indexes. 
 
Important: Clustered indexes should be used on large table (>5 million of rows) where less than 10% of rows are ever modified.  Deletes can cause fragmentation  thus reducing the index efficency. Updates are expensive because are done by a deleting and inserting technique.

So, what is a tipical OLTP workload that use a large table and a query suitable for a clustered index?
 
I think to tables that contain account movements, inventory movements or lines of invoices, delivery notes or orders. All these logical entities are commonly used in many ERP.
 
 
Example 1 - Adding a non clustered columnstore index
 
Let's do an example taking our example table ORDRIG.

This table contain a list of row of orders and it is so defined:
SELECT [id]
      ,[idordtes]
      ,[idcustomer]
      ,[idproduct]
      ,[Qty]
FROM [OrdRig]
All field are integer while Qty is float.
On the id column there is a clustered index.
The table has 1.5 million of rows

Now i write a simple query that group by idproduct and perform the sum of the column Qty:
       
SELECT IDPRODUCT,SUM(QTY)  
FROM OrdRig 
GROUP BY IDPRODUCT ORDER BY IDPRODUCT
We will analyze 3 cases:
 
1) Only the clustered index on the ID column
2) Adding a non clustered (rowstore) index on the columns IDPRODUCT , QTY
3) Adding a non clustered columnstore index on the columns IDPRODUCT , QTY

For each case we will read the execution plan and the numbers of pages read from the disk (set statistics io and statistics time to ON) 

In the first case we get this result
 
Query Result
 
The execution plan show me that the Clustered index on the ID column is used:

Execution plan


Statistics IO and Statistics TIME to ON show me that we have read 6445 pages of 8 KB Size and that we have an execution time of 620 ms.
       

Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Tempo di esecuzione SQL Server: 
 tempo di CPU = 0 ms, tempo trascorso = 0 ms.
Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 2 ms.

(2 righe interessate)
Tabella 'Worktable'. Conteggio analisi 0, letture logiche 0, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.
Tabella 'Workfile'. Conteggio analisi 0, letture logiche 0, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.
Tabella 'OrdRig'. Conteggio analisi 1, letture logiche 6445, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.

(1 riga interessata)

Tempo di esecuzione SQL Server: 
 tempo di CPU = 625 ms, tempo trascorso = 620 ms.
Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Tempo di esecuzione SQL Server: 
 tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Ora di completamento: 2021-08-14T23:49:31.2297857+02:00

For the second case let me add a non clustered index on the column IDPRODUCT , QTY.

Running the Query we obtain that the non clustered index just added is effectively used.

execution plan
Read pages are dropped, Execution time is dropped too.
       

Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Tempo di esecuzione SQL Server: 
 tempo di CPU = 0 ms, tempo trascorso = 0 ms.
Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 2 ms.

(2 righe interessate)
Tabella 'OrdRig'. Conteggio analisi 1, letture logiche 4292, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.

(1 riga interessata)

Tempo di esecuzione SQL Server: 
 tempo di CPU = 485 ms, tempo trascorso = 485 ms.
Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Tempo di esecuzione SQL Server: 
 tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Ora di completamento: 2021-08-15T00:04:01.1891322+02:00

Now the the third case i will add a non clustered columnstore index on the same columns.
 
To create a columnstore index is enough add the word "columnstore" in the index definition after the create word.

Create Columnstore Index IDX_CS_ORDRIG_IDPRODUCT_QTA ON Ordrig (Idproduct,Qty)
Running the query again we get that the columnstore index is used.

execution plan

The execution time is awesome. Only 53 ms versus 485 ms!

       

Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Tempo di esecuzione SQL Server: 
 tempo di CPU = 0 ms, tempo trascorso = 0 ms.
Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 2 ms.

(2 righe interessate)
Tabella 'OrdRig'. Conteggio analisi 2, letture logiche 0, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 1098, letture fisiche LOB 7, letture LOB server di pagine 0, letture LOB read-ahead 3529, letture read-ahead LOB server di pagine 0.
Tabella 'OrdRig'. Letture segmento 2, segmento ignorato 0.
Tabella 'Worktable'. Conteggio analisi 0, letture logiche 0, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.

(1 riga interessata)

Tempo di esecuzione SQL Server: 
 tempo di CPU = 47 ms, tempo trascorso = 53 ms.
Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Tempo di esecuzione SQL Server: 
 tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Ora di completamento: 2021-08-15T00:13:16.0179180+02:00

We consider that this is in line with what was declared by microsoft or 10 times higher performance (10x)
In this case it is true!



Example 2 - Adding a clustered columnstore index

Adding a clustered columnstore index create your data will be physically stored as a columnstore.

So for this example we will drop the rowstore index and will add a columnstore index:


CREATE CLUSTERED COLUMNSTORE INDEX IDX_CS_ORDRIG_ID ON ORDRIG

Note that if your query performs analytics only on few columns of your table then the great part of the segment can de disregarded.
For example: if your table contains 10 columns and your query performs analytics only on 1 columns, then the result will be that 90% of the segments (for other columns) can be disregarded.
 
This is the our query: 
       
SELECT MIN(QTY), MAX(QTY) FROM OrdRig
As mentioned above only the QTY column will be read.
 
The execution plan show that the cluster columnstore index is used:


execution plan

Furthermore you can see that the storage is Columnstore and that the execution mode is Batch.

execution plan

Finally we can see that the execution time is of only 62 ms.
      

Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Tempo di esecuzione SQL Server: 
 tempo di CPU = 0 ms, tempo trascorso = 0 ms.
Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 6 ms, tempo trascorso = 6 ms.

(1 riga interessata)
Tabella 'OrdRig'. Conteggio analisi 1, letture logiche 0, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 6, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.
Tabella 'OrdRig'. Letture segmento 2, segmento ignorato 0.

(1 riga interessata)

Tempo di esecuzione SQL Server: 
 tempo di CPU = 62 ms, tempo trascorso = 62 ms.
Tempo di analisi e compilazione SQL Server: 
   tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Tempo di esecuzione SQL Server: 
 tempo di CPU = 0 ms, tempo trascorso = 0 ms.

Ora di completamento: 2021-08-15T12:51:48.2934744+02:00

Conclusions

Are you wondering if you can use columnstore indexes for your query that you are developing for your OLTP ERP? Yes you can!

Are you developing statistics, analytics, reports or pivot tables for your ERP and do you need realtime results? Maybe a columnstore index could be applied.

Just you need to be in a right scenario and knows what the PROs and CONs are.

In this post i have not told you the whole columnstore indexes theory but if it is of your interest i can be more deep in a future post
 
Many ERP like SAP (the version with SQL Server not with the hana in memory database) already use columnstore indexes in their query. 
And you? What are you waiting for?

 
 
Thank you for reading my posts.
Send me your comments if you like this format or if you have questions.

Luca Dr. Biondi

Luca Biondi @ SQLServerPerformance blog 2021!






 

  

 

Previous post: SQL Server 2019 CU12 is out and other updates




 
 
 
 


Comments

Post a Comment

I Post più popolari

SQL Server, execution plan and the lazy spool (clearly explained)

Image
Hi Guys, Welcome back! In one of the last posts we talked about Table Spool . We had explained what Spool are for by telling the somewhat curious story of the halloween problem . Here we specifically talked about a type of spool called Eager Spool   You can look to the execution plan below where the eager Spool is used to avoid the halloween problem in classic query of the "salary increase".. i suggest Well, if the story of the eager spool intrigued you, today instead we will speak about another type of Spool, the Lazy Spool . The Lazy spool Let's take a small step back. So, what is a Spool operation?  As we have already understood a spool operation is simply a temporary storage of data . Data are read from a source table and stored inside a worktable in the Tempdb database . While an Eager Spool is used to prevent the Halloween problem, the Lazy Spool is instead used to store data that will later be needed again when running a query . So, when a Lazy spool is used?  ...
Read more

SQL Server, datetime vs. datetime2

Image
Hi guys, This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two. Enjoy the reading!     Datetime Vs. Datetime2 First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server. However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates. It is in fact from SQL server 2008 that it is present in datetime2 data type. This "new" type of data was born primarily to have higher accuracy . Let's in fact talk about ... precision ! Datetime Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient. Let's see it with an example. Open the SQL Management studio (SSMS) and write the fo...
Read more

La clausola NOLOCK. Approfondiamo e facciamo chiarezza!

Image
Ehi ragazzi! La domanda è di quelle importanti! Siete pronti per scoprire tutto ma proprio tutto sulla clausola NOLOCK?   Se la riposta è si, allora seguitemi! Oggi ne spiegheremo il funzionamento in maniera approfondita. Faremo una introduzione al concetto di tipi di LOCK Ed infine vedremo come mai una SELECT ne può bloccare un'altra. Non vi nascondo che il motivo di questo articolo è fare chiarezza. Su questo argomento si sentono spesso affermazioni “curiose” ma tra le tante questa è la prima: “ uso la SELECT con l’opzione WITH(LOCK) così non blocco le altre tabelle ”.   Ma, è proprio vero quanto affermato? Beh oggi facciamo il punto!   Buona lettura!     La clausola NOLOCK Diciamo subito per iniziare che cos’è la NOLOCK. NOLOCK è il nome di una clausola che è possibile specificare all’interno di uno statement di tipo SELECT utilizzando la seguente sintassi: SELECT * FROM TABELLA WITH (NOLOCK) Si noti che non possiamo ...
Read more