locked
TABLE PARTITIONING USING REFERANCE TABLE DATE COLUMN RRS feed

  • Question

  • I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

    also take suggestion to which way doing it will benefit us. i have think three ways to do it.

    1. leave as it is.

    2. 7 years partition on one server

    3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

    Thank you


    • Edited by cRzyptl Wednesday, October 7, 2015 4:49 PM
    Wednesday, October 7, 2015 4:46 PM

Answers

  • >>>>is there a way i can do the partitioning with out adding date column on table?

    That's problematic,  you need a  partitioning column

    >>>>also does partition will make query faster? 

    It may or may not. From my experience the partitioning is more for manageability rather performance.. But if the users query for specific range of the date that reside in one partition then you will be benefit from performance

    I think you can move 3 yeas old data to the archive server/database and must create proper indexes to speed up the queries.

    Can you show us sample query against this table? How much data does it return?

    PS , take a look at column-store index to improve query performance in SQL Server 2014


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 8, 2015 5:30 AM
  • If you want to go ahead with partition one method is to first add the date column to table and update values from reference table. Since this is a huge table you may be better off doing updates in batches. Once this is done then you can apply partitioning on the table and allocate it to multiple FGs. Then you can add indexes to required partitions (mostly the latest ones) depending on how frequent you use them and then query should benefit from them

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, October 8, 2015 5:43 AM

All replies

  • >>>>is there a way i can do the partitioning with out adding date column on table?

    That's problematic,  you need a  partitioning column

    >>>>also does partition will make query faster? 

    It may or may not. From my experience the partitioning is more for manageability rather performance.. But if the users query for specific range of the date that reside in one partition then you will be benefit from performance

    I think you can move 3 yeas old data to the archive server/database and must create proper indexes to speed up the queries.

    Can you show us sample query against this table? How much data does it return?

    PS , take a look at column-store index to improve query performance in SQL Server 2014


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 8, 2015 5:30 AM
  • If you want to go ahead with partition one method is to first add the date column to table and update values from reference table. Since this is a huge table you may be better off doing updates in batches. Once this is done then you can apply partitioning on the table and allocate it to multiple FGs. Then you can add indexes to required partitions (mostly the latest ones) depending on how frequent you use them and then query should benefit from them

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, October 8, 2015 5:43 AM