Need to report on TOP SQL by Machine

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)

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

Thank you,

David Kranes

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

Okay.  Thank you for your expertise.

David