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 PaisTake a look here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
Yes it's very important, although it has nothing to do with
performance. If user applications connect as a system administrator
then security is non-existent - one rogue user, faulty piece of code or
malicious hack could destroy your database and your server.
--
David Portas
SQL Server MVP
--|||"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:OVwy8STBFHA.2012@.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.
This is almost certainly a code problem. It could be the application is
keeping locks for far longer than necessary. It could be your queries have
changed enough that your indices don't work as well.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
As David mentions, this is a HUGE security risk. Not only can users do
anything to the db, they can do anything to the OS.
With the XP_CMDSHELL command they can create domain users, add them to the
domain admin group and take over your system.
> What can I do to troubleshoot this problem?
Simplest, look at output of sp_who2 active and look for blocking. If it's
there consistently, that's your biggest problem. Eliminate it.
Use dbcc inputbuffer(spid) on the blocking spid to see what the code looks
like.
If this doesn't help, time to break out profiler.
> 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
>