SQL Server 2012 - Column Store Index

Question

Post reply

SQL Server 2012 - Column Store Index

isuckatsql

SSChampion

Points: 10381
More actions
March 10, 2012 at 5:37 am

#265689

Has anyone tried this?
Is it really as fast as they say ?
Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

In testing several people have. Yes, they can be.

Look up Joseph Sack's blog post on them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

Isnt it more suited for OLAP than OLTP applications cause I think it is more of a read only index.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

I wouldn't say 'more of read-only', it's completely read only, the index and the table that it's on. I can see uses in OLTP systems (especially historical data). It's being touted as a BI feature

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

isuckatsql SSChampion Points: 10381 More actions · Answer 4

isuckatsql

SSChampion

Points: 10381

March 10, 2012 at 12:45 pm

#1457520

Thanks

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

GilaMonster (3/10/2012)
In testing several people have. Yes, they can be.
Look up Joseph Sack's blog post on them.

interesting article from the blog on performance

http://www.sqlskills.com/blogs/joe/post/Comparing-Query-Performance-when-using-an-e2809cideale2809d-Nonclustered-Index-versus-Columnstore-Index.aspx

Thanks Gail

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

isuckatsql SSChampion Points: 10381 More actions · Answer 6

I noticed that the Column Store index is quite selective regarding data types and included fields.

Does anyone know if running the Actual Execution Plan in SQL Server 2012 is 'smart' enough to suggest a Column Store index, if needed, over a regular non-clustered index?

Thanks

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

I certainly hope not, it's not a replacement for 'regular' nonclustered indexes. I can just imagine the chaos if the missing indexes did suggest columnstore indexes and people added them without checking.

It's very doubtful. As it is, the missing indexes doesn't suggest clustered indexes, spatial or XML indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Confusing Queries Ten Centuries Points: 1137 More actions · Answer 8

When we say it is read only what happens if I insert fresh rows into the table having columnstore index? And provided hint to use columnstore index, would it use it in conjunction with key look up or it won't let us use it at all?

Can we implement slowly changing dimension on the tables having columnstore index?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Star Trek (3/12/2012)
When we say it is read only what happens if I insert fresh rows into the table having columnstore index?

The insert will fail. Any table with a columnstore index is read only.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 10

Star Trek (3/12/2012)
Can we implement slowly changing dimension on the tables having columnstore index?

Only by dropping the columnstore index as part of your batch process to update the data warehouse, then re-creating it at the end of the ETL.

You can also partition columnstore indexed tables, insert a new partition into a staging table, build a columnstore index onto this, then switch it into the partitioned table.

Columnstore indexes are definitely an advanced feature, they shouldn't just be added without understanding what they do in detail and how you can fit their limitations around your processes

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

isuckatsql (3/10/2012)
Has anyone tried this?
Is it really as fast as they say ?
Thanks

If you use them within their (currently narrow) allowed parameters, they are STUNNINGLY efficient. As someone else said, don't think you can just slap them out there and you will be good to go. And you won't get to the point of knowing what/how/when/why on a forum.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sauri.jss169 Newbie Points: 3 More actions · Answer 12

White paper on SQL Server 2012 Column Store Index

http://www.a2zmenu.com/Blogs/SQL/SQL-Server-2012-Column-Store-Index.aspx

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

I have to ask... since you have to drop and recreate the ColumnStore Index to update the underlying tables, does anyone have any specs on how long it takes to rebuild a ColumnStore Index? I also think a comparison against an Indexed View might be appropriate.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

Jeff Moden (4/22/2012)
I have to ask... since you have to drop and recreate the ColumnStore Index to update the underlying tables, does anyone have any specs on how long it takes to rebuild a ColumnStore Index? I also think a comparison against an Indexed View might be appropriate.

That question is too nebulous to answer Jeff, at least the time-to-build one. It takes a table (or possibly NC index) scan to gather all data, then lots of computations and some writes to build out the structures. Both are incredibly dependent on many things, such as IO speed, RAM, CPU power, data distributions, total number of rows, etc.

I would be interested to hear some details of your idea to compare IV to CSI...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service