MS SQL Server - SQL Server Performance problem

Author

SQL Server Performance problem

Francois Malgreve

2005-06-29, 9:23 am

Hi all,

I have 2 SQL server DBs on production both have the same kind of DBs having
about the same size. The first one, let's call it Server A performs well and
the CPU usage is always around 50%. For the second one (server B), the CPU
usage is always over 90%.
I noticed 2 main differences between the 2 servers:
Server A does much more read operations than writes operations (80% read,
20% writes) while Server B does slightly more writing operations than
reading (60% write, 40% read).
An other main difference is that Server A has about 100 processes running
while Server B has about 700 processes (Process info node in the Enterprise
Manager). The status of most of those process are sleeping, while some are
either background, dormant, runnable. 90% of the sleeping processes have the
application field set to .Net SqlClient Data Provider.

Well, with that I have 2 questions:

1. What is a process and when is it created? What is the lifecycle of a
process? Does having so many processes running in the same time degrade
performance - even most of them seem to be sleeping?
2. How can i track what part of my SQL server needs to be tuned? For exemple
about the extremely high ratio of write, is it possible to see in what
tables those write are made? WQith that I could have a look at tweaking the
concerned tables, index, sql statements.

Thanks a lot.

Francois Malgreve

Ravi

2005-06-29, 9:23 am

1. What is a process and when is it created? What is the lifecycle of a
process? Does having so many processes running in the same time degrade
performance - even most of them seem to be sleeping?
ave it open. Performance degrade is possible, each open connection will take resource (memory ..) in the .NET side

2. How can i track what part of my SQL server needs to be tuned? For exemple
about the extremely high ratio of write, is it possible to see in what
tables those write are made? WQith that I could have a look at tweaking the
concerned tables, index, sql statements.

Use SQL profiler to trace the sql statements from the application

Hope this helps

--
Thanks
Ravi

"Francois Malgreve" wrote:
[color=darkred]
> Hi all,
>
> I have 2 SQL server DBs on production both have the same kind of DBs having
> about the same size. The first one, let's call it Server A performs well and
> the CPU usage is always around 50%. For the second one (server B), the CPU
> usage is always over 90%.
> I noticed 2 main differences between the 2 servers:
> Server A does much more read operations than writes operations (80% read,
> 20% writes) while Server B does slightly more writing operations than
> reading (60% write, 40% read).
> An other main difference is that Server A has about 100 processes running
> while Server B has about 700 processes (Process info node in the Enterprise
> Manager). The status of most of those process are sleeping, while some are
> either background, dormant, runnable. 90% of the sleeping processes have the
> application field set to .Net SqlClient Data Provider.
>
> Well, with that I have 2 questions:
>
> 1. What is a process and when is it created? What is the lifecycle of a
> process? Does having so many processes running in the same time degrade
> performance - even most of them seem to be sleeping?
> 2. How can i track what part of my SQL server needs to be tuned? For exemple
> about the extremely high ratio of write, is it possible to see in what
> tables those write are made? WQith that I could have a look at tweaking the
> concerned tables, index, sql statements.
>
> Thanks a lot.
>
> Francois Malgreve
>
>
>

Sponsored Links