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
