7 Replies Latest reply on Mar 22, 2019 4:34 PM by jaminsql

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

    mhenry

      Why is it that I can look at what's going on now and see issues, but the next day nothing?

       

      For example, I can look at a specific server and see that there are currently blocked sessions occuring - sometimes way more than the five for a critical. When I look the next day there are none showing at all. Same thing on queries, currently I'm seeing warnings and criticals. Next day nothing.

       

      I've tried searching and DPA docs but no love.

       

      BONUS QUESTION - How are sesssions measured? The graph doesn't list any scale. I can see 20 sessions at a given time, but it doesn't even qualify as "1" for the daily.

       

      Thanks in advance

        • Re: Solarwinds DPA - What's the difference between daily and hourly metrics?
          darichar

          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.

          1 of 1 people found this helpful
          • Re: Solarwinds DPA - What's the difference between daily and hourly metrics?
            jaminsql

            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?

              • Re: Solarwinds DPA - What's the difference between daily and hourly metrics?
                mhenry

                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.

                  • Re: Solarwinds DPA - What's the difference between daily and hourly metrics?
                    jaminsql

                    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 Add information to the trend view blockers tab like the current view blockers tab.

                    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

                    1 of 1 people found this helpful