Database Performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco Pais
Usually the causes are bad design, poorly written queries, and
poor/inadequate indexing. BOL has a lot of trouble-shooting information
under the topic "Troubleshooting". You probably want to look at blocking
first.
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:ebcvgSTBFHA.3644@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>
|||There are lots of things it can be and you need to narrow down the
possibilities. Blocking does indeed sound like a good possibility. Try
running sp_who2 when you have multiple users in the slow state and see if
any show up with a spid in the Blcked by column. These articles should help
to get you going in the right direction.
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:ebcvgSTBFHA.3644@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
> SQL Server 2000 Database. The server that holds SQL Server has 2.8 GB of
> RAM memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
> think that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>