Number of blocked processes from CONSW table

Question

Hi,

I am looking to create a query that returns the number of blocked processes from the dpa repository, without going through system tables in the monitored instance. The repository is SQL Server.

I understand that the CONSW_X table holdes detailed information on this reported in seconds, but the column names are very difficult to make sense of without any referance to what they mean.

I am currently trying this query to count the number of blocks for the last 60 minutes with the following query:

select count(*) from consw_1 sw

INNER JOIN conev_1 ev ON sw.keeq = ev.id

WHERE sw.d >= DATEADD(minute, -60, CURRENT_TIMESTAMP)

and ev.id = 23

and blee < 0

The problem is that the query does not match the information I have in my DPA GUI. Could anybody care to reveal what the columns in the table mean (especially the column blee) or correct my query so that it returns number of blocked processes (not interested in how many seconds)?

Best regards

mandevil · Answer

So I think we may be talking apples and oranges on this one. Here's some explanation that I hope helps: * For the "Blocked Sessions" under resources, we actually query the monitored instance with the below SQL, then plot that per the interval using a diff in java. You could use the same query and specify whatever timeframe you * SELECT COUNT(1) FROM master..sysprocesses AS s WHERE s.status<>'background' AND s.cmd<>'AWAITING COMMAND' AND s.blocked>0 * In the CONSW table, we look at sessions that were either a blockee or blocker "observed" during our polling. So that may not line up entirely with the query above which is a separate tally. We use the CONSW table to really point to wait impacts of blocking vs a count of how many blocked sessions were observed.

alekrist · Answer

This is exactly right. I am looking for the number of blocked sessions as per the resource tab reports on "DB Blocked Sessions".

How do I make sense of the VDSI column? I simply want the count of blocked sessions for a given timestamp.

mandevil · Answer

Point of clarification - are you talking about the blocking at the session level and the blocked sessions we show as a resource? Scrubbed screenshots may help.

Also, if you aren't keen on posting in this forum, a ticket may be in order so you can do a screen share...