locked
Table Partition RRS feed

  • Question

  • Hi all,

    i need help to partitioning the table on which most expensive query run.

    every day 500000 lac records inserted/update in that table

    so suggest me to how to create what impact on performance while retrieving the records.

    • Changed type Olaf HelperMVP Thursday, July 2, 2015 9:20 AM More a question rather then a discussion
    Thursday, July 2, 2015 9:10 AM

Answers

  • every day 500000 lac records inserted/update in that table

    so suggest me to how to create what impact on performance while retrieving the records.

    Indexes will generally help query performance, partitioned or not.  Performance largely depends on the specifics of your queries and the amount of data that must be accessed to returned the result.  If the partitioning column is specified in the WHERE or JOIN clauses, SQL Server might be able to eliminate unneeded partitions but a full partition scan will be required unless an index exists that is useful for query optimization. 

    Post a sample query along with DDL and indexes for help with performance.  You mention 50M rows a day are inserted.  How many rows in the table and what is the average partition size? 

    If the nature of queries are such that a scans are required, consider columnstore indexes. 


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

    Thursday, July 2, 2015 11:02 AM

All replies

  • Partition is not about performance it is more about manageability...Do you want to improve performance? 

    BTW, you can easily archive the old data with/without partition


    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, July 2, 2015 9:41 AM
    Answerer
  • I agree with Uri

    Partitioning is primarily intended to make managing large tables easier for database administrators. However, partitioned objects can also improve performance of SELECT, UPDATE and DELETE queries. If a table is partitioned, SQL Server can often eliminate irrelevant partitions from even being considered by query optimizer and only examine those partitions that contain the requested data, as defined by query predicates. This feature is called partition elimination. In order to benefit from partition elimination, the query should not return data from all partitions.

    refer

    https://msdn.microsoft.com/en-IN/library/ms188730.aspx

    http://www.sqlshack.com/database-table-partitioning-sql-server/

    http://www.toadworld.com/platforms/sql-server/w/wiki/9651.example-of-creating-partitioned-tables.aspx

    Thanks

    Saravana Kumar C


    • Edited by SaravanaC Thursday, July 2, 2015 9:55 AM
    Thursday, July 2, 2015 9:51 AM
  • The purpose of partitioning is more for archiving the data\managing unused data than to improve the performance. Otherwise you will have to delete the data and that won't scale up after a certain extent. It will also consume far more time and log space. 

    There are some more benefits as well but I have not been able to measure the overall benefits on an OLTP system. For example you can rebuild the index based on the partitions. So you do not have to rebuild the entire index on the table.  I think there are some improvement in lock escalation as well.

    I also believe(do not remember fully though) that there are some performance benefits for a partitioned tables(Parallelism and no fragmentation when rebuilding index with maxdop for sure) due to quick maintenance.

    I do not see any negative impact of having the table partitioned though.

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

    Thursday, July 2, 2015 9:55 AM
  • every day 500000 lac records inserted/update in that table

    so suggest me to how to create what impact on performance while retrieving the records.

    Indexes will generally help query performance, partitioned or not.  Performance largely depends on the specifics of your queries and the amount of data that must be accessed to returned the result.  If the partitioning column is specified in the WHERE or JOIN clauses, SQL Server might be able to eliminate unneeded partitions but a full partition scan will be required unless an index exists that is useful for query optimization. 

    Post a sample query along with DDL and indexes for help with performance.  You mention 50M rows a day are inserted.  How many rows in the table and what is the average partition size? 

    If the nature of queries are such that a scans are required, consider columnstore indexes. 


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

    Thursday, July 2, 2015 11:02 AM
  • >every day 500000 lac records inserted/update in that table

    ...

    You mention 50M rows a day are inserted.

    500,000 lac = 500,000 * 100,000 = 50 billion rows a day inserted/updated?

    Or maybe OP meant 50M as you say!

    Even 50M is a pretty large number, and 50B starts being a very large number!

    ... and I hope this is not cumulative, so after a year you have 365 times that many!

    I agree with what has been said so far, that only with an excellent set of indexes are you going to get good performance.  In general, as the numbers get big, either you have an excellent logical and physical model and things work, or else you have a disaster and partitioning won't help.

    But I'll go one further, which is that as the numbers get large there may be nothing you can do with SQL Server to get good performance, it simply lacks the features for scaling above a certain point, and features that help performance on smaller databases start hurting instead.

    Josh

    Thursday, July 2, 2015 4:39 PM