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.
- 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';
Yes, that is correct. This is great! Can this be turned into a custom report in DPA?
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.