tsql query help

Question

tsql query help

vsamantha35

SSChampion

Points: 11551
More actions
April 2, 2022 at 9:41 am
Go to Answer
#4009692

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
Jeff Moden

SSC Guru

Points: 1004363
More actions
April 3, 2022 at 4:21 am
Answer
#4009945
This does the trick for me.
```
   WITH cteByDB AS
(--==== Preaggregate that data including a grand total using ROLLUP and marking that total row with GROUPING_ID.
     -- The total row will have a GROUPING_ID = 1 and the detail rows will have a 0.
 SELECT  DatabaseName        = DB_NAME(dbid)
        ,NumberOfConnections = COUNT(*)
        ,IsTotal             = GROUPING_ID(DB_NAME(dbid))
   FROM sys.sysprocesses
  GROUP BY ROLLUP (DB_NAME(dbid))
)--===== The sum for all of the NumberOfConnection available to each row includes the total,
      -- which doubles the number of connections and so we need to divide that by 2 to make it right.
 SELECT  DatabaseName        = IIF(IsTotal = 0,DatabaseName,'*** Grand Total ***') 
        ,NumberOfConnections
        ,PercentOfConnection = CONVERT(DECIMAL(4,1),NumberOfConnections*100.0
                                                   /(SUM(NumberOfConnections) OVER ()/2.0))
   FROM cteByDB
  ORDER BY IsTotal, NumberOfConnections DESC
;
```
That's my "first blush" rendition. Someone else might be able to get the reads down even further but the "pre-aggregation" did it for me. It's a common performance trick that I learned a couple of decades ago from Peter "Peso" Larsson. Including the total line and the grouping function trick (it was slightly different back in the '90's) and the Divide-by-2 trick is a old spreadsheet trick I used to use in the '80's.
A "Running Total" doesn't enter the picture here, What is needed is a "Grand Total" (SUM of all rows) that's available to all rows to do the Percent calculation This is why it's important to show what you actually want for a result. I hope my SWAG as to what you were looking for is actually what you want,
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

The use of the SUM(OVER) Windowing function should do it for you.

I also have to ask... why to you need a "running total" for this? Are you actually looking for a "percentage of the total" for each database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vsamantha35 SSChampion Points: 11551 More actions · Answer 2

Jeff Moden wrote:

The use of the SUM(OVER) Windowing function should do it for you.
I also have to ask... why to you need a "running total" for this? Are you actually looking for a "percentage of the total" for each database?

Yes Jeff.

vsamantha35 SSChampion Points: 11551 More actions · Answer 3

Jeff Moden wrote:

This does the trick for me.
   WITH cteByDB AS
(--==== Preaggregate that data including a grand total using ROLLUP and marking that total row with GROUPING_ID.
     -- The total row will have a GROUPING_ID = 1 and the detail rows will have a 0.
 SELECT  DatabaseName        = DB_NAME(dbid)
        ,NumberOfConnections = COUNT(*)
        ,IsTotal             = GROUPING_ID(DB_NAME(dbid))
   FROM sys.sysprocesses
  GROUP BY ROLLUP (DB_NAME(dbid))
)--===== The sum for all of the NumberOfConnection available to each row includes the total,
      -- which doubles the number of connections and so we need to divide that by 2 to make it right.
 SELECT  DatabaseName        = IIF(IsTotal = 0,DatabaseName,'*** Grand Total ***') 
        ,NumberOfConnections
        ,PercentOfConnection = CONVERT(DECIMAL(4,1),NumberOfConnections*100.0
                                                   /(SUM(NumberOfConnections) OVER ()/2.0))
   FROM cteByDB
  ORDER BY IsTotal, NumberOfConnections DESC
;
That's my "first blush" rendition. Someone else might be able to get the reads down even further but the "pre-aggregation" did it for me. It's a common performance trick that I learned a couple of decades ago from Peter "Peso" Larsson. Including the total line and the grouping function trick (it was slightly different back in the '90's) and the Divide-by-2 trick is a old spreadsheet trick I used to use in the '80's.
A "Running Total" doesn't enter the picture here, What is needed is a "Grand Total" (SUM of all rows) that's available to all rows to do the Percent calculation This is why it's important to show what you actually want for a result. I hope my SWAG as to what you were looking for is actually what you want,

It works like a charm. Thank you very very much.

-- I tried below to get the running totals

SELECT

DB_NAME(dbid) as DatabaseName,

COUNT(*) as NumberOfConnections,

SUM(COUNT(*)) OVER (ORDER BY COUNT(*) DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

FROM sys.sysprocesses

GROUP BY

DB_NAME(dbid)

order by COUNT(*) desc

go

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Ar you saying that you still need real live "running totals" in the output? If so, my question, again, would be WHY? What problem would those help you solve?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vsamantha35 SSChampion Points: 11551 More actions · Answer 5

In QA env, multiple databases load tests goes in parallel and sometime they complain that server is slow intermittantly .

So, by using this query I ll be ablt to know what all databases are active at that point of time and how many connections are made to each database on that SQL Instance. I am not sure, if that correct way to check the load on the server. Once I get the information, I reach out to DEV & QA teams that these many databases are being used currently and may see slow performance while executing queries. I also use sp_whoisactive to know active processes.

Regards,

Sam