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 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 !
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 !