Performance questions on SQL Server 2K

Hello, all. We were directed by Mark Schupp in the ASP newsgroup
(microsoft.public.inetserver.asp.general) to post this here and see if
anyone has an answer. He thought it might be a problem with the SQL Server
and not the web server. The apps with problems hit our SQL Server 2K across
the network. We've had occasions where we get Timeout Expired errors in some
of our ASP apps, and non-ASP vendor apps on separate servers that hit the
SQL Server get into the same problem of hourglassing and not coming back.
When the admins reboot the SQL Server machine, it clears it up for awhile.
What we're wondering is this: Is it the web server, the network, or the SQL
Server that is creating this headache? During the times when we've had those
issues, we've immediately started running the Performance Monitor to see if
we can track the issue, with the following counters added:
- Request Wait Time (ASP)
- Requests Executing (ASP)
- Requests Timed Out (ASP)
- Pages/sec. (Memory)
- % Processor Time
We didn't see the above items spike during the times users were getting the
Timeout Expired errors. How can we look at one individual ASP application to
see if we can rule it out as the culprit? Also, how can we tell if this is
caused by the network or the database server?
Thanks.
SQL Server Profiler is your tool.
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:%23Dgw9kNnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Hello, all. We were directed by Mark Schupp in the ASP newsgroup
> (microsoft.public.inetserver.asp.general) to post this here and see if
> anyone has an answer. He thought it might be a problem with the SQL Server
> and not the web server. The apps with problems hit our SQL Server 2K
across
> the network. We've had occasions where we get Timeout Expired errors in
some
> of our ASP apps, and non-ASP vendor apps on separate servers that hit the
> SQL Server get into the same problem of hourglassing and not coming back.
> When the admins reboot the SQL Server machine, it clears it up for awhile.
> What we're wondering is this: Is it the web server, the network, or the
SQL
> Server that is creating this headache? During the times when we've had
those
> issues, we've immediately started running the Performance Monitor to see
if
> we can track the issue, with the following counters added:
> - Request Wait Time (ASP)
> - Requests Executing (ASP)
> - Requests Timed Out (ASP)
> - Pages/sec. (Memory)
> - % Processor Time
> We didn't see the above items spike during the times users were getting
the
> Timeout Expired errors. How can we look at one individual ASP application
to
> see if we can rule it out as the culprit? Also, how can we tell if this is
> caused by the network or the database server?
> Thanks.
>
>

Performance questions on SQL Server 2K

Hello, all. We were directed by Mark Schupp in the ASP newsgroup
(microsoft.public.inetserver.asp.general) to post this here and see if
anyone has an answer. He thought it might be a problem with the SQL Server
and not the web server. The apps with problems hit our SQL Server 2K across
the network. We've had occasions where we get Timeout Expired errors in some
of our ASP apps, and non-ASP vendor apps on separate servers that hit the
SQL Server get into the same problem of hourglassing and not coming back.
When the admins reboot the SQL Server machine, it clears it up for awhile.
What we're wondering is this: Is it the web server, the network, or the SQL
Server that is creating this headache? During the times when we've had those
issues, we've immediately started running the Performance Monitor to see if
we can track the issue, with the following counters added:
- Request Wait Time (ASP)
- Requests Executing (ASP)
- Requests Timed Out (ASP)
- Pages/sec. (Memory)
- % Processor Time
We didn't see the above items spike during the times users were getting the
Timeout Expired errors. How can we look at one individual ASP application to
see if we can rule it out as the culprit? Also, how can we tell if this is
caused by the network or the database server?
Thanks.SQL Server Profiler is your tool.
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:%23Dgw9kNnEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Hello, all. We were directed by Mark Schupp in the ASP newsgroup
> (microsoft.public.inetserver.asp.general) to post this here and see if
> anyone has an answer. He thought it might be a problem with the SQL Server
> and not the web server. The apps with problems hit our SQL Server 2K
across
> the network. We've had occasions where we get Timeout Expired errors in
some
> of our ASP apps, and non-ASP vendor apps on separate servers that hit the
> SQL Server get into the same problem of hourglassing and not coming back.
> When the admins reboot the SQL Server machine, it clears it up for awhile.
> What we're wondering is this: Is it the web server, the network, or the
SQL
> Server that is creating this headache? During the times when we've had
those
> issues, we've immediately started running the Performance Monitor to see
if
> we can track the issue, with the following counters added:
> - Request Wait Time (ASP)
> - Requests Executing (ASP)
> - Requests Timed Out (ASP)
> - Pages/sec. (Memory)
> - % Processor Time
> We didn't see the above items spike during the times users were getting
the
> Timeout Expired errors. How can we look at one individual ASP application
to
> see if we can rule it out as the culprit? Also, how can we tell if this is
> caused by the network or the database server?
> Thanks.
>
>

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 Question

Hi, I have a sql2000 server running and I have a asp.net app that accesses this server. At the moment I am using 2 different databases to get information from and such, one database I have a bit of control over the other I am not allowed to touch without special permissions due to the sesitivity of the data. The issue is that my app is not going very fast when it comes to database calls.

I need to index some tables but I am unsure what actually happens to the data when I do this, I have one table that has ~600,000 rows and that is alot to sort through as you can imagine. This table is on the sensitive database, so I am hesitant to touch it, but I really need a performance boost somehow, can anyone suggest something to help me solve my performance problem?

I have posted around on the asp.net forums but the help is limited and most people say 99% of the time its the database calls that are the problem.

Thanks for the time,
MarkIts hard to just suggest anything without knowing your physical and logical design, but if you have many sort operations, try to put indexes on the sorted columns. If you are joining a lot of data, do also put an index on the joined columns. 600.000 is not that much data, perhaps you might also have an issue on your physical design ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for the reply Jens, I am quite new to Indexes in SQL Server 2000, I suppose it is always best to use them no matter what database you have, but is there any risk to the data by putting them on? I just dont want to corrupt anything. And as far as I can tell, there are no indexes on any tables in the database that I am allowed to touch.

I should mention too, that I am using views to look onto the sensitive database, will this affect permormance?

thanks
Mark|||Actually there is no risks using indexes. If you use them on a separate physical storage location you can also put the load of the indexes from the database (but you don′t have to) The best way would be to evaluate the uqery plans of your queries fired against the database. They should tell you if data is retrieved by using cost extensive scans etc.

HTH, Jens Sü?meyer.

http://www.sqlserver2005.de|||Ok thats good advice thanks. When you say that I should evaluate the query plans, is that using the feature in the management studio that shows the execution plan of a given query? If so what should I be looking for? I dont really understand the details I am being shown

Thanks Jens|||Many sites already discussed about this topic, it would be too much describing the different symbols in here, but have a look on www.sqlserverperformance.com this iste can be a good starting point for finding out ways to tweak the performance of the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
sql