I am designing a partitioning based solution for a high performance large SQL Server 2016 database. Some of the data will be coming in with hundreds of millions of records per day. During the day we will be also running reporting queries and queries that looks for trends over multiple day and weeks.

My current solution will use 70 days in daily partitions using a dedicated filegroup per partition. After the data moves beyond the 70 day mark, it will go into a weekly partition for 42 weeks which are also using dedicated filegroups per partition, then 12 month and then 6 years, all set up in the same fashion.

We need really high performance and massive scale up capabilities (PB+ range). In order to minimize rework down the line, I am thinking about using multiple files per filegroup/partition for the daily and weekly filegroups/partitions. 4 per daily and 2 per weekly to be exact.

This way we could potentially increase read/load throughput per partition, as well as increase maximal capacity of the partitions (don't ask why, but we are worried to actually need that level of capacity on certain days).

Has anyone ever done it this way and what were your results? Is there any reason not to do it this way other than management overhead?

All the weekly, monthly, and yearly partitions would be in the same database on same server (application design issues, but multi-DB could be an option if properly motivated. Multiple servers or instances are not desirable).

The partition breaks are currently being discussed and evaluated. I chose the values above based on information I received on query patterns. Different number of days is of course possible but I kinda like the 10 weeks' worth of daily partitions.

We do have a seriously high-end datacenter, well 2 actually. We are discussing the purchase of a converged solution specific for this platform and one other. I personally would like to see dedicated AFA's (All Flash Arrays) but there are a some bridges to cross before I can get my hands on those.

I am aware of Data Warehouse Fast Track solutions, but they will not work for us. For one, we will be doing OLTP mostly so the benchmark figures will not be representative for what we will get. Second, they don't scale-up enough (at the moment). Some of the elements that come from the reference architectures, will be used of course but the 'turn-key' SKU's won't be an option. I am an ex-MS PFE so those resources are the first I look at.

  • I tried something similar that brought me up to the 1000 partition limit and I found the IO was just not possible with so many open file handles. This was done on a 2008r2 SSEE with direct attached storage. I would recommend looking hard at the specs for your io sub system and working with the vendor on the settings and tuning. You may find you need multiple io channels to meet the scale you are looking for. – Aaron May 8 '18 at 14:19

This is an interesting question, and I have a similar one in myself. It is my belief that in certain situations, multiple files per file group can help with performance depending on your hardware configuration (number of cores) and physical I/O design (the files can be distributed across multiple drives)

Although of course you will be using SSDs in Raid 10 configuration for the production data drive(s), and have a ton of server memory, so that the majority of pages are being read from disk. With that hardware I'm not sure how much the physical design will matter.


A partition may belong to only one filegroup whereas a filegroup may contain multiple disks distributed on multiple disks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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