SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 OLTP Performance issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

steve14437
Starting Member

4 Posts

Posted - 06/03/2011 :  14:16:46  Show Profile  Reply with Quote
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 - 06/03/2011 :  14:18:24  Show Profile  Reply with Quote
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."
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 06/04/2011 :  03:06:27  Show Profile  Visit jackv's Homepage  Reply with Quote
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 delays
4)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 check

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

steve14437
Starting Member

4 Posts

Posted - 06/13/2011 :  13:34:05  Show Profile  Reply with Quote
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.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 06/13/2011 :  14:50:26  Show Profile  Visit jackv's Homepage  Reply with Quote
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 bottlenecks

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 06/17/2011 :  17:07:01  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37127 Posts

Posted - 06/17/2011 :  17:38:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 06/18/2011 :  01:39:33  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000