cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Need to report on TOP SQL by Machine

Jump to solution

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

Labels (1)
0 Kudos
1 Solution
Level 11

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)

View solution in original post

0 Kudos
4 Replies
Level 11

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)

View solution in original post

0 Kudos

Yes, that is correct.  This is great!  Can this be turned into a custom report in DPA?

Thank you,

David Kranes

0 Kudos

Unfortunately DPA does not have custom report capabilities, so you will have to execute from SSMS or other query tool.

0 Kudos

Okay.  Thank you for your expertise.

David

0 Kudos