Performance question - Restarting the SQL server resolved my timeout problem

We have an ASP application installed on one powerful Win 2003 and
the SQL Server on another Win 2003.

We have two copies of the application (identical, one for production
and one for test purposes) that are operational. The 2nd application
is connected to the same SQL Server but a test copy of the production
database.

Today, running the application, a place where it shows a list of records,
the ASP application was timing out after 30 seconds for a lot of users.

I backed up the production db and restored it over the test db and tested
the ASP application; within 10-15 seconds the ASP page loaded the list
of the records, so i didn't get any timeout. So i started to think that
maybe
the test db when it was being restored, SQL Server would do some kind of
data cleanup and defragment the new db.

But, then i checked the Task Manager, SQL Server was almost using 1.2GB
of memory and the server had only 100megs available.

As soon as i restarted the SQL Server, the ASP page did no longer timeout
on the production database.

So now i am trying to figure out why the SQL Server restart fixed my
problem?

Unfortunately i did not check if there were a lot of connections in the
Process info
under the Current Activity in SQL EM.

Do you think there were a lot of sessions that weren't killed by the
application?
And that was causing the timeout? But why only for the Production db and not
also
for the test db?

I mean, i am puzzled as to why before restarting the SQL Server, running the
ASP
application connected to the TEST database did not time out!

As always, I appreciate any feedback, comments.

Thank you

I've the same problem.

"serge" <sergea@.nospam.ehmail.com> ha scritto nel messaggio
news:x70cd.60064$3C6.2287125@.news20.bellglobal.com ...
> We have an ASP application installed on one powerful Win 2003 and
> the SQL Server on another Win 2003.
> As always, I appreciate any feedback, comments.
> Thank you

