Non-Clustered Column Store Index in SQL Server 2012 by Srikanth Manda

63
13118
row store

Hi Friends,

I would like to add a pretty good article on “Non-Clustered Column Store Index in SQL Server 2012” from my friend “Srikanth Manda”. Hope you will enjoy this.

Everyone agree the fact that hardware speed and capacity has increased past two or three decades, but disk I/O (Input/Output) or disk access or data transfer rate has not grown up to the expected level and is still the slow. One key point to remember, as time is moving forward the size of the database become larger and larger. Data present now would increase by almost 10 times in next 2 to 3 years from no. We have provide a technology in SQL Server which can be addressed this kind of data growth with Data Warehouses. Secondly, the size becomes bigger the query performance is also very critical. Customers would like to have a response like a inter active, they want to have large amount of data, they want to process the data and get the results in the query like attractive fashion. Thirdly, that we are seeing is Data Warehouse has become more like a commodity and provide Data Warehouse technology to masses. Finally, the amount of data in data warehouse (DWH) is growing tremendously day by day. When you want to retrieve (Query) data from Data Warehouse, it takes quite huge amount of time. This would degrade the performance of the Data Warehouse. All these issues can be addressed by Non-Clustered Column Store Index.
In the Article, We will learn about this new feature, how can we build this, how it is in SQL Server, how exactly the data is stored, what happens underneath the engine, how this improves performance of Data Warehousing Queries.
In any traditional relational DBMS, the data is stored as rows (B-Tree format). Like, Microsoft SQL Server stores rows in a page of size of 8 K. If you have a row of 10 columns, you store Row 1 , Row 2 and when page becomes full the page 8 K, then Row goes to second page and so on. This is how the data is stored, successfully formats and successfully for OLTP Workloads. For example consider the image below the data for ten columns for each row gets stored together contiguously on the same page and once the data is full and the row goes to second page.

What has changed is, instead of storing data in the row format other way to look out is can I store data in the Column Store format. For example, I have a table with C1 to C10 columns, instead of storing as rows will store as columns. Then we have storage as Column C1, C2… C10. When we store data in the column store format, we get very good compression. The reason is data from same column is of same type and domain, so it compresses very well. For example, A company is operating globally throughout the world. All the employees from India, there mention the Country as India. Similar, employee from US would mention as ‘US’ as Country. Here, Column with Country would be compressed because it is a repetitive pattern. This kind of opportunity is available in Column Store Format rather than Row Store Format.
In the Row Store Format, data stored for all ten columns C1, C2, C3, …., C10. If we want to retrieve only columns like C1,C3,C5. What happens in the Row Store Format is we need read/fetch data for the entire row of 10 columns then predicate is applied for the specified columns. But, in case of Column Store Format, we can fetch only the required columns i.e.; Columns C1,C2,C3 etc. In this case, it reduces I/O and data fits in memory with which you get much improved performance. You can improve how the query is processed using Column Store technology that gives much better response time.
If we create Non-Clustered Column Store Index, the data is stored in column format.

If we store data in column format, suppose we store 10 million rows, we cannot store all 10 million rows of column C1 as storage unit. What we do is we break those rows into smaller chunks, which we call have as row group.

We have grouped the rows of 1 million; call it as Row Group Chunk. In each Row Group which has 10 columns here and each column is stored in its segment. It would be 10 segments. The benefit of storing each column in segment, when I want to rows of columns C1, C2, then I just get segment for column C1, segment for column C2.

Note: Blue color box are nothing but segments.
Important Points to remember:
1) Row group
• set of rows (typically 1 million)
2) Column Segment
• Contains values from one column from row group
3) Segments are individually compressed
4) Each segment stored separately as LOB’s as Binary Format
5) Segment is unit of transfer between disk and memory

New Batch Processing Mode
1) Some of the more expensive operators(Hash Match for joins and aggregations) utilize a new execution mode called Batch Mode
2) Batch mode takes advantage of advanced hardware architectures, processor cache and RAM improves parallelism
3) Packets of about 1000 rows are passed between operators, with column data represented as a vector
4) Reduces CPU usage by factor of 10(sometimes up to a factor of 40)
5) Much faster than row-mode processing
6) Other execution plan operators that use batch processing mode are bitmap filter, filter, compute scalar
7) Include all columns in a ColumnStore Index

