March 5, 2016 at 10:01 am
Dear all,
Do we have any advantage in partition tables by year if all are in the same disk?
Or to take advantage is partition should be in a separated disk?
Thanks.
March 5, 2016 at 12:14 pm
Define "advantage".
Are you looking for performance benefits? Then partitioning provides little advantage, regardless of single or multiple disks.
Are you looking for easier management with swapping partitions in and out? Then the diskss are irrelevant.
Are you looking for flexible ways to get the most critical data online fast after an emergency? Then you need not only multiple filegroups, but not necessary multiple disks.
Are you trying to minimis data loss on a disk failure? Then you need multiple disks. (And ensure that it's really physical disks, not just logical units all on the same SAN).
Etc.
March 5, 2016 at 1:21 pm
Hello. Many thanks for that complete answer.
In this case I am searching performance
March 5, 2016 at 2:56 pm
Partitioning is not a performance feature. Indexing should help you lots more.
March 6, 2016 at 11:39 pm
Make sure the definition of the clustered index on the table reflects the definition of the intended partitioning.
And all performance issues which you want to resolve with partitioning will be solved. Without adding overheads of maintaining th partitions.
For example, if you intend to make partitions by year of, say, InvoiceDate, then you need to define the clustered index on InvoiceDate.
_____________
Code for TallyGenerator
March 7, 2016 at 9:08 am
You'll get the most performance benefit by determining and implementing the best clustered index for that table, in this case very likely the date.
But you might also gain some performance by partitioning data, it depends on the specifics of your situation. For example, you could page compress and use a 100% fillfactor on older data while leaving current data only row compressed and with a lower fillfactor to allow for updates (but go below 90% ff only in exceptional cases where you've demonstrated a true need for it).
Partitioning might also allow you to put older partitions on slower drives and the current partition(s) on SSDs, whereas without partitioning you wouldn't be able to afford SSDs at all for this table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply