[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL server 2000 sometiems it's very slow

Posted on 2005-05-09
6
Medium Priority
?
172 Views
Last Modified: 2010-03-19
Hello everyone,

We recently converted all our data from Access to SQL server.
Apps seem to work fine, no major problem except for once in a while it's so slow to pull up a record. One of our apps running on 20,000 records database, when it's slow it takes a good 10 seconds as opposed to a usual 2 seconds just to pull up a record. In both cases, there is absolutely no other user on the network except me testing. So I really don't know where should I start to trobleshoot this.

So please anyone, any suggestions into how to fix this would be appreciated

Thanks
Tam
0
Comment
Question by:TranTO
6 Comments
 
LVL 23

Accepted Solution

by:
adathelad earned 750 total points
ID: 13958813
A starting point would be to monitor the DB using SQL Profiler - this can help identify slow running queries. You may have to leave it running a while until you experience another "slow spell".

Once you have identified a slow running query, it's time to start tuning the query (i.e. is it written efficiently? could it need indexes to run faster?)
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13958817
just some general things:

keep an eye on your "current activity" folder in enterprise manager,
see if there are any locks on the tables you're querying. if so, find out why.

try the queries you're executing in QA.
take a look at the execution plan. Does it differ when a long waiting time occurs?

if both of these don't help.
try the SQL Profiler to look at what exactly is going on at the sql server.
0
 
LVL 8

Expert Comment

by:tomvergote
ID: 13958882
is there a job running?
- backup?
- DTS
- database/log file growing?
- ...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 19

Expert Comment

by:Melih SARICA
ID: 13959003
is ur indexes uptodate?

is ur indexes created while upsizing ?

0
 

Author Comment

by:TranTO
ID: 13959275
Hello everyone

Thanks for all the suggestion, will try and keep updated

Tam
0
 

Expert Comment

by:mwrye
ID: 13968601
Is the Transaction logs on a separate physical disk as the data files? If they are not then that can also sometimes cause some performance issues.  

SQL Profiler (as 'adathelad' said) is where I would start.   Create a Baseline of normal processing to be compared to trace files that you take when you are having the performance issues.  

Don't forget though that sometimes running the profile trace can also slow down performance while it is running.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

872 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