Use DynamicPerf- Beginner Guide-Find Expensive Query by time period

Use DynamicsPerf

SELECT TOP 100 SERVER_NAME,

               DATABASE_NAME,

               QUERY_HASH,

               QUERY_PLAN_HASH,

               SUM(CAST(TIME_THIS_PERIOD / 1000.000 AS DECIMAL(20, 3)))                               AS TOTAL_TIME_MS,

               SUM(EXECUTIONS_THIS_PERIOD)                                                            AS TOTAL_EXECUTIONS,

               CASE SUM(EXECUTIONS_THIS_PERIOD)

                 WHEN 0 THEN 0

                 ELSE ( SUM(CAST(TIME_THIS_PERIOD / 1000.000 AS DECIMAL(20, 3))) / SUM(EXECUTIONS_THIS_PERIOD) )

               END                                                                                    AS AVG_TIME_MS,

               (SELECT TOP 1 QUERY_PLAN_PARSED

                FROM   QUERY_PLANS_PARSED_VW V2

                WHERE  V2.SERVER_NAME = CTE.SERVER_NAME

                       AND V2.DATABASE_NAME = CTE.DATABASE_NAME

                       AND V2.QUERY_HASH = CTE.QUERY_HASH

                       AND V2.QUERY_PLAN_HASH = CTE.QUERY_PLAN_HASH)                                  AS QUERY_PLAN_PARSED,

               (SELECT SQL_TEXT

                FROM   QUERY_TEXT V2

                WHERE  V2.SERVER_NAME = CTE.SERVER_NAME

                       AND V2.DATABASE_NAME = CTE.DATABASE_NAME

                       AND V2.QUERY_HASH = CTE.QUERY_HASH)                                            AS SQL_TEXT,

               (SELECT QUERY_PLAN

                FROM   QUERY_PLANS V2

                WHERE  V2.SERVER_NAME = CTE.SERVER_NAME

                       AND V2.DATABASE_NAME = CTE.DATABASE_NAME

                       AND V2.QUERY_PLAN_HASH = CTE.QUERY_PLAN_HASH)                                  AS QUERY_PLAN,

               SUM(CAST(WORKER_TIME_THIS_PERIOD / 1000.000 AS DECIMAL(20, 3)))                        AS WORK_TIME,

               SUM(CAST(( TIME_THIS_PERIOD - WORKER_TIME_THIS_PERIOD ) / 1000.000 AS DECIMAL(14, 3))) AS WAIT_TIME

FROM   QUERY_STATS_CTE_VW CTE

WHERE  1 = 1

--AND STATS_TIME BETWEEN '2018-10-29 07:15:00.000' AND '2018-10-29 07:26:00.000' --REH 1 minute past last collection

--AND STATS_TIME IN (SELECT MAX(STATS_TIME) FROM QUERY_STATS)  --Look at last data collection

--  Find all queries using a specific table/index,

--AND QUERY_PLAN_HASH IN

--             (SELECT DISTINCT  QUERY_PLAN_HASH FROM QUERY_PLANS_PARSED QPP WHERE CTE.QUERY_PLAN_HASH = QPP.QUERY_PLAN_HASH

--                             AND TABLE_NAME = 'INVENTDIM' AND INDEX_NAME = 'I_698DIMIDIDX')

--AND DATABASE_NAME = 'XXXXXXXXXXXXXX'

--AND QUERY_HASH = 0x020C200A1715926C

--AND SQL_TEXT like '%TABLE_NAME%'

GROUP  BY SERVER_NAME,

          DATABASE_NAME,

          QUERY_HASH,

          QUERY_PLAN_HASH

ORDER  BY TOTAL_TIME_MS DESC