Post reply

Need table partitioning implementation guidance for real scenario

  • January 10, 2018 at 4:04 am

    #327129

    There are several tables in a database. In this db there is one table in it which daily 5 million rows per day added. Within few months its size grown to 80 GB. This DB is on a VM with C and D drives only, The d drive is of size 1 terabyte. This table is of Transaction Data of each debit/credit activity by customer based on account. After moving daily data to DWH, we don't need these all transactions in this table but for sometime we want to keep them for history reporting etc. I want to separate them from daily activity to speed up inserts and make queries to bring results faster for a month or 2 on OLTP.

    The suggested solution is table partitioning. I don't have practically implemented this yet. Only have an idea, I don't know how to make partitioning automated so that there is no need for manually creation of partitions if i want to have 3 months function.
    I used some google to find if there is any way but they are not seems real example only explaining a short intro and then how they are managed further only daily/weekly or based on partition. How data is moved/shifted automatically?

    https://msdn.microsoft.com/library/aa964122(SQL.90).aspx

    If anyone has implemented and covered with all real life scenarios then please share useful blog/links. We are using mirroring for high availability on this database as well.

    Your right direction, suggestion and help is really appreciated!

    Regards,
    Shamshad Ali.

  • January 10, 2018 at 5:15 am

    #1975251

    In most cases partitioning will not help with query performance.
    It's there for data management. Switching sets of data in and out of the table, moving rows to archive, index management on the partition level, different compression settings on parts of the table, stuff like that.

    Data's not moved automatically with partitions, partitions are not created automatically. You'd have to create scheduled tasks that create new partitions as necessary, and if you intend to use the ability to switch data from one table to another, that has to be implemented in a scheduled task of some form as well.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 10, 2018 at 6:56 am

    This was removed by the editor as SPAM

  • January 10, 2018 at 7:17 am

    #1975271

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • January 10, 2018 at 7:21 am

    #1975273

    Thanks!

  • January 10, 2018 at 7:22 am

    #1975275

    subramaniam.chandrasekar - Wednesday, January 10, 2018 6:55 AM

    shamshad.ali - Wednesday, January 10, 2018 4:04 AM

    There are several tables in a database. In this db there is one table in it which daily 5 million rows per day added. Within few months its size grown to 80 GB. This DB is on a VM with C and D drives only, The d drive is of size 1 terabyte. This table is of Transaction Data of each debit/credit activity by customer based on account. After moving daily data to DWH, we don't need these all transactions in this table but for sometime we want to keep them for history reporting etc. I want to separate them from daily activity to speed up inserts and make queries to bring results faster for a month or 2 on OLTP.

    The suggested solution is table partitioning. I don't have practically implemented this yet. Only have an idea, I don't know how to make partitioning automated so that there is no need for manually creation of partitions if i want to have 3 months function.
    I used some google to find if there is any way but they are not seems real example only explaining a short intro and then how they are managed further only daily/weekly or based on partition. How data is moved/shifted automatically?

    https://msdn.microsoft.com/library/aa964122(SQL.90).aspx

    If anyone has implemented and covered with all real life scenarios then please share useful blog/links. We are using mirroring for high availability on this database as well.

    Your right direction, suggestion and help is really appreciated!

    Regards,
    Shamshad Ali.

    As GliaMonster suggested, Please enable archival on the mentioned table. From my end, I can say even if you create partitioning on any table, Still the table would cause performance issues in future not now, We need to apply a fix so that in future as well we can look to manage the data growth, Which would require addition of .ndf files.

    Please enable archival on the mentioned table

    What do you mean by enable archival? I still did not get a proper explanation, would this not help me If i do not need data more than a year, I can eliminate it and lighten the DB easily? use the partitioned index to retrieve data faster? 
    In benefits of following MS link see ...
    https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes
    and this is something how they schedule a procedure in a job to automate it.
    https://msdn.microsoft.com/library/aa964122(SQL.90).aspx

    Anyone have seen this?

  • January 10, 2018 at 7:27 am

    This was removed by the editor as SPAM

  • January 10, 2018 at 7:30 am

    #1975277

    shamshad.ali - Wednesday, January 10, 2018 7:22 AM

    What do you mean by enable archival?

    Presumably adding a scheduled job to archive old data in the table elsewhere, since there's no automatic archive in SQL

    I still did not get a proper explanation, would this not help me If i do not need data more than a year, I can eliminate it and lighten the DB easily?

    Use partitioning to easily move data from one table to another (in the same DB) or remove old data with little overhead? Yes, that's the point of partitioning, it's what it's designed for,
    Note that fast switching requires that the source and destination tables are in the same filegroup, in the same database.

    use the partitioned index to retrieve data faster? 

    No. Partitioning is not for performance and will often give little to no gains over indexes designed to support your workload, and can easily slow queries down if the queries don't filter on the partition column.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 10, 2018 at 9:15 am

    #1975315

    If you are looking at table partitioning then you have Enterprise Edition of SQL Server. If an 80GB table (or 5M inserts per day) is causing performance problems on Enterprise Edition of SQL Server (actually Standard Edition too for that matter) then your problem is the SERVER processing this data, and possibly the indexing of the table.

    Please tell us the configuration of the HOST and the VIRTUAL MACHINE on which this data resides, with details about the CPU, the RAM and the IO characteristics and PERFORMANCE. 

    Please also provide the table definition, to include indexing and all keys.

    I will go further than Gail: There is ABSOLUTELY NO REASON you need partitioning for this table!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply