SQL server 2000 sometiems it's very slow

Posted on 2005-05-09
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

Question by:TranTO
    LVL 23

    Accepted Solution

    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?)
    LVL 11

    Expert Comment

    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.
    LVL 8

    Expert Comment

    is there a job running?
    - backup?
    - DTS
    - database/log file growing?
    - ...
    LVL 19

    Expert Comment

    by:Melih SARICA
    is ur indexes uptodate?

    is ur indexes created while upsizing ?


    Author Comment

    Hello everyone

    Thanks for all the suggestion, will try and keep updated


    Expert Comment

    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.

    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

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    754 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

    23 Experts available now in Live!

    Get 1:1 Help Now