Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

columnstore index issue Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2014 9:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:03 PM
Points: 93, Visits: 156
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



Post #1605495
Posted Wednesday, August 20, 2014 9:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:49 AM
Points: 2,542, Visits: 7,140
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.
Post #1605504
Posted Wednesday, August 20, 2014 11:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:03 PM
Points: 93, Visits: 156
I am just running a bench marking test. With and without columnstore indexes....


Post #1605539
Posted Wednesday, August 20, 2014 11:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:49 AM
Points: 2,542, Visits: 7,140
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.
Post #1605548
Posted Wednesday, August 20, 2014 12:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:03 PM
Points: 93, Visits: 156
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



Post #1605568
Posted Wednesday, August 20, 2014 12:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:49 AM
Points: 2,542, Visits: 7,140
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.
Post #1605570
Posted Thursday, August 21, 2014 3:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 14,205, Visits: 28,536
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
Post #1605715
Posted Thursday, August 21, 2014 5:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:03 PM
Points: 93, Visits: 156
Thanks Grant!!!


Post #1605765
Posted Wednesday, August 27, 2014 4:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:26 AM
Points: 18, Visits: 191
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...

Post #1607758
Posted Wednesday, August 27, 2014 5:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 14,205, Visits: 28,536
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
Post #1607776
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse