Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

SQL Server - Performance Very Sluggish

Good Day Experts!

I hope you can lend some insight.  We have SQL Server 2000 running on our server(I know I know).  The problem of late is very sluggish performance.  It could be performing acceptable but then the next hour it is like something is choking the life out of it. We have about 45 Users at any time using a VB6 app with this server as the backend.  The Users could be doing anything from DataEntry to doing a lookup to running daily functions  against a DB.

I have looked around on the net and tried a few things looking for the performance issue...but they were not very friendly.  

Is there anything simple that I can run in QryAnalyzer, when performance is sluggish,  to tell me which app, user, connections etc are causing the issue?

Thanks,
jimbo99999
0
Jimbo99999
Asked:
Jimbo99999
  • 7
  • 6
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
run sp_Who2 and see for any blocks ? I am not sure whether sp_WhoIsactive works in SQL 2000 (you can google it and download it ) .. if there is a block you can note the SPID, and pass it to DBCC INPUTBUFFER(<spid>) to  get the sql statement that's causing blocking.
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server Profiler was already available for SQL Server 2000. Did you try to run a trace to capture the locks and long running queries?
0
 
Jimbo99999Author Commented:
Thank you for replying.  I have not run anything yet.  I am very light on this kind of debugging knowledge.  I will try your suggestions.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you need to know how to work with SQL Profiler read this article and the MSDN.
0
 
Jimbo99999Author Commented:
Thanks...I will read the article and work on this today.
0
 
Jimbo99999Author Commented:
It is really bad today so I am going to have to try and figure something out.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
jimbo, any feedback for us?
0
 
Jimbo99999Author Commented:
Hello there...

We experienced it again this morning.  I checked for locks.  I am still searching for a way to tell me the connections at any given time using a query.  I don't know when this is going to happen each time.

Thanks,
jimbo99999
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You had the Profiler running?
Without the traces, only place you can check for some information is SQL Server log and Windows Event logs.
0
 
Jimbo99999Author Commented:
I did not have them running.  I will start them today and see if we get lucky tomorrow.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Jimbo, any feedback for us?
0
 
Jimbo99999Author Commented:
Hello There

We have been operating without any issues since my last posting.  Do I just keep that trace window open and running until it happens again?

Thanks,
jimbo99999
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do I just keep that trace window open and running until it happens again?
Better not. It has some impact in the performance since it requires some SQL resources. Save the trace definition and next time it happens you just run again the trace.
0
 
Jimbo99999Author Commented:
Ok, gotcha.  I understand now.  Thanks for the insight.  So we wait for the next time performance gets bad.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now