Intermittent performance problems

  • Hello

    Our organisation is near the end of a rollout plan, switching sites from one key application to another, three every fortnight, forty sites total. Old and new apps both talk to SQL Server 2000. Forty different db's for the old app, one db for the new app.

    We've recently noticed fairly modest periods of time (10 - 30 mins, once or twice a day) when performance is so poor that users may be unable to even log in to the system before a timeout occurs. Now, there are several key areas which are currently under investigation: Websphere, the network, Java runtimes - all of these have been tweaked and although general performance has improved considerably, we continue to suffer short periods of near-downtime.

    We're not confident that our third-party SQL Server support company are up to the task of identifying any issues, whether SQL-related or impacting on the SQL servers - recently we had 2 hours outage while they figured out what to do with a full transaction log. And since we don't yet know if the performance dips are anything to do with SQL Server, and we don't have a tame local DBA, I've been asked to help.

    I've been keeping an eye on locking / blocking, disk activity etc in EM's "current activity" and playing with the Profiler. But I'm a programmer with no previous experience of this nature of work and while I quite enjoy playing with the new toybox and learning new stuff, time passes by and we still have no resolution. I reckon there must be a fair few of you out there for which this kind of work, and this nature of problem, are your bread and butter.

    Given that I have a full-time job already and can spend only limited time on this, is it feasible? If so, where should I start looking? The scenario is like this: one or two people in the office raise the shout that application performance is deteriorating. They don't really say it like this at all, they use lots of old Anglo-Saxon words which I don't understand. Someone then asks me to "look at the server".

    I've got reasonable access around the network, and I can log on to relevant servers as sa when I need to.

    All and any suggestions are welcome.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I would look at 2 things very quickly. Try to see if there is a AUTOGROW going on in the current activity window. Also, look to see if there are any BACKUPS going on at these times.

  • Many thanks for your prompt reply, Rich. Both checked and negative.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply