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

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)