14

Background:
I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test.

Table Features:

  1. No of Dimensions ~ 45
  2. Measures ~ 30
  3. Non-additive measures and other columns~ 25
  4. Current data size ~ 200 Million (2 years data)
  5. Time View: 3 different Month views: Fiscal/Calendar/Adjusted (i.e same row can fall in different months based on which view one is looking for)
  6. Only One view will be required at a time by a user. (ie. only one Month Column will be used in the query, it's stopping us to do partitioning on time view)
  7. Indexes: 1 Clustered Index on the Natural Keys (8 columns).Created 3 covering Non Clustered Indexes one on the each Month column including few Dimension SKs (FKs) and all the measures).
  8. Indexes are huge(total 190 GB) because of this.
  9. Space is not constraint (1 TB allocated)
  10. 64 GB of RAM available in server.
  11. Table compression also done.

Requirement:
Queries on this Fact table should give result within 30 seconds (General queries select sum(measure) joining few Dims group by Dim Values). Reports are directly done on top of this Fact table.

Issue:
Any query which includes columns available in the Index works fine, but if we include any other columns which are not in the include..It sucks. It takes more than 5-10 minutes. Can any one suggest some solution where it works fine for any dimension/column we select. Can Index view help in this situation?

Improve this question
0

2 Answers 2

Reset to default
6

Upgrade to SQL Server 2012 and use columnstores. They thrive in these requirements. Seriously, download the evaluation edition and give it a try. Drop all indexes, drop the clustered index, simply add a non-clustered columnstore index on all columns and give it a whirl. I've seen cases just like your that reduced the execution time to 2-3 seconds, mostly because of segment elimination kicking in. Some supplemental reads:

Improve this answer
0
0

Will an indexed view solve your issue? How up to date does the data need to be? You could create an indexed view for a few permutations. But with that many dimemsions and measures you might run out of space quickly!

How about using SSD's?

Improve this answer
4
  • Data will be updated every month. How much time will it take to update the View?
    – user1801862
    Commented Nov 6, 2012 at 5:28
  • If your existing query takes 5-10 minutes, then the indexed view will take 5-10 minutes. When complete, when you run the same query it will come back as though it's coming out of a table (i.e. immediately). An indexed view pre-runs a particular bit of SQL. If you submit SQL that matches it, it takes it from the indexed view, rather than running it all over again. The major advantage of an indexed view is that you don't need to change your existing queries, they will automatically use it. The disadvantage is you pretty much have to create one for a few different combinations.
    – Nick.Mc
    Commented Nov 6, 2012 at 9:34
  • But I don't suggest that you go creating multiple indexed views to speed things up - you'll run out of time and disk space eventually. It might just be one thing to put in your arsenal.
    – Nick.Mc
    Commented Nov 6, 2012 at 9:41
  • and please... look into columnstores as suggested!
    – Nick.Mc
    Commented Nov 6, 2012 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.