SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


columnstore index issue


columnstore index issue

Author
Message
Paresh Motiwala
Paresh Motiwala
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 311
This result should tell you:
Query Rows Time of execution
w/o compression with compression with 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 78 78

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



Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15020 Visits: 18596
Paresh Motiwala (8/20/2014)
This result should tell you:
Query Rows Time of execution
w/o compression with compression with 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 78 78

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.
Cool
Paresh Motiwala
Paresh Motiwala
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 311
I am just running a bench marking test. With and without columnstore indexes....



Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15020 Visits: 18596
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.
Cool
Paresh Motiwala
Paresh Motiwala
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 311
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



Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15020 Visits: 18596
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.
Cool
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39580 Visits: 32638
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Paresh Motiwala
Paresh Motiwala
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 311
Thanks Grant!!!



Kulgan_
Kulgan_
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 268
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
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39580 Visits: 32638
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search