Batch Mode restrictions:
1) Queries using OUTER Join directly against ColumnStore data, NOT IN (Sub query), UNION ALL won’t leverage batch mode, will revert to row processing mode
Examples:
1) In this Demo, Creating two tables i.e.; one with regular index and other with Non-Clustered ColumnStore Index. Below is the scrip to create two tables
Table with Regular Index
CREATE TABLE [dbo].[FactInternetSalesWithRegularIndex](
[DummyIdentity] [int] IDENTITY(1,1) NOT NULL,
[ProductKey] [int] NOT NULL,
[OrderDateKey] [int] NOT NULL,
[OrderQuantity] [smallint] NULL,
[SalesAmount] [money] NULL
CONSTRAINT [PK_FactInternetSalesWithRegularIndex_ProductKey_OrderDateKey]
PRIMARY KEY CLUSTERED
(
[DummyIdentity] ASC,
[ProductKey] ASC
)) ON [PRIMARY]

Table with Non-Clustered ColumnStore Index

CREATE TABLE [dbo].[FactInternetSalesWithColumnStoreIDX](
[DummyIdentity] [int] IDENTITY(1,1) NOT NULL,
[ProductKey] [int] NOT NULL,
[OrderDateKey] [int] NOT NULL,
[OrderQuantity] [smallint] NULL,
[SalesAmount] [money] NULL
CONSTRAINT [PK_FactInternetSalesWithColumnStoreIDX_ProductKey_OrderDateKey]
PRIMARY KEY CLUSTERED
(
[DummyIdentity] ASC,
[ProductKey] ASC
)) ON [PRIMARY]

GO

2) Insert data into both tables. Here is the insert script
Insert Script for FactInternetSalesWithRegularIndex Table
INSERT INTO FactInternetSalesWithRegularIndex
(
ProductKey, OrderDateKey,
OrderQuantity,SalesAmount
)
SELECT
ProductKey,OrderDateKey,
OrderQuantity,SalesAmount
FROM [AdventureWorksDW2012].dbo.[FactInternetSales]

GO 50

Insert Script for FactInternetSalesWithColumnStoreIDX Table
INSERT INTO FactInternetSalesWithColumnStoreIDX
(
ProductKey, OrderDateKey,
OrderQuantity,SalesAmount
)
SELECT
ProductKey,OrderDateKey,
OrderQuantity,SalesAmount
FROM [AdventureWorksDW2012].dbo.[FactInternetSales]

GO 50

3) And finally I want to create a regular non-cluster index (on ProductKey and Salesamount columns) on the first table, and column store index on the second table, which will include ProductKey and Salesamount columns.

CREATE NONCLUSTERED INDEX [NC_FactInternetSalesWithRegularIndex_ProductKey_Salesamount]
ON FactInternetSalesWithRegularIndex
(ProductKey,Salesamount)
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [CS_FactInternetSalesWithColumnStoreIDX_ProductKey_Salesamount]
ON FactInternetSalesWithColumnStoreIDX
(ProductKey,Salesamount)

GO

4) Execution of Queries

When I ran the query with STATISTICS IO ON, I found stunning results (with significant performance) of using column store index vs regular index, as you can see below:

SET STATISTICS IO ON

Select ProductKey,sum(Salesamount)
from FactInternetSalesWithRegularIndex
GROUP BY ProductKey
ORDER BY ProductKey

Select ProductKey,sum(Salesamount)
from FactInternetSalesWithColumnStoreIDX
GROUP BY ProductKey
ORDER BY ProductKey

SET STATISTICS IO OFF

Result:

(158 row(s) affected)
Table ‘FactInternetSalesWithRegularIndex’. Scan count 5, logical reads 4339, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1342 ms, elapsed time = 504 ms.

(158 row(s) affected)
Table ‘FactInternetSalesWithColumnStoreIDX’. Scan count 4, logical reads 34, 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.

SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 27 ms.

