SQL Server Performance

Why are there so many processes running?

Discussion in 'Performance Tuning for DBAs' started by rpieszak, Jul 6, 2004.

  1. rpieszak New Member

    Greetings,
    When I look in Enterprise Manager, under Management/Current Activity/Process Info, there are many processes running, and there shouldn't be. All of them say "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" as their last tsql command batch. We're sporadically getting the message of "The maximum simultaneous user count of 25 licenses for this 'Enterprise Edition' server has been exceeded", and we shouldn't have this many processes running. Why aren't these processes ending?
    Thanks,
    RP
  2. vbkenya New Member

    One simple application with some run-away code spawn more connections and processes than physically supported by any server. Please find out the application(s), logon(s) and machine(s) that are generating this high traffic. You should then identify the code used to connect to the databases(s). As an old pirate once said "Here lies the dragons".



    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  3. Argyle New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by vbkenya</i><br />As an old pirate once said "Here lies the dragons".<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Haha. Great line. I'm going to use that next time I troubleshoot some developer's code [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  4. derrickleggett New Member

    Here's a couple queries that might help you out a tad. Just summarizes the connections a million ways to Wednesday.

    PRINT 'connections by database'
    PRINT ' '
    SELECT
    sd.name,
    COUNT(sp.spid) AS HostConnections,
    (SELECT COUNT(spid) FROM master.dbo.sysprocesses) AS TotalConnections
    FROM
    master.dbo.sysprocesses sp with(nolock)
    INNER JOIN master.dbo.sysdatabases sd with(nolock) ON sp.dbid = sd.dbid
    GROUP BY
    sd.name
    ORDER BY
    2 DESC

    PRINT 'connections by loginame'
    PRINT ' '
    SELECT
    loginame,
    --hostname,
    COUNT(*) AS HostConnection
    FROM
    master.dbo.sysprocesses with(nolock)
    GROUP BY
    loginame--,
    --hostname
    ORDER BY
    2 DESC

    PRINT 'connections by host/program'
    PRINT ' '
    SELECT
    program_name,
    hostname,
    COUNT(*) AS HostConnections,
    (SELECT COUNT(*) FROM master.dbo.sysprocesses) AS TotalConnections
    FROM
    master.dbo.sysprocesses with(nolock)
    GROUP BY
    program_name,
    hostname
    ORDER BY
    1,3

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page