June 3, 2011 at 12:37 pm
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?
June 13, 2011 at 9:10 am
I am not sure how much of a difference this makes in SQL 2000, but it should help. If you can partition on a field that will remove most of the records from the queries, meaning you don't have lots of aggregates that require the full set of data, then it should reduce I/O.
However check the queries that you often use. Do they table scan? Do they need to table scan (or CI scan)? If so, partitioning doesn't help.
Partitioning also ought to take some time to test/setup. I wouldn't think that would help you sleep right now. Better hardware might be the quickest thing that could help you. Do you have lots of paging or lower buffer cache hit ratio?Is the CPU pegged? Perhaps you can spend some $$ for quick improvement.
June 13, 2011 at 11:39 am
Steve Jones - SSC Editor (6/13/2011)
I am not sure how much of a difference this makes in SQL 2000, but it should help. If you can partition on a field that will remove most of the records from the queries, meaning you don't have lots of aggregates that require the full set of data, then it should reduce I/O.However check the queries that you often use. Do they table scan? Do they need to table scan (or CI scan)? If so, partitioning doesn't help.
Partitioning also ought to take some time to test/setup. I wouldn't think that would help you sleep right now. Better hardware might be the quickest thing that could help you. Do you have lots of paging or lower buffer cache hit ratio?Is the CPU pegged? Perhaps you can spend some $$ for quick improvement.
Steve, thanks for the insight about using partitioning. I wasn't aware about the table scan factor. Not sure about the paging or buffer cache as I'm not on the DBA team. However, I've heard the CPU gets pegged occasionally. This does not generally happen when we're experiencing latency or blocking.
June 15, 2011 at 5:03 am
steve14437 (6/13/2011)
Steve Jones - SSC Editor (6/13/2011)
I am not sure how much of a difference this makes in SQL 2000, but it should help. If you can partition on a field that will remove most of the records from the queries, meaning you don't have lots of aggregates that require the full set of data, then it should reduce I/O.However check the queries that you often use. Do they table scan? Do they need to table scan (or CI scan)? If so, partitioning doesn't help.
Partitioning also ought to take some time to test/setup. I wouldn't think that would help you sleep right now. Better hardware might be the quickest thing that could help you. Do you have lots of paging or lower buffer cache hit ratio?Is the CPU pegged? Perhaps you can spend some $$ for quick improvement.
Steve, thanks for the insight about using partitioning. I wasn't aware about the table scan factor. Not sure about the paging or buffer cache as I'm not on the DBA team. However, I've heard the CPU gets pegged occasionally. This does not generally happen when we're experiencing latency or blocking.
Not being a DBA, do you have any database tools are your disposal? E.g. do you have Enterprise Manager available to you? (If not, bug your DBA's to get it installed on your system! 😉
Using Enterprise Manager, you can get the Estimated Query Plan for the problematic query and look for Table Scans or Index Scans. I am expecting you will find some of those in the Query Plan. If you do find them, you may need to add (or get added) some indexes to the tables.
A "Rule of Thumb" for addressing this sort of problem whould there be an index issue is that you want to have an index on each table of each join and that the index should include the same columns in the same order that they are in the join. Now, if you are using a comma separated list of tables and then constraining things in the WHERE clause, you are going to have more difficulty than if you use the following basic format:
SELECT {list of coluns}
FROM TableA A
INNER/OUTER JOIN TableB B
ON A.somecolumn = B.somecolumn AND
A.someothercolumn = B.someothercolumn
With the constraints in the WHERE clause, you may have to do some digging to extract which columns are involved in each of the JOINS.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply