4 Replies Latest reply on Nov 28, 2018 12:08 PM by dkranes

    Need to report on TOP SQL by Machine

    dkranes

      I do not see a standard report where we can specify a machine and return top SQL statements.  Does anyone have a report or raw SQL for that?  I am on DPA version 12.

      Thank you,

      David kranes

        • Re: Need to report on TOP SQL by Machine
          darichar

          David,

           

          I assume you mean within a monitored instance, you would like to specify one of the machine names listed in the Machines tab and get a list of Top SQLs. Here is a script that should get you started and you can run it from Management Studio while connected to the DPA repository database.

           

          Notes:

          • Each monitored instance in DPA has a set of tables specific to it that end with a number
          • CONSW_XX is a table that holds detailed second-by-second activity
          • CONM_XX is the table where the machines are kept
          • CONST_XX holds SQL text
          • Think of CONSW as a fact table and CONM and CONST as dimension tables.

           

          Before running this script, change the following as needed:

          • The instance name in the "WHERE NAME = 'AVANTIS'", put the instance name here as shown on the Home page in DPA
          • The date range in the "WHERE sw.D BETWEEN" clause
          • The machine name in the" AND m.NAME like" clause

           

          DECLARE @DBID varchar(5), @SQL varchar(1000);

           

          SELECT @DBID = ID FROM ignite.COND WHERE NAME = 'AVANTIS'; -- substitute the instance name here

           

          SET @SQL = 'SELECT m.NAME AS [Machine], st.ST AS [SQL Text], sum(sw.QP)/100 as [WaitSeconds]

          FROM ignite.CONSW_'+@DBID+' sw

          INNER JOIN ignite.CONM_'+@DBID+' m ON m.ID = sw.PWMY

          INNER JOIN ignite.CONST_'+@DBID+' st ON st.H = sw.IZHO

          WHERE sw.D BETWEEN ''11/01/2018'' AND ''11/28/2018''  -- change date values as needed

          AND m.NAME like ''DRICHARDS%''  -- change the machine name as needed

          GROUP BY m.NAME, st.ST

          ORDER BY [WaitSeconds] DESC';

          EXEC (@SQL)