Performance and Detection

I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application whic
h
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the serve
r
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:

> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>
>|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.

> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !