Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    20

    Unanswered: Process Info: # of connnections

    I have a SQLServer that gets accessed by some medical software through Citrix. So there are about 100 users at any point in time,but I can see 650+ Processes in Current Activity->Process Info in Enterprise Manager.

    The company is complaining that if they dont reboot their SQL Server everyday it gets too slow to use. Does the # of Processes have any relation to their preformance issues?

  2. #2
    Join Date
    Sep 2004
    Posts
    20
    Doesn't anybody know?

  3. #3
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    surely the number of active process (and so of active connection) will impact on performace...if a connection is open it is probably serving an application that is querying the server.

    With the information you give, its quite impossibile to say that is SQL Server the cause of the slowdown or not.

    You need to check your entire application, not only SQL Server. Is the application using stored procedure? someone has used cursors? Is the database split across multiple discs? Is the network functioning correctly?

    There are so many possibile bottlenecks that you need - first of all - to figure what bottleneck you have, and then optimize.
    Davide Mauri
    http://www.davidemauri.it

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    DBCC OPENTRAN ('<database_name>') will show you if there are any open transactions in a database. Their presence may be the reason that the server resources are being depleted.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Sep 2004
    Posts
    20
    Thank you for the input.
    aobut 90% or the 650 connections are sleeping, they could be unclosed ODBC connections. Will they have an impact on SQL performance?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It is >possible< that you have a connection leak. If this is a third party product, I would think this sort of thing would have been detected by one of the first few customers. But then, I am silly that way. Can it cause a problem, yes. The amount of memory that SQL Server has to manage for connections will get a bit heavy (I think it is on the order of 80Kb per connection). Does that mean you should go on a SPID Chainsaw Massacre? Maybe not. Check on the last_batch (time the last query finished) values and compare to the Login_time values in sysprocesses (might be displayed in EM, but I am too lazy to check). If you see large discrepancies in these two times, then you might have to go on a killing spree. But before you do something you may regret, do some testing to see what the effect is going to be on your users and your support agreements with your vendor.

Posting Permissions

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