query help

Sam 1,476 Reputation points
2022-04-02T09:46:24.38+00:00

Hi All,

Need tsql help in getting running total.
I have this below query which gives me connection count per database.
I am need another column at the end showing the running total of all connections on the server. So, kind of getting the overview on how much load is there on the server.

SELECT
DB_NAME(dbid) as DatabaseName,
COUNT() as NumberOfConnections
FROM
sys.sysprocesses
GROUP BY
DB_NAME(dbid)
order by COUNT(
) desc
go

Thank you.

Regards,
Sam

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106K Reputation points MVP
    2022-04-02T10:02:44.687+00:00

    Here you go.

    I took the liberty to modernise the query to use sys.dm_exec_sessions rather than the old sysprocesses. sysprocesses will give you inflated values when there is parallel processing going on. I also added a filter to remove system processes.

    SELECT DB_NAME(database_id) as DatabaseName,
           COUNT(*) as NumberOfConnections,
           SUM(COUNT(*)) OVER (ORDER BY COUNT(*) DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM  sys.dm_exec_sessions
    WHERE is_user_process = 1
    GROUP BY DB_NAME(database_id)
    ORDER BY NumberOfConnections DESC
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful