Anthony Stephens (12/16/2012)
Appreciate feedback to this question. I'm working on a design for VLDB that will be in the several TB size range. The load activity will be vary infrequently as the data will be for research purposes only. People will not have to make business critical decision with this data nor will there be any OLTP activity against the data. In fact data queries will be mostly in the form of an export which also will not happen daily. Frequency of the export will most likely be along the lines of bi-monthly or quarterly.That said, I'm having a hard time to justify the use of partitioning and even file-groups seem like a stretch in term of producing any real performance benefits. The Cost/Benefit (C/B) breakdown doesn't appear to offer much of a compiling advantage. Yes, the data repository will be large. But I 'm thinking with the right performing storage, CPU and Memory, the export time-to-completion should be fairly reasonable without the use of these strategies.
Am I missing something in my thought process? Given these requirements should I consider the use of partition or filegroups?
Table partitioning rarely brings any performance advantages to the table - do not even think on table partitioning if the reason is "performance".
Different filegroups, as stated on previous post would help IF, and this is a VERY BIG "IF", if tables are distributed in a way that ensures I/O load balancing otherwise, hot table might end up on the same filegroup then on the same LUN therefore defeating the purpose.
Reading the specifications of the project it looks to me like "performance" is not really a top priority and, if it is, then proper data modeling and physical implementation including a reasonable indexing strategy would do the trick.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.