locked
OLTP Performance Issue (SQL 2000) RRS feed

  • Question

  • We have two different OLTP databases on the same node within a 3 node SQL 2000 Veritas cluster. I'm one of two developers tasked with improving query performance as we often get support calls for latency issues.

    Both databases house large numbers of records in multiple tables (a couple tables have ~9M records each) and should be archived. We have spent a lot of time working with the DBA team to fine-tune indexing, index rebuilds, adjust backup schedules, etc. However, that is a longer-term strategy and we need to improve performance now so we can all get some sleep at night!

    My colleague and I have been discussing using a table partition approach but have not tested this idea yet. As we're a small shop, we thought it would be wise to ask for advice on the best practice to manage this issue.

    What seems to be the best option for dealing with this type of scenario? Is partitioning a good choice or is there something better for short term improvment?


    S
    Friday, June 3, 2011 6:42 PM

Answers

  • What seems to be the best option for dealing with this type of scenario? Is partitioning a good choice or is there something better for short term improvment?

    Note that table partitioning was introduced in SQL 2005 and requires Enterprise Edition.  In SQL 2000, partitioning requires separate tables with the partitioning column part of the primary key and a UNION ALL partitioned view.

    An OLTP workload consists of a high number of lightweight queries and short transactions.  Why do you expect that partitioning will improve performance with such a workload?  Will partitioning result in queries accessing less data?  I would think indexing would do that.  In addition to query and index tuning, application and schema design are large contributors to OLTP performance.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Mr. Wharty Sunday, June 5, 2011 6:57 AM
    • Marked as answer by a14437 Monday, June 13, 2011 5:27 PM
    Saturday, June 4, 2011 7:05 PM
    Answerer

All replies

  • What seems to be the best option for dealing with this type of scenario? Is partitioning a good choice or is there something better for short term improvment?

    Note that table partitioning was introduced in SQL 2005 and requires Enterprise Edition.  In SQL 2000, partitioning requires separate tables with the partitioning column part of the primary key and a UNION ALL partitioned view.

    An OLTP workload consists of a high number of lightweight queries and short transactions.  Why do you expect that partitioning will improve performance with such a workload?  Will partitioning result in queries accessing less data?  I would think indexing would do that.  In addition to query and index tuning, application and schema design are large contributors to OLTP performance.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Mr. Wharty Sunday, June 5, 2011 6:57 AM
    • Marked as answer by a14437 Monday, June 13, 2011 5:27 PM
    Saturday, June 4, 2011 7:05 PM
    Answerer
  • Thanks Dan...

    I'm aware of the difference between 2000 and 2005 regarding partitioning and don't know if it will be a short-term solution which is why I proposed it here.

    As for an OLTP workload consisting of a high number of lightweight queries and short transactions, I think that's the ideal we tend to start with but not realized as often as we would like. In reality, due to the glut of records and the high number of users, these systems sputter over the simple queries. Long-term, I know that we need to archive the database. While we design the archive solution, the short-term that is plaguing us.

    The databases are properly indexed and the indexes are rebuilt weekly. I think we need to set up a test of such a partition and profile/benchmark it against current state.


    S
    Monday, June 13, 2011 5:26 PM
  • I agree that testing is a good approach.  The issue with partitioning is that all tables/partitions will need to be touched when the partitioning column is not specified in a query predicate.  This can actually worsen performance compared to no partitioning at all. 

    If your queries specify the partitioning column, the result in terms of performance is similar to having the partitioning column as the first key in all indexes except that rows from other partitions will not be in the leaf data and index pages, thus improving buffer efficiency.  Archiving can similarly improve performance because rows not needed are not in the pages. 

    Partitioning also adds provides manageability benefits, such as the ability to rebuild indexes on specific partititons instead of the entire table.  How partitioning will affect performance depends much on the overall workload mix and actual queries.  Testing will help you make the right decision.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, June 14, 2011 12:29 PM
    Answerer