MS SQL Server - Users complaining of slowness

Author

Users complaining of slowness

fnadal via SQLMonster.com

2006-12-13, 7:12 pm

Users are entering and saving orders through an application that writes to
the database (sql server 2000 enterprise edition). Around 3:00pm users
complain of slowness. It usually takes 3-4 seconds to save an order, now they
are complaining that it takes over a minute. Also the spid counts go up from
about 10 to 40. This has been happening for the last 4 days.

There are no blocking issues, cpu isn't off the chart. I've ran traces
(mstracelight) and didn't find anything out of the ordinary.

When this type of thing happens its usually something running that doesn't
show up on sysprocesses or any other trace.

How can I nail down the culprit? We also use diagnostic manager to look at
stats and overhead.

Any advice would be wonderful, thanks in advance.

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200612/1

Uri Dimant

2006-12-13, 7:12 pm

I'd run SQL Server Profiler to identify a long running queries (group by
Duration event) and start to investigate them.
Have you checked locking as well as blocking ?

"fnadal via webservertalk.com" <u10790@uwe> wrote in message
news:6ab458346660a@u
we...
> Users are entering and saving orders through an application that writes to
> the database (sql server 2000 enterprise edition). Around 3:00pm users
> complain of slowness. It usually takes 3-4 seconds to save an order, now
> they
> are complaining that it takes over a minute. Also the spid counts go up
> from
> about 10 to 40. This has been happening for the last 4 days.
>
> There are no blocking issues, cpu isn't off the chart. I've ran traces
> (mstracelight) and didn't find anything out of the ordinary.
>
> When this type of thing happens its usually something running that doesn't
> show up on sysprocesses or any other trace.
>
> How can I nail down the culprit? We also use diagnostic manager to look at
> stats and overhead.
>
> Any advice would be wonderful, thanks in advance.
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200612/1
>

John Bell

2006-12-13, 7:12 pm

Hi

As well as Uri's suggestion, you have not said what maintenance is run on
the database. If this always occurs at the same time have you checked the
scheduled tasks? If your database is set to autogrow when does it do it?

John

"fnadal via webservertalk.com" wrote:

> Users are entering and saving orders through an application that writes to
> the database (sql server 2000 enterprise edition). Around 3:00pm users
> complain of slowness. It usually takes 3-4 seconds to save an order, now they
> are complaining that it takes over a minute. Also the spid counts go up from
> about 10 to 40. This has been happening for the last 4 days.
>
> There are no blocking issues, cpu isn't off the chart. I've ran traces
> (mstracelight) and didn't find anything out of the ordinary.
>
> When this type of thing happens its usually something running that doesn't
> show up on sysprocesses or any other trace.
>
> How can I nail down the culprit? We also use diagnostic manager to look at
> stats and overhead.
>
> Any advice would be wonderful, thanks in advance.
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200612/1
>
>

ChrisR

2006-12-13, 7:12 pm

Maybe it's just the busiest time of day, which may explain the spid count.
You said that you ran a trace, are stored procs taking longer during this
time than they normally do? After all, this may not be a DB issue. Perhaps
some goofy activity is occuring at that time, and SQL Server is not at all
the culprit.

My general rule of thumb for this scenario (beyond what you've already
looked for) is to examine how long queries are taking. Most of the time that
can rule SQL Server in/ out as being the problem.

"fnadal via webservertalk.com" <u10790@uwe> wrote in message
news:6ab458346660a@u
we...
> Users are entering and saving orders through an application that writes to
> the database (sql server 2000 enterprise edition). Around 3:00pm users
> complain of slowness. It usually takes 3-4 seconds to save an order, now
they
> are complaining that it takes over a minute. Also the spid counts go up
from
> about 10 to 40. This has been happening for the last 4 days.
>
> There are no blocking issues, cpu isn't off the chart. I've ran traces
> (mstracelight) and didn't find anything out of the ordinary.
>
> When this type of thing happens its usually something running that doesn't
> show up on sysprocesses or any other trace.
>
> How can I nail down the culprit? We also use diagnostic manager to look at
> stats and overhead.
>
> Any advice would be wonderful, thanks in advance.
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200612/1
>

Roy Harvey

2006-12-13, 7:12 pm

What is the typical life span for a spid when this problem is not
happening? If it is very brief - seconds - then a general slowdown
would result in more spids. If it is longer - say ten minutes - then
the larger number of spids would tend more to point to a daily peak of
activity around that time.

Another area worth looking into is the network. If packets start
getting lost the delays can add up quickly. I have heard stories of
network problems that only occur at specific times, or on specific
days. Sometimes that is when one system is turned on and added to the
network, or some piece of machinery is powered on (or off) somewhere.

Roy

On Wed, 13 Dec 2006 15:28:17 GMT, "fnadal via webservertalk.com"
<u10790@uwe> wrote:

>Users are entering and saving orders through an application that writes to
>the database (sql server 2000 enterprise edition). Around 3:00pm users
>complain of slowness. It usually takes 3-4 seconds to save an order, now they
>are complaining that it takes over a minute. Also the spid counts go up from
>about 10 to 40. This has been happening for the last 4 days.
>
>There are no blocking issues, cpu isn't off the chart. I've ran traces
>(mstracelight) and didn't find anything out of the ordinary.
>
>When this type of thing happens its usually something running that doesn't
>show up on sysprocesses or any other trace.
>
>How can I nail down the culprit? We also use diagnostic manager to look at
>stats and overhead.
>
>Any advice would be wonderful, thanks in advance.

fnadal via SQLMonster.com

2006-12-13, 7:12 pm

I checked for locking and blocking issues during this time, and there are
none. That's what is killing me. The database looks fine.

I'll run a trace for long queries and check for network problems at that time.

There wouldn't be any rogue processes that would not normally show up using
this statement would there? I keep thinking its a process thats running that
I can't see through normal database investigation.

select spid, loginame,program_nam
e,cpu,blocked, physical_io, memusage,
open_tran, login_time, last_batch,* from master..sysprocesses where kpid > 0
order by cpu desc

Uri Dimant wrote:[color=darkred
]
>I'd run SQL Server Profiler to identify a long running queries (group by
>Duration event) and start to investigate them.
>Have you checked locking as well as blocking ?
>
>[quoted text clipped - 14 lines]

--
Message posted via http://www.webservertalk.com

fnadal via SQLMonster.com

2006-12-13, 7:12 pm

Typical life span for this problem when its not happening is a few seconds.

Roy Harvey wrote:[color=darkred
]
>What is the typical life span for a spid when this problem is not
>happening? If it is very brief - seconds - then a general slowdown
>would result in more spids. If it is longer - say ten minutes - then
>the larger number of spids would tend more to point to a daily peak of
>activity around that time.
>
>Another area worth looking into is the network. If packets start
>getting lost the delays can add up quickly. I have heard stories of
>network problems that only occur at specific times, or on specific
>days. Sometimes that is when one system is turned on and added to the
>network, or some piece of machinery is powered on (or off) somewhere.
>
>Roy
>
>[quoted text clipped - 12 lines]

--
Message posted via http://www.webservertalk.com

Sponsored Links