On one machine, sometimes my VB applications get timed out.
The only solution to avoid this is to shut down all the applications running
on the DB,
Stopping the SQL Server service and then restarting it.
After this operation, the applications start to run fine.
I'm sure I've no connection or server side recordset open that could use for
memory leak since I've tested software monitoring every operations and do
not leave any recordset or connection open.
I always destroy them, even in case of system or application errors, there
is always the on error statement calling for the recordset and connection
destruction (the set cn (or rs) = Nothing).
I do not know why, but I suppose is due to ..... lack of memory on the
server (I've to use a 256 MB computer as SQLServer).
Any idea would be very usefull also for me.

Performance Problems Question

a lot of our users get Sql Server timeout errors from what appear to be
pretty basic queries run from our web app. the server should be well
equipped hardware wise to deal with server load (dual proc xeon with nearly
4 gb ram raid 5). concurrent user connections average around 300; it handles
an average of 4 transactions a second. running the queries themselves via QA
in some cases takes an extremely long time. otehr times, they run in a
timely fashion.
I notice on perfmon a something that raised an eyebrow - the database is
spread over 2 filegroups, primary is about 4.5GB, secondary index filegroup
is about 2.3 GB. but we are getting very high disk queue values - averaging
50 over an hour or so.
While I know that as general rule of thum the threshold for disk queue is 2
* physical drives, 2 of the drives are hardly used. One drive contains only
backups, the second contains Diskeeper files, the 3rd is the OS drive. In
theory, only 2 of the drives are being written to during normal database
usage.
My question - is THIS normal (can I rule out the queue as a bottleneck),
given that we arent separating the filegroups over the RAID?I notice the system gets tweaked during Page Reads... probably indexes then?
"Ick" <nospamplease> wrote in message
news:uFZMAsphDHA.4088@.tk2msftngp13.phx.gbl...
> a lot of our users get Sql Server timeout errors from what appear to be
> pretty basic queries run from our web app. the server should be well
> equipped hardware wise to deal with server load (dual proc xeon with
nearly
> 4 gb ram raid 5). concurrent user connections average around 300; it
handles
> an average of 4 transactions a second. running the queries themselves via
QA
> in some cases takes an extremely long time. otehr times, they run in a
> timely fashion.
> I notice on perfmon a something that raised an eyebrow - the database is
> spread over 2 filegroups, primary is about 4.5GB, secondary index
filegroup
> is about 2.3 GB. but we are getting very high disk queue values -
averaging
> 50 over an hour or so.
> While I know that as general rule of thum the threshold for disk queue is
2
> * physical drives, 2 of the drives are hardly used. One drive contains
only
> backups, the second contains Diskeeper files, the 3rd is the OS drive. In
> theory, only 2 of the drives are being written to during normal database
> usage.
> My question - is THIS normal (can I rule out the queue as a bottleneck),
> given that we arent separating the filegroups over the RAID?
>
>

Performance problems

Hi,
I have an OLTP database and the users get timeout messages
in the mornings when they initially start the application
and it is fine for the rest of the day.I have looked into
all other applications and users and found that this
application is only the problem.I have been monitoring the
performance monitor and found out that there are some
latches at this time and the avg disk queue length is very
high during this time.It only happens to a couple of
procedures in the whole application .
I am running SQL 2000 Ent with cluster,4 CPU and 6Gb of
RAM.
Any help is really appreciated.
Thanks
SatAre backups happening at that time ?
Greg Jackson
PDX, Oregon|||I woudl consider using SQL Profiler to see what else is happening at this
time.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:6fc101c40228$07372b60$a301280a@.phx.gbl...
> Hi,
> I have an OLTP database and the users get timeout messages
> in the mornings when they initially start the application
> and it is fine for the rest of the day.I have looked into
> all other applications and users and found that this
> application is only the problem.I have been monitoring the
> performance monitor and found out that there are some
> latches at this time and the avg disk queue length is very
> high during this time.It only happens to a couple of
> procedures in the whole application .
> I am running SQL 2000 Ent with cluster,4 CPU and 6Gb of
> RAM.
> Any help is really appreciated.
> Thanks
> Sat
>
>|||The backups are not running and i have eliminated other users possibility al
so.I have tried running profiler earlier and the
users are having some problems(performance) even if i run on a workstation.A
ny other help is appreciated.But i will try to
run the profiler again.
Thanks
Sat|||Sorry, I don't have an answer, only some questions that you might want to as
k yourself.
Are you sure the problem IS the database ?
You say this this problem only happens to one certain application, but not o
thers.
Are the other applications connect to the same SQL Server node?
Do they all have the same architeture (ASP, COM+, thick clients or thin clie
nts). ?
Is the time out on the database, or is it the application? So, if you have u
sing object pooling, the problem might be a time-out waiting for the COM obj
ect to be created in the pool.
Or if using database connection pooling, this might be the problem. If conne
ction pooling is being used, you might be able to do a couple of actions, an
d then a free connection isn't avalaible for the next transaction, so you ge
t the time out.
The high disk queue length; I take it this is on the disk array with the dat
abases on. What else is on disk array, or is it only the DB.
Has the node been running some memory heavy job in the evening, not related
to the SQL Server ? What is the SQL Server memory allocation in the morning
before they start login in ? What is the Buffer Cache Hit Ratio at this time
of day ?sql

Performance problems

Hi,
I have an OLTP database and the users get timeout messages
in the mornings when they initially start the application
and it is fine for the rest of the day.I have looked into
all other applications and users and found that this
application is only the problem.I have been monitoring the
performance monitor and found out that there are some
latches at this time and the avg disk queue length is very
high during this time.It only happens to a couple of
procedures in the whole application .
I am running SQL 2000 Ent with cluster,4 CPU and 6Gb of
RAM.
Any help is really appreciated.
Thanks
SatAre backups happening at that time ?
Greg Jackson
PDX, Oregon|||I woudl consider using SQL Profiler to see what else is happening at this
time.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:6fc101c40228$07372b60$a301280a@.phx.gbl...
> Hi,
> I have an OLTP database and the users get timeout messages
> in the mornings when they initially start the application
> and it is fine for the rest of the day.I have looked into
> all other applications and users and found that this
> application is only the problem.I have been monitoring the
> performance monitor and found out that there are some
> latches at this time and the avg disk queue length is very
> high during this time.It only happens to a couple of
> procedures in the whole application .
> I am running SQL 2000 Ent with cluster,4 CPU and 6Gb of
> RAM.
> Any help is really appreciated.
> Thanks
> Sat
>
>|||The backups are not running and i have eliminated other users possibility also.I have tried running profiler earlier and th
users are having some problems(performance) even if i run on a workstation.Any other help is appreciated.But i will try t
run the profiler again
Thank
Sa|||Sorry, I don't have an answer, only some questions that you might want to ask yourself
Are you sure the problem IS the database
You say this this problem only happens to one certain application, but not others
Are the other applications connect to the same SQL Server node?
Do they all have the same architeture (ASP, COM+, thick clients or thin clients).
Is the time out on the database, or is it the application? So, if you have using object pooling, the problem might be a time-out waiting for the COM object to be created in the pool.
Or if using database connection pooling, this might be the problem. If connection pooling is being used, you might be able to do a couple of actions, and then a free connection isn't avalaible for the next transaction, so you get the time out
The high disk queue length; I take it this is on the disk array with the databases on. What else is on disk array, or is it only the DB
Has the node been running some memory heavy job in the evening, not related to the SQL Server ? What is the SQL Server memory allocation in the morning before they start login in ? What is the Buffer Cache Hit Ratio at this time of day ?