https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-2017

DECLARE @MaxResultCount INT=100 --返回行数
DECLARE @SQLWhere VARCHAR(500)='' --查询条件
DECLARE @MinExecs INT=1 --最少执行次数 
DECLARE @MinExecsPerMin INT=1 --最少执行次数/分钟
DECLARE @MinLastRunDate VARCHAR(20)='' --CONVERT(VARCHAR(20),DATEADD(SECOND,-1*5*60,GETDATE()),120) --上次执行时间
DECLARE @Database INT=0 --数据库对应的Id
DECLARE @Search VARCHAR(200)='' --Text Search
DECLARE @SQLSearch VARCHAR(500)=''
DECLARE @DatabaseName VARCHAR(100)='' --数据库名称
DECLARE @SQLOrder VARCHAR(100)=' ORDER BY AvgCPU DESC' --按AvgCPU 降序
IF(@DatabaseName>'')
BEGIN
 SELECT @Database=database_id FROM sys.databases WHERE name=@DatabaseName
END
--cpu单位 微秒(μs)
IF(@MinExecs>0)
BEGIN
  SET @SQLWhere+= ' And execution_count >='+CAST( @MinExecs AS VARCHAR(10)) 
END
IF(@MinExecsPerMin>0)
BEGIN
  SET @SQLWhere+= ' And (Case When DATEDIFF(mi, creation_time, qs.last_execution_time) > 0 Then CAST((1.00 * execution_count / DATEDIFF(mi, creation_time, qs.last_execution_time)) AS money) Else Null End) >= '+CAST( @MinExecsPerMin AS VARCHAR(10)) 
END
IF(@MinLastRunDate>'1900-01-01')
BEGIN
  SET @SQLWhere+=' And qs.last_execution_time >= '''+@MinLastRunDate+''''
END
IF(@Database>0)
BEGIN
    SET @SQLWhere+=' And Cast(pa.value as Int) = '+CAST( @Database AS VARCHAR(10)) 
END
IF(@Search>'')
BEGIN
 SET @SQLSearch='Where SUBSTRING(st.text,
                 (StatementStartOffset / 2) + 1,
                 ((CASE StatementEndOffset
                   WHEN -1 THEN DATALENGTH(st.text)
                   ELSE StatementEndOffset
                   END - StatementStartOffset) / 2) + 1) Like ''%' + @Search + '%'''
END
DECLARE @SQL VARCHAR(MAX)=''

SET @SQL='SELECT AvgCPU, AvgDuration, AvgReads, AvgCPUPerMinute,
       TotalCPU, TotalDuration, TotalReads,
       PercentCPU, PercentDuration, PercentReads, PercentExecutions,
       ExecutionCount,
       ExecutionsPerMinute,
       PlanCreationTime, LastExecutionTime,
       SUBSTRING(st.text,
                 (StatementStartOffset / 2) + 1,
                 ((CASE StatementEndOffset
                   WHEN -1 THEN DATALENGTH(st.text)
                   ELSE StatementEndOffset
                   END - StatementStartOffset) / 2) + 1) AS QueryText,
        st.Text FullText,
        query_plan AS QueryPlan,
        PlanHandle,
        StatementStartOffset,
        StatementEndOffset,
        MinReturnedRows,
        MaxReturnedRows,
        AvgReturnedRows,
        TotalReturnedRows,
        LastReturnedRows,
        DB_NAME(DatabaseId) AS CompiledOnDatabase
FROM (SELECT TOP ('+CAST(@MaxResultCount AS VARCHAR(12))+') 
             total_worker_time / execution_count AS AvgCPU,
             total_elapsed_time / execution_count AS AvgDuration,
             total_logical_reads / execution_count AS AvgReads,
             Cast(total_worker_time / age_minutes As BigInt) AS AvgCPUPerMinute,
             execution_count / age_minutes AS ExecutionsPerMinute,
             Cast(total_worker_time / age_minutes_lifetime As BigInt) AS AvgCPUPerMinuteLifetime,
             execution_count / age_minutes_lifetime AS ExecutionsPerMinuteLifetime,
             total_worker_time AS TotalCPU,
             total_elapsed_time AS TotalDuration,
             total_logical_reads AS TotalReads,
             execution_count ExecutionCount,
             CAST(ROUND(100.00 * total_worker_time / t.TotalWorker, 2) AS MONEY) AS PercentCPU,
             CAST(ROUND(100.00 * total_elapsed_time / t.TotalElapsed, 2) AS MONEY) AS PercentDuration,
             CAST(ROUND(100.00 * total_logical_reads / t.TotalReads, 2) AS MONEY) AS PercentReads,
             CAST(ROUND(100.00 * execution_count / t.TotalExecs, 2) AS MONEY) AS PercentExecutions,
             qs.creation_time AS PlanCreationTime,
             qs.last_execution_time AS LastExecutionTime,
             qs.plan_handle AS PlanHandle,
             qs.statement_start_offset AS StatementStartOffset,
             qs.statement_end_offset AS StatementEndOffset,
             qs.min_rows AS MinReturnedRows,
             qs.max_rows AS MaxReturnedRows,
             CAST(qs.total_rows as MONEY) / execution_count AS AvgReturnedRows,
             qs.total_rows AS TotalReturnedRows,
             qs.last_rows AS LastReturnedRows,
             qs.sql_handle AS SqlHandle,
             Cast(pa.value as Int) DatabaseId
        FROM (SELECT *, 
                     CAST((CASE WHEN DATEDIFF(second, creation_time, GETDATE()) > 0 And execution_count > 1
                                THEN DATEDIFF(second, creation_time, GETDATE()) / 60.0
                                ELSE Null END) as MONEY) as age_minutes, 
                     CAST((CASE WHEN DATEDIFF(second, creation_time, last_execution_time) > 0 And execution_count > 1
                                THEN DATEDIFF(second, creation_time, last_execution_time) / 60.0
                                ELSE Null END) as MONEY) as age_minutes_lifetime
                FROM sys.dm_exec_query_stats) AS qs
             CROSS JOIN(SELECT SUM(execution_count) TotalExecs,
                               SUM(total_elapsed_time) TotalElapsed,
                               SUM(total_worker_time) TotalWorker,
                               SUM(Cast(total_logical_reads as DECIMAL(38,0))) TotalReads
                          FROM sys.dm_exec_query_stats) AS t
             CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
     WHERE pa.attribute = ''dbid'' '+@SQLWhere+'

       ) sq
    CROSS APPLY sys.dm_exec_sql_text(SqlHandle) AS st
    CROSS APPLY sys.dm_exec_query_plan(PlanHandle) AS qp  '+@SQLSearch +@SQLOrder

    --PRINT @SQL
    EXEC(@SQL)