none
Table partitioning questions?

    Question

  • I have 4  tables which need to do partition. each table has one clustered index which is required & all tables have same column modified_on having datetime type except one.

    My questions as follows

    1) Do i need to create partition function & partition scheme  for each table?

    2) Do i need to create 4 file groups for each table? is it good or bad?

    3) what happens if i create 4 file groups & while creating partition scheme i can specify range value of date to each file group

    (for ex:- FG1 for 2013, FG2 for 2014) same like for all 3 tables which has modified_on column.

    4) what happens if i specify separate data file while creating file  groups then do partitioning.

    5) sql has only 2 CPU's, is paritioning depend on CPU?

    6) what are prerequisites & precautions for partitioning?

    Please help me to clear my doubts.

    Wednesday, November 06, 2013 9:02 PM

Answers

  • 1) Do i need to create partition function & partition scheme  for each table?

    No, you have much flexibility regarding the relationship between partition functions, schemes and objects.  A single partition function may be associated with zero or more partition schemes and a partition scheme can be used by zero or more tables.  You need to be mindful of the implications of sharing a partition function because changing the function (i.e. by SPLIT and MERGE) will affect all of the referencing schemes and underlying objects.

    2) Do i need to create 4 file groups for each table? is it good or bad?

    Probably not.  If your purpose of partitioning is to isolate I/O for each partition (a specialized use case), you will need separate physical storage (spindles) for each filegroup.  That can quickly result in a large number of filegroups.

    3) what happens if i create 4 file groups & while creating partition scheme i can specify range value of date to each file group

    Yes, each partition has an associated date range. 

    4) what happens if i specify separate data file while creating file  groups then do partitioning.

    The underlying data files the storage locations for objects that use the filegroup.  You only need multiple data files if you need to spread files among multiple devices.  But consider that SAN/RAID can do this at the storage layer without creating multiple files.  However, you may still want multiple files to limit file size for manageability purposes.

     

    5) sql has only 2 CPU's, is paritioning depend on CPU?

    There is no dependency between partitioning and CPUs.  However, SQL Server can leverage partitioning for parallel operations at the partition level.  Personally, I would not consider the number of CPUs for partitioning planning purposes and let the optimizer do it's job.

     

    6) what are prerequisites & precautions for partitioning?

     

    SQL Server Enterprise Edition is required for table partitioning. 

    The main precaution is careful planning.  Partitioning is most often used to improve manageability of very large tables and these are unforgiving if you make a mistake.  The most common errors I see are excessive data movement during SPLIT an MERGE operations and unintended filegroup mapping.  The easiest way to avoid data movement is to plan such that only empty partitions are affected.  For filegroup mapping, be sure you fully understand the implications of the LEFT or RIGHT range specification. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, November 07, 2013 12:01 PM

All replies

  • Hi Mayur-DEW,

    The partition function defines the number of partitions that the table will have and how the boundaries of the partitions are defined. More than one table or index can use the same partition function. Partition scheme maps the partitions of a partition function to a set of filegroups. The number of filegroups don’t have to 4 for tables. You also can specify separate data file while creating file  groups then do partitioning.

    For more information about partitioned, I recommend you can review the Microsoft documents for deeply analysis.
    http://technet.microsoft.com/en-us/library/ms190787.aspx

    Thanks,
    Sofiya Li


    Your Name
    TechNet Community Support

    Thursday, November 07, 2013 9:07 AM
  • 1) Do i need to create partition function & partition scheme  for each table?

    No, you have much flexibility regarding the relationship between partition functions, schemes and objects.  A single partition function may be associated with zero or more partition schemes and a partition scheme can be used by zero or more tables.  You need to be mindful of the implications of sharing a partition function because changing the function (i.e. by SPLIT and MERGE) will affect all of the referencing schemes and underlying objects.

    2) Do i need to create 4 file groups for each table? is it good or bad?

    Probably not.  If your purpose of partitioning is to isolate I/O for each partition (a specialized use case), you will need separate physical storage (spindles) for each filegroup.  That can quickly result in a large number of filegroups.

    3) what happens if i create 4 file groups & while creating partition scheme i can specify range value of date to each file group

    Yes, each partition has an associated date range. 

    4) what happens if i specify separate data file while creating file  groups then do partitioning.

    The underlying data files the storage locations for objects that use the filegroup.  You only need multiple data files if you need to spread files among multiple devices.  But consider that SAN/RAID can do this at the storage layer without creating multiple files.  However, you may still want multiple files to limit file size for manageability purposes.

     

    5) sql has only 2 CPU's, is paritioning depend on CPU?

    There is no dependency between partitioning and CPUs.  However, SQL Server can leverage partitioning for parallel operations at the partition level.  Personally, I would not consider the number of CPUs for partitioning planning purposes and let the optimizer do it's job.

     

    6) what are prerequisites & precautions for partitioning?

     

    SQL Server Enterprise Edition is required for table partitioning. 

    The main precaution is careful planning.  Partitioning is most often used to improve manageability of very large tables and these are unforgiving if you make a mistake.  The most common errors I see are excessive data movement during SPLIT an MERGE operations and unintended filegroup mapping.  The easiest way to avoid data movement is to plan such that only empty partitions are affected.  For filegroup mapping, be sure you fully understand the implications of the LEFT or RIGHT range specification. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, November 07, 2013 12:01 PM
  • Thanks DAN for your answer. so what would be the best plan in my case?

    Tuesday, November 19, 2013 9:30 PM
  • Thanks DAN for your answer. so what would be the best plan in my case?

    We'll will need more information before making recommendations.  The only information we have is that you have 4 tables you want to partition and 3 have a modified_on datetime column that is a candidate partitioning column.  Can you provide the DDL for the existing tables?

    The main question is what is your objective for partitioning these tables.  Is this to improve performance, manageability, incremental loads/sliding window, or some other reason?  What sort of workload (OLTP, reporting)?  Be aware that queries that do not specify the partitioning column will need to touch every partition.

    I should add that indexing is often the key to query performance regardless of table size.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, November 21, 2013 12:28 PM