Application Performance

I'm not sure if this is the best forum to post this in (if not, if somebody
can suggest a good forum to post this question, I would appreciate it!)
I have a client with an application setup as follows:
(1) Web Server running Windows 2003 Standard Edition Server & IIS.
(1) Web Server also running Windows 2003 Standard Edition Server &
Microsoft SQL Server 2000
The database is large - the MDF file is approximately 2.5 Gig. There are
well over 100 tables and many tables have hundreds of thousands of records.
On the web server, we have two applications running that communicate with
the database. One is written in ASP - this is used by their customers and
has a good amount of activity during business hours. The other is written
in ASP.NET/VB.NET 1.1 and is used by their internal staff to manage their
business. This side is used heavily, especially during business hours.
When only a few people are on the system, it flys. i.e. first thing in the
morning before everybody has gotten into the office and started using the
system. It's very fast. But as time progresses, and more and more people
log on, the system (both the ASP system and the ASP.NET system) gets slower
and slower, to the point of timing out regularly. If we do an IISReset,
reboot the server, or do anything that essentially kills all the active
sessions, access to the system speeds up again - for a time - until
everybody has logged back on, when it starts dogging again.
I'm looking for any suggestions on how to go about optimizing the system.
At first we thought it was bandwidth to the system, but everything else on
the network seems to not have a problem - only this system. Should we
investigate a cluster with load balancing on the SQL Server side? Or maybe
it's our application itself. Maybe we are not handling those large record
sets in the most efficient manner? Mabye we need to change some settings
in the SQL Server database itself to make access to the database more
efficient. I'm not really sure where to start with this issue.
If anybody has any advice on where to start looking at this problem, or
what forum I should post this on, I would greatly appreciate it.
Thank you very much for your help!
Sincerely,
Matt SchwartzMatt,
First off clustering does not give you load balancing, it is a hardware fail
over solution only. I am a little confused as to what configuration you
actually have. Is it two different servers and only one has SQL Server or
are they both the same? In any case it is usually not a good idea to run
both the SQL Server and the web service on the same server as they compete
for resources. This is especially true of memory. How much memory does the
machine with SQL Server have and how have you configured the memory? If you
have not set the MAX memory setting to give the ASP stuff enough memory to
use they will constantly be fighting with each other for the memory. Here
are some links that should get you started to determine where the issues
are.
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Matt Schwartz" <matt@.nelix.com> wrote in message
news:Xns97CA88A7B151Dmattnelixcom@.216.196.97.142...
> I'm not sure if this is the best forum to post this in (if not, if
> somebody
> can suggest a good forum to post this question, I would appreciate it!)
> I have a client with an application setup as follows:
> (1) Web Server running Windows 2003 Standard Edition Server & IIS.
> (1) Web Server also running Windows 2003 Standard Edition Server &
> Microsoft SQL Server 2000
> The database is large - the MDF file is approximately 2.5 Gig. There are
> well over 100 tables and many tables have hundreds of thousands of
> records.
> On the web server, we have two applications running that communicate with
> the database. One is written in ASP - this is used by their customers and
> has a good amount of activity during business hours. The other is written
> in ASP.NET/VB.NET 1.1 and is used by their internal staff to manage their
> business. This side is used heavily, especially during business hours.
> When only a few people are on the system, it flys. i.e. first thing in
> the
> morning before everybody has gotten into the office and started using the
> system. It's very fast. But as time progresses, and more and more people
> log on, the system (both the ASP system and the ASP.NET system) gets
> slower
> and slower, to the point of timing out regularly. If we do an IISReset,
> reboot the server, or do anything that essentially kills all the active
> sessions, access to the system speeds up again - for a time - until
> everybody has logged back on, when it starts dogging again.
> I'm looking for any suggestions on how to go about optimizing the system.
> At first we thought it was bandwidth to the system, but everything else on
> the network seems to not have a problem - only this system. Should we
> investigate a cluster with load balancing on the SQL Server side? Or
> maybe
> it's our application itself. Maybe we are not handling those large record
> sets in the most efficient manner? Mabye we need to change some settings
> in the SQL Server database itself to make access to the database more
> efficient. I'm not really sure where to start with this issue.
> If anybody has any advice on where to start looking at this problem, or
> what forum I should post this on, I would greatly appreciate it.
> Thank you very much for your help!
> Sincerely,
> Matt Schwartz
>|||Thank you very much for your assistance! We have two servers - one is
running IIS, the other running SQL Server. As far as memory, There is 4
Gig on the SQL Server, but it's setup as the default installation.
I'll take a look at the articles you recommended. Thank you very much!
Sincerely,
Matt Schwartz
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:er9IyLRfGHA.1208@.TK2MSFTNGP02.phx.gbl:

> Matt,
> First off clustering does not give you load balancing, it is a
> hardware fail over solution only. I am a little confused as to what
> configuration you actually have. Is it two different servers and only
> one has SQL Server or are they both the same? In any case it is
> usually not a good idea to run both the SQL Server and the web service
> on the same server as they compete for resources. This is especially
> true of memory. How much memory does the machine with SQL Server have
> and how have you configured the memory? If you have not set the MAX
> memory setting to give the ASP stuff enough memory to use they will
> constantly be fighting with each other for the memory. Here are some
> links that should get you started to determine where the issues are.
>|||OK so it is not really a WEB server then, just a server with SQL Server on
it. If you are running Std Edition of SQL Server 2000 you can only use 2GB
anyway and the default config should work just fine. It is hard to say what
the issues can be since there is so little to go on. But those articles
should help to narrow it down some. Feel free to post any follow on
questions as a result of it.
Andrew J. Kelly SQL MVP
"Matt Schwartz" <matt@.nelix.com> wrote in message
news:Xns97CBB0EE525B6mattnelixcom@.216.196.97.142...[vbcol=seagreen]
> Thank you very much for your assistance! We have two servers - one is
> running IIS, the other running SQL Server. As far as memory, There is 4
> Gig on the SQL Server, but it's setup as the default installation.
> I'll take a look at the articles you recommended. Thank you very much!
> Sincerely,
> Matt Schwartz
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:er9IyLRfGHA.1208@.TK2MSFTNGP02.phx.gbl:
>