SQL Server 2000 (Performance Trace)

Microsoft SQL Server 2000
SQL Profiler
SQL Server performance
Hi All, I have a job which is running since last 6 months. The job was taking around 2-3 hrs to be completed. However, for the last couple of weeks, its taking more than required time (Max-26 hours)and we have to kill the job for users to acces the database. I am not able to find any clue to it. I have used SQL Profiler to trace the server database activities. I do not have much idea to analyze the data. Pls. suggest. I would really appreciate ant kind of help. Thanks in Advance

Answer Wiki

Thanks. We'll let you know when a new response is added.

A job that ran successfully (2-3hrs) for the last six months.
BUT, a couple of weeks ago it started failing to complete in a timely fashion. Requiring crashing to access the database.

1st – what did you or one of your DBA do to the system in the last 30 days? If you roll back to a stable setup on your test machine does the problem go away.

You have three problems –
A – whatever that program was it required exlcusive control of the database. BAD programming. SQL Server is designed to service multiple requests by default. A program that directly ties up the database is always a scary concept. Even backup can get around other users.
B – Since this problem is now a couple of weeks old, how good are your backups and can you replicate this problem on your test system. (You do have a test system as weel as the productions server!)
C – In reverse order of probability the problem will be found to be:
A patch to the operating system.
A patch to SQL Server.
A patch or update to some other application on the server
A change to the program in question.

Good luck

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • FerencMantfeld
    Concur with Howard's response: Something has changed. I had something similar when the database files were moved to a super-fast SAN. When the datbase files were on server local storage (simple RAID 10 config) a particular query ran in 2 - 3 minutes. Then on the super-fast SAN, the same query ran in about 50 minutes. After much tweaking of the SAN and the SAN subnet, we moved the database files back to local server storage and we were back to the 400,000 files in a single directory. that is because Windows does not have clue about inodes, the real way to do file structures). Last but not least, if none of this yiels a result, find someone to blame for it. :) Cheers: Ferenc
    0 pointsBadges:
  • Jeffbane
    The profiler may be a good start. Have any table sizes grown? A change in table size may change the execution plan. Are all the queries running slow or are certain queries running slow? Look for suspects and then look at their execution plan to see if the plan is using the most optimum methods.
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: