sql - COLUMN STORE INDEX vs CLUSTERED INDEX..Which one to use? -
i’m trying evaluate type of indexes use on our tables in sql server 2014 data mart, using power our olap cube in ssas. have read documentation on msdn , still bit unclear right strategy our use case ultimate goal of speeding queries on sql server cube issues when people browse cube.
i have tables related each other shown in following snow flake dimensional model. majority of calculations going in cube, count distinct of users (userinfokey) based on different combination of dimensions (both filters , pivots). keeping in mind, sql experts suggest in terms of creating indexes on tables?. have option of creating column store indexes on tables (partitioned hash of primary keys) or create regular primary keys (clustered indexes) on tables. 1 better scenario? understanding cube doing lot of joins , groupby’s under covers based on dimensions selected user.
i tried both versions sample data , performance isn’t different in both cases. before same experiment real data (it’s going take lot of time produce real data , load our data mart), wanted check experts suggestions.
we evaluating if should use pdw( parallel datawarehouse) our data mart instead of vanilla sql server 2014.
just give idea on scale of data dealing with
the 2 largest tables
- activitydata fact table : 784+ million rows
- dimuserinfo dimension table: 30 + million rows
any or pointers appreciated
Comments
Post a Comment