Even the time required to run these two queries greatly varied, the queries with regular index took 1342 ms for CPU cycle and 504 ms as elapsed time vs just 47 ms for CPU cycle and 27 ms as elapsed time for the second query, which uses column store index

The relative cost of the second query (which uses column store index) is just 11% as opposed to the relative cost of first query (which uses regular index) which is 89%.

For column store index exclusively, SQL Server 2012 introduces a new execution mode called Batch Mode, which processes batches of rows (as opposed to the row by row processing in case of regular index) that is optimized for multicore CPUs and increased memory throughput of modern hardware architecture. It also introduced a new operator for column store index processing as shown below:

Restrictions:
1) Cannot be clustered
2) Cannot act as PK or FK
3) Does not include sparse columns
4) Can’t be used with tables that are part of Change Data Capture or FileStream data
5) Cannot be used with certain data types, such as binary, text/image, row version /timestamp, CLR data types (hierarchyID/spatial), nor with data types Created with Max keyword eg: varchar(max)
6) Cannot be modified with an Alter – must be dropped and recreated
7) Can’t participate in replication
8) It’s a read-only index
a. Cannot insert rows and expect column store index be maintained

What’s New in SQL Server 2014
Columnstore index has been designed to substantially increase performance of data warehouse queries, which require aggregation and filtering of large amounts of data or joining multiple tables (primarily performs bulk loads and read-only queries).
There were several limitations in SQL Server 2012, SQL Server 2014 overcomes them:
1) We can create only one non-clustered column store index which can include all or few columns of table in a single index on a table.
2) SQL Server 2014 has come up with an enhancement of creating Clustered Column Store Index.
3) SQL Server 2012, when we create a Non Clustered Column Store index then it makes table read only.
4) With SQL Server 2014, you can create a Clustered Column Store Index without any impact on the insertion on table. You can issue some INSERT, UPDATE, DELETE statements with a table with clustered column store index. No more workaround is required for writing data to a table with Non Clustered Column Store Index like drop the existing one and re-create the index.

Hope you enjoyed the post ..
Thanks,
Srikanth Manda

63 COMMENTS

  1. Best Editing Service

    The amount of written content has been growing exponentially recently, and it’s no wonder. For students, writing is an inseparable part of college and university assignments; for businesses, online publishing is a way to reach customers. The need for…

  2. [url=https://arendaavtobusa2121.ru/]аренда автобуса[/url]

    Транспортная компания «Перевозка24» предлагает аренду автобуса с водителем в Москве. В нашем автопарке – современные комфортабельные автобусы различной вместимости. Мы гарантируем быструю и безопасную перевозку пассажиров, внимательное отношение к клиентам, первоклассный транспорт.

    аренда автобуса

  3. По результатом исследований после 6 недель систематического использования сыворотки Revitalash для ресниц, 98% испытуемых отмечали укрепление, улучшение внешнего вида и здоровья своих ресниц.  Кроме высокоактивных растительных веществ, которые оказывают мощный ухаживающий и стимулирующий эффект, сыворотка также содержит глицерин, гиалуронат натрия, органический агент антибактериального действия. Эта сыворотка для ресниц от корейского бренда Etude House обогащена витаминами, пантенолом и экстрактами ягод кизила для заметно более полной линии ресниц. Она поставляется в самой симпатичной трубке с аппликатором, который достигает самых маленьких ресниц и наносит продукт, не вызывая беспорядка. В своей точке низкой цены, эта сыворотка определенно стоит денег, чтобы достичь ресниц вашей мечты. В состав сыворотки для ресниц Esthetic House Shocking Lash Eyelash Ampoule входят следующие компоненты: https://wiki-nest.win/index.php?title=Масло_для_быстрого_роста_ресниц Мы собрали для вас восемь лучших карандашей для глаз, которые вас точно не разочаруют. Выбирайте! Гелевая подводка марки Вobbi Brown выпускается в четырнадцати оттенках, от классических черного, коричневого и графитового до цветных фиолетового, зеленого и кобальтового. В палитре есть как матовые, так и шиммерные тона для любых вариантов макияжа. Эдуардо Феррейра, визажист международного класса и арт-директор компании, для профессионального мейка рекомендует именно эту подводку. Приметила этот карандаш еще осенью и сразу вписала его в бест года. Шикарно растушевывается, мягкий и стойкий одновременно, и да, в нем можно нырять в море (если вам вдруг так повезет), он выдержит. ФОТОДОСТАВКАОТЗЫВЫ Стойкость — средняя: если случайно задеть пальцем в течение дня — слегка смажется, к вечеру может поплыть. Но на каждый день, как мне кажется, большего и не надо. 2012-2022 © OTZIV-OTZIV.RU — независимый сайт-агрегатор отзывов и альтернатив товаров на основе нейросети. 

  4. Lista Ruletek CSGO, Jackpotów oraz stron do otwierania skrzynek wraz z darmowymi punktami. Sprawdź listę ruletek oraz stron z otwieraniem skrzynek CS:GO i zdobądź darmowe punkty na grę, bez potrzeby wpłacania skinów lub wysyłania SMS. Kliknij w link poniżej, aby przejść do listy ruletek CS:GO w 2022. Francuscy migranci udający się do USA podjęli ten pomysł i rozsławili grę po drugiej stronie Atlantyku. Od tego czasu ruletka stała się podstawą świata kasyn i często cieszy się nią tysiące graczy w różnych wersjach kasyn online.  Ten twój kolega to za pewnie się poczuł, poszedł grać na zbyt wysokie stawki i zatonął. A co do totolotka to najmniej opłacalna forma hazardu, to taj jakbyś płacił 3zł za szansę na wygranie 1zł. Znajdziesz tu ponad 500 godzin odcinków na każdy temat. https://vopvn.com/hoithanh/community/profile/kathrynfosbery/ Tłumaczenia znawców tematu, wśród których najgłośniej słychać było głos słynnego piosenkarza Michała Wiśniewskiego, że poker nie jest dyscypliną, w której liczy się tylko szczęście, a zatem nie można go traktować jako gry czysto hazardowej, niewiele pomogły. Nowelizacja ustawy hazardowej w 2016 roku praktycznie zablokowała dostęp do wielu platform, na których można było zagrać w pokera i uniemożliwiły dostęp do legalnego pokera w sieci. Amatorzy tej szlachetnej rozgrywki muszą zatem szukać innych możliwości, a legalny poker online w Polsce oznacza zupełnie coś innego niż moglibyśmy się spodziewać. Czy można pokonać pokera królewskiego? Poker królewski to zimne jak kamień orzechy, co oznacza, że nigdy nie można go pokonać. W grach Hold’em i Omaha niemożliwe jest, aby dwóch graczy jednocześnie ułożyło pokera królewskiego. Dlatego ręka nigdy nie może posiekać garnka.

  5. I’m writing on this topic these days, slotsite, but I have stopped writing because there is no reference material. Then I accidentally found your article. I can refer to a variety of materials, so I think the work I was preparing will work! Thank you for your efforts.

  6. Ana arrived at Medical News Today armed with two bachelor’s degrees, one master’s degree, and a strong belief in “combinatorial play” — the idea that we need to feed our brains with things as diverse as physics and poetry to come up with anything creative. Before joining the team, Ana worked as a freelance writer, bringing philosophy to a wider audience and examining its intersections with technology and popular culture. In her role as an Editor, she helps nurture freelance writers and improve the authority and visibility of the Medical News Today brand. Pastime activities include experimenting with different hair colors, dancing until 5 a.m., or binge-watching RuPaul’s Drag Race. Yes, psilocybin is illegal and its possession, use, and sale carry heavy prison sentences and fines and disciplinary consequences at UCSC. See UCSC’s Student Conduct Responsible Action site for more information. https://www.thenaturalnouveau.com/forum/profile/rustyfeez394601/ In another study, at Johns Hopkins University in Baltimore, a mushroom trip was shown to ease the anxiety of people with life-threatening cancer, leading to dramatic improvements in mood and wellbeing. I also found an article in the Lancet that concluded that magic mushrooms are the least harmful “recreational” drug you can take. Mushrooms are not addictive and you can’t kill yourself by overdosing on them, as long as you take them in a safe environment. It is a mystery to me why they share class A illegal status with crack cocaine and heroin. Magic mushrooms are one of the easiest things to grow in the world – they need a few specific parameters and a bit of patience. The kits that we sell come with a perlite and vermiculite substrate which has had mycelium added to it, which is where the shrooms come from. It’s super easy to activate and begin growing your shrooms; keep reading for a full guide on how to do it properly.

  7. [url=https://buycialis40cost.quest/]cheap generic cialis from india[/url] [url=https://aclomid.com/]clomid online order[/url] [url=https://tetracycline.email/]tetracyclinbe[/url] [url=https://orderdiflucan.online/]diflucan in usa[/url] [url=https://apropecia.com/]buy propecia singapore[/url] [url=https://buyviagra50mgtablets.monster/]over the counter generic sildenafil[/url]

  8. Input the 888poker promo code WELCOME100 if you decide that you most like pitting your skills against your peers at the card tables. Or enter in the 888casino promo code Welcome1 if trying to scoop up the house’s cash in games of chance is more your speed. 888poker is one of the most established online poker rooms. The company behind 888 started the online casino business in 1997 and began running the 888poker site in 2002. Just by creating a new account, 888 Poker will award you with up to $88 absolutely free – without ever making a deposit! Simply follow the directions below to receive your free 888 poker no deposit bonus: Best of all, 888 Poker offers one of the most generous welcome bonuses in the industry. First, you’ll get $20 free just for registering a new account — no deposit required. And if you choose to make a real money deposit, 888 will match that amount 100%, up to $1,500. https://mcsdogtraining.net/community/profile/terribobbitt082/ In an age that is seemingly obsessed with style and in-your-face aesthetics, from the busy layout featured on MELbet Casino to the retro stylings of 7BitCasino, it’s nice to see a casino that bucks the trend somewhat, sticking with its simplistic old-school roots. Bitcasino also has a lot to offer to all kinds of punters. Should players grow tired of betting on the same disciplines, they can always wager some more on the operator’s varied suite of more than 2,800 games coming from the best suppliers in the industry. Another benefit for choosing Bitcasino is the operator’s Loyalty Club. Despite not having welcome offers to new customers, the crypto operator offers a loyalty program that benefits users that make it their platform of choice. Additionally, esports bettors who pick Bitcoin can expect some of the fastest withdrawal times in the industry as a whole.

  9. Effects: This is more powerful than some other common mushrooms, with a psilocybin rate of .85. For some reason, this mushroom is known to have a very varied effect, and even mushrooms from within the same patch can have very different impacts. The most common are strange colors and a heightened sense of fun, but some bottlecaps can create terrifying visions. Again, much of this depends upon the individual, but perhaps more than any other species in the genus, there’s a wide variety of strength. Also like cannabis and its varying strains, consumers are finding out there isn’t just one type of magic mushroom. In reality, there are over 180 species of mushrooms that contain the medically beneficial psilocybin. For example, some mushrooms are being used as coffee but the actual amount of mushroom present in the sachet is incredibly small. There was one company that was putting in 10 g of instant coffee, and 0.1 g of chaga extract and calling that chaga coffee. https://www.greyhoundsolutions.com/community/profile/alvarogarland25/ He said it’s a classic case of FOMO — the fear of missing out. And he warns that unless the US legalizes marijuana for recreational use, the current prices of most pot stocks are now way too high. Marijuana companies aren’t unlike other types of companies. When you evaluate their stock, be sure to research their management teams, competitive position, financial statements, and growth strategies. Pay careful attention to their all-in cost of sales per gram (their total per-gram cost of producing cannabis) and cash cost per gram (their total per-gram cost of producing cannabis). See our exclusive index of companies on the move: Through owned and partnered cannabis production platforms, as well as education to help customers safely, effectively and responsibly use cannabis, Canopy has created a dominant, global business with the potential to generate a significant and sustained return on shareholder capital over the long-term.

  10. бесплатная порнушка муж https://ebushka.top/ порно онлайн жену с другом [url=https://ebushka.top/]Порно онлайн[/url] порно видео онлайн в хорошем качестве

    [img]https://ebushka.top/picture/Grubo-poimel-rot-i-pizdenku–smazlivoi-blondinki-i-konchil-v-razdolbannuiu-shchelku.jpg[/img]

    [url=https://fahlgrenanna.blogg.se/2013/october/entry.html]порно онлайн без анала[/url]
    [url=http://xnonqu75bcvap11j.ctfda.com/viewthread.php?tid=7341972&extra=]волосатые пизды бесплатно без смс[/url]
    [url=https://befriend.london/2021/03/12/how-much-for-a-cuppa-and-a-crossword/#comment-773]бесплатная порнушка толстушек[/url]
    [url=http://www.vancosty.com.br/as-melhores-carnes-para-churrasco/#comment-364512]бесплатная порнушка женщин[/url]
    [url=http://daiduongxanhcorp.com/cach-ve-sinh-ghe-sofa-da/#comment-30175]пизда без трахните[/url]
    [url=http://nvbruiqaz.is-programmer.com/guestbook/]пизда без трусов на улице[/url]
    [url=https://asrx.com/blogs/news/66608771-pinkredefined?comment=128820314209#comments]смотреть порно онлайн бесплатно без регистрации[/url]
    [url=https://astanatextile.kz/blog/1-e-mesto-zoloto#comment_192282]русская порнушка взрослых[/url]
    [url=https://www.jacquelinestearoom.com/cara-dewilink-memberi-yang-terbaik-untuk-anda/#comment-7186]голые бабы без пизды[/url]
    [url=http://borispol.biz.ua/news/brazilskiy-manikyurv#comment-6793]бесплатное порно онлайн русская сестра[/url]
    c8bfb0d

  11. Der Fokus wird bereits auf der Firmenhomepage deutlich. Das Thema Asien zieht sich wirklich durchs komplette Portfolio. Dass trotzdem keine Langeweile aufkommt, das dürfen wir an der Stelle vorwegnehmen. Jeder Spielautomat, den Red Tiger Gaming entwickelt, ist einmalig. So haben es die Betreiber überhaupt geschafft, sich hierzulande einen Namen zu machen. Es gibt zwei Möglichkeiten, unsere mobile Version zu nutzen. Einmal können Sie dazu einfach unsere Website über den Browser Ihres Mobilgerätes besuchen. Dies geht sehr einfach und sorgt dafür, dass unsere mobile Seite auf allen Geräten und auf allen Betriebssystemen zuverlässig und flüssig läuft. Wenn wir auf einigen sogenannten schwarzen Listen ein bestimmtes Casino entdecken, so informieren und warnen wir unsere Besucher sofort. Anschließend analysieren wir die betreffende Sachlage und gegebenenfalls setzen wir auch unsere Reputationsbewertung dementsprechend hinunter. https://wiki-fusion.win/index.php?title=Live_casino_gratis Für die Profis unter euch ist sicher auch die Casino Software ein Kriterium bei der Auswahl des richtigen Spielautomaten Casinos. Hersteller der Spiele, wie Merkur, Bally Wulff, Novoline, Microgaming, NetEnt und Play’n Go, führen zum Teil über Jahrzehnte beliebte Portfolios von Online Spielen der Spielautomaten. Einige Spieler sind bereits treue Fans der Software Entwickler und freuen sich stets auf einen neuen Slot in deren Repertoire. Der Spielautomat verfügt über 5 Walzen. Im Basisspiel ist das Spielfeld diamantenförmig angeordnet, da 12 Positionen von Skarabäus Blockern eingenommen sind. Einen Gewinn erhalten Sie immer, wenn drei oder mehr identische Symbole nebeneinander einlaufen. Mit einer Gewinnkombination erhalten Sie bei Valley of the Gods zusätzlich einen kostenlosen Respin und die Blocker werden nach und nach zerstört. Insgesamt kann Ihnen der Slot dadurch bis zu 3.125 Gewinnwege bieten. Die vier am besten bezahlten Symbole werden durch ägyptische Götter dargestellt.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

2 + 7 =