Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2006
    Posts
    17

    Question Unanswered: Need to Refresh (Killing is not an Option)

    Hi Peepz! my problem is this i am managing more than 3 servers which has a many users. this servers have one common problem. the most users uses high cpu utilizations what make it worst is even if the process is already done for a long time (status = sleep) they still uses high cpu or IO utilization. One time i ask to confirm one user if they are really having that process and found out that the user have gone home already and no other is using thier computer. And assuming that we have more than 20 users with the same case it really make the server slow and occationally hangup. i try to kill these process/user but i think killing is not an option. Kindly help pls. are there any way to refresh connections or terminate it, how do you handle this situations?


    Thanks,
    Keez.


    If you give me a fish ill eat for a day but if you teach me how to fish ill eat for life.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am going to assume you are looking at the cpu/physical_io numbers in either the process listing in Enterprise Manager, or in the output of sp_who2. Either way, these numbers are cumulative over the life of the connection. If a connection displays 1,000 for CPU, they may have run a single query that took up 1000 ticks of CPU time, or 10 queries that took up 100 ticks each. Either way, if the numbers are not increasing, the connection is idle, and does no real harm.

  3. #3
    Join Date
    Oct 2006
    Posts
    17
    Quote Originally Posted by MCrowley
    I am going to assume you are looking at the cpu/physical_io numbers in either the process listing in Enterprise Manager, or in the output of sp_who2. Either way, these numbers are cumulative over the life of the connection. If a connection displays 1,000 for CPU, they may have run a single query that took up 1000 ticks of CPU time, or 10 queries that took up 100 ticks each. Either way, if the numbers are not increasing, the connection is idle, and does no real harm.
    ok.. but what if i have more than 20 processes having more than 100k cpu utiliztion. even if they are idle is that ok? will that degrade the performance of the server.? Assuming that is ok but since we have a lot of users we really need to refresh the connection or remove idle connection becoz the server would really hungup if they try to connect simultaneously. any idea to do this?


    Thanks alot

  4. #4
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    How old are those processes?

    My "SQL Agent - Alert Engine" 1,248,674 CPU seconds but it started on 9/21/2006.

    I am curious; if you look at the Windows Task Manager, is SQL Server the main culprit?

    What else is on the server? If you have Panda or Symantec / Norton Antivirus, they are often CPU hogs. Also HP printer software can kill a server (not the usually the print driver itself, but the stupid tool they distribute on the CD). Of course; if you surf the net from your Windows Server, it is possible to become infected with Spyware or a Virus that can kill the system.

    Are you patched against the Slammer virus (SQL Server 2000 service pack 3a I believe)?

  5. #5
    Join Date
    Oct 2006
    Posts
    17
    Quote Originally Posted by vich
    How old are those processes?

    My "SQL Agent - Alert Engine" 1,248,674 CPU seconds but it started on 9/21/2006.

    I am curious; if you look at the Windows Task Manager, is SQL Server the main culprit?

    What else is on the server? If you have Panda or Symantec / Norton Antivirus, they are often CPU hogs. Also HP printer software can kill a server (not the usually the print driver itself, but the stupid tool they distribute on the CD). Of course; if you surf the net from your Windows Server, it is possible to become infected with Spyware or a Virus that can kill the system.

    Are you patched against the Slammer virus (SQL Server 2000 service pack 3a I believe)?

    the process are just a day or two old and around 100k up cpu seconds, no its not the server.

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    You say 3 servers. Do you have a distributed database or do they each run their own instance? Are they replications of the same thing?

    It's just so odd to have 3 servers slow down like that unless it's an infection either on the servers or the workstations that are pounding them.

    Have you been able to monitor the CPU utilization (and network traffic) for the workstations? If they're all running the same application, maybe some genious programmer distributed a looping program.

    We had a similar thing recently and it turned out to be 1 user and Symantec Antivirus was stuck in some kind of loopback. We're a small shop and can't afford a real Sniffer, and our switch wasn't reporting the excessive traffic. We happened to power off that PC and BINGO, the extreem slowdowns we were having for 3 days suddenly disappeared.

    That user had the slowest PC in the building but that just exasporated the problem. When we removed Symantec from anyone's machine, ... problem gone (for them). Remove that PC and problem gone, for everyone (including the servers).

    Your problem is different since multiple users are pounding the SQL process on multiple servers - and the problem started occurring simultaneously. That should offer some clues right there.
    Last edited by vich; 10-12-06 at 04:44.

  7. #7
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    my dear friend, tell me why do you need all the connections open all the time?
    In GOD we believe. Everything else we Test!

  8. #8
    Join Date
    Oct 2006
    Posts
    17
    Quote Originally Posted by vich
    Do you have a distributed database or do they each run their own instance?
    Are they replications of the same thing?
    They have different instances. they are not replications althought we have that as a reserved server incase a downtime occur on the production server.

    Quote Originally Posted by vich
    It's just so odd to have 3 servers slow down like that unless it's an infection either on the servers or the workstations that are pounding them.

    Have you been able to monitor the CPU utilization (and network traffic) for the workstations? If they're all running the same application, maybe some genious programmer distributed a looping program.
    Yea. ive been monitoring everything. ive been considering also the application that are being used probably they are not properly disconnecting the connection when it logout.

  9. #9
    Join Date
    Oct 2006
    Posts
    17
    Quote Originally Posted by wash
    my dear friend, tell me why do you need all the connections open all the time?

    Basically we have many users. atleast the resources would be distributed properly. if your not doing anything give your resources to others who realy need it. burden to the server would be avoided.

  10. #10
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Try to increase the virtual memory of your DB server machine to gain performance.

    is this a typical "Client-Server" app or Web App?
    In GOD we believe. Everything else we Test!

  11. #11
    Join Date
    Oct 2006
    Posts
    17
    Quote Originally Posted by wash
    Try to increase the virtual memory of your DB server machine to gain performance.

    is this a typical "Client-Server" app or Web App?
    Both. but the client server im having problem about.

  12. #12
    Join Date
    Sep 2004
    Location
    St. Louis
    Posts
    2
    Sudden high cpu usage can also be an indicator that statistics are out of date. How often do you update stats?

  13. #13
    Join Date
    Oct 2006
    Posts
    17
    Quote Originally Posted by davidwv
    Sudden high cpu usage can also be an indicator that statistics are out of date. How often do you update stats?
    how do you do that?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •