1 Reply Latest reply on Oct 14, 2016 11:22 AM by jaminsql

    Is there a way to monitor one or two stored procedures that are not visible in DPA?

    Rob Huston

      Is there a way to monitor one or two stored procedures that are not visible in DPA as high wait, longest running, most executed...?

       

      There are particular sp's that I want to monitor without having to use SQL Profiler.

        • Re: Is there a way to monitor one or two stored procedures that are not visible in DPA?
          jaminsql

          DPA ranks and shows the statements that are the highest wait. (biggest concern from a end user perspective). If the procedure you are wanting to monitor isn't in the list it may be that DPA captured it but, it isn't as high of a wait time and is therefore way down the list. The other thing to note here is DPA stores the statement level not the stored procedure on the whole so you would have a hash value in DPA for each statement we see in that stored procedure.

           

          If for example you are doing a select, Insert then a delete you would have a hash value on each even though they are all part of the same procedure.

          There is a feature request here to group them both so they can be tracked as one for stored procedures and so if the hash values change from a query edit we can report on it easier after grouping the new values. Group individual statements/batches in stored procedures 

           

          To your set issue what I have had the most success with is first finding the hash of the statement either by expanding the GUI to give more then the top 15 items and looking for it in the interface you can do this in options -> Administration tab -> System options  "NUMBER_OF_ITEMS_IN_TIMESERIES_CHARTS". If you expand this you will get more items but, charts take longer to render.

           

          Another way is using a query like one of these against the DPA repository  and searching for it by some part of the SQL text. Then use a query to see where the sql ranked against others by wait time with another after we find DPA's hash values.

           

          So not sure if your Repository is SQL server or Oracle.

          First step find both the hashes. use these.

          https://thwack.solarwinds.com/docs/DOC-172662 -Oracle repo

          https://thwack.solarwinds.com/docs/DOC-172608 -SQL server repo

           

          then use something like the report in this discussion to see where your Rank is for these statements against others we have captured. Read that discussion also as there are some things there you might find helpful.

           

          https://thwack.solarwinds.com/thread/68975

           

          SQL Server - ROW_NUMBER() only supported on SQL Server 2008+

           

          select *

          from (select SW.IZHO SQLHASH,

                       sum(SW.QP)/100 TIMESECS,

                       ROW_NUMBER() OVER(ORDER BY sum(SW.QP)/100 DESC) AS RANK

                from CONSW_X SW  with (nolock)

                where SW.D between DATEADD(dd, DATEDIFF(dd,0, GETDATE()), 0) and GETDATE()

                and SW.IZHO > 0

                group by SW.IZHO

               ) r

          where SQLHASH = ?

           

           

          Oracle

           

          select *

          from (select SW.IZHO SQLHASH,

                       sum(SW.QP)/100 TIMESECS,

                       ROW_NUMBER() OVER(ORDER BY sum(SW.QP)/100 DESC) AS RANK

                from CONSW_X SW

                where SW.D between TRUNC(SYSDATE) and SYSDATE

                and SW.IZHO > 0

                group by SW.IZHO

               ) r

          where SQLHASH = ?