Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2012 VLDB Data Warehouse Design Question Expand / Collapse
Author
Message
Posted Sunday, December 16, 2012 4:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 3, 2013 2:02 AM
Points: 2, Visits: 83
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?
Post #1397028
Posted Wednesday, December 19, 2012 8:32 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 3,118, Visits: 792
Partitioning is mainly for incrementally loading data by say a date column. If the Monthly or quarterly data is new in terms of in the last month time stamped and no update to previously imported data, there is value.

File Groups are used to separate the files in the database on different LUNs (SAN) or hard drives. That would be your only benefit if you have separate drives or LUNs to place the files in file groups.

For performance, you might want to look into Indexing strategies. I have seen huge performance improvements on loading the data in a data warehouse where a fact table was completely rebuilt and we dropped the indexes first, truncated the table, loaded the data and added the indexes.

The table was mostly duplicated from an OLTP system, but the indexes on the OLTP where different than the fact table because the reporting and querying where different in the 2 systems.


Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #1398748
Posted Tuesday, January 22, 2013 6:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1410309
Posted Thursday, March 28, 2013 10:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:20 PM
Points: 373, Visits: 909
File groups could help if you do frequent data backups on this VLDB where you can back up each devise simultaneously. I don't read that you need to do this.
Post #1436610
Posted Thursday, March 28, 2013 12:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:03 PM
Points: 124, Visits: 488
Anthony,

You are correct in your thinking - I don't see any reason to implement any performance strategies to your data warehouse. It would be a waste of your time given the generous amounts of time between actions.
Post #1436670
Posted Tuesday, April 2, 2013 4:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:30 AM
Points: 2,885, Visits: 3,253
If you are designing a multi-TB database and do not understand all the design issues you need to pay to outside help. Period.

When processing a database of this size, a 2% difference in performance can result in extra hours of processing. Unless you really know how to fine tune your design to get the last 1% or 2% out of it, you almost certainly will end up with something that is either very slow or is using a few $100k more kit than it needs.

IMHO you should build a business case for your employer to engage an external consultant to help you with these issues. There are a number of high-reputation organisations in the market that can do this work, but if you are really stuck for names ask your Microsoft Account Manager for advice.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1437801
Posted Tuesday, April 2, 2013 11:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:03 PM
Points: 124, Visits: 488
EdVassie (4/2/2013)
If you are designing a multi-TB database and do not understand all the design issues you need to pay to outside help. Period.

When processing a database of this size, a 2% difference in performance can result in extra hours of processing. Unless you really know how to fine tune your design to get the last 1% or 2% out of it, you almost certainly will end up with something that is either very slow or is using a few $100k more kit than it needs.

IMHO you should build a business case for your employer to engage an external consultant to help you with these issues. There are a number of high-reputation organisations in the market that can do this work, but if you are really stuck for names ask your Microsoft Account Manager for advice.


The loads will be, by his word, very infrequent. It sounds like he has the luxury of extra hours of processing. I'm a huge proponent of outside consulting, but for all we know the OP has the skills to successfully implement a coherent performance strategy. Why spend money on consultants when the benefits won't be noticed due to the infrequency of loads and flat file extracts? I don't see the cost outweighing the benefits in this particular case.
Post #1438020
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse