5 Most CPU Intensive Queries.

Syntax

Lists the top 5 CPU utilization by database


        WITH cteCPUStats AS
        (
          SELECT
          FDB.DatabaseID
          ,DatabaseName = DB_Name(FDB.DatabaseID)
          ,CPUTime_ms = SUM(QS.total_worker_time)
          FROM
          [master].sys.dm_exec_query_stats QS WITH (NOLOCK)
          CROSS APPLY
          (
            SELECT DatabaseID = CONVERT(int,value)
            FROM sys.dm_exec_plan_attributes(QS.plan_handle)
            WHERE attribute = N'dbid'
          ) FDB
          GROUP BY
          FDB.DatabaseID
        )
        SELECT TOP(5)
        RowNum = ROW_NUMBER() OVER(ORDER BY CPUTime_ms DESC)
        ,DatabaseName
        ,CPUTime_ms
        ,CPUPercent = CAST(CPUTime_ms * 1.0 / SUM(CPUTime_ms) OVER() * 100.0 AS DECIMAL(5, 2))
        FROM
        cteCPUStats
        WHERE
        DatabaseID > 4 -- not a system database
        AND DatabaseID <> 32767 -- not ResourceDB
        ORDER BY
        RowNum OPTION (RECOMPILE)
        ;