query help

Question

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

Answer 1

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