March 16, 2011 at 5:39 pm
I come across a partition setup which I had no experience with before.
Someone setup a single filegroup and single logical file for partitioning. I had always created multiple files either single FG or multiple FG.
Anyone tested the benefits in this design?
March 17, 2011 at 12:47 am
You can choose according to your need. But if you have single partition schema nad function then its easy to manage. Problem here is when you have different archival approche for the objects hosted on partiion function. In this case it would be nice to have different schema and fucntion.
March 18, 2011 at 2:13 pm
If you need to switch partition to another table (e.g. move data to archive) keep in mind that source and target tables should be in the same filegroup. That means if your target table resides in different filegroup you should 1) switch partition to some (auxiliary) table in the same file group and only after that 2) move data into target table in another filegroup
March 18, 2011 at 2:44 pm
This is an example of what I saw someone did. And there is only 1 file associated with the FG_TEST filegroup. I wonder if there is any benefit to this design.
CREATE PARTITION SCHEME [PS_TEST] AS PARTITION [PF_TEST] TO ([FG_TEST], [FG_TEST], [FG_TEST],....,[FG_TEST])
March 19, 2011 at 2:14 am
There's no IO benefit, but you can still use the sliding window technique to quickly load the table with new data and quickly drop older stuff.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply