If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Help.. simple query took 20+ seconds

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

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

Re: Help.. simple query took 20+ seconds

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.