Solved

SQL Server - Performance Very Sluggish

Posted on 2016-08-15
14
66 Views
Last Modified: 2016-10-10
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
Comment
Question by:Jimbo99999
  • 7
  • 6
14 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:Jimbo99999
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
If you need to know how to work with SQL Profiler read this article and the MSDN.
0
 

Author Comment

by:Jimbo99999
Comment Utility
Thanks...I will read the article and work on this today.
0
 

Author Comment

by:Jimbo99999
Comment Utility
It is really bad today so I am going to have to try and figure something out.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
jimbo, any feedback for us?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Jimbo99999
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:Jimbo99999
Comment Utility
I did not have them running.  I will start them today and see if we get lucky tomorrow.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Jimbo, any feedback for us?
0
 

Author Comment

by:Jimbo99999
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
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
 

Author Comment

by:Jimbo99999
Comment Utility
Ok, gotcha.  I understand now.  Thanks for the insight.  So we wait for the next time performance gets bad.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now