0

I have a two part question really for anyone working with data warehousing where they have large fact tables.

Question 1

Lets say you have a table that has 500 million or more records in it that is clustered on time. You are only posting incremental records for the last 24 hours on a daily basis to this table.

How do you handle inserting delayed records that are a month or older to that table? Would you do nothing unless the insert caused a lot of fragmentation or would you attempt to drop the indexes, insert and rebuild?

I do not have enterprise edition available to me for table partitioning.

Question 2

If you have a fact table that is growing large like the above example, would it be wise to split the fact table up into multiple tables or would it be better to look towards adding additional files to the filegroup of said table if table partitioning is not an option?

Thanks in advance. I'm dealing with some large growth and trying to approach it the right way.

Improve this question
6
  • I can't say this for sure, but this actually sounds like a good situation for Partitioning. Here is a great resource for that brentozar.com/sql/table-partitioning-resources If you were doing a sliding window partitioning scenario, I believe it would probably make this very easy for you, since you would likely be inserting all new records into that current "live" partition and then when you needed to add something a month old they would go into a different partition. It may not fit your scenario but it sounds like it from reading this.
    – mskinner
    Commented Nov 13, 2015 at 22:57
  • Can't do partitioning because on standard edition. I'll include it in the question.
    – Fastidious
    Commented Nov 13, 2015 at 22:59
  • 500 million records and you can't get a single enterprise license? =( I'd think multiple tables with dedicated file groups... approximate partitioning as well as you can. It really depends on your query patterns though since you could very easily make a bad situation worse.
    – Dave
    Commented Nov 13, 2015 at 23:05
  • 1
    You're telling me Dave! But seriously, growth happened over night and enterprise will happen, just not tomorrow. I will aim for table partitioning for the long term. Any ideas on the interim would help, otherwise I may just try republishing over night from the old month and getting the data in order by morning. Query patterns are all based on time as aligned with the clustered index.
    – Fastidious
    Commented Nov 13, 2015 at 23:11
  • 1
    Is this "out of order" insert one-off or do you expect to do this semi-regularly?
    – Hannah Vernon ♦
    Commented Nov 13, 2015 at 23:18

2 Answers 2

Reset to default
0

For question 2.

I think I will break the large table up into many different tables and then use a view to join them together. This will allow me to have something like table partitioning without the feature of table partitioning as outlined in this article.

https://www.simple-talk.com/sql/sql-tools/sql-server-partitioning-without-enterprise-edition/

View with schemabinding that unions all the tables together. Then I can insert data and select data from this view as if it was the primary fact table. I would only need to ensure all my SELECT queries on this view include the column I chose to partition the tables with to get the full benefit.

Improve this answer
0

Since you don't have enterprise edition you can use table partition manually. Best practices are including : 1. create file group for the new table. 2. Place the file into different disk 3. give sql server service account priviledge 'perform volume maintenance'. This will speed up disk allocation up to 40% 4. ntfs is a must. 64 Kb disk cluster is recommended. At least 32 kb. 5. Create the file as big as it can and set growth size to 1 gig. 6. combine the multi tables using view.

Improve this answer

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.