Show all returns from a query without manually sort, also count returns by value

[Former Member]

I have this code:

select LastReqTime,

BlockedOn,

UncommitOps

from sa_conn_info()

WHERE LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedOn > 0 AND UncommitOps = 0

An example of the return is:

Is there a way to instruct SQL to return and show all values, without limiting the display? In the above

query, it shows the first 115 rows and I must manually sort or scroll down to see the total number.

Also is there a way to get a count of each unique BlockedOn value, for use in a different query?

[former_member194571 · Accepted solution]

select BlockedOn, count (*) n

from sa_conn_info ()

where ...

group by BlockedOn

[Former Member · Accepted solution]

Volker, I'm sorry, I don't think I phrased my question properly.

I would like to take the original query and display the original

information, but also have an additional field on each line which

includes the total count for each unique BlockedOn value.

This is what I've come up with, and of course I have an error in

syntax:

---------------

select LastReqTime, BlockedOn, UncommitOps, COUNT(DISTINCT BlockedOn) AS "TOTAL COUNT"

from sa_conn_info()

WHERE LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedOn > 0 AND UncommitOps = 0

GROUP BY LastReqTime BlockedOn UncommitOps

ORDER BY BlockedOn

-----------------

NOTE: Please be tolerant of whatever mistakes the code contains, I'm

critically low on caffeine this morning.

[former_member194571 · Accepted solution]

You might try the Window function count

> count (distinct BlockedOn) over (rows between unbounded preceding and unbounded following)

but at the risk of entering disappointment mode now, I recommend you familiarize yourself with SQL fundamentals first before starting with advanced stuff. You may or may not finally end up with a code snippet that does the job (sometimes / always?) without you knowing why.

I suspect that you're trying to follow an approach that doesn't precisely match the idea of SQL. The Total Count is a scalar aggregation of the whole result set. Why mingle it into each row of the result set when it's always the same value anyway.

The closest thing to basic SQL is to UNION a summary row with the individual rows:

select LastReqTime, BlockedOn, UncommitOps

from sa_conn_info()

WHERE nullif (LastReqTime, '') < DATEADD(mi,-60,GETDATE())

AND BlockedOn > 0 AND UncommitOps = 0

UNION ALL

select NULL, count (distinct BlockedOn), sum (UncommitOps)

from sa_conn_info()

WHERE nullif (LastReqTime, '') < DATEADD(mi,-60,GETDATE())

AND BlockedOn > 0 AND UncommitOps = 0

HTH

Volker

[Former Member · Accepted solution]

Once again, because of the terrible lackacaffeine disease, I don't think I was clear.

I want to run a query, get totals for BlockedOn that are older than an hour with

UncommitOps as 0, and list them like the sample below with the BlockedOn having

the highest total count at the top of the report.

As for getting familiar with basic queries, I've purchased several SQL books

so I can work on increasing my knowledge, but there's a steep learning

curve and I'm trying to get these queries figured out in the next few days.

Thanks again.

[former_member194571 · Accepted solution]

This looks like the result for

>>

select max (convert (datetime, LastReqTime)) LastReqTime, BlockedOn, sum (UncommitOps) UncommitOps, count (*) "Total Count"

from sa_conn_info() o

where BlockedON > 0 and nullif (o.LastReqTime, '') < dateadd (mi,-60, getdate ())

group by BlockedOn order by count (*) desc;

<<

This means:

You look at all rows from the sa_conn_info result representing a blocked thread (BlockedOn > 0) where the last request was more than 60 minutes ago. Out of these, you count the number of occurrences for each process listed in BlockedOn, the latest LastReqTime of the processes blocked by this one and the total count of uncommitted operations.

But if you are interested in the details of the blocking rather than the blocked processes (which I consider more relevant), the query would be

>>

select LastReqTime, "Number" as BlockedOn, UncommitOps,

      (select count (*) from sa_conn_info () i where i.BlockedOn = o."Number") as "Total Count"

from sa_conn_info () o

where "Number" in (select BlockedOn from sa_conn_info ())

  and nullif (o.LastReqTime, '') < dateadd (mi,-60, getdate ())

order by "Total Count" desc;

<<

This at least would be the way to go if the evaluation base was a regular table. For a system procedure result, I strongly recommend to materialize the intermediate result in a temp table once and do the extra stuff on the materialization afterwards. This is best achieved by wrapping these steps in a procedure:

>>

create procedure vs_blocking_stats ()

result (LastReqTime datetime, BlockedOn integer, UncommitOps integer, VictimsCount integer)

begin

declare local temporary table mat_conn_info

( ProcID integer not null primary key,

  LastReqTime datetime null,

  UncommitOps integer null,

  BlockedOn integer not null) not transactional;

declare local temporary table lt_victims_count

( BlockedOn integer not null primary key,

  VCOunt integer not null) not transactional;

insert into mat_conn_info (ProcID, LastReqTime, UncommitOps, BlockedOn)

select "Number", convert (datetime, nullif (LastReqTime, '')), UncommitOps, BlockedOn from sa_conn_info ();

insert into lt_victims_count (BlockedOn, VCount)

select BlockedOn, count (*) from mat_conn_info

where BlockedOn > 0

group by BlockedOn;

select ci.LastReqTime, vc.BlockedOn, ci.UncommitOps, vc.VCount

  from mat_conn_info ci join lt_victims_count vc on vc.BlockedOn = ci.ProcID

where ci.LastReqTime < dateadd (mi, -60, getdate ())

order by vc.VCount desc;

end;

<<

I don't have access to a server with relevant processes currently, so I've only tested it for correct syntax.

HTH

Volker

[Former Member · Accepted solution]

Wow, as usual, you've exceeded my expectations. Not only that, you've given me something to study and increase my knowledge. Thanks again!

[former_member188493]

What client software are you using?

The dbisql utility is sometimes annoying when it forces you to scroll to see all the rows, but in my experience the number is far higher than 115.

One method is to use OUTPUT TO 'c:\\temp\\select.txt' and then browse the file with notepad.

FWIW, Foxhound lets you perform all sorts of adhoc queries on connection data after the fact.

[Former Member]

Breck,

I'm using isql. Thanks for the tip on OUTPUT TO, I

can use that to generate/email reports, which is my

next project once I get the count totals finished.