| Author |
Topic |
|
steve14437
Starting Member
4 Posts |
 Posted - 2011-06-03 : 14:16:46
|
| 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 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? |
|
|
steve14437
Starting Member
4 Posts |
Posted - 2011-06-03 : 14:18:24
|
| Sorry, just noticed an error in the second paragraph. It should read: "We have spent a lot of time working with the DBA team to fine-tune indexing, index rebuilds, adjust backup schedules, etc." |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-06-04 : 03:06:27
|
| 1)Are you having specific problems?such as a particular function? or is it a general slow slow down? Or has it been sudden?2)Have you listed out an reviewed your maintenace policy - such as statistics and index maintenace?3)Take a look at wait stats , this will give you some good ideas on delays4)Is your database layed out on different drives - such as splitting data|log|tempdb There are potentially many underlying reasons. Start out with some quick analysis - then if necessary go for a wider health checkJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
steve14437
Starting Member
4 Posts |
Posted - 2011-06-13 : 13:34:05
|
| Thanks Jack...We're not having specific problems. Our DBA team has combed through the transaction log snapshots periodically but cannot point to any specific variable or combination of variables as the cause.It will run fine during most of the day but generally slows down at certain times of day. This prompted a schedule check and adjustment to some jobs. I believe the database is contained on a single drive, not several but don't know for certain.Our current plan is to test the partition plan and benchmark it against current state. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-06-13 : 14:50:26
|
| steve14437,with a lot of these types of performance issues - a general health check of the performance stack helps - meaning everything from queries down to storage subsystem - to identify bottlenecksJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-06-17 : 17:07:01
|
| 9M or rows is not large nowdays. No table partition is needed and archiving will not help performance. The solution is to find a root cause of the problem.Better analyze if it is locking problem, poor db design, poor indexes or bad queries. Also make sure you do not shrink your databases and logs. Something of that is usually responsible for bad performance in case of dbs as small as yours.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-17 : 17:38:58
|
| I agree that partitioning isn't going to help you. You are using SQL 2000, so you can't use table partitions anyway but you can use partitioned views. Your data size is too small to even bother with those.I'd start by examining execution plans as you are likely missing indexes. If you were using SQL Server 2005 or greater, we could easily help you out with this. It'll take some good old fashioned analysis to get to the bottom of this one. Verify your statistics aren't out of date and that your fragmentation levels are acceptable (less than 30% or thereabouts). Show us some sample queries as well as DDL for the tables involves, so we can help you specifically with your problem.I'd also recommend running SQL Profiler (checking for high reads and also high CPU) as well as PerfMon (especially IO - 12ms or less for average reads/writes, memory - PLE, BCHR). If you are encountering a blocking problem, you'll want to look into the wait stats. Again, not as easy with SQL 2000 but still doable.What does "combed through the transaction log snapshots" even mean? I'm not clear on what your DBA is doing to help you with this problem, but it doesn't sound like the DBA is looking at the right things.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-06-18 : 01:39:33
|
| steve14437, picking up on an earlier comment you made " believe the database is contained on a single drive, not several but don't know for certain" - have you had a chance to split out onto separate drives . i.e data , logs, tempdb . Also , check levels of IO stalls of other databases on the same drives.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
OLTP Performance issue