Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: Help.. simple query took 20+ seconds

    Hi all,

    can someone give me some advise on how to troubleshoot this.... thanks.

    Here is the situation:

    We have a server running nicely for the past 1 years. And just yesterday, things started to acting very slow.
    A simple query that will return one row:

    "select * from product where itemno = 1234 and visible =1"

    will take 20 second sometime 30! The weird thing is.. it only happens intermittently. The table also only contain 4000 rows. so its not even a big table. I am not sure if this is caused by lack of memory or what (256mb ram, p4 1ghz on this server).

    The system cpu usage % avg around 3-8%. but when that weird behavior started. All the other queries will wait for it to finished and then burst the cpu usage up to 100%. Afterward, it will level off and then the cycle repeat again.

    I'm just hoping if anyone can give me a few pointers as of where to troubleshoot. I've tried rebuild the index on that table but it didn't help. This weird behavior not only happen on this particular table, but also on different db within the same server. (note: they are small dbs only)

    p.s. here is the site that is running on this db, you'll see this weird delay after a few click on the pages.
    http://www.animepot.com

    Thanks you for your time,
    Derick

    forgot to mentioned, the sql server is 7.0

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Help.. simple query took 20+ seconds

    I don't have any really brilliant ideas, but I might check:

    1. Disk space
    2. Space limits on data file or log file
    3. interference with other processes running on the server
    4. run DBCC SHOWCONTIG on the affected table(s)
    5. check underlying network/connectivity (there are counters for bad packets that you can query directly)
    6. check your drive manager to be sure there are no disk errors
    7. check your applications and system error logs
    8. find a goat, a hibachi and a sharp knife and make a burnt offering

    regards,

    hmscott

    PS. if you are involved with law enforcement in any way, i was only kidding about the last one

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28

    Re: Help.. simple query took 20+ seconds

    Originally posted by derickc
    Hi all,

    can someone give me some advise on how to troubleshoot this.... thanks.

    Here is the situation:

    We have a server running nicely for the past 1 years. And just yesterday, things started to acting very slow.
    A simple query that will return one row:

    "select * from product where itemno = 1234 and visible =1"

    will take 20 second sometime 30! The weird thing is.. it only happens intermittently. The table also only contain 4000 rows. so its not even a big table. I am not sure if this is caused by lack of memory or what (256mb ram, p4 1ghz on this server).

    The system cpu usage % avg around 3-8%. but when that weird behavior started. All the other queries will wait for it to finished and then burst the cpu usage up to 100%. Afterward, it will level off and then the cycle repeat again.

    I'm just hoping if anyone can give me a few pointers as of where to troubleshoot. I've tried rebuild the index on that table but it didn't help. This weird behavior not only happen on this particular table, but also on different db within the same server. (note: they are small dbs only)

    p.s. here is the site that is running on this db, you'll see this weird delay after a few click on the pages.
    http://www.animepot.com

    Thanks you for your time,
    Derick

    forgot to mentioned, the sql server is 7.0
    I looked at a similar problem a few weeks ago. A small db appl had ran fine for a few months, then slowed down all of a sudden. The problem was that statistics had never been updated on the tables.

    Have you updated statistics on these tables recently?

    UPDATE STATISTICS tablename

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •