Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
ptheriault

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1507
Members Online:
3
Guests Online:
3

Total Post History
Posts:
80058
Topics:
18308

7-Day Post History
New Posts:
18
New Topics:
1
Active Topics:
3

Our newest member
joegollakner

Other

FAQ
All times are UTC [ DST ]

Google Ads

SQL Server Denali Column Store indexes

Microsoft SQL Server
Please wait...

SQL Server Denali Column Store indexes

Postby SQLDenis on Wed Nov 10, 2010 2:07 pm

They showed at PASS yesterday a scan against a 800 Billion table and it took only 19 seconds

You can download a PDF describing the columnar storage here: http://download.microsoft.com/download/ ... r%2011.pdf
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21636
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: SQL Server Denali Column Store indexes

Postby ramireddyindia on Wed Nov 10, 2010 2:40 pm

Denis,

In that they mentioned its for datawarehouses only... Can we use for our normal OLTP applications also???
ramireddyindia
Sage
Sage
LTD Bronze - Rating: 146LTD Bronze - Rating: 146LTD Bronze - Rating: 146
 
Posts: 267
Joined: Tue Jul 14, 2009 1:45 pm
Unrated

Re: SQL Server Denali Column Store indexes

Postby SQLDenis on Wed Nov 10, 2010 2:47 pm

ramireddyindia wrote:Denis,

In that they mentioned its for datawarehouses only... Can we use for our normal OLTP applications also???



it is read only so no OLTP..remember storing data in a columnar table is more expensive than a row table, getting it out is much faster
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21636
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQL Server Denali Column Store indexes

Postby ramireddyindia on Wed Nov 10, 2010 3:00 pm

Thanks Denis,

We are having an database, in which daily morning we will be importing 200000 records into single table. Can i use column store here if i use Denali? Do i need to setup any extra effort to use this column store like creating any fact tables etc etc???
ramireddyindia
Sage
Sage
LTD Bronze - Rating: 146LTD Bronze - Rating: 146LTD Bronze - Rating: 146
 
Posts: 267
Joined: Tue Jul 14, 2009 1:45 pm
Unrated

Re: SQL Server Denali Column Store indexes

Postby Thirster42 on Wed Nov 10, 2010 3:05 pm

SQLDenis wrote:They showed at PASS yesterday a scan against a 800 Billion table and it took only 19 seconds

You can download a PDF describing the columnar storage here: http://download.microsoft.com/download/ ... r%2011.pdf


i about pooed myself when i read that...
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4639
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: SQL Server Denali Column Store indexes

Postby SQLDenis on Wed Nov 10, 2010 3:10 pm

ramireddyindia wrote:Thanks Denis,

We are having an database, in which daily morning we will be importing 200000 records into single table. Can i use column store here if i use Denali? Do i need to setup any extra effort to use this column store like creating any fact tables etc etc???


The column storage is not available in CTP1, it will be part of CTP2 probably
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21636
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQL Server Denali Column Store indexes

Postby ramireddyindia on Wed Nov 10, 2010 3:20 pm

ok...Thanks Denis...

Another question Denis :)

How you are playing with CTP from last 2 weeks, if it is announced only on monday?? Being a MVP, you will get additional privileges?
ramireddyindia
Sage
Sage
LTD Bronze - Rating: 146LTD Bronze - Rating: 146LTD Bronze - Rating: 146
 
Posts: 267
Joined: Tue Jul 14, 2009 1:45 pm
Unrated

Re: SQL Server Denali Column Store indexes

Postby Thirster42 on Wed Nov 10, 2010 3:27 pm

ramireddyindia wrote:ok...Thanks Denis...

Another question Denis :)

How you are playing with CTP from last 2 weeks, if it is announced only on monday?? Being a MVP, you will get additional privileges?


Denis is privy to a lot of info that he can't tell us. He gets all the shiny things early.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4639
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: SQL Server Denali Column Store indexes

Postby SQLDenis on Wed Nov 10, 2010 3:39 pm

ramireddyindia wrote:Being a MVP, you will get additional privileges?


Yes
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21636
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQL Server Denali Column Store indexes

Postby SQLDenis on Wed Nov 10, 2010 4:01 pm

Think of it this way.... you have 10 years of data in monthly partitions..all you have to do is upload the latest data and then just switch in a partition


See page 7 of the pdf
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21636
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: SQL Server Denali Column Store indexes

Postby Thirster42 on Wed Nov 10, 2010 4:14 pm

riverguy wrote:
SQLDenis wrote:
ramireddyindia wrote:Denis,

In that they mentioned its for datawarehouses only... Can we use for our normal OLTP applications also???



it is read only so no OLTP..remember storing data in a columnar table is more expensive than a row table, getting it out is much faster


If it is read-only, that implies that the indexes would need to be dropped and then rebuilt after a load of data? So in effect, this is not unlike processing a measure group after a data load in SSAS. And I would assume the equivalent to SSAS partitions would be to simply partition the SQL tables? If this is the case, it sounds like it could save some work if someone did not want to go down the SSAS route. However, the UDM and MDX are valuable enough in their own right to stick with SSAS for many.



To move data into a columnstore table you can switch in a partition, or disable the columnstore index, update the table, and rebuild the index.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4639
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated