Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Partitioning Large MS SQL-Server FACT table

2 posters

Go down

Partitioning Large MS SQL-Server FACT table Empty Partitioning Large MS SQL-Server FACT table

Post  dwman Tue Dec 07, 2010 12:52 am

Scenario:
• Data should be retained for 5 years or 60 months.
• Table will have about 800 million records
• Have adapted monthly partition.
• Each File group have 8 files to match the Number of CPU improve parallelism.
• Each partition will be kept in one File group.
• The above scenario requires 62 file groups need to be created (60 for partition and two for window sliding)

Question:[u]
Do I need to create 62 file groups and required data files using DDLs ? Any better way of handling this situation? Any ideas or scripts would be great?

dwman

Posts : 7
Join date : 2010-11-08

Back to top Go down

Partitioning Large MS SQL-Server FACT table Empty Re: Partitioning Large MS SQL-Server FACT table

Post  trickbooter Thu Apr 07, 2011 4:56 am

This is probably a dead topic.

On Project REAL, Microsoft's reference implementation from a few years back, they set up many disks across many HBA's, (say they had 6 HBA's, not sure exactly). They had a filegroup per HBA, with multiple files in it. Then they rotated the usage of the FG such that the monthly partitions were offset (i.e. Jan 2011 tables for 5 fact groups sat on 5 different filegroups to improve parallelism).

However, during testing they found there was hardly any read/write contention, so ended up merging down all of the HBA's into a single massive disk (better IOPS and bandwidth due to massive spindle count). These performance gains outweighed that filegroup optimsation.

They still ended up doing some FG work to move old data to a less expensive disk array, but that was all.

As with all these things, testing testing testing, but I would suggest that 60+ FG's are unnecessary. If you have 3 fact groups, try 4 filegroups (one for each fact table and one for dims and other stuff). Have the FG's rotate so each fact group for a given month are on different FG's.

trickbooter

Posts : 2
Join date : 2011-04-06

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum