columnstore index issue

Question

columnstore index issue

Paresh Motiwala

SSCommitted

Points: 1772
More actions
August 20, 2014 at 9:18 am

#310350

This result should tell you:
Query Rows Time of execution
w/o compressionwith compressionwith Columnar Indexes select * from warehouse.[dbo].[Timekeeper_Matter_Time_Summary_Period] 1,102,894 69 371
select * from warehouse.[dbo].Matter_Timekeeper_History 2,796,014 78 7878
basically the query runs approximately 5.5 times longer with columnar index in one table and no improvement in the second one.
anybody have any clues as to where I should look?
Cheers
Paresh
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Viewing 10 posts - 1 through 9 (of 9 total)

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

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 1

Paresh Motiwala (8/20/2014)
This result should tell you:
Query Rows Time of execution
w/o compressionwith compressionwith Columnar Indexes select * from warehouse.[dbo].[Timekeeper_Matter_Time_Summary_Period] 1,102,894 69 371
select * from warehouse.[dbo].Matter_Timekeeper_History 2,796,014 78 7878
basically the query runs approximately 5.5 times longer with columnar index in one table and no improvement in the second one.
anybody have any clues as to where I should look?
Cheers
Paresh

Quick thought, have you compared this using aggregation? You should look into the intended use of this feature.

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 2

I am just running a bench marking test. With and without columnstore indexes....

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 3

Paresh Motiwala (8/20/2014)
I am just running a bench marking test. With and without columnstore indexes....

Columnstore index will not aid normal row retrieval, the test may therefore portray the negative impact if appropriate indexes are not in place. Curious about the purpose of the exercise.

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 4

Hi SSCommitted,

I have been reading too much good stuff about CSI. hence I have been pushing our datawarehouse be upgraded to sql 2014.

Citing this as one of the advantages.

Paresh

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Eirikur Eiriksson SSC Guru Points: 182947 More actions · Answer 5

Paresh Motiwala (8/20/2014)
Hi SSCommitted,
I have been reading too much good stuff about CSI. hence I have been pushing our datawarehouse be upgraded to sql 2014.
Citing this as one of the advantages.
Paresh

Good stuff, there are great advantages in terms of DW's but one has to make certain that the application of the technology fits both the purpose and the target. A conventional model might and most certainly will not be optimal so that would be a place to start the restructure.

Grant Fritchey SSC Guru Points: 399038 More actions · Answer 6

Paresh Motiwala (8/20/2014)
Hi SSCommitted,
I have been reading too much good stuff about CSI. hence I have been pushing our datawarehouse be upgraded to sql 2014.
Citing this as one of the advantages.
Paresh

Column Store Indexes do great things, with aggregate queries and other types of reporting queries. Simple SELECT statements and individual row queries, they don't help with. Nor were they designed for those. So, your tests are testing the one thing they're bad at. If that's your standard query pattern, then they won't help you. But, if your datawarehouse is primarily used for aggregate style queries, test the columnstore with those and you'll see a massive improvement.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 7

Thanks Grant!!!

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Kulgan_ SSC Enthusiast Points: 181 More actions · Answer 8

Hi,

In addition... when You do the "select *" DB still have to grab all the data from disk... If columnstore index would include all columns then in theory this operation could be faster (because of compression) ... but if Your query grab another columns not included in columnstore then DB probably is doing full scan...

Grant Fritchey SSC Guru Points: 399038 More actions · Answer 9

Kulgan_ (8/27/2014)
Hi,
In addition... when You do the "select *" DB still have to grab all the data from disk... If columnstore index would include all columns then in theory this operation could be faster (because of compression) ... but if Your query grab another columns not included in columnstore then DB probably is doing full scan...

Well, that depends. With 2014 and the introduction of the clustered columnstore, all the columns are stored with the columnstore index. There is no additional lookup to any other resource.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

columnstore index issue

Cookies on SQLServerCentral