Solarwinds DPA - What's the difference between daily and hourly metrics?

When viewing metrics in a shorter term, DPA shows the raw data and individual data points. In a longer term view, these metrics get averaged and the peaks get softened. I think there is a way to change this behavior so I will find out and reply here to what I find.

mhenry

I think I know the answer to this but, to be sure of my answer can you clarify a few things?

1) What is the monitored platform you are seeing this on in DPA? (SQL Server, Oracle, MySQL, Sybase, DB2)

2) I think you mean you see blocking in the current "super tab" in the real-time sessions blocking sub-tab compared to then not see the same blocking in trend view later.  Is that correct?

3) the chart you mention is that the one in the resources super tab under sessions?

1. SQL Server

2. Not sure what a super tab is. The regular graph in Resources doesn't show any blocked sessions for the day/week/month. But when I'm looking at current, I'll see several blocked sessions. (sorry, don't remember where I saw them).

3. yes, the chart is the one in resources.

Sorry for the super tab wording if that was confusing. I guess that is some jargon we use here in support for the tabs in DPA.

So I think there are a few issues with this one. One is what darichar    has said as far as averages etc. The other is that the current tab in SQL server doesn't use exactly the same query as the Trend view.

1) It displays more columns see

2) the query in trend view is populated by the DPA quick poll and that has more filters in the "where clause" then the query on current does.

The current tab for SQL server is "[select s.*, case s.dbid when 0 then null else db_name(s.dbid) end AS name from master..sysprocesses s ];" last I knew.

The quick poll is as follows.

SELECT s.loginame, db_name(s.dbid) name, s.hostname,

       s.program_name, s.sql_handle,

       s.stmt_start, s.stmt_end, s.spid,

       CONVERT(smallint, s.waittype) waittype, s.lastwaittype,

       s.ecid, s.waittime, CONVERT(varchar(64), s.context_info) context_info,

       RTRIM(r.wait_resource) waitresource, s.blocked ,

       r.plan_handle, r.statement_start_offset, r.statement_end_offset, r.start_time,

       r.query_plan_hash

FROM master..sysprocesses AS s WITH(NOLOCK)

LEFT OUTER JOIN sys.dm_exec_requests r WITH(NOLOCK) ON r.session_id = s.spid

WHERE (s.dbid<>0

       AND s.cmd<>'AWAITING COMMAND'

       AND s.cmd NOT LIKE '%BACKUP%'

       AND s.cmd NOT LIKE '%RESTORE%'

       AND s.cmd NOT LIKE 'FG MONITOR%'

       AND s.hostprocess > ''

       AND s.spid>50 AND s.spid<>@@SPID)

       AND lastwaittype NOT IN ('BROKER_EVENTHANDLER', 'CLR_MANUAL_EVENT', 'SLEEP_BPOOL_FLUSH', 'SLEEP_TASK', 'WAITFOR')

So you can see the filtering in the where isn't the same. As for the chart on the resources tab. It also seems to have a slightly different query. You can see any query from resources if you look in a file that is talked about in this KB

DPA Database Metrics Architecture - SolarWinds Worldwide, LLC. Help and Support

DPA home/iwc/tomcat/webapps/iwc/WEB-INF/classes/resources/metrics/default-metrics-queries.xml

Here is a section of that query in the XML.

    SELECT COUNT(1) FROM master..sysprocesses AS s

        WHERE s.status<>'background'

        AND s.cmd<>'AWAITING COMMAND'

        AND s.blocked>0

        ]]></sql>

    <frequency>60</frequency>

    <queryTimeout>20</queryTimeout>

    <name>Blocked Sessions</name>

So I think the issue is not all of these have the same WHERE Clause even though they all look in master..sysprocesses

Thanks, I would love to see if there is a way to change it.

So on the chart in Resources for Sessions, if it shows one blocked session that's the sessions that have been blocked on average?

I was hoping to find out that the measurement was in 100s or something.

It looks like it is just the count of blocked sessions when we sample every 1 minute. I think what was meant about the average was that if you select the longer time frame it does the average.