March 10, 2012 at 5:37 am
Has anyone tried this?
Is it really as fast as they say ?
Thanks
March 10, 2012 at 10:21 am
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
March 10, 2012 at 10:36 am
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
March 10, 2012 at 12:06 pm
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
March 10, 2012 at 12:45 pm
Thanks
March 10, 2012 at 12:54 pm
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
Thanks Gail
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 12:31 pm
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
March 11, 2012 at 1:37 pm
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
March 12, 2012 at 12:32 am
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?
March 12, 2012 at 4:30 am
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
March 12, 2012 at 4:51 am
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
March 12, 2012 at 10:04 am
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
April 22, 2012 at 12:10 pm
White paper on SQL Server 2012 Column Store Index
http://www.a2zmenu.com/Blogs/SQL/SQL-Server-2012-Column-Store-Index.aspx
April 22, 2012 at 4:49 pm
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
Change is inevitable... Change for the better is not.
April 23, 2012 at 6:54 am
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply