March 12, 2014 at 8:54 am
Hi,
We have the following scenario on our company.
There's a table which keeps 60 days of data and every day, there's a sliding window maintenance that merge the getdate()-60 partitions. This table is partitioned on a datetime column and each partition keeps 15 minutes of data. So, the sliding window has to merge a partition 96 times (4 partitions per hour times 24 hours in a day).
Some facts:
- All partitions are in the same filegroup
- The function and scheme partitioning are shared for other 5 tables
The issue is the merge partition takes a huge time to execute, even if we switch out the partitions that needs to be merged.
Is there any best practices I am not following? Should I create a filegroup for each partition? Should I create a function and scheme partitioning for each table?
I didn't find any helpful article or whitepaper on this.
Thanks
March 12, 2014 at 9:08 am
You want to make sure that you are only performing a metadata operation and not a data move operation. There are best practices around what you are trying to do. I would start here: http://www.brentozar.com/sql/table-partitioning-resources/
March 12, 2014 at 12:25 pm
All your partitions are in the same filegroup. What you are trying to achive with partitioning?
March 12, 2014 at 1:18 pm
Manageability and performance, for both are doing OK.
March 12, 2014 at 1:27 pm
Can you elaborate more about manageability ?
About performance: all your partitions placed in the same filegroup. If you have 1-1 between FG and files, you can't achive performance gain because all partitions located on the same pfysical disk. If you have many files per filegroup and they are spread scross multiple physical disks, all your gain in performance could be achieved without partitioning, just by having multiple files per non-partitioned filegroup.
March 13, 2014 at 4:07 am
SQL Guy 1 (3/12/2014)
Can you elaborate more about manageability ?About performance: all your partitions placed in the same filegroup. If you have 1-1 between FG and files, you can't achive performance gain because all partitions located on the same pfysical disk. If you have many files per filegroup and they are spread scross multiple physical disks, all your gain in performance could be achieved without partitioning, just by having multiple files per non-partitioned filegroup.
Thats not strictly true, you "could" get a performance improvement through partition elimination.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply