CODEWITHSUNDEEP
asp.netsql-server-2008
Ye Myat Aung
Ye Myat Aung

Reputation: 1853

How to monitor active connection pool in SQL Server?

I'm suspecting that my web application has connection leaks (getting the timeout and max connection reached error). So I wanted to monitor how many database connections are active in the pool. I'm using the SQL Express so I don't have User Connections performance counter as suggested in some help guides.

I do found that I could also use Performance Monitor of Win 2008 server but I've no idea how to do so. Any guide on that would be appreciated.

Upvotes: 30

Views: 80529

Answers (3)

Georgi Lubomirov
Georgi Lubomirov

Reputation: 317

This will give number of connections per database, per user:

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame
;

Upvotes: 21

Emad Mokhtar
Emad Mokhtar

Reputation: 3297

You can execute built-in stored procedure

exec sp_who
exec sp_who2

Upvotes: 8

Oleg Dok
Oleg Dok

Reputation: 21766

Use simple

SELECT * FROM sys.dm_exec_connections

OR

Try and adopt this script in case if you need some other data

declare @now    datetime
set     @now    = getdate()
set nocount off
select  p.spid                      as  spid
    ,   rtrim(p.loginame)           as  SQLUser
    ,   rtrim(p.nt_username)        as  NTUser
    ,   rtrim(p.nt_domain)          as  NTDomain
    ,   rtrim(case
        when p.blocked <> 0 then 'BLOCKED'
        else p.status
        end)                        as status
    ,   case 
        when p.blocked is null or p.blocked = 0 then ''     
        else convert(varchar(10),p.blocked)
        end                         as  BlockedBySpid
    ,   rtrim(p.cmd)                as  CurrentCommand
    ,   case when p.dbid = 0 then '' else rtrim(db_name(p.dbid)) end    as  DBName
    ,   isnull(rtrim(p.program_name),'')        as  ProgramName
    ,   cast( cast(p.waittype as int) as nvarchar(10)) as  CurrentWaitType
    ,   p.waittime              as  CurrentWaitTime
    ,   p.lastwaittype              as  LastWaitType
    ,   rtrim(p.waitresource)       as  LastWaitResource
    ,   p.open_tran                 as  OpenTransactionCnt
    ,   p.cpu                       as  CPUTime
    ,   convert(bigint, p.physical_io) as   DiskIO
    ,   p.memusage                  as  MemoryUsage
    ,   p.hostprocess               as  HostProcess
    ,   rtrim(p.hostname)           as  HostName
    ,   p.login_time                as  LoginTime
    ,   p.last_batch                as  LastBatchTime
    ,   p.net_address               as  NetAddress
    ,   ltrim(rtrim(p.net_library)) as  NetLibrary
    ,   case 
        when    lower(p.status) not in ('sleeping', 'background', 'dormant', 'suspended') 
        or      p.open_tran > 0
        or      p.blocked   > 0
        or      upper(ltrim(rtrim(p.cmd))) like 'WAITFOR%'
        then    'Y'
        else    'N'
        end                         as  Active
    ,   case
        when    p.net_address <> '' --  Non system processes
        and     p.program_name not like 'SQLAgent - %'
        then 'N'
        else 'Y'
        end                         as  SystemProcess
    ,   case 
        when p.last_batch = '19000101'      then 'n/a'
        when datediff(day,      p.last_batch, @now) >   2   then convert(varchar(10),datediff(day,      p.last_batch, @now)) + ' days'
        when datediff(hour,     p.last_batch, @now) >=  4   then convert(varchar(10),datediff(hour,     p.last_batch, @now)) + ' hrs'
        when datediff(minute,   p.last_batch, @now) >=  10  then convert(varchar(10),datediff(minute,   p.last_batch, @now)) + ' min'
        else convert(varchar(10),datediff(second, p.last_batch, @now)) + ' sec'
        end                         as  TimeSinceLastBatch
    ,   p.kpid                      as  InternalKPID
    ,   case    
        when    (lower(p.status) in ('background', 'dormant') 
        and     p.open_tran <= 0
        and     p.blocked   <= 0
        and     upper(ltrim(rtrim(p.cmd))) not like 'WAITFOR%'
        ) or (
        lower(p.status) like '%sleeping%'
        )
        then    0
        else    p.kpid
        end                         as  kpid
    , (convert(nvarchar,p.spid) + '.' + case    
                                        when    (lower(p.status) in ('background', 'dormant') 
                                        and     p.open_tran <= 0
                                        and     p.blocked   <= 0
                                        and     upper(ltrim(rtrim(p.cmd))) not like 'WAITFOR%'
                                        ) or (
                                        lower(p.status) like '%sleeping%'
                                        )
                                        then    '0'
                                        else    convert(nvarchar,p.kpid)
                                        end) + '.' + convert(nvarchar,convert(float, p.login_time)) as SessionLifeTimeKey                
    ,   convert(float, p.login_time) as 'LoginTimeFloatDiff'
from    sys.sysprocesses            p   with (readpast)

Upvotes: 37

Related